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

2019年10月7日月曜日

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

TL;DR

  • 以下の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とそれ以降で使える式インデックスだが、どうもなんかバグを抱えているっぽい。。
式インデックスは内部的に「目には見えない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 でポチってあげてください…。

2019年9月27日金曜日

db tech showcase Tokyo 2019 2日目に参加してきました

db tech showcase Tokyo 2019 2日目に参加してきました。
3日目(今日)も午後から参加するんですが、取り急ぎ2日目(9/26(木))の感想を。

受付

  • ふらっと昼前にスピーカー受付だけしたら「控室を使えるのは講演前の1時間だけなので13時になったらまた来てくれ…すまぬ…」的な感じだった
    • そして13時になると俺の枠で控室が使えるので、その机に続々と集まってもくもくを始めるOracle MySQLチームwww
    • この「控室を知り合い同士で融通する」システムを上手くやると面白い会話が生まれたりするのかなあ(適当)
  • 『ああ、スーツ来た人たちが商談っぽいのをしてたり、畑の違うDBの人たちがホワイトボードの前で議論を交わしていたり、「おお、一年ぶり!」な挨拶を交わすあの頃のdb tech showcaseとは変わってしまったんだな』という感じ
  • 去年までのコーヒースペースもなくなっていたので、これ特に聞くセッションがないからもくもく仕事してよーとかそういう人が生存できなくなっていた…(これは仕方ないといえば仕方ないけど、直前まで資料に手を入れたかった俺にはちょっとつらかった)
インサイトの 石川さん からこんなコメントをいただいたので、懲りずにまた来年を楽しみにしようと思います!






Shooting a trouble of MySQL

  • わたしこと yoku0825 によるセッション
  • コンセプトは 日々の覚書: MySQLが刺さった時にざっと見るためのメモ と同じで、「同僚に俺がやっているやり方を伝える」ようなコンセプトで進んでいきます。
  • 全部を全部やったりできたりする必要はないですが、「そういえば他の観点で見られるものってどっかで聞いたことあったような?」って感じになってもらえれば幸いです。






Galera Cluster 4の新機能

  • Presented by Colin Charles
  • 俺が真面目に Galera Cluster (ただし本家ではなく Percona XtraDB Cluster の方)をやっていたのはもう5~6年くらい前で、随分進化したんだなあと感じるなど
    • 俺が最初に試し始めたのはどうやらChiba.pm #2の頃だった 様子

Oracle Database 18c/19c 辛口新機能解説

  • Presented by 篠田さん
  • yoku0825にはOracleがわからぬ…けれども、篠田さんのセッションだったら絶対面白い ⇒ 大当たり
  • Oracle畑の人には「こんなことができるようになったのか」、俺には「こんな機能MySQLにもあったらいいな」「こんな変態機能Oracleにもあったの…」みたいなすごく楽しいセッションでした

Oracle Database バージョン選択における考察’19

  • Presented by 諸橋さん
  • これ! ホントこれ!!
  • ライフサイクルポリシーの話はMySQLとしても今後どうなるのか感あるしクラウドの台頭という点ではRDS for MySQLという巨人がいるので色々考えさせられる話であった…

メルカリにおけるMySQLの運用

  • Presented by いちりんちゃんさん
  • 数年前にはてな東京オフィスとはてな京都オフィスの間のビデオ通話で顔を合わせた(?)っきりで物理いちりんちゃんさんを認識できてなかった…
  • メルカリ、 orchestrator 始めたってよ
  • 導入するまでに調査したorchestratorの内部動作とかを丁寧に教えてくれるという、ガチ方面のMySQLer垂涎のセッションでした
  • 資料が公開されるのを服を着て待ちます

スピーカーディナーちょっとだけ

  • ああ…顔ぶれ変わったなぁ…という感じ。。

2019年9月5日木曜日

日本MySQLユーザ会会(MyNA会) 2019年8月に参加してきました

日本MySQLユーザ会会(MyNA会) 2019年8月 に参加してきました。

とんぼさんのMySQL weeklyばなし

  • 「メリット: 普段、流れてくるブログエントリを全部とか読めないけど強制的に読むようになるから読めるようになる」
    • とてもわかる
  • 「デメリット: つらい。アップデートがリリース(8.0.17)された時は超つらかった」
    • とてもわかる
  • とても読みやすくてわたしもお世話になっているので、長続きしてほしい…!

yoku0825のCloneばなし



  • 「さすが変態」が “As expected” になったのはちょっと面白かったw
    • 実際には「変態」を何故か読み飛ばして「さすが」だけ翻訳してたっぽいけど

坂井さんのMySQL 5.7認定試験ばなし




  • この(日本語試験がリリースされた)時期に何故か観測範囲でこの試験を受けている人多かったな…と思ってたんですが、実はそんなに多くはなかった。。



  • 個人的にあの試験に思うところはこんな感じです。

杉山さんのInnoDB Clusterばなし


  • MySQL 8.0の「それ以外の部分」でハマるの大変だなと思うなど…。
  • そろそろGroup Replicationしたいので大変参考になりました。
    • やっぱりMySQL Router方面の基本的な考え方はMySQL Fabricの時と似てますね。

とみたさんのutf8mb4_0900_binのはなし





  • utf8mb4_0900_binutf8mb4_binNO_PAD_COLLATE 版。yokuおぼえた。