お題
CHECKSUM TABLE
はROW_FORMAT
が異なっても同じ値を出すときがある。- どういう場合に、
ROW_FORMAT
が異なっていても、チェックサムが一致するのか?
- どういう場合に、
CHECKSUM TABLE(おさらい)
CHECKSUM TABLE
はテーブルの中身のチェックサムを取得するコマンドです。
比較対象のデータベースサーバで、静止点を設けられないとデータの比較ができないわけですが、 pt-table-checksum と違って、コマンド1つでデータを比較できるので手軽ですね。
データは一致しているのに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
の結果が異なる場合がある- しかし、型の組み合わせによっては、同じチェックサムになる場合がある