2019年10月8日火曜日

MySQL 8.0.17現在、PRIMARY KEYやUNIQUE KEYのCOLLATEを変更しても何故か再起動まで反映されない

TL;DR


まずは何も考えずに val varchar(32) にユニークキーを作る。
この時の collation_server はデフォルトの utf8mb4_0900_ai_ci のままで、「おっとこれって kamipoのハハパパ問題 が起こるやつじゃん、そういえばデフォルト変わったんだっけ」なイメージ。
mysql80 10> SELECT @@collation_server;
+--------------------+
| @@collation_server |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+
1 row in set (0.00 sec)

mysql80 10> CREATE TABLE t1 (num int NOT NULL PRIMARY KEY, val varchar(32) NOT NULL, UNIQUE KEY(val));
Query OK, 0 rows affected (0.03 sec)

mysql80 10> INSERT INTO t1 VALUES (1, 'ハハ');
Query OK, 1 row affected (0.00 sec)

mysql80 10> INSERT INTO t1 VALUES (2, 'パパ');
ERROR 1062 (23000): Duplicate entry 'パパ' for key 'val'

mysql80 10> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) NOT NULL,
  PRIMARY KEY (`num`),
  UNIQUE KEY `val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ハハパパ問題を避けるには utf8mb4_ja_0900_as_cs って ~進研ゼミで~ 習ったので、華麗にCOLLATIONを変更して事なきを得ようとする。
mysql80 10> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL COLLATE utf8mb4_ja_0900_as_cs;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 10> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  PRIMARY KEY (`num`),
  UNIQUE KEY `val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
よし問題なくなったので安心して「パパ」を追加できるz
mysql80 10> INSERT INTO t1 VALUES (2, 'パパ');
ERROR 1062 (23000): Duplicate entry 'パパ' for key 'val'
アイエエエエエエエエ
これ 元のバグレポート にも書いてあるんだけど、mysqldを再起動するか OPTIMIZE TABLE 相当(テーブル再構築)のことをすると直る。
mysql80 10> OPTIMIZE TABLE t1;
+-------+----------+----------+-------------------------------------------------------------------+
| Table | Op       | Msg_type | Msg_text                                                          |
+-------+----------+----------+-------------------------------------------------------------------+
| d1.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| d1.t1 | optimize | status   | OK                                                                |
+-------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.15 sec)

mysql80 10> INSERT INTO t1 VALUES (2, 'パパ');
Query OK, 1 row affected (0.01 sec)

mysql80 10> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
|   2 | パパ   |
+-----+--------+
2 rows in set (0.00 sec)
…これ、逆のことやったらどうなるんだろう。
mysql80 10> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 10> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`num`),
  UNIQUE KEY `val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 10> INSERT INTO t1 VALUES (3, 'ババ');
Query OK, 1 row affected (0.00 sec)

mysql80 10> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
|   3 | ババ   |
|   2 | パパ   |
+-----+--------+
3 rows in set (0.00 sec)
:(;゙゚’ω゚’): ハハパパババを全部同じ文字に扱うはずなのに入りましたね…
mysql80 10> SELECT * FROM t1 WHERE val = 'ハハ';
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'パパ';
+-----+--------+
| num | val    |
+-----+--------+
|   2 | パパ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'ババ';
+-----+--------+
| num | val    |
+-----+--------+
|   3 | ババ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'ばば';
+-----+--------+
| num | val    |
+-----+--------+
|   3 | ババ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'ハハ';
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
+-----+--------+
1 row in set (0.00 sec)
動きは変更前の utf8mb4_0900_ja_as_csに近いですね…:(;゙゚’ω゚’):
mysql80 10> RESTART;
Query OK, 0 rows affected (0.02 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'ハハ';
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'ばば';
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
+-----+--------+
1 row in set (0.00 sec)

mysql80 10> SELECT * FROM t1 WHERE val = 'パパ';
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
+-----+--------+
1 row in set (0.00 sec)
RESTART したら、今度は utf8mb4_0900_ai_ci っぽくなったけど、ユニーク制約だからか1レコードしか返ってきませんね…:(;゙゚’ω゚’):
これ UPDATEDELETE でもたぶんそうなるんですよね…。
mysql80 10> DELETE FROM t1 WHERE val = 'ババ';
Query OK, 1 row affected (0.13 sec)

mysql80 10> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   3 | ババ   |
|   2 | パパ   |
+-----+--------+
2 rows in set (0.00 sec)
ヒィ
ちなみに utf8mb4_0900_bin, utf8mb4_bin への変更はちゃんと即時反映された。
mysql80 10> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL COLLATE utf8mb4_0900_bin;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 10> INSERT INTO t1 VALUES (1, 'ハハ');
Query OK, 1 row affected (0.00 sec)

mysql80 10> SELECT * FROM t1;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | ハハ   |
|   3 | ババ   |
|   2 | パパ   |
+-----+--------+
3 rows in set (0.00 sec)
RESTARTOPTIMIZE TABLE ってことは、データディクショナリー上はちゃんと書き換えられてるけどオンメモリーの方が追随できてないんだろうなぁとかなんとか、 *ai_ci が悪いのかなどうなんだろうなぁとか。
取り敢えず Bug#97103 はしばらくウォッチすることにします…

0 件のコメント :

コメントを投稿