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

CREATE TABLE `t` ( `id` int(11) AUTO_INCREMENT, `t1` time(3), `t2` timestamp(6), `t3` datetime(1), `t4` datetime, PRIMARY KEY (`id`) ) ENGINE=InnoDB 1 2 3 4 5 6 7 8 CREATE TABLE ` t ` ( ` id ` int ( 11 ) AUTO_INCREMENT , ` t1 ` time ( 3 ) , ` t2 ` timestamp ( 6 ) , ` t3 ` datetime ( 1 ) , ` t4 ` datetime , PRIMARY KEY ( ` id ` ) ) ENGINE = InnoDB

mysql> select * from t; +----+--------------+----------------------------+-----------------------+---------------------+ | id | t1 | t2 | t3 | t4 | +----+--------------+----------------------------+-----------------------+---------------------+ | 1 | 05:05:10.000 | 2013-07-04 05:05:10.000000 | 2013-07-04 05:05:10.0 | 2013-07-04 05:05:10 | | 2 | 05:14:24.414 | 2013-07-04 05:14:24.125000 | 2013-07-04 05:14:24.4 | 2013-07-04 05:14:25 | | 3 | 05:14:32.566 | 2013-07-04 05:14:32.207031 | 2013-07-04 05:14:32.3 | 2013-07-04 05:14:32 | | 4 | 05:14:34.344 | 2013-07-04 05:14:34.507813 | 2013-07-04 05:14:34.5 | 2013-07-04 05:14:35 | | 5 | 05:14:45.348 | 2013-07-04 05:14:45.832031 | 2013-07-04 05:14:45.1 | 2013-07-04 05:14:45 | +----+--------------+----------------------------+-----------------------+---------------------+ 1 2 3 4 5 6 7 8 9 10 mysql > select * from t ; + -- -- + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + | id | t1 | t2 | t3 | t4 | + -- -- + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + | 1 | 05 : 05 : 10.000 | 2013 - 07 - 04 05 : 05 : 10.000000 | 2013 - 07 - 04 05 : 05 : 10.0 | 2013 - 07 - 04 05 : 05 : 10 | | 2 | 05 : 14 : 24.414 | 2013 - 07 - 04 05 : 14 : 24.125000 | 2013 - 07 - 04 05 : 14 : 24.4 | 2013 - 07 - 04 05 : 14 : 25 | | 3 | 05 : 14 : 32.566 | 2013 - 07 - 04 05 : 14 : 32.207031 | 2013 - 07 - 04 05 : 14 : 32.3 | 2013 - 07 - 04 05 : 14 : 32 | | 4 | 05 : 14 : 34.344 | 2013 - 07 - 04 05 : 14 : 34.507813 | 2013 - 07 - 04 05 : 14 : 34.5 | 2013 - 07 - 04 05 : 14 : 35 | | 5 | 05 : 14 : 45.348 | 2013 - 07 - 04 05 : 14 : 45.832031 | 2013 - 07 - 04 05 : 14 : 45.1 | 2013 - 07 - 04 05 : 14 : 45 | + -- -- + -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +

First, we should gerenrate a table definition file:

./create_defs.pl --db test --table t > include/table_defs.h 1 . / create_defs .pl -- db test -- table t > include / table_defs .h

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

./page_parser -f /var/lib/mysql/test/t.ibd 1 . / page_parser - f / var / lib / mysql / test / t .ibd

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

