Search code examples
mysqlsqlloggingmariadbinformation-schema

In-built database "information_schema" not logging the last update time of a table in user created database

The tables table of the in-built database information_schema does not log the activities of the tables in user created databases as the fields table_schema and table_name lacks the values of the user created databases and table names respectively, disallowing me to run the SQL query 

SELECT update_time 
FROM information_schema.tables 
WHERE table_schema='my_db' and table_name='my_table';
-- For table named 'my_table' in database 'my_db'

for retrieving last update time (or changed time) of a table in database (i.e. the last time when data in a specific table is updated, deleted or inserted) for deciding whether a backup is needed or not.

Can anyone please help me solve this problem of logging activities in database and explain me the reason behind it?

Thanks in advance.

Solution

  • Apparently your tables are InnoDB, and you are using MariaDB version below 10.2.

    What you have encountered is an old MySQL bug (or rather an absence of feature, as it was envisioned by the InnoDB team). It has been fixed in the latest stable InnoDB, which is included into MariaDB 10.2.

    For MyISAM tables it should work in previous versions too.