EmergencyEMERGENCY? Get 24/7 Help Now!

Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME

 | July 16, 2013 |  Posted In: Insight for DBAs, MySQL


MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.

Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.

The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.

As of revision 79 the recovery toolkit supports the new format.

Let’s recover a sample table to see how it works. The table is

First, we should gerenrate a table definition file:

Then recompile the tool and split a table space that contains records of the table t:

The next step is to get records from the InnoDB index:

To load this dump use the LOAD DATA INFILE command that’s generated by the contraints_parser, I then redirected it to /dev/null in the example above.

As you can see it’s pretty straightforward. There are two notes:

  • Because of format differences the tool can detect whether the field is in the new format or old
  • For the TIME field (w/o fractional part) in the new format you need to give a hint to contraints_parser. This is -6 option. Let me illustrate this

Before 5.6.4 TIME was packed in three bytes: DD×24×3600 + HH×3600 + MM×60 + SS. As of 5.6.4 it still uses three bytes, but format is different:

TIME new format 

bits description values
1 sign 1= non-negative, 0= negative
1 unused reserved for future extensions
10 hour 0-838
6 minute 0-59
6 second 0-59

If a field is created without a fractional part it’s impossible to determite the format from a field value. Let’s take a table:

If there is no fractional part constraints_parser assumes old format. So if we try to recover the the records from the table above the result will be wrong:

Thus, we need to give a hint, then TIME values are correct:

Aleksandr Kuzminsky

Aleksandr is a consultant and data recovery specialist. He is a former Percona employee.


  • It is not that obvious that this new feature requires a format change. In fact, microseconds were implemented before MySQL5.6 , in MariaDB 5.3, and this did not require backward-incompatible changes in data representation.

  • I’m migrating to 5.6 from 5.0 and I have a lot of tables with TIME/TIMESTAMP/DATETIME, but none of them with (number) on their definition.
    Do I have to worry? (I’m doing a non-binary upgrade, that is, dumping and load the dump in 5.6)

Leave a Reply