Is there any way to let MySQL server 'push' DB updates to a client program?

Question

A typical scenario: imaging a user is feeding data to MySQL, while a client is connected to MySQL remotely. What I want is that, whenever the user update some fields in the DB, this client can immediately be notified.

Think this as the 'push-mail' function from Blackberry.

What I do NOT want, is that the client has to 'ping' MySQL server once a while to see if there is any update. I believe this will be to expensive, computation wise.

How can I implement such an idea? does anyone here has a clue? is there any MySQL-based CALLBACK mechanism?

or is there any other database (better be open-source) that support this kind of mechanism?

thanks a lot in advance

Answer 1

My recommendation is to either use a timer service that randomly polls the db for any changes or to use a message queue and every time the size of the queue is greater than 0 send out a message to whomever should find out about it.

Answer 2

For an actively connected client interested in cache invalidation techniques:

• SQL Server support Query Notifications
• Oracle support Continuous Query Notifications.
• MySQL supports replication streams, but they're not the same as update notification: they cannot be set up and tear down dynamically for a client and they do not work well for monitoring individual rows a particular application is interested in.

For a disconnected client interested in data sync, all vendors support some sort of replication.

Answer 3

No Callback mechanism, but if you made a service that others used to make the update, then you could craft that service to push the notification that you want.

Answer 4

In PostgreSQL (which is open source) has triggers. Triggers can be written in any language and can send a notification to your application. Unfortunately I do not know of an existing API for such notifications, but with PostgreSQL you can build your own. Documentation for PostgreSQL triggers

Answer 5

I just answered a similar question earlier today about PostgreSQL. In a nutshell, it was:

Write a TRIGGERed stored procedure in Java, and have that procedure reach out via a socket connection to some external program listening on a socket.

After getting burned on the matter of PostgreSQL and Java stored procedures, I won't dare to speculate on whether MySQL "does" Java stored procedures. If not, perhaps there's some other program you can run from within the stored proc that will accomplish something similar. So far, this is a do-it-yourself assistance answer.

Answer 6

In Firebird and Interbase, we have event than can be listened by client application. But for MySQL event like this don't exist.