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

TL;DR

• MySQL Bugs: #97103: Altering column collation doesn’t seem to affect unique indexes until restart

【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 はしばらくウォッチすることにします…

​

binlog_format= ROW + 式インデックス + mysqldumpでレプリケーションに失敗する可能性がある

TL;DR

• MySQL Bugs: #96986: Column Ordinal Position Not orderly updating
• 以下の4つの条件を満たすと MySQL error code MY-013146 (ER_SERVER_SLAVE_CONVERSION_FAILED): Column %d of table '%-.192s.%-.192s' cannot be converted from type '%-.32s' to type '%-.32s' が発火してSQLスレッドが止まる
  1. binlog_format= ROW である
  2. 式インデックスを使っている
  3. 式インデックスを作って以降、そのテーブルにカラムを追加した
  4. 論理バックアップからリストアしてスレーブを作成
• この記事を書いている現在、俺には「マスターのテーブルを再作成する」か、「スレーブの該当テーブルをマスターと同じ順序で構成してからデータをINSERTする」以外の回避策が思いつかない…

---

MySQL 8.0.13とそれ以降で使える式インデックスだが、どうもなんかバグを抱えているっぽい。。

• 日々の覚書: MySQL 8.0.13の式インデックス
• MySQL Bugs: #96986: Column Ordinal Position Not orderly updating

式インデックスは内部的に「目には見えないgenerated column」を作ってそれにインデックスを貼っているようで、それが binlog_format = ROW 、つまり「 n番目のカラムの値はこれ！」という形式のレプリケーションと相性が悪い(´･ω･｀)

マスターに対して式インデックスを含む CREATE TABLE を流し、その後カラムを追加する。

master> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

master> CREATE TABLE d1.t1 (num INT PRIMARY KEY, KEY((num < 100)));
Query OK, 0 rows affected (0.01 sec)

master> ALTER TABLE d1.t1 ADD COLUMN val varchar(32);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

この時、マスターの内部的には 「1番目は num カラム、2番目は (num < 100) を評価する仮想列、3番目が val カラム」になっている。
ibd2sdi でibdファイルからテーブルの定義情報をダンプするとを使うと簡単に見られた。
（もし、information_schemaとかでこれを確認しようと思うと、デバッグビルドのmysqldが必要になってめんどい…）

$ ibd2sdi t1.ibd
["ibd2sdi"
,
{
..
        "columns": [
..
            {
                "name": "3bb8c14d415110ac3b3c55ce9108ae2d",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": true,
                "hidden": 3,
                "ordinal_position": 2,
                "char_length": 1,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": false,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "AAAAAA==",
                "default_value_utf8_null": false,
                "default_value_utf8": "0",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "(`num` < 100)",
                "generation_expression_utf8": "(`num` < 100)",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1064;",
                "column_key": 4,
                "column_type_utf8": "int(1)",
                "elements": [],
                "collation_id": 8,
                "is_explicit_collation": false
            },
..
        "indexes": [
..
            {
                "name": "functional_index",
                "hidden": false,
                "is_generated": false,
                "ordinal_position": 2,
                "comment": "",
                "options": "flags=0;",
                "se_private_data": "id=150;root=5;space_id=3;table_id=1064;trx_id=2077;",
                "type": 3,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "InnoDB",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 4,
                        "order": 2,
                        "hidden": false,
                        "column_opx": 1
                    },
                    {
                        "ordinal_position": 2,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 0
                    }
                ],
                "tablespace_ref": "d1/t1"
            }
        ],
..

このマスターに対して INSERT ステートメントを叩き込む。
ちゃんとカラム名までフル修飾しても変わらないはず。

master> INSERT INTO d1.t1 VALUES (1, 'one');
Query OK, 1 row affected (0.01 sec)

master> SELECT * FROM d1.t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

マスターの内部的に「numが1番目、valが3番目」なので、バイナリログは当然こうなる。

# at 833
#191007 11:56:23 server id 111  end_log_pos 904 CRC32 0x3f31c3bd        Query   thread_id=17    exec_time=0     error_code=0
SET TIMESTAMP=1570416983/*!*/;
BEGIN
/*!*/;
# at 904
#191007 11:56:23 server id 111  end_log_pos 959 CRC32 0x1659d7ad        Table_map: `d1`.`t1` mapped to number 83
# at 959
#191007 11:56:23 server id 111  end_log_pos 1003 CRC32 0x00f71d7f       Write_rows: table id 83 flags: STMT_END_F

BINLOG '
V6maXRNvAAAANwAAAL8DAAAAAFMAAAAAAAEAAmQxAAJ0MQADAwMPAoAABAEBAAID/P8ArddZFg==
V6maXR5vAAAALAAAAOsDAAAAAFMAAAAAAAEAAgAD/QABAAAAA29uZX8d9wA=
'/*!*/;
### INSERT INTO `d1`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='one' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 1003
#191007 11:56:23 server id 111  end_log_pos 1034 CRC32 0x610336ff       Xid = 572
COMMIT/*!*/;

仮に最初の時点からスレーブが存在していたとしたら、「CREATE TABLEの時にnumと式インデックスが1, 2番になって後からADD COLUMNされたvalは3番」の順序が保たれるのでレプリケーションは壊れないが、このタイミングで論理バックアップを取ってリストアしてしまうと、

CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`num`),
  KEY `functional_index` (((`num` < 100)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

論理バックアップをリストアしたスレーブでは内部的に「 num が1番目、 val が2番目、式インデックスの隠し生成列が3番目」になり、1番目と3番目のカラムに値を突っ込もうとするバイナリログのイベントと矛盾（というか、データ型のミスマッチ）を起こす。

slave> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
..
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
..
               Last_SQL_Errno: 13146
               Last_SQL_Error: Column 1 of table 'd1.t1' cannot be converted from type 'int' to type 'varchar(128(bytes) utf8mb4)'

..

…あれ、エラーメッセージを見ると、 num (マスターで1番目、スレーブで1番目）を val （マスターで3番目、スレーブで2番目）に突っ込もうとしてる…？
それか、Columnの値が0オリジンで Column 1 = マスターの仮想生成列の値をスレーブの Column 1 = val に突っ込もうとしてる…？

エラーメッセージが悪いのか俺の認識がおかしいのかだけど、はてさてこれはなかなかエグい…本番に放り込んじゃったよ式インデックス…。

何とかなってほしいので、是非 Affects me でポチってあげてください…。

MySQL Bugs: #96986: Column Ordinal Position Not orderly updating

​