$ ./constraints_parser -5f pages-1372929630/FIL_PAGE_INDEX/0-30/00000000-00000003.page 2> /dev/null -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5) 0000000007DB A6000001A20110 t 1 "05:05:10.0" "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0" 0000000007DE A8000001530110 t 2 "05:14:24.4140" "2013-07-04 05:14:24.125000" "2013-07-04 05:14:24.40" "2013-07-04 05:14:25.0" 0000000007EC B2000001A40110 t 3 "05:14:32.5660" "2013-07-04 05:14:32.207031" "2013-07-04 05:14:32.30" "2013-07-04 05:14:32.0" 0000000007ED B3000001A50110 t 4 "05:14:34.3440" "2013-07-04 05:14:34.507813" "2013-07-04 05:14:34.50" "2013-07-04 05:14:35.0" 0000000007FB BD000001670110 t 5 "05:14:45.3480" "2013-07-04 05:14:45.832031" "2013-07-04 05:14:45.10" "2013-07-04 05:14:45.0" -- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES 1 2 3 4 5 6 7 8 $ . / constraints_parser - 5f pages - 1372929630 / FIL_PAGE_INDEX / 0 - 30 / 00000000 - 00000003.page 2 > / dev / null -- Page id : 3 , Format : COMPACT , Records list : Valid , Expected records : ( 5 5 ) 0000000007DB A6000001A20110 t 1 "05:05:10.0" "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0" "2013-07-04 05:05:10.0" 0000000007DE A8000001530110 t 2 "05:14:24.4140" "2013-07-04 05:14:24.125000" "2013-07-04 05:14:24.40" "2013-07-04 05:14:25.0" 0000000007EC B2000001A40110 t 3 "05:14:32.5660" "2013-07-04 05:14:32.207031" "2013-07-04 05:14:32.30" "2013-07-04 05:14:32.0" 0000000007ED B3000001A50110 t 4 "05:14:34.3440" "2013-07-04 05:14:34.507813" "2013-07-04 05:14:34.50" "2013-07-04 05:14:35.0" 0000000007FB BD000001670110 t 5 "05:14:45.3480" "2013-07-04 05:14:45.832031" "2013-07-04 05:14:45.10" "2013-07-04 05:14:45.0" -- Page id : 3 , Found records : 5 , Lost records : NO , Leaf page : YES

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:

CREATE TABLE `t` ( `id` int(11) AUTO_INCREMENT, `t1` time , `t2` time(3) , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 2 3 4 5 6 CREATE TABLE ` t ` ( ` id ` int ( 11 ) AUTO_INCREMENT , ` t1 ` time , ` t2 ` time ( 3 ) , PRIMARY KEY ( ` id ` ) ) ENGINE = InnoDB DEFAULT CHARSET = latin1

mysql> select * from t; +----+----------+--------------+ | id | t1 | t2 | +----+----------+--------------+ | 1 | 11:01:17 | 11:01:17.000 | | 2 | 11:01:17 | 11:01:17.125 | | 3 | 11:01:17 | 11:01:17.125 | +----+----------+--------------+ 1 2 3 4 5 6 7 8 mysql > select * from t ; + -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- + | id | t1 | t2 | + -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- + | 1 | 11 : 01 : 17 | 11 : 01 : 17.000 | | 2 | 11 : 01 : 17 | 11 : 01 : 17.125 | | 3 | 11 : 01 : 17 | 11 : 01 : 17.125 | + -- -- + -- -- -- -- -- + -- -- -- -- -- -- -- +

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:

$ ./constraints_parser -5f pages-1373023772/FIL_PAGE_INDEX/0-31/00000000-00000003.page -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3) 000000000807 C5000001AC0110 t8 1 "12:32:17" "11:01:17.0" 000000000807 C5000001AC011C t8 2 "12:32:17" "11:01:17.1250" 000000000807 C5000001AC0128 t8 3 "12:32:17" "11:01:17.1250" -- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES 1 2 3 4 5 6 $ . / constraints_parser - 5f pages - 1373023772 / FIL_PAGE_INDEX / 0 - 31 / 00000000 - 00000003.page -- Page id : 3 , Format : COMPACT , Records list : Valid , Expected records : ( 3 3 ) 000000000807 C5000001AC0110 t8 1 "12:32:17" "11:01:17.0" 000000000807 C5000001AC011C t8 2 "12:32:17" "11:01:17.1250" 000000000807 C5000001AC0128 t8 3 "12:32:17" "11:01:17.1250" -- Page id : 3 , Found records : 3 , Lost records : NO , Leaf page : YES

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

./constraints_parser -5f pages-1373023772/FIL_PAGE_INDEX/0-31/00000000-00000003.page -6 -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3) 000000000807 C5000001AC0110 t8 1 "11:01:17.0" "11:01:17.0" 000000000807 C5000001AC011C t8 2 "11:01:17.0" "11:01:17.1250" 000000000807 C5000001AC0128 t8 3 "11:01:17.0" "11:01:17.1250" -- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES 1 2 3 4 5 6 . / constraints_parser - 5f pages - 1373023772 / FIL_PAGE_INDEX / 0 - 31 / 00000000 - 00000003.page - 6 -- Page id : 3 , Format : COMPACT , Records list : Valid , Expected records : ( 3 3 ) 000000000807 C5000001AC0110 t8 1 "11:01:17.0" "11:01:17.0" 000000000807 C5000001AC011C t8 2 "11:01:17.0" "11:01:17.1250" 000000000807 C5000001AC0128 t8 3 "11:01:17.0" "11:01:17.1250" -- Page id : 3 , Found records : 3 , Lost records : NO , Leaf page : YES