4

I'm using mysql 5.1.41-3ubuntu12.10 and would like to know when my table was last ALTERed (or CREATEd, if it was never ALTERed).

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA();
gives the CREATE and last UPDATE time, but not AFAICT the last ALTER time.

msh210
  • 256
  • 6
  • 23

1 Answers1

10

The answer depends somewhat on the storage engine. The most reliable indicator of when the table was last altered is to look at the modified time on the .frm file in the data directory. That file should be updated every time you alter the table, even for changes like updating a column default that don't require a table rebuild.

information_schema.tables.create_time is a bit of a misnomer, since that value actually changes most of the time when you alter a table. However, this is one area where the storage engine is relevant. If you do an alter without a rebuild (like changing a column default value) in InnoDB then information_schema.tables.create_time is updated, but if you do the same in MyISAM information_schema.tables.create_time is not updated. In both cases the .frm file should be updated, so I'd recommend you check the file timestamp for the most accurate data if you have access to it.

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • +1 for bringing up this important difference. I use InnoDB, so I guess `information_schema.tables.create_time` updates whenever the table is altered. Other than looking in the filesystem for the Create time of the `.ibd` or `.opt` file, I'm not able to find the true Create_Time of the table. That seems like that should be considered a bug, no? – Phil Tune Apr 26 '16 at 14:03
  • Digging through my database tables, I found `mysql.innodb_table_stats.last_update` which seems to display the CREATE time rather than the last UPDATE done. Quite silly, especially since `information_schema.tables.create_time` seems to show the last UPDATE instead of CREATE time. Hmm. – Phil Tune Apr 26 '16 at 15:35
  • 1
    Sorry to add clutter to this 4 year old post... it would seem that ALTER-ing the table metadata will update `information_schema.tables.create_time` and not `mysql.innodb_table_stats.last_update`, whereas ALTER-ing a table column constraint will update `mysql.innodb_table_stats.last_update` and not `information_schema.tables.create_time`. Side note: doing an INSERT will not update `mysql.innodb_table_stats.n_rows` as would be expected, but it will update when `last_update` updates. Ok, I'm finished cluttering this post. – Phil Tune Apr 26 '16 at 16:44