1

I have tried a php script like:

$res=mysql_query("SELECT TABLE_NAME 
                  FROM  information_schema.'TABLES' 
                  WHERE  UPDATE_TIME > NOW() - INTERVAL 2 DAY 
                  AND  ABLE_SCHEMA = 'mvgis_lewiscounty'");

which is not working.

Can anyone please guide me how should I proceed with this?

Fernando Carvalhosa
  • 1,098
  • 1
  • 15
  • 23
Kunal Shrivastava
  • 612
  • 8
  • 18

1 Answers1

3

There's no solution using INFORMATION_SCHEMA that works for both InnoDB and MyISAM.

The UPDATE_TIME is not updated for InnoDB tables. Update time for an InnoDB table is kind of subjective. Is it the time someone last did an UPDATE? Is it the time that update was COMMITed? Is it the time the change was flushed to the tablespace?

You'd be better off using a shell command like:

$ find /var/lib/mysql -name '*.ibd' -mtime -1

Another solution would be for all tables to have a trigger that updates a summary table with one row per table name, to record the latest INSERT/UPDATE/DELETE.

See also How can I tell when a MySQL table was last updated?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I want to keep a check on update. How about MyISAM only? Is there any way where I can get this information without using shell command if I use MyISAM instead? – Kunal Shrivastava May 28 '14 at 23:15
  • Please don't use MyISAM. It's slow, susceptible to corruption easily, and does not support ACID behavior. It's likely to be deprecated in a few years. – Bill Karwin May 28 '14 at 23:23