1

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

Pippeloo
  • 15
  • 5
  • 1
    You cannot do this with vanilla mysql, you need a user defined function written in C++ that can talk to an external application. However, it is a lot simpler to do this from your application, rather than from mysql – Shadow Oct 31 '20 at 17:43

2 Answers2

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
-1

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 messsage to a message queue system like . 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

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I think that I will try the autopoll, but I hope that my system won't slow down to hard. And I'll stick with mysql because I have a lot of scripts based on mysql. But thanks for your help! :) – Pippeloo Oct 31 '20 at 18:34
  • 1
    Mysql.does have a way to call external scripts from a trigger: through a compiled udf. You can find some examples on the internet. You probably don't want to do this, nevertheless it is is possible. – Shadow Oct 31 '20 at 19:58