Are there any MySQL Triggers or Events that my program can wait for?

Question

Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new emails, would MySQL triggers be a good way to do this?

Lock tables waits for the tables to become available before it returns. Is there a way I can wait for another person to lock it instead of waiting for all others to unlock it.

I could just poll every few seconds to see if the table is updated, but I think it would be better if I could just use some sort of wait until the table is either locked-then-unlocked or wait for the table to be updated before seeing if there are new emails.

Alternatively there might be a way to execute a command when the table is updated?

Do I have to implement my own event waiting service for this, or can it be done in MySQL?

Answer 1

There is a simple way to find out if a table has been updated recently. Just check the table information_schema.tables. Here is the layout:

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

EXAMPLE : You have a table called mydata.mytable and you want to see if that table was updated in the last 10 minutes. Here is the simple query to tell you:

SELECT COUNT(1) UpdatedCount FROM information_schema.tables
WHERE table_schema = 'mydata'
AND table_name = 'mytable'
AND update_time > ( NOW() - INTERVAL 10 MINUTE );

It will give you either 0 (Was not updated) or 1 (Was Updated).

The update_time of the table should be updated before anyone locks it, especially if the table you are polling is MyISAM. InnoDB is another story. Given InnoDB's transactional nature, you could poll the timestamps in the table rather that information_schema.tables just in case information_schema.tables does not update as quickly as desired. Just make sure the table's timestamp column is indexed.

There is an alternative outside of MySQL but does not work for InnoDB tables configured with innodb_file_per_table disabled.

EXAMPLE : Data is installed in default datadir (var/lib/mysql) and you want to poll the table mydata.mytable.

For mydata.mytable being MyISAM

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.MYD | awk '{print $8}'`

For mydata.mytable being InnoDB

TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.ibd | awk '{print $8}'`

Of course, you would shell script the time comparison to see if is is less than 10 minutes or whatever interval you want.

With regard to events, you will be OK if you use CREATE EVENT but make sure you do not update InnoDB tables in an event. I went through heartburn helping someone troubleshoot this in a chat room on a past question.

Give it a Try !!!

Answer 2

Using update triggers seems to be the best option because polling is waste of resources in this case.(why check your post box again and again when the postman will ring a bell when he delivers the mail?)