mita2 database life

主にMySQLに関するメモです

MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは

このエントリーは MySQL Advent Calendar 2020 の 12/7 のエントリーです。

照合順序(COLLATION)とは

照合順序は文字列の比較やソート順のルールのことです。各キャラクタセットごとに照合順序が定義されています。

-- SHOW COLLATIONS で一覧が見れる
mysql> SHOW COLLATIONS;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin                   | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary                     | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
<snip>

MySQL 8.0 で、utf8mb4 の照合順序が増えました。以下の表で太字にしたものが、新規に追加されたものです。各文字列が、同一と扱われる場合は、○としています。 ci/csCase [In]sensitive の略で、asAcent SensiteveksKatakana Sensitive の略です。

COLLATION A、a はは、ぱぱ はは、ハハ びょういん、びよういん 🍣、🍺 +、+
utf8mb4_bin × × × × × ×
utf8mb4_0900_bin × × × × × ×
utf8mb4_unicode_ci
utf8mb4_general_ci × × × ×
utf8mb4_unicode_520_ci ×
utf8mb4_0900_ai_ci ×
utf8mb4_0900_as_ci × ×
utf8mb4_ja_0900_as_cs × × × ×
utf8mb4_ja_0900_as_cs_ks × × × × ×

アルファベットの大文字・小文字を区別しない要件で、どれが選ばれそうか・・・ utf8mb4_0900_as_ci は「びょういん」「びよういん」が同一と扱われてしまい、いまいちに感じます。

そもそも、日本語の文字列比較やソート結果を網羅的に精査するのは現実的に可能なんでしょうか(上記の表以外にも考えないといけない、パターンがありそうです)。日本語には異字体・長音記号・漢数字・・・ちょっと思いつくだけでも、扱いに悩みそうな要素が多くあります。

絵文字が区別できないとは言え、utf8mb4_general_ci にはずっと使ってきた実績と安心があります。 MySQL 8.0 でも utf8mb4_general_ci を 引き続き使うケースが多いのではないでしょうか。

MySQL 8.0 で utf8mb4_general_ci を使うときの注意点

ALTER TABLE CONVERT TO 時に COLLATION の指定が必要

MySQL 8.0 で utf8mb4 のデフォルトの照合順序が utf8mb4_general_ci から utf8mb4_0900_as_ci に変更になりました。

あわせて、従来の3バイトUTF8、utf8(mb3) は deprecated になっています。 utf8mb4 に変換するときに COLLATE を明示的に指定しないと、utf8_general_ci から utf8mb4_0900_ai_ci へとテーブルのデフォルト照合順序になってしまいます。

mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
Empty set (0.00 sec)

mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 「ぱぱ」で「はは」がヒットしてしまう
mysql> SELECT * FROM utf8t WHERE c1 = "ぱぱ";
+----+--------+
| pk | c1     |
+----+--------+
|  1 | はは   |
+----+--------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
       Table: utf8t
Create Table: CREATE TABLE `utf8t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

このように、COLLATE を指定してALTERする必要があります。

mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
       Table: utf8t
Create Table: CREATE TABLE `utf8t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

SET NAMES では COLLATE の指定が必要

同様に、SET NAMES で照合順序を明示的に指定していない場合、MySQL 8.0 からは utf8mb4_0900_as_ci が使われてしまいます。

# MySQL 8.0 以降は utf8mb4_0900_as_ci が使われる
mysql> SET NAMES utf8mb4;

# MySQL 8.0 以降は 明示的に utf8mb4_general_ci を指定する必要がある。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

過去のMySQL Advent Calendar

気付けば、MySQL Advent Calendar を 6年も続けてました・・・。時が経つのは早い・・・。 明日は、lhfukamachi さんです!

MySQL ヒストグラムはバックアップされるか

MySQL 8.0 でヒストグラム統計が追加されました。従来、MySQLはデータが均等に分布していると仮定し、実行計画を組み立てていました。 ヒストグラムを使えば正確なデータの分布に基づいて、より最適な実行計画が選択されるようになります。

yakst.com

ヒストグラムANALIZE TABLE 〜 UPDATE HISTOGRAM で追加します。そして、information_schema.column_statistics を参照すると、ヒストグラムが追加されていることが確認できます。

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON val;
+-------+-----------+----------+------------------------------------------------+
| Table | Op        | Msg_type | Msg_text                                       |
+-------+-----------+----------+------------------------------------------------+
| t.t   | histogram | status   | Histogram statistics created for column 'val'. |
+-------+-----------+----------+------------------------------------------------+
1 row in set (0.06 sec)

