(suggestion) Checking Table Changes by using UPDATE_TIME in table status

[EvanGeminika]

I wonder whether it's possible to check table changes by using UPDATE_TIME in table status. For example, below query result will give UPDATE_TIME

show table status where name='players';

The module can run above query every minute to check whether any changes in player table in the last 1 minute. This way, this module can still be reactive without binary log.

[numtel]

That is quite interesting. I didn't know about SHOW TABLE STATUS.

@Geminika Is there a way to select rows that have been updating since a specified time?

[EvanGeminika]

@numtel, currently it works fine with MyISAM engine, but there's still some problem with InnoDB. That's why by default the update_time is null for InnoDB. They said it will work for InnoDB in MySQL 5.7.2, based on this link https://bugs.mysql.com/bug.php?id=14374

SHOW TABLE STATUS, is basically gives the same result as below query:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

We can use below create table command, if we want to test it with MyISAM engine. CREATE TABLE players ( id int PRIMARY KEY NOT NULL, name varchar(45), score int DEFAULT 0 NOT NULL ) engine MyISAM;

We can use below sample query if we want to get the update_time for players table.

select table_name, update_time from information_schema.tables where table_schema = DATABASE() and table_name="players";

The update_time will change when any insert, update, or delete happen for that table.

To check whether any changes in the table since the last 5 minutes we can use below query:

select table_name, update_time from information_schema.tables where table_schema = DATABASE() and table_name="players" and update_time > (now() - interval 5 minute);