table meta data: "created" and "updated"

fbachofner's profile image fbachofner posted 8 years ago in General Permalink
In the tables view of a database, Heidi shows table metadata columns "created" and "updated"

From where do these come? [Is it the same for InnoDB and MyISAM ?]

Can these values be edited?

I have had to recover a DB using InnoDB tables and this metadata is incorrect whether I recreate a DB from backup and/or "recover" the DB using the various InnoDB recovery options.
fbachofner's profile image fbachofner posted 8 years ago Permalink
I just spent a couple minutes looking into this and found that in the MySQL database "information_schema" (which accompanies every MySQL installation) there is a table called "tables"

The rows in the table give various metadata about all tables for the databases of the MySQL instance on the server, including the table metadata columns "created" and "updated" I asked about in the first post.

As one might expect, the "tables" table is query-able, and seems to be updateable, although in the instance with which I am playing I need to change user permissions first . . .
fbachofner's profile image fbachofner posted 8 years ago Permalink

I forgot to post back here what I figured out:

  • for MyISAM tables, the date seems to come from the .frm files' created/updated file dates
  • for InnoDB tables, it also seems to come from the individual tables .frm files, which is interesting given the data is actually stored in ibdata and ib_logfile0/1

Anyway, main takeaway: the "information_schema" tables are not writeable!

From the MySQL 5.7 manual:

"INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them."

ansgar's profile image ansgar posted 8 years ago Permalink

HeidiSQL makes use of the created + updated columns from the result of

SHOW TABLE STATUS FROM `yourdatabase`;

That's basically the same as in INFORMATION_SCHEMA.TABLES I guess. Unfortunately, InnoDB does not report the last update time.

Please login to leave a reply, or register at first.