mysql> SELECT * FROM  information_schema.column_statistics \G
*************************** 1. row ***************************
SCHEMA_NAME: t
 TABLE_NAME: t
COLUMN_NAME: val
  HISTOGRAM: {"buckets": [[93, 139, 0.009009009009009009, 3], [153, 326, 0.021021021021021023, 4],〜
1 row in set (0.00 sec)

ヒストグラム を追加しても、CREATE TABLE文 には、変化がないようです。

mysql> SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `pk` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` int DEFAULT NULL,
  UNIQUE KEY `pk` (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=334 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

mysqldumpmysqlsh で論理バックアップを取得した場合、どうなるんでしょうか?

  • mysqldump

ヒストグラムの定義自体はバックアップされるようです(当たり前)。 取得したヒストグラム統計はバックアップをインポートするときに再度、取得しなおされます。

$ mysqldump -uroot t t | grep HISTOGRAM
/*!80002 ANALYZE TABLE `t` UPDATE HISTOGRAM ON `val` WITH 100 BUCKETS */;
  • mysqlsh util.dumpTable

MySQL Shell もちゃんと対応してますね。JSONhistograms が含まれています。

$ cat t@t.json
{
    "options": {
        "schema": "t",
        "table": "t",
        "columns": [
            "pk",
            "val"
        ],
        "primaryIndex": "",
        "compression": "zstd",
        "defaultCharacterSet": "utf8mb4",
        "fieldsTerminatedBy": "\t",
        "fieldsEnclosedBy": "",
        "fieldsOptionallyEnclosed": false,
        "fieldsEscapedBy": "\\",
        "linesTerminatedBy": "\n"
    },
    "triggers": [],
    "histograms": [
        {
            "column": "val",
            "buckets": 100
        }
    ],
    "includesData": true,
    "includesDdl": true,
    "extension": "tsv.zst",
    "chunking": true
}

MySQL 8.0 より前の mysqldump を使うとヒストグラムは移植されない

ヒストグラムMySQL 8.0 の機能なので、MySQL 5.7 の mysqldump で 8.0のテーブルをダンプするとヒストグラムの定義は抜け落ちてしまいます。

$ mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.31, for Linux (x86_64)

$ mysqldump -ubk -pPassword -h mysql80 t t | grep HISTO
(何も出力されない)

来週はMySQLユーザ会会

来週 11/25(水) は MyNA会ですね!楽しみです。 mysql.connpass.com

MySQL Lock wait timeout exceeded が発生している現場を押さえる

pt-stalk は、vmstatpsといったOSから見える情報や SHOW PROCESSLIST SHOW GLOBAL STATUSといったDBの情報など、MySQLのトラブルシュートに必要な情報をごっそり収集するツールです。

$ sudo ls -l /var/lib/pt-stalk
total 1428
-rw-r--r--. 1 root root  13260 11月 19 06:15 2020_11_19_06_15_10-df
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-disk-space
-rw-r--r--. 1 root root   5730 11月 19 06:15 2020_11_19_06_15_10-diskstats
-rw-r--r--. 1 root root     22 11月 19 06:15 2020_11_19_06_15_10-hostname
-rw-r--r--. 1 root root   4714 11月 19 06:15 2020_11_19_06_15_10-innodbstatus1
-rw-r--r--. 1 root root   4715 11月 19 06:15 2020_11_19_06_15_10-innodbstatus2
-rw-r--r--. 1 root root  65640 11月 19 06:15 2020_11_19_06_15_10-interrupts
-rw-r--r--. 1 root root  15624 11月 19 06:15 2020_11_19_06_15_10-lock-waits
-rw-r--r--. 1 root root    845 11月 19 06:15 2020_11_19_06_15_10-log_error
-rw-r--r--. 1 root root   2724 11月 19 06:15 2020_11_19_06_15_10-lsof
-rw-r--r--. 1 root root  39780 11月 19 06:15 2020_11_19_06_15_10-meminfo
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-mutex-status1
-rw-r--r--. 1 root root    121 11月 19 06:15 2020_11_19_06_15_10-mutex-status2
-rw-r--r--. 1 root root  22145 11月 19 06:15 2020_11_19_06_15_10-mysqladmin
-rw-r--r--. 1 root root  30210 11月 19 06:15 2020_11_19_06_15_10-netstat
-rw-r--r--. 1 root root  42810 11月 19 06:15 2020_11_19_06_15_10-netstat_s
-rw-r--r--. 1 root root   3052 11月 19 06:15 2020_11_19_06_15_10-opentables1
-rw-r--r--. 1 root root   3052 11月 19 06:15 2020_11_19_06_15_10-opentables2
-rw-r--r--. 1 root root   8439 11月 19 06:15 2020_11_19_06_15_10-output
-rw-r--r--. 1 root root  10483 11月 19 06:15 2020_11_19_06_15_10-pmap
-rw-r--r--. 1 root root  20060 11月 19 06:15 2020_11_19_06_15_10-processlist
-rw-r--r--. 1 root root  39388 11月 19 06:15 2020_11_19_06_15_10-procstat
-rw-r--r--. 1 root root  73146 11月 19 06:15 2020_11_19_06_15_10-procvmstat
-rw-r--r--. 1 root root  10055 11月 19 06:15 2020_11_19_06_15_10-ps
-rw-r--r--. 1 root root 332400 11月 19 06:15 2020_11_19_06_15_10-slabinfo
-rw-r--r--. 1 root root    660 11月 19 06:15 2020_11_19_06_15_10-slave-status
-rw-r--r--. 1 root root  32799 11月 19 06:15 2020_11_19_06_15_10-sysctl
-rw-r--r--. 1 root root   9648 11月 19 06:15 2020_11_19_06_15_10-top
-rw-r--r--. 1 root root  72480 11月 19 06:15 2020_11_19_06_15_10-transactions
-rw-r--r--. 1 root root    381 11月 19 06:15 2020_11_19_06_15_10-trigger
-rw-r--r--. 1 root root  12789 11月 19 06:15 2020_11_19_06_15_10-variables
-rw-r--r--. 1 root root   2785 11月 19 06:15 2020_11_19_06_15_10-vmstat
-rw-r--r--. 1 root root    327 11月 19 06:15 2020_11_19_06_15_10-vmstat-overall

データベースを定期的に監視し、指定したトリガーが発動したタイミングで、情報収集を行います。 事前に pt-stalk を仕掛けておくことで、障害の現場を押さえることができます。

例)実行状態のコネクションが100を超えたら何かが起きてるので情報を取る

$ pt-stalk --function status --variable Threads_running --threshold=100

SHOW GLOBAL STATUSSHOW PROCCESSLIST の結果をトリガーの条件とすることができます。 それぞれ、--function status--function processlist と指定します。

--function status では ステータスの値が閾値を超えたら、--function processlist では条件を満たすプロセス(接続)数が閾値を超えたら情報収集が発動します。

カスタムトリガーを設定してロック待ち発生現場を押さえる

--functionシェルスクリプトを指定することで、トリガー条件をカスタマイズできます。シェルスクリプトで定義した trg_plugin 関数がpt-stalkから定期的に呼び出されます。

ロック待ちの時間をトリガーの条件としてみます。

$ cat locktime.sh
trg_plugin() {
   mysql  $EXT_ARGV -N -B -e "SELECT IFNULL((SELECT MAX(now() - trx_wait_started) FROM information_schema.INNODB_TRX), 0) AS wt"
}

locktime.sh が出力する数値が --threshold を超えたら、情報収集が発動します。この例では、3秒以上ロックで待たされているクエリが発生したら、情報収集が走ります。

$ sudo pt-stalk --function locktime.sh --threshold=3

ロック待ちが発生している瞬間に、必ずしもロック待ちの原因となったクエリが実行状態とは限りませんが(未コミットのトランザクションがロックを持っている場合がある)、何度か繰り返し情報を集めていれば pt-stalk の結果から原因を見つけることができるでしょう。

MySQL 8.0 でパスワードのハッシュ値でユーザを作成する構文が変わってた

MySQL では、パスワードのハッシュ値を指定してユーザを作ることが出来ます。

mysql> SELECT authentication_string FROM mysql.user WHERE User = 'usr';
+-------------------------------------------+
| authentication_string                     |
+-------------------------------------------+
| *9DE95B4999920AF052A4DC2BC3D229D4E9D1B676 |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> CREATE USER usr@'localhost' IDENTIFIED BY PASSWORD '*9DE95B4999920AF052A4DC2BC3D229D4E9D1B676';
Query OK, 0 rows affected, 1 warning (0.00 sec)

この方法は複数のデータベースに同一のパスワードでユーザを作成する時に便利です。

ユーザ作成は、データベース管理者が仮パスワードでユーザを作成し、本人がパスワードを変更する流れが一般的だと思います。 仮パスワードの発行とパスワード変更を複数のデータベースで行うのは面倒です。最初のデータベースだけこの流れでパスワードを設定し、残りはハッシュ値をコピーすれば手間を省けます*1

また、平文のパスワードを含むSQLは取り扱いに非常に注意が必要ですが、ハッシュ値であれば、気軽に取り扱える点もメリットです。

IDENTIFIED BY PASSWORD〜構文が8.0で廃止された

上記のIDENTIFIED BY PASSWORD 構文 はMySQL 5.7 のものです。deprecated になっており廃止が予告されていました。

8.0 からは、IDENTIFIED WITH auth_plugin AS ハッシュ値(authentication_string) に構文が変わっています。

mysql> CREATE USER 'usr'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035241F53216C28304B071C573A37015F252C54631B66446E7064626471444E51683766426336377635585A756E6E7A442E497339716942596A2E6D7755514A4C38

ハッシュ値は、mysql.user テーブルもしくは SHOW CREATE USER で確認できます。

mysql> SELECT authentication_string FROM mysql.user WHERE User = 'usr';
+------------------------------------------------------------------------+
| authentication_string                                                                                   |
+------------------------------------------------------------------------+
| $A$005$S!l(0KW:7_%,TcDnpdbdqDNQh7fBc67v5XZunnzD.Is9qiBYj.mwUQJL8 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE USER usr@'localhost' \G
*************************** 1. row ***************************
CREATE USER for usr@localhost: CREATE USER 'usr'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$S!l(0KW:7_%,TcDnpdbdqDNQh7fBc67v5XZunnzD.Is9qiBYj.mwUQJL8' 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)

また、ハッシュ値は16進数で指定することも可能になっています。authentication_string にバイナリが入る可能性を考慮しているのでしょう。 16進数の表記は、print_identified_with_as_hexon で確認できます。

mysql> SET print_identified_with_as_hex = on;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE USER usr@'localhost' \G
*************************** 1. row ***************************
CREATE USER for usr@localhost: CREATE USER 'usr'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035241F53216C28304B071C573A37015F252C54631B66446E7064626471444E51683766426336377635585A756E6E7A442E497339716942596A2E6D7755514A4C38 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)

*1:Linuxで/etc/shadow をコピーするのと同じ原理です

MySQL Shell 8.0 を CentOS7 上でソースからビルドする

前回のエントリーでMySQL Shellのバグについて書きました。バグの原因を調査するために、MySQL Shellをビルドしたんですが、ビルドに苦労しました、、、 MySQL Shell をビルドするために乗り越えた障害と対応を方法をメモしておきます。

まず、MySQL Shell をビルドするには、事前にMySQL Server もビルドしておく必要があります。

mita2db.hateblo.jp

2つのcmakeが必要

MySQL Server 本体は cmake 3系 が必要なのですが、MySQL Shell は cmake 2 系でないと通りません。。。いつものノリでcmake3を使うとコケます。

$ cmake3 .. -DMYSQL_SOURCE_DIR=/home/user1/mysql-8.0.22/ -DMYSQL_BUILD_DIR=/home/user1/mysql-8.0.22/bld -DHAVE_PYTHON=1
<snip>
CMake Error at /usr/share/cmake3/Modules/FindPackageHandleStandardArgs.cmake:164 (message):
  Could NOT find Protobuf: Found unsuitable version "ERROR.ERROR.ERROR", but
  required is at least "3.11.4" (found
  /home/user1/mysql-8.0.22/bld/extra/protobuf/protobuf-3.11.4/cmake/libprotobuf.a)
Call Stack (most recent call first):
  /usr/share/cmake3/Modules/FindPackageHandleStandardArgs.cmake:443 (_FPHSA_FAILURE_MESSAGE)
  /usr/share/cmake3/Modules/FindProtobuf.cmake:626 (FIND_PACKAGE_HANDLE_STANDARD_ARGS)
  cmake/protobuf.cmake:117 (FIND_PACKAGE)
  CMakeLists.txt:414 (INCLUDE)

CentOS7の標準のcmakeは2系なので、普通にyumで入れれば大丈夫。

$ sudo yum install -y cmake
<snip>
Installed:
  cmake.x86_64 0:2.8.12.2-2.el7

Dependency Installed:
  libarchive.x86_64 0:3.1.2-14.el7_7
# ビルドに必要なヘッダファイルたちもインストール
$ sudo yum install python3-devel libcurl-devel
<snip>

python が見つからないと言われる

このバージョン(2.8.12)のcmake は、python-3.6 に対応していないようです。そのままだと「pythonが見つからん!」とエラーが出ます。

$ cd mysql-shell-8.0.22-src
$ mkdir bld
$ cd bld

$ scl enable devtoolset-7 bash
$ cmake .. -DMYSQL_SOURCE_DIR=/home/user1/mysql-8.0.22/ -DMYSQL_BUILD_DIR=/home/user1/mysql-8.0.22/bld -DHAVE_PYTHON=1
<snip>
CMake Error at /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:108 (message):
  Could NOT find PythonLibs (missing: PYTHON_LIBRARIES PYTHON_INCLUDE_DIRS)
  (Required is at least version "3.4.3")
Call Stack (most recent call first):
  /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:315 (_FPHSA_FAILURE_MESSAGE)
  /usr/share/cmake/Modules/FindPythonLibs.cmake:197 (FIND_PACKAGE_HANDLE_STANDARD_ARGS)
  CMakeLists.txt:277 (FIND_PACKAGE)

FindPythonLibs.cmake_PYTHON3_VERSIONS に、python 3.6 を追加して回避します(もしかしたら、python を 3.4以下にダウングレードするほうが正しいのかもしれませんが、とりあえず、これで通ったので)。

$ sudo vim /usr/share/cmake/Modules/FindPythonLibs.cmake
set(_PYTHON3_VERSIONS 3.6 3.4 3.3 3.2 3.1 3.0)

MySQL Server のビルドオプションも調整が必要

cmake が終わって、make を走らせていくと、libprotobuf.a が見つからないためエラーになりました。

$ make -j 4
<snip>
Scanning dependencies of target mysqlshrec
Scanning dependencies of target mysqlsh
make[2]: *** No rule to make target `/home/user1/mysql-8.0.22/bld/extra/protobuf/protobuf-3.11.4/cmake/libprotobuf.a', needed by `bin/mysqlsh'.  Stop.
<snip>

protobuf のソースは MySQL Server 本体のほうにあります。libprotobuf を static ライブラリとしてビルドするよう、MySQL Server のビルドオプション protobuf_BUILD_SHARED_LIBS=OFF を足してビルドしなおす必要がありました。

$ cmake3 .. -DWITH_BOOST=/home/user1/mysql-8.0.22/boost  -Dprotobuf_BUILD_SHARED_LIBS=OFF
-- Running cmake version 3.17.3
-- Could NOT find Git (missing: GIT_EXECUTABLE)
-- MySQL 8.0.22

$ make -j 4

MySQL Shell dumpInstance が一貫性の壊れたバックアップを生成するバグの件(修正済み)

このバグは MySQL Shell 8.0.23 で修正済みです。


前回のエントリーMySQL Shell の dumpInstance にはバグがあると書きました。 今回は、そのバグについてです。

どんなバグか

バックアップの一貫性が失われるバグです。一貫性が失われるている状態とは、バックアップデータの時間軸がずれていることを指します。 あるレコードは10:00:00の状態だが、別のレコードは10:00:01の状態であるといったケースです。

MySQL Shell 8.0.22 でバグがあることを確認しています(それ以前も同じバグがあるかもしれない、未確認です)

Workaround

このバグには簡単なワークアラウンド(回避策)があります。 プロシージャとファンクションをバックアップ対象外にします。 MySQL Shell でバックアップを取っている人はこのオプションを設定しましょう。

util.dumpInstance("mydump", {routines:false});

このバグはMySQL Shellのバグです。mysqldumpやxtrabackupやら他の方法のバックアップには影響ありません(ご安心ください)。

再現方法

簡単に再現します。このような2つのテーブルにINSERTするトランザクションを連続して実行しながら dumpInstance でバックアップを取ります。

BEGIN
  INSERT INTO db1.t1 (c) VALUES()
  INSERT INTO db2.t1 (c) VALUES()
COMMIT
# 運が良いと再現しないので何度もバックアップします
mysqlsh> \py
mysqlsh> for i in range(10): util.dump_instance('/tmp/bk%s' % i, compression='none')

一貫性が正しく保たれていれば、生成されたバックアップの db1.t1db2.t1 の件数が一致するはずです。 しかし、ズレたバックアップが生成されてしまいます。。。

$ tail -n3 bk5/db*@t1*tsv | less
==> bk5/db1@t1@@0.tsv <==
2222 
2223
2224

==> bk5/db2@t1@@0.tsv <==
2221 
2222
2223

バグの原因

MySQL Shellの dumpInstance は、FLUSH TABLES WITH READ LOCKLOCK TABLES で静止点を設け、その間に各Dumpスレッドが、トランザクションを開始します。 各ダンプスレッドが静止点から開始したトランザクションを使って、データをSELECTすることで一貫性を保ったバックアップを生成する仕組みです。

f:id:mita2db:20201104230031p:plain

よくよく調べると、一部のダンプスレッドではトランザクションを開始したあと、プロシージャをバックアップするため、mysql.proc テーブルのロックをとりに行ってました。 LOCK TABLES は暗黙的にトランザクションをコミットします。これによって特定のダンプスレッドだけ静止点からずれたデータをダンプしてしまう状態になってしまっていました。

f:id:mita2db:20201104230857p:plain

ということで、バグレポを上げています。気になる人は Subscribe しておくと良いでしょう。

MySQL Bugs: #101410: mysqlsh dumpInstance backup consistency is broken

MySQL Shell dumpInstance の仕組みと8.0.22 わいわい会

MySQL Release note でわいわい言う勉強会 8.0.22 でLTしてきました。

MySQL Shell の dumpInstance の仕組み

MySQL Shell のバックアップ機能 dumpInstance がどのようにして、一貫性のあるバックアップを実現しているか説明しました。 8.0.21 までは FLUSH TALBES WITH READ LOCK を利用してロックをかける方式のみでしたが、 8.0.22 以降では、FTWRLが使えない場合は、LOCK TABLES を代替として利用するようになっています。

これで、Amazon RDSなどFTWRLが使えない環境でも、一貫性のあるバックアップが取れるようになりました。 このあたりは、@atsuizo さんも解説されています。

atsuizo.hatenadiary.jp

実は、MySQL Shell 8.0.22 時点ではバグがあって、一貫性のあるバックアップが取れないときがあります(追記:このバグは MySQL Shell 8.0.23 で修正済みです) この件はバグレポートを上げています。詳細は以下のエントリーに書いています。

mita2db.hateblo.jp

サーバサイド Prepared Statement の仕組み

8.0.22 会では、Derived Condition Pushdown Optimization、SRVレコードを使った接続の話、などなど。。。いろいろな話題が出ました。 中でも、Prepared Statement の話が印象深かったので、書き残しておきたいと思います。

みなさんは、サーバサイド Prepared Statement は使っていますか?自分は使ってません。。。 経験則ですが、アプリとDBサーバを一往復余計にする分、むしろ使うと遅くなることが多いように思います。言い換えると、MySQLのサーバサイドプリペアドステートメントではPREPARE時に生成しておける情報が少ないとも言えると思います *1

8.0.22 でこの辺りに多少改善が入ったようです(サーバサイドプリペアドステートメントを積極利用するほどの改善ではないですが、、、)

Historically, only the parse stage has been reused for each execution. With this work both the parse stage and the resolve stage will be prepared once and executed many times when prepared statements are executed. The planning stage will still be repeated for each prepared statement execution. The motivation for this work is to improve performance by repeated executions and also to simplify the code base.

https://mysqlserverteam.com/the-mysql-8-0-22-maintenance-release-is-generally-available/

プリペア時にはSQLparse だけをやっていて、resolveplanning は EXECUTE 時に毎回やっていました。 これが、8.0.22 では parse に加えて、resolve ステージもPREPARE時に1回だけやるように改善したと。 resolve stage って何かっていうと、「オブジェクト(テーブルやら、カラムやら)が存在している確認したりするフェーズ」らしいです。

プリペアドステートメントの実装の一旦が知れて面白かったです。

8.022 のプリペアドステートメントまわりの挙動の変化は tom__bo さんや tmtms さんも解説してます。

tombo2.hatenablog.com

tmtms.hatenablog.com

*1:Oracleだと逆にプリペアドステートメントを使わないと、ハードパースになり、遅くなった記憶があります