Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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 !!!

share|improve this answer

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?)

share|improve this answer
    
Any information on how to use triggers to signal my app would be appreciated. So far, I have only seen how to run SQL statements when a trigger is triggered. What I would like is a way to either wait for the trigger or to run a command when the trigger occurs. –  George Bailey Aug 2 '11 at 1:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.