GA

2019/10/08

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

TL;DR

【2019/12/24 15:50】
8.0.20で直る、とバグレポートに書いてありますね。
  • ユニークキー、PRIMARY KEYが文字列型の場合に良くないことが起こる。
    • このバグが直るまでのバージョンで、「作り間違っちゃった」「あとからやっぱり変更したい」みたいなケースは十分気を付けた方が良いかと…
まずは何も考えずに 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)
よし問題なくなったので安心して「パパ」を追加できるｚ
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レコードしか返ってきませんね…:(；ﾞﾟ’ωﾟ’):
これ UPDATE や DELETE でもたぶんそうなるんですよね…。
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)
RESTART や OPTIMIZE TABLE ってことは、データディクショナリー上はちゃんと書き換えられてるけどオンメモリーの方が追随できてないんだろうなぁとかなんとか、 *ai_ci が悪いのかなどうなんだろうなぁとか。
取り敢えず Bug#97103 はしばらくウォッチすることにします…
​
投稿者 時刻:
ラベル: ,

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )