Updating MySQL's information_schema

Hi,

I recently migrated MySQL from one disk to another disk. It looks like as a result the informationschema.TABLES.CREATETIME has been updated on multiple InnoDB records to be the date of the migration rather than the correct original dates. I can see the correct date on the file system for the representative .frm files.

-rw-rw----  1 mysql mysql 8.4K Mar 17 17:03 home_201503b.frm

I tried to update informationschema.TABLES.CREATETIME for one of these tables and received an error message.

mysql> UPDATE information_schema.TABLES SET CREATE_TIME='2015-03-17 17:03:00' WHERE TABLE_SCHEMA='foo' AND TABLE_NAME='home_201503b';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

root has full access.

Is there any way to make this change?

Thanks, Josh

1 Reply

I believe I have solved this one. the information_schema database is read-only and reads the modification time of the .frm files representing the InnoDB tables. I need to adjust the modification time of these .frm files to match their creation time.

Explanation of modification time read:

https://bugs.mysql.com/bug.php?id=69990

Suggestion for modifying file modification time:

http://askubuntu.com/questions/62492/ho … -of-a-file">http://askubuntu.com/questions/62492/how-can-i-change-the-date-modified-created-of-a-file

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct