お題

CHECKSUM TABLE は ROW_FORMAT が異なっても同じ値を出すときがある。
- どういう場合に、ROW_FORMAT が異なっていても、チェックサムが一致するのか？

CHECKSUM TABLE（おさらい）

CHECKSUM TABLE はテーブルの中身のチェックサムを取得するコマンドです。比較対象のデータベースサーバで、静止点を設けられないとデータの比較ができないわけですが、 pt-table-checksum と違って、コマンド１つでデータを比較できるので手軽ですね。

データは一致しているのにCHECKSUMの結果が違うパターン

MySQLのバージョンによる違い

こちらはマニュアルにも書いてあります。5.6で TIME, DATETIME, TIMESTAMP 型の実装が変わりました。 5.5 と 5.6以降で時刻を含むテーブルのCHECKSUMの値は一致しなくなりました。

The checksum value depends on the table row format. If the row format changes, the checksum also changes.

For example, the storage format for temporal types such as TIME, DATETIME, and TIMESTAMP changed in MySQL 5.6 prior to MySQL 5.6.5, so if a 5.5 table is upgraded to MySQL 5.6, the checksum value may change.

サーバにインストールされているMySQLのバージョンでなく、データファイルが作られた時のバージョンに依存する点に注意です。 5.5をアップグレードし5.5の時に作成されたデータファイルを引き継いでいる場合は、CHECKSUMの値は、5.5のままです。

ROW_FORMATの違い

The checksum value depends on the table row format. If the row format changes, the checksum also changes.

と記載のあるように、ROW_FORMATが違う場合は、データが同じでも異なるチェックサムになる可能性があります。

実際に試します

COMPACTとDYNAMICで比較してみます。 FLOAT, DOUBLE型の場合、ROW_FORMATの違いによりチェックサムが異なりました。

mysql> CREATE TABLE t_float_compact (syousu FLOAT) ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t_float_dynamic (syousu FLOAT) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_float_compact VALUES(1.11111);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_float_dynamic VALUES(1.11111);
Query OK, 1 row affected (0.00 sec)

mysql> CHECKSUM TABLE t_float_compact EXTENDED;
+-------------------+-----------+
| Table             | Checksum  |
+-------------------+-----------+
| t.t_float_compact | 108074716 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t_float_dynamic EXTENDED;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| t.t_float_dynamic | 1104243724 |
+-------------------+------------+
1 row in set (0.00 sec)

しかし、VARCHAR は問題ないようです。

mysql> CHECKSUM TABLE t_varchar_compact EXTENDED;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| t.t_varchar_compact | 4048785154 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t_varchar_dynamic EXTENDED;
+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| t.t_varchar_dynamic | 4048785154 |
+---------------------+------------+
1 row in set (0.00 sec)

一通りの型を試すと以下のようになりました。

型	ROW_FORMAT=COMPACT/DYNAMIC による CHECKSUM 値の違い
CHAR	あり
VARCHAR	なし
TEXT	なし
INT	なし
DATETIME	あり
TIMESTAMP	あり
FLOAT	あり
DOUBLE	あり

複雑な条件がありそう

上記のように型に依存してCHECKSUMへの影響の有無が判断できるかというと、そうでもなさそうです。以下は、VARCHAR と DATETIME を組み合わせた場合です。CHECKSUMの値は一致しています。

mysql> CREATE TABLE t_datetime_compact (c1 varchar(250) NOT NULL, hiduke DATETIME) ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t_datetime_dynamic (c1 varchar(250) NOT NULL, hiduke DATETIME) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_datetime_compact VALUES('aaaa', '2020-01-01 10:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_datetime_dynamic VALUES('aaaa', '2020-01-01 10:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> CHECKSUM TABLE t_datetime_compact EXTENDED;
+----------------------+------------+
| Table                | Checksum   |
+----------------------+------------+
| t.t_datetime_compact | 1594517058 |
+----------------------+------------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t_datetime_dynamic EXTENDED;
+----------------------+------------+
| Table                | Checksum   |
+----------------------+------------+
| t.t_datetime_dynamic | 1594517058 |
+----------------------+------------+
1 row in set (0.00 sec)

しかし、DATETIME型、単体だと、CHECKSUMが異なります・・・

mysql> CREATE TABLE t_datetime_compact (hiduke DATETIME) ENGINE=InnoDB ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t_datetime_dynamic (hiduke DATETIME) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t_datetime_compact VALUES('2020-01-01 10:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t_datetime_dynamic VALUES('2020-01-01 10:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> CHECKSUM TABLE t_datetime_compact EXTENDED;
+----------------------+------------+
| Table                | Checksum   |
+----------------------+------------+
| t.t_datetime_compact | 1668604533 |
+----------------------+------------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t_datetime_dynamic EXTENDED;
+----------------------+------------+
| Table                | Checksum   |
+----------------------+------------+
| t.t_datetime_dynamic | 3856699611 |
+----------------------+------------+
1 row in set (0.00 sec)

FLOAT型も同様でした。

まとめ

ROW_FORMATが違う場合は型によってCHECKSUM TABLEの結果が異なる場合がある
しかし、型の組み合わせによっては、同じチェックサムになる場合がある

mita2 database life

主にMySQLに関するメモです

ROW_FORMATとCHECKSUM TABLEの結果の違い

お題