MySQL trigger + notify a long-polling Apache/PHP connection

Question

I know there are Comet server technologies that do this but I want to write something simple and home-grown.

When a record is inserted into a MySQL table, I want it to somehow communicate this data to a series of long-polled Apache connections using PHP (or whatever). So multiple people are "listening" through their browser and the second the MySQL INSERT happens, it is sent to their browser and executed.

The easy way is to have the PHP script poll the MySQL database, but this isn't really pushing from the server and introduces some unacceptable order of unnecessary database queries. I want to get that data from MySQL to the long-polling connection essentially without the listeners querying at all.

Any ideas on how to implement this?

Remora

Answer 1

I have been trying all kinds of ideas for a solution to this as well and the only way to take out the polling of sql queries is to poll a file instead. If the fill equals 0 then continue looping. If file equals 1 have loop run sql queries and send to users. It does add another level of complexity but I would think it means less work by mysql but same for apache or what ever looping daemon. You could also send the command to a daemon "comet" style but it is going to fork and loop on each request as well from what I have seen on how sockets work so hopefully someone will find a solution to this.

Hoch

Answer 2

This is something I have been looking for as well for many years. I have not found any functionality where the SQL server pushes out a message on INSERTS, DELETE and UPDATES.

TRIGGERS can run SQL on these events, but that is of no use here.

I guess you have to construct your own system. You can easily broadcast a UDP from PHP (Example in first comment), the problem is that PHP is running on the server side and the clients are static.

My guess would be that you could do a Java Applet running on the client, listening for the UDP message and then trigger an update of the page.

This was only some thoughts in the moment of writing...

Aurea

Perhaps the trigger could write to something like memcached and the PHP could poll that? – Counterbalance

PHP is static and not "running" in the background. There are some tricks off the book of how to make it appear as a "thread", but then comes the next problem, update the clients page. I would say that the polling has to be done by AJAX. It could be enough to poll for the last modified date (written by triggers). If it is newer, reload the page. – Aurea

Answer 3

MySQL probably isn't the right tool for this problem. Regilero suggested switching your DB, but an easier solution might be to use something like redis which has a pub/sub feature.

http://redis.io/topics/pubsub

Unanimous