Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

get last update of database return null (mysql)

Tags:

database

mysql

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' 
like image 445
Zhihar Avatar asked Oct 05 '16 10:10

Zhihar


1 Answers

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.

like image 64
Shadow Avatar answered Sep 19 '22 02:09

Shadow