tags:

views:

31

answers:

2

CRETAE_TIME column of "TABLES" table from INFORMATION_SCHEMA shows the same CREATE_TIME for all my innodb tables. It means all these tables were created between 2010-03-26 06:52:00 and 2010-03-26 06:53:00 while actually they were created a few months ago.

Does the CREATE_TABLE field change automatically for Innodb tables?

+2  A: 

The create_time and update_time fields of information_schema correspond to the creation/modification timestamps of the underlying storage for the table.

With MyISAM, each table has its own file, so the create/modify timestamp of that file is returned.

For InnoDB however, storage for all tables is inside a single file, ibdata, so there is only one create/modify timestamp. This one timestamp is returned for all InnoDB tables.

rjh
How did it change today? I checked yesterday and it did not show yesterday's date as creation time.
shantanuo
you can create a single file per table with the innodb engine and it's highly recommended you do so.http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.htmlinnodb_file_per_table
f00
I don't think this is accurate. I think CREATE_TIME is based on the FRM file for InnoDB, and UPDATE_TIME is always null for InnoDB. So innodb_file_per_Table is not relevant.
Ike Walker
A: 

For InnoDB, the CREATE_TIME value in INFORMATION_SCHEMA.TABLES is based on the modified time of the table's FRM file. So this will most likely represent the last time you ran ALTER TABLE or OPTIMIZE TABLE.

Ike Walker