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

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 

1sign1= non-negative, 0= negative
1unusedreserved for future extensions

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:

Share this post

Comments (2)

  • Wlad

    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.

    July 16, 2013 at 12:44 pm
  • sebastian gomez morales

    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)

    January 15, 2015 at 9:41 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.