Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
6年弱前投稿 修正あり

MySQL 5.6における時刻型のリカバリ

MySQL Performance Blogの翻訳。 MySQL 5.6から、時刻型にマイクロ秒精度が使えるようになった。Percona Data Recovery Toolでこれらのを含むテーブルをリカバリする際の注意点とは。

原文
Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME (English)
原文ライセンス
CC BY-NC-SA
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
原著者への翻訳報告
未報告


MySQL 5.6では、いくつかの時刻型にマイクロ秒単位の精度が導入された。5.6.4では、TIME、TIMESTAMP、DATETIME型が、小数部を持つことができるようになった。秒単位以下のフィールドを作るには、 TIME(3) や DATETIME(6) のように、かっこ内に精度を指定する。

これは見て分かる通り、フォーマットの変更を必要とする。3つの型は全て小数部を後に持つことになる。この部分は、1または2あるいは3バイトの長さで、小数点以下6桁まで持ちうる。

整数部も同様に変更がある。すなわち、以前のバージョンでは8バイトだったものが5バイトしか使わないことになった。

Percona Data Recovery Tool for InnoDBRevision 79 では、新しいフォーマットもサポートしている。

どのように動作するか、例を見てみよう。テーブルは以下の通り。

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

まず、テーブル定義ファイルを作成する必要がある。

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

それから、ツールをリコンパイルし、テーブル t のレコードを含んだテーブルスペースを分割する。

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

その次は、InnoDBのインデックスからレコードを取りだす。

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

このダンプデータをロードするには、contraints_parserが生成した LOAD DATA INFILE コマンドを使おう。それから、上のように /dev/null にリダイレクトしている。

以上のように、割と簡単にできてしまう。2つのことに注意しよう。

  • 表記の違いにより、フィールドが新旧どちらのフォーマットなのかをツールが判断する。
  • 小数部のない新フォーマットのTIME型の場合、 contraints_parser にヒントを与える必要がある。これが -6 オプションだ。

5.6.4より前のバージョンでは、TIME型は DDx24x3600 + HHx3600 + MMx60 + SS を3バイトで表現していた。5.6.4では、3バイトではあるがフォーマットが異なる。

ビット 説明
1 フラグ 1=正、0=負
1 未使用 将来のための予約
10 0-838
6 0-59
6 0-59

フィールドが小数部を含まないで作成された場合、フィールドの値からフォーマットは判断できない。以下のテーブルで見てみよう。

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

小数部分がない場合、constraints_parserは古いフォーマットだと推測する。そのため、上のテーブルからレコードをリカバリすると値がおかしくなる。

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

そのため、ヒントを与える必要がある。そうすればTIME型の値が正しく入る。

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

次の記事
MySQLによくないクエリの種類とは?
前の記事
5歳の娘が銀河の女王になることに興味を示しているのですが、銀河征服のためにどんなプログラミング言語や技術を教えたらよいでしょうか?

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント