I'm trying to make a live react control panel, so when you push a button on the web control panel the data (true or false) goes to the SQL database (phpmyadmin) and the when the data changes te SQL database should trigger a script on the raspberry pi that will turn the light on.
I know how to write data to the SQL database and how to control a lamp with a raspberry pi but I dont know how to trigger or execute something when data in the SQL database gets updated.
It needs to live, like react in max 20 ms or something. Can anyone help me with this? The SQL Database runs on Ubuntu and is phpmyadmin based.
Greets, Jules
Schematic: DataUpdateGraphical
It's not a good idea to use a trigger in MySQL to activate any external process. The reason is that the trigger fires when the INSERT/UPDATE/DELETE executes, not when the transaction commits. So if the external process receives the event, it may immediately go query the database to get other details about that data change, and find it cannot see the uncommitted data.
Instead, I recommend whatever app is writing to the database should be responsible for creating the notification. Only then can the app wait until after the transaction is confirmed to be committed.
So your PHP code that handles the button press would insert/update some data the database, and check that the SQL completed without errors (always check the result of executing an SQL statement) and the transaction committed.
Then the same PHP code subsequently calls your script, or posts an even to a message queue that the script is waiting for, or something like that.
Just don't use the MySQL as a poor man's message queue! It's not the right tool for that.
The same advice applies to any other action you want to do external to the database. Like sending an email, writing a file, making an http API call, etc.
Don't do it in an SQL trigger, because external actions don't obey transaction isolation. The trigger or one of the cascading data updates could get rolled back, but the effect of an external action cannot be rolled back.
