Question posted in 
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
2
Answers
MySQL doesn’t have a way to deliver an event to external software from within a trigger. That’s what you need to have your database push events to your app.
(Actually, it’s possible to install a user-defined function that sends an industry-standard stomp messsage to a message queue system like rabbitmq . But you will have to control the entire server, AND your database administrator, to get that installed.)
The alternative: run a query every so often to retrieve changed information, and push it to your app. That’s a nasty alternative: polling is a pain in the xxx neck.
Can you get your server app to detect changes as it UPDATEs the database? It’ll take some programming and testing, but it’s a good solution to your problem.
You could use redis instead of / in addition to MySql. redis sends events to web servers whenever values change, which is close to perfect for what you want to do. https://redis.io/topics/notifications
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.