2

There are a lot of tables, perhaps more than fifty which are read from and written to by code which is a bit too much to understand quickly. Many of the tables unfortunately contain no timestamp columns. I want to know which of them are being altered when the code runs once a day.

So I tried this solution:

SELECT update_time FROM information_schema.tables WHERE table_name='tablename'

...which executed but returned Null. Can some simple command be run on the MySQL server so that it actually starts saving that and persisting it so I can check it in a few days?

cardamom
  • 6,873
  • 11
  • 48
  • 102

1 Answers1

3

The answer is: well, it depends. Unfortunately, MySQL documentation on update_time field in the information_schema.tables is not too detailed, however, you can find the relevant information in the documentation on show table status statement, which also retrieves data from the information_schema.tables view:

When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its system tablespace and the data file timestamp does not apply. Even with file-per-table mode with each InnoDB table in a separate .ibd file, change buffering can delay the write to the data file, so the file modification time is different from the time of the last insert, update, or delete. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates so the value is inaccurate.

In summary: if you want to use it with innodb, then you have to use file-per-table mode to have a chance to display this data (even then it will be inaccurate). MyISAM should be fine (unless you are using Windows). There is no mention of other storage engines.

Enabling the file-per-table mode on a large database will be quite a challenge, you may have to back up tables, drop them, change the setting, and then import them again. So, before rushing to do this, you must really carefully evaluate if this feature is worth the change. Furthermore, my understanding is that even if file-per-table mode is enabled, InnoDB only supports this property from version 5.7.2 as per the last developer comment on this feature request:

[15 Sep 2014 5:21] Marko Mäkelä Posted by developer:

WL#6658 implemented update_time for InnoDB tables in MySQL 5.7.2. Note that it is not persistent across server restarts. WL#6917 was filed for making update_time persistent.

Community
  • 1
  • 1
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thanks. I just use databases, am not an admin of them, but wish there was a one-liner one could suggest the admin type in somewhere which makes it start saving this. If they were thinking of fixing that in 2014 and still haven't done it, is not looking good. Does MySQL always use InnoDB or can it have something else in it? – cardamom Jan 31 '18 at 16:53
  • 1
    I think you should look into alternative solutions to achieve what you want because changing storage engines can again have huge impact on your application. I would rather try checking the number of affected rows from your code that updates the tables to determine if any of them were changed. – Shadow Jan 31 '18 at 20:25
  • That's really sucky situation we discovered after switching from MyISAM to InnoDB. I'm wondering if I could create a trigger on information_schema.TABLES to replicate updates and inserts into my own self stored table to keeps track of update times persistently. Seems little cringy, but might just work. – DonRico Jun 18 '19 at 07:38