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:

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 |
+----+--------------+----------------------------+-----------------------+---------------------+

First, we should gerenrate a table definition file:

./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

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

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:

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

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

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