7

I need to get last time of changes (update, modification etc) to the database I do the following query, but it returns null for all tables. What can be wrong?

SELECT 
    update_time 
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'myschema'
Zhihar
  • 1,306
  • 1
  • 22
  • 45
  • 1
    Possible duplicate of [How can I determine when an InnoDB table was last changed?](http://stackoverflow.com/questions/2785429/how-can-i-determine-when-an-innodb-table-was-last-changed) – LuFFy Mar 22 '17 at 14:14

1 Answers1

11

As MySQL documentation on information_schema.tables says (emphasis added):

Beginning with MySQL 5.7.2, UPDATE_TIME displays a timestamp value for the last UPDATE, INSERT, or DELETE performed on InnoDB tables that are not partitioned. Previously, UPDATE_TIME displayed a NULL value for InnoDB tables. For MVCC, the timestamp value reflects the COMMIT time, which is considered the last update time. Timestamps are not persisted when the server is restarted or when the table is evicted from the InnoDB data dictionary cache.

The UPDATE_TIME column also shows this information for partitioned InnoDB tables in MySQL 5.7.8 and later. Previously this column was always NULL for such tables. (Bug #17299181, Bug #69990)

Probably you are using innodb tables and your MySQL version is earlier than described in the documentation.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • is there any fix to the bug yet? – Irvan Hilmi Feb 27 '23 at 08:39
  • 1
    @IrvanHilmi check the bug status on the mysql site. But if you are using an earlier version of mysql than 5.7.8, then you mysql is seriously out of date and should be upgraded. – Shadow Feb 27 '23 at 09:12