September 22, 2014

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 

bitsdescriptionvalues
1sign1= non-negative, 0= negative
1unusedreserved for future extensions
10hour0-838
6minute0-59
6second0-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:

About Aleksandr Kuzminsky

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

Comments

  1. Wlad says:

    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.

Speak Your Mind

*