2019/11/07

MySQL Shellのdba.deploySandboxInstanceでサクッとmysqldを起動する

TL;DR

いつからできたのかは知らないけれど、気が付いたらMySQL Shellに dba.deploySandboxInstance() なんてものが出来ていてそれが感動的に楽なので熱が冷めないうちにメモ

如何に「MySQLとMySQL Shell以外他に何もいらない」かの感動を伝えるために、 ubuntu:latest のDockerコンテナを起動しただけの状態から始めます。

取り敢えず、 MySQLのaptリポジトリの依存に指定されてるやつらをインストール。

# apt update
# apt install wget lsb-release gnupg

MySQLのaptリポジトリをインストール。最近の .deb ファイルなら向き先が mysql-8.0になっていると思われる。

# wget https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb
# dpkg -i mysql-apt-config_0.8.14-1_all.deb
..
  1. MySQL Server & Cluster (Currently selected: mysql-8.0)  3. MySQL Preview Packages (Currently selected: Disabled)
  2. MySQL Tools & Connectors (Currently selected: Enabled)  4. Ok
Which MySQL product do you wish to configure? 4

# apt update

MySQL ServerとMySQL Shellをインストールする。

# apt install mysql-community-server mysql-shell

準備おしまい。

ただ /bin/bash を呼び出したコンテナなので他には何も浮いていない。

root@b2fbd7b7148f:~# ps auxww
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.2  18504  3432 pts/0    Ss   09:21   0:00 bash
root      4103  0.0  0.2  34396  2912 pts/0    R+   09:31   0:00 ps auxww

ここからおもむろに mysqlsh を起動。接続先は指定しない（し、しようもないし）

root@b2fbd7b7148f:~# mysqlsh
Logger: Tried to log to an uninitialized logger.
MySQL Shell 8.0.18

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS >

接続先を指定せずに、サーバープロセスを起動させるためにクライアントを起動する、ってなんか sqlplus っぽさを感じます。

長いメソッドの名前とか憶えてられないので、 dba. まで打ち込んでからTabを2回で補完候補が現れる。

 MySQL  JS > dba.
checkInstanceConfiguration()       deploySandboxInstance()            rebootClusterFromCompleteOutage()
configureInstance()                dropMetadataSchema()               startSandboxInstance()
configureLocalInstance()           getCluster()                       stopSandboxInstance()
createCluster()                    help()                             verbose
deleteSandboxInstance()            killSandboxInstance()

たぶん deploySandboxInstance() だろ。実行。

 MySQL  JS > dba.deploySandboxInstance()
Dba.deploySandboxInstance: Invalid number of arguments, expected 1 to 2 but got 0 (ArgumentError)

エラった＞＜
引数がおかしいのはわかるんだけど引数に何を指定すればいいのかわからないので、 dba.help('deploySandboxInstance') と実行。

 MySQL  JS > dba.help('deploySandboxInstance')
NAME
      deploySandboxInstance - Creates a new MySQL Server instance on localhost.

SYNTAX
      dba.deploySandboxInstance(port[, options])
..

ドバっと出てくる。使い方を引きやすい対話型クライアントは好きです。
取り敢えず、必須引数はportだということなので3306でいく。

 MySQL  JS > dba.deploySandboxInstance(3306)
A new MySQL sandbox instance will be created on this host in
/root/mysql-sandboxes/3306

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance:

Deploying new MySQL instance...

Instance localhost:3306 successfully deployed and started.
Use shell.connect('root@localhost:3306'); to connect to the instance.

root@localhostのパスワードだけ対話入力するとおしまい。

root@b2fbd7b7148f:~# ps auxwww
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.2  18504  3432 pts/0    Ss   09:21   0:00 bash
root      4163  0.0  0.2  18372  3088 pts/0    S    09:36   0:00 /bin/bash /root/mysql-sandboxes/3306/start.sh --user=root
root      4164  2.0 32.8 2302340 418604 pts/0  Sl   09:36   0:00 /root/mysql-sandboxes/3306/bin/mysqld --defaults-file=/root/mysql-san
dboxes/3306/my.cnf --user=root
root      4208  0.0  0.2  34396  2864 pts/0    R+   09:36   0:00 ps auxwww

