1

I have a PHP/MySQL web site, running on IIS. There are a handful of stand-alone compiled apps (exe) that also add records to the database. I want to set it up so that any time an exe adds a record to a certain table in the DB, it triggers a PHP script to run.

Unfortunately, the compiled apps cannot be modified. So I cannot simply have the exe call the PHP script. Somehow either MySQL needs to call the script on update, or PHP has to notice any time a record is added.

[EDIT TO ADD: I've seen solutions that suggest using MySQL triggers and MySQL add-ons to execute PHP from within MySQL, but there are both security and performance issues with such solutions that I would rather not deal with. Also the issue that such triggers happen mid-transaction and are synchronous with the triggered PHP script, meaning the PHP script can't actually see the data that was altered because it doesn't exist yet (transaction hasn't finalized.)]

I'm sure I could make a scheduled task to run a PHP script to check the DB table for new records every couple minutes; but I was wondering if there is any way to do this more directly and cleanly. Security of course is a plus.

(NOTE: For reasons I won't get in to, I'm still running MySQL 5.6. Hoping to get it updated soon. PHP is on 8.1)

Stephen R
  • 3,512
  • 1
  • 28
  • 45
  • https://dba.stackexchange.com/questions/107032/executing-command-prompt-command-in-mysql-trigger might help? – ADyson Dec 14 '22 at 16:10
  • It doesn't seem so. "You can't run the system command inside a stored program (and that includes a trigger)". The solution given there gets MySQL to write to a text file, which for me just shifts the issue to getting PHP to detect changes to a text file. – Stephen R Dec 14 '22 at 16:18
  • Well the point was that PHP can be executed from a command-line script. So you could get mysql to run a command-line PHP script via the system call. But yeah I guess you'd need something you can run from a trigger. Sorry, was a bit too quick off the mark there. – ADyson Dec 14 '22 at 16:21
  • There's also this link, but they suggest (without specifics) that this method has security issues(?) https://stackoverflow.com/questions/3651951/mysql-triggers-after-insert-trigger-udf-sys-exec-issue – Stephen R Dec 14 '22 at 16:24
  • Does this answer your question? [Invoking a PHP script from a MySQL trigger](https://stackoverflow.com/questions/1467369/invoking-a-php-script-from-a-mysql-trigger) – Grzegorz Adam Kowalski Dec 14 '22 at 17:35

1 Answers1

3

Some people suggest using a MySQL trigger to invoke your PHP script. This is not supported in standard MySQL instances, but you can find an implementation of sys_exec() as a UDF that you can compile into your MySQL server as a plugin. Beware though, if this causes your MySQL Server to crash, it will interrupt all other user sessions. I don't recommend it.

But the biggest problem with using a trigger-based solution is that triggers fire when you insert a row, not when that transaction is committed. So you can get unintended problems if the trigger executes a PHP script, then the script tries to view the inserted row, but it can't see data that isn't committed. Even if it could see that data, if the transaction is subsequently rolled back, then the PHP script may have acted on data that no one will ever see again.

Besides, triggers execute synchronously. If you call out to a PHP script with sys_exec(), the trigger waits for that PHP script to finish. Which means the INSERT that spawned the trigger waits. If the PHP script takes some time, this could cause a big throughput problem.

So triggers are the wrong way to solve this.

A better way to solve it is with a Change Data Capture (CDC) technology. An open source example is Debezium.

The idea of a CDC is that it reads the MySQL binary log, which by definition contains only changes after they have been committed. It doesn't poll, it is more like a queue reader.

A CDC reader is also asynchronous. It does not block your INSERT from finishing. It reads the binary log later (which might be only a fraction of a second later, as soon as your transaction commits and writes to the log).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I suspected there were issues with the trigger approach, but thank you for so clearly explaining what the issues *are*. Looking in to Debezium now. This may be exactly what I was looking for. – Stephen R Dec 14 '22 at 18:45
  • Can a trigger fire when a *transaction* is committed involving a certain table? – Stephen R Dec 14 '22 at 21:16
  • No. MySQL triggers fire only at the time the DML (INSERT/UPDATE/DELETE) statement executes. There are no triggers on commit. – Bill Karwin Dec 14 '22 at 22:24