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 !!!
Similar to my problem if you have the performance_schema
enabled, solved after the upgrade of MySql server version.
Starting from 5.5.16 to 5.5.22 is affected by a deadlock.
No queries can be run when it happens:
Bug 13898343 - THREAD LOOPS ENDLESSLY IN LF_PINBOX_PUT_PINS WHILE HOLDING LOCK_THREAD_COUNT
An infinite thread loop could develop within Performance Schema,
causing the server to become unresponsive. (Bug #13898343)
See changelog
First of all try disabling performance_schema
Hope his helps
Best Answer
To quote the manual:
In other words, the new copy will run.
This could lead to more and more running, so you should worry about it.