勿論、ポート番号を変えればこのノリでザクザク mysqld を起動できる。
データとかmy.cnfとかは $HOME/mysql-sandboxes あたりに転がるので、サンドボックス用途ならもう公式のツールだけで作れる時代になってしまったのだ。令和ばんざい。

ちなみに、mysqlx-portにport * 10を指定しようとするので、ポート番号に 6554 を超える値を渡そうとするとエラる。その場合は dba.deploySandboxInstance(13306, {"portx": 13307}) とか明示してやればおｋ

それでは、楽しいMySQL Shellライフを。

2019/11/05

MySQL 8.0.18からCREATE USER, ALTER USER, SET PASSWORDで "RANDOM" を指定できるようになった

TL;DR

MySQL :: MySQL 8.0 Reference Manual :: 6.2.15 Password Management の “Random Password Generation” のあたりに全部書いてある。
MySQL 8.0.18から。

MySQL 8.0.18から、ランダムなパスワードを勝手に生成する RANDOM PASSWORD 構文が使えるようになった。
~~外部のパスワードジェネレータでいいじゃｎ~~ ううんなんでもない。

CREATE USER と ALTER USER は、本来パスワード文字列を渡すところにそのまま RANDOM PASSWORD と置き換えると使える。

mysql80 18> CREATE USER b IDENTIFIED WITH mysql_native_password BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | CYvTzZuEK</1JwfFrFkM |
+------+------+----------------------+
1 row in set (0.01 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B463EC3F855577E3EC49BC205D66ECEAF7ABC106' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 18> ALTER USER b IDENTIFIED BY RANDOM PASSWORD;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | HnZwYKlnl]*b1lJ>JxQt |
+------+------+----------------------+
1 row in set (0.02 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*15B4BD0DD178CB3C258B8867A1BB570A7CF9E799' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SET PASSWORD だけ、 = 'パスワード文字列' だったところが TO RANDOM になる。
こういうところ妙に英語っぽくするよねSQLって（偏見）

mysql80 18> SET PASSWORD FOR b TO RANDOM;
+------+------+----------------------+
| user | host | generated password   |
+------+------+----------------------+
| b    | %    | 6jKNjZBhz2fJHLM@N13* |
+------+------+----------------------+
1 row in set (0.01 sec)

mysql80 18> SHOW CREATE USER b;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for b@%                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'b'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E0D266D8CA4F993339B63CB8C3D7AB103B6D08B5' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ちなみにランダムパスワードの長さは generated_random_password_length で指定できるらしい。

完全な余談だけど、ドキュメントを見ずにテキトーな値を突っ込んだらワーニングになって、

mysql80 16> SET SESSION generated_random_password_length = 1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 16> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect generated_random_password_length value: '1024' |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

こんな時MySQL 8.0の performance_schema.variables_info があれば一発で便利だなあと思いました。

どうやら前に書いてた
- 日々の覚書: MySQL 8.0.0時代のmy.cnfの探り方

mysql80 16> SELECT * FROM performance_schema.variables_info WHERE variable_name = 'generated_random_password_length';
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME                    | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST  |
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
| generated_random_password_length | DYNAMIC         |               | 5         | 255       | 2019-11-05 15:56:14.070703 | root     | localhost |
+----------------------------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+
1 row in set (0.01 sec)

2019/10/08

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

2019/10/07

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とそれ以降で使える式インデックスだが、どうもなんかバグを抱えているっぽい。。

式インデックスは内部的に「目には見えない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

登録: 投稿 ( Atom )

GA

2019/11/07

MySQL Shellのdba.deploySandboxInstanceでサクッとmysqldを起動する

TL;DR

2019/11/05

MySQL 8.0.18からCREATE USER, ALTER USER, SET PASSWORDで "RANDOM" を指定できるようになった

TL;DR

2019/10/08

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

TL;DR

2019/10/07

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

TL;DR