26

Is there a better way to watch for new entries in a table besides selecting from it every n ticks of time or something like that?

I have a table that an external program updates very often, and clients can watch for this new data as it arrive, how can I make that without having to set a fixed period of repeatable select statements?

Edwin Jarvis
  • 5,980
  • 6
  • 36
  • 41
  • Possible duplicate: [MySQL listen notify equivalent](https://stackoverflow.com/questions/23031723/mysql-listen-notify-equivalent) – rustyx May 01 '20 at 12:07

6 Answers6

10

In MySQL there's no best way than to poll (you create a specific table to simplify the polling though), in other databases you can have triggers that have impact outside the database. In MySQL triggers can only do stuff inside the database itself (for instance, populating the helper table).

Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
10

Another similar approach would be to add

add column Last_Modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP

to each table and preface your select queries to compare the last request date/time with the max(Last_Modified).

Databases are typically pull sources and not push so you'll still need to programmatically probe for changes no matter what.

Rob Allen
  • 17,381
  • 5
  • 52
  • 70
8

Here's what I do: I've got some triggers set up for the table (insert, delete, update) and those triggers increment a counter in another table. My DB access code keeps a local counter and compares it to the returned value, ultimately sending a bool back to the caller, answering the question IsDataCurrent().

Our programs that use this DB access code either poll or check it on-request and then make the appropriate calls to keep themselves up to date.

I'm sure there are other ways to solve this. It worked for me pretty well, though.

itsmatt
  • 31,265
  • 10
  • 100
  • 164
  • So it still requires polling, only slightly better to poll: select * from lastupdatetable; versus select count(*) from datatable; – davr Oct 03 '08 at 15:00
  • 4
    I disagree. The query select count(*) might produce erroneous results. If I perform an add/ a delete/ and an add the count will be the same, right? So did the data change? You wouldn't know in that case. The last update table won't produce this situation. – itsmatt Oct 03 '08 at 15:07
  • 1
    But it still requires a select every n ticks, but that would be more low cost than selecting the actual data table, that's a way I'm considering too. – Edwin Jarvis Oct 03 '08 at 17:00
6

It is possible to trigger behaviour outside the database, if you have control of the host.

A) This question's answer suggests writing to a file using 'select into outfile' with some system process watch that file for changes (e.g. inotify-based approach such as node-inotify or even a Grunt-watch might suffice)

B) For the brave: The answer to this question points out that if you can install C/C++ add-ons to your database server, you could use a User Defined Function (UDF) to call sys_exec() and thus trigger external processes, or presumably write the actual process directly in the UDF.

http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-B-5-1-11

(I'm researching this at the moment, for an AWS RDS hosted application so unfortunately neither of these options are right for me.)

Community
  • 1
  • 1
scipilot
  • 6,681
  • 1
  • 46
  • 65
1

This is just a small improvement to your method. Write a trigger on the table(s) you are watching to update a Last_Changed table.

CheeZe5
  • 975
  • 1
  • 8
  • 24
1

For those who are doing this in go. You can enable binlog and read binlog for new insert/update/delete on any table. There is a go package here that does the trick. And here is a sample implementation.

spark
  • 41
  • 3