2022/10/10

MySQLは実はWindowsのdatadirをそのままLinuxに持って行っても動く(8.0では一ひねり必要)

タイトルがすべて。

MySQL 5.7.39をWindowsでinitializeして起動。

PS > bin\mysqld --initialize-insecure
mysqld: Could not create or access the registry key needed for the MySQL application
to log to the Windows EventLog. Run the application with sufficient
privileges once to create the key, add the key manually, or turn off
logging for that application.

PS > bin\mysqld --console
mysqld: Could not create or access the registry key needed for the MySQL application
to log to the Windows EventLog. Run the application with sufficient
privileges once to create the key, add the key manually, or turn off
logging for that application.
2022-10-10T05:54:48.343808Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-10T05:54:48.343866Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-10-10T05:54:48.344166Z 0 [ERROR] Cannot open Windows EventLog; check privileges, or start server with --log_syslog=0
2022-10-10T05:54:48.344184Z 0 [Note] C:\Users\yoku0\Downloads\mysql-5.7.39-winx64\mysql-5.7.39-winx64\bin\mysqld.exe (mysqld 5.7.39) starting as process 10508 ...
<snip>
2022-10-10T05:54:48.538979Z 0 [Note] C:\Users\yoku0\Downloads\mysql-5.7.39-winx64\mysql-5.7.39-winx64\bin\mysqld.exe: ready for connections.
Version: '5.7.39'  socket: ''  port: 3306  MySQL Community Server (GPL)

MySQL on Windows状態でテーブルにINSERT。

PS > bin\mysql -uroot

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

mysql> CREATE TABLE d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO d1.t1 VALUES (1, 'Initialized on Windows');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val                    |
+-----+------------------------+
|   1 | Initialized on Windows |
+-----+------------------------+
1 row in set (0.00 sec)

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

mysqld.exeをシャットダウンして、datadirをLinuxホストにコピー。
Linux(今回はCentOS 7.9だった)側でそのdatadirを指定して起動。

$ scp -r data conoha:/tmp/5739

$ /usr/mysql/5.7.39/bin/mysqld --no-defaults --datadir=/tmp/5739
2022-10-10T05:59:35.702961Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2022-10-10T05:59:35.703117Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2022-10-10T05:59:35.877316Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-10-10T05:59:35.877370Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-10-10T05:59:35.877425Z 0 [Note] /usr/mysql/5.7.39/bin/mysqld (mysqld 5.7.39) starting as process 17330 ...
<snip>
2022-10-10T05:59:36.077227Z 0 [Note] Event Scheduler: Loaded 0 events
2022-10-10T05:59:36.077608Z 0 [Note] /usr/mysql/5.7.39/bin/mysqld: ready for connections.
Version: '5.7.39'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

$ /usr/mysql/5.7.39/bin/mysql -uroot

mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val                    |
+-----+------------------------+
|   1 | Initialized on Windows |
+-----+------------------------+
1 row in set (0.01 sec)

フツーに浮くし、フツーにSELECTできた。
(ただし、8.0で禁止された動作のように途中で lower_case_table_names が変わってるのでテーブル名の大文字小文字を迂闊に変えるとハマる可能性はある)

同じく8.0。

PS > bin\mysqld --initialize-insecure

PS > bin\mysqld --console
2022-10-10T06:01:51.655936Z 0 [System] [MY-010116] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe (mysqld 8.0.30) starting as process 7024
2022-10-10T06:01:51.668243Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:01:51.944691Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:01:52.288537Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-10T06:01:52.288941Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-10T06:01:52.318492Z 0 [System] [MY-010931] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.30'  socket: ''  port: 3306  MySQL Community Server - GPL.
2022-10-10T06:01:52.318498Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060

PS > bin\mysql -uroot

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE d1.t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO d1.t1 VALUES (1, '8.0.30 Init on Windows');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val                    |
+-----+------------------------+
|   1 | 8.0.30 Init on Windows |
+-----+------------------------+
1 row in set (0.00 sec)

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

8.0.30はさっきと同じように—no-defaultsでdatadirを食わせるだけだと上がらない。

$ scp -r data conoha:/tmp/8030

$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030
2022-10-10T06:06:10.864755Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:06:10.864766Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:06:11.113624Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 17699
2022-10-10T06:06:11.139472Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:06:11.437327Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:06:11.468902Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').
2022-10-10T06:06:11.469136Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-10-10T06:06:11.469160Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-10-10T06:06:11.999160Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.30/bin/mysqld: Shutdown complete (mysqld 8.0.30)  Source distribution.

8.0からは lower_case_table_names が

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized.

で、MySQL on Windowsのデフォルトは lower_case_table_names=1 , MySQL on Linuxのデフォルトは lower_case_table_names=0 なのでエラる。

initializeした時と同じ値に合わせてやれば良いので（ただしもちろんLinuxなのにテーブル名の大文字小文字を区別しなくなるという弊害(?)は起こる。俺はどんな環境でも lower_case_table_names=1 推奨派なので全く困らないけれども）

$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030 --lower_case_table_names=1
2022-10-10T06:10:29.738697Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:10:29.738719Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:10:29.986666Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 17785
2022-10-10T06:10:29.997767Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:10:30.222849Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: File '.\binlog.000001' not found (OS errno 2 - No such file or directory)
2022-10-10T06:10:30.520954Z 0 [ERROR] [MY-010958] [Server] Could not open log file.
2022-10-10T06:10:30.520977Z 0 [ERROR] [MY-010041] [Server] Can't init tc log
2022-10-10T06:10:30.520987Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-10-10T06:10:32.001836Z 0 [System] [MY-010910] [Server] /usr/mysql/8.0.30/bin/mysqld: Shutdown complete (mysqld 8.0.30)  Source distribution.

今度はバイナリログが見つからないらしい。
これの正体は binlog.index ファイルにWindowsファイルパス形式でバイナリログの在り処を記録しているからで

$ cat /tmp/8030/binlog.index
.\binlog.000001

binlog.index ごとバイナリログを全部消しちゃうか、 binlog.index を書き換えて ./ でLinuxファイルパス形式に書き換えてやれば良い。

$ vim /tmp/8030/binlog.index

$ cat /tmp/8030/binlog.index
./binlog.000001

三度目の正直で起動した。

$ /usr/mysql/8.0.30/bin/mysqld --no-defaults --datadir=/tmp/8030 --lower_case_table_names=1
2022-10-10T06:30:23.396869Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2022-10-10T06:30:23.396885Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2022-10-10T06:30:23.649027Z 0 [System] [MY-010116] [Server] /usr/mysql/8.0.30/bin/mysqld (mysqld 8.0.30) starting as process 18175
2022-10-10T06:30:23.660111Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-10T06:30:23.910902Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-10T06:30:24.223477Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-10T06:30:24.223575Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-10T06:30:24.225695Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp/8030' in the path is accessible to all OS users. Consider choosing a different directory.
2022-10-10T06:30:24.254458Z 0 [System] [MY-010931] [Server] /usr/mysql/8.0.30/bin/mysqld: ready for connections. Version: '8.0.30'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.
2022-10-10T06:30:24.254470Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock

$ /usr/mysql/8.0.30/bin/mysql -uroot

mysql> SELECT * FROM d1.t1;
+-----+------------------------+
| num | val                    |
+-----+------------------------+
|   1 | 8.0.30 Init on Windows |
+-----+------------------------+
1 row in set (0.01 sec)

逆も似たようなもんだと思うけれど、俺にはWindowsとLinuxを跨いでdatadirの移動はやったこともないしやる予定もないと思う。

2022/09/12

MySQL 8.0.30でもrpl_semi_sync_master_enabledとrpl_semi_sync_source_enabledは同じものではない

TL;DR

rpl_semi_sync_master_enabled は semisync_master.so 由来で rpl_semi_sync_source_eanbled は semisync_source.so 由来
- 他のMaster/Source系と違って、MySQLサーバーの中で丸められているわけではなく、 INSTALL PLUGIN した時にどちらを入れたかに依存する

日々の覚書: MySQLのシノニムではないけど同じような動きをする何か(と、SHOW SLAVE STATUSとSHOW REPLICA STATUSの地味な違い)
でほんのちょっとだけ触れたけれど。

rpl_semi_sync_master_enabled と rpl_semi_sync_source_eanbled (enabledに限らず、 rpl_semi_sync_* な設定は全て) は INSTALL PLGUIN した .soファイルに依存するので、MySQL 8.0.30現在、SOURCE, MASTERの用語変更が行われたバージョンであってもこれらは勝手には翻訳されない（ semisync_master.so をINSTALLした場合、 rpl_semi_sync_master_* を使用するしかなくて、 rpl_semi_sync_source_* で設定しようとすると unknown variable って怒られる）

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                               |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Note  | 1287 | 'rpl_semi_sync_master' is deprecated and will be removed in a future release. Please use rpl_semi_sync_source instead |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_source_enabled                | OFF        |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+
6 rows in set (0.00 sec)

ちなみに両方入れようとすると怒られる。

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
ERROR 4045 (HY000): Cannot install the rpl_semi_sync_master plugin when the rpl_semi_sync_source plugin is installed.

semisync_source と semisync_slave みたいな混ぜたパターンもできる（ややこしいからやめた方が良い気がする）

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                               |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Note  | 1287 | 'rpl_semi_sync_slave' is deprecated and will be removed in a future release. Please use rpl_semi_sync_replica instead |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%semi%';
+---------------------------------------------+------------+
| Variable_name                               | Value      |
+---------------------------------------------+------------+
| rpl_semi_sync_slave_enabled                 | OFF        |
| rpl_semi_sync_slave_trace_level             | 32         |
| rpl_semi_sync_source_enabled                | OFF        |
| rpl_semi_sync_source_timeout                | 10000      |
| rpl_semi_sync_source_trace_level            | 32         |
| rpl_semi_sync_source_wait_for_replica_count | 1          |
| rpl_semi_sync_source_wait_no_replica        | ON         |
| rpl_semi_sync_source_wait_point             | AFTER_SYNC |
+---------------------------------------------+------------+
8 rows in set (0.00 sec)

やろうと思う人がいるとも思えないしすぐエラーになるけど、 semisync_master.so で rpl_semi_sync_source をインストールしようとしてもエラる。

mysql> INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_master.so';
ERROR 1127 (HY000): Can't find symbol 'rpl_semi_sync_source' in library

「8.0.28なのに SET GLOBAL rpl_semi_sync_source_enabled=OFF が効かないぞ？」って思ったので残しておきます。

2022/08/30

MySQL on Windowsで8.0.20とそれ以降のmysqld.exeが--skip-grant-tablesでクラッシュすることがある

TL;DR

パラメーターの組み合わせを間違えた場合、本来Abortして起動しないもの(ここまでは意図的)がAbortの処理に失敗してクラッシュする
- “Not a Bug”, 仕様だということらしいので、気にせず正しい組み合わせで起動してやれば良いだけのはず
  - MySQL Bugs: #108304: mysqld.exe crashes --skip-grant-tables without --shared-memory or --named-pipe
skip-grant-tablesしたい時はmy.iniに一緒にshared-memoryを書いてね！

MySQL 8.0では skip-grant-tables を設定した時に自動で skip-networking も設定されるようになった。

これ自体は、「権限テーブル無視してノーガード状態(どこからでもroot相当の権限でノーパスワードでログインできる)のMySQLを守る」ためなので妥当な変更だと思う。

が、Linuix系のMySQLはskip-networkingでもソケット接続が自動(?)で残るので問題ないが、WindowsのMySQLはデフォルトではTCP/IP接続しか使えない = skip-networkingだけを指定するとアクセス方法が全断するのでAbortする。

↓クラッシュしない8.0.19のAbortのログ。

C:\Users\yoku0825\Downloads\mysql-8.0.19-winx64\mysql-8.0.19-winx64>bin\mysqld --console --skip-grant-tables
2022-08-30T02:13:13.537027Z 0 [System] [MY-010116] [Server] C:\Users\yoku0825\Downloads\mysql-8.0.19-winx64\mysql-8.0.19-winx64\bin\mysqld.exe (mysqld 8.0.19) starting as process 11792
2022-08-30T02:13:14.070774Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-30T02:13:14.086299Z 0 [System] [MY-010931] [Server] C:\Users\yoku0825\Downloads\mysql-8.0.19-winx64\mysql-8.0.19-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.19'  socket: ''  port: 0  MySQL Community Server - GPL.
2022-08-30T02:13:14.086409Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2022-08-30T02:13:14.086814Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-30T02:13:14.271648Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2022-08-30T02:13:15.239054Z 0 [System] [MY-010910] [Server] C:\Users\yoku0825\Downloads\mysql-8.0.19-winx64\mysql-8.0.19-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.

Windowsで非TCP/IP通信をするには shared-memory （これは俺使ったことある）または named-pipe （なんか使うのに失敗して以来使ってない）を「明示的に」my.iniに追加しないといけない。

追加していない場合、 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS (skip-networkingによって)TCP/IPも有効じゃないし、shared-memoryもnamed-pipeも1つも有効になってないよ（少なくとも1つは有効にしろよ）とエラーになって起動に失敗する（ [MY-010119] [Server] Aborting ）

ここまでは良いのだが、8.0.20とそれ以降ではこのAbort処理に失敗して（ [ERROR] [MY-013183] [InnoDB] Assertion failure ）バックトレースを吐く。

PS C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64> bin\mysqld --console --skip-grant-tables

2022-08-26T16:11:22.498164Z 0 [System] [MY-010116] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe (mysqld 8.0.30) starting as process 20292
2022-08-26T16:11:22.519344Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-26T16:11:22.803359Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-26T16:11:22.970565Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2022-08-26T16:11:23.124791Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-26T16:11:23.125007Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-08-26T16:11:23.137076Z 0 [System] [MY-010931] [Server] C:\Users\yoku0\Downloads\mysql-8.0.30-winx64\mysql-8.0.30-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.30'  socket: ''  port: 0  MySQL Community Server - GPL.
2022-08-26T16:11:23.137232Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2022-08-26T16:11:23.137332Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-08-26T16:11:25.018791Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: trx0sys.cc:643:UT_LIST_GET_LEN(trx_sys->mysql_trx_list) == 0 thread 8748
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
16:11:25 UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff752740858    mysqld.exe!my_print_stacktrace()[stacktrace.cc:429]
7ff7518de92b    mysqld.exe!print_fatal_signal()[signal_handler.cc:130]
7ff7518de6f3    mysqld.exe!my_server_abort()[signal_handler.cc:241]
7ff7527247fa    mysqld.exe!my_abort()[my_init.cc:263]
7ff75296ca99    mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:99]
7ff752951d0e    mysqld.exe!trx_sys_close()[trx0sys.cc:643]
7ff752903f81    mysqld.exe!srv_shutdown()[srv0start.cc:3240]
7ff75281cb6a    mysqld.exe!innodb_shutdown()[ha_innodb.cc:1603]
7ff7516add5b    mysqld.exe!ha_finalize_handlerton()[handler.cc:726]
7ff7516e9754    mysqld.exe!plugin_deinitialize()[sql_plugin.cc:1121]
7ff7516ef199    mysqld.exe!reap_plugins()[sql_plugin.cc:1190]
7ff7516ede10    mysqld.exe!plugin_shutdown()[sql_plugin.cc:2090]
7ff7516866b9    mysqld.exe!clean_up()[mysqld.cc:2581]
7ff75169aa5a    mysqld.exe!unireg_abort()[mysqld.cc:2444]
7ff75169941f    mysqld.exe!setup_conn_event_handler_threads()[mysqld.cc:3280]
7ff75169d196    mysqld.exe!win_main()[mysqld.cc:8120]
7ff7516954b5    mysqld.exe!mysql_service()[mysqld.cc:8215]
7ff751695adb    mysqld.exe!mysqld_main()[mysqld.cc:8431]
7ff752de4cb4    mysqld.exe!__scrt_common_main_seh()[exe_common.inl:288]
7ffa9f2554e0    KERNEL32.DLL!BaseThreadInitThunk()
7ffa9fee485b    ntdll.dll!RtlUserThreadStart()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

明らかにクラッシュはしているものの、直すのは大変だからこのまま（だったらStatusは “Not a Bug” ではなく “Won’t fix” では？とは思うけど）らしいので、この事象にあたった人が焦らないようにこの情報をここに書き残しておきます。

MySQL Bugs: #108304: mysqld.exe crashes --skip-grant-tables without --shared-memory or --named-pipe

skip-grant-tablesしたい時はmy.iniに一緒にshared-memoryを書いてね！

完全な余談。

これ、起動時に起こるクラッシュながら、見えるバックトレースは完全に「終了シーケンスの中でのクラッシュ」なのがちょっと面白かった。Abort処理の中でおかしくなってる（たぶん、InnoDBの初期化が終わってないのにInnoDBの終了処理をしているのがいけない）から終了シーケンスの中でのクラッシュなんだけど、逆にそれが「起動時…だよね…？」とバックトレースに目を取られて [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS を見落とさないように注意。

2022/08/25

PKの無いテーブルに、後からAUTO_INCREMENT PRIMARY KEYなカラムを足すときの罠

TL;DR

InnoDBの暗黙の行IDはテーブルにINSERTした順番で払い出される
ソースでは並列で走ると、INSERTした順番がCOMMITした順番とは限らない
レプリカではMulti Thread Applierを使っていない限り、COMMITした順番にINSERTがされる
よってソースとレプリカでは暗黙の行IDがズレることがある
【2022/08/25 12:01】書き込みロックをとっても(テーブルを読み書き一切できない時間があって)良いなら多少安全にやる方法はある

https://gist.github.com/yoku0825/9482455b42cb2cdfaf52288792441b12

百聞は一見に如かず。再現方法。


### on Source, connection 1

master [localhost] {msandbox} ((none)) > CREATE DATABASE d1;

Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > CREATE TABLE d1.t1(num int);

Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} ((none)) > BEGIN;

Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (1);

Query OK, 1 row affected (0.03 sec)

### まだCOMMITしない

connection1でINSERTをした後に、connection2で先にCOMMIT

### on Source, connection 2
master [localhost] {msandbox} ((none)) > BEGIN;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (2);
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > COMMIT;
Query OK, 0 rows affected (0.00 sec)

で、connection1でCOMMIT

master [localhost] {msandbox} ((none)) > COMMIT;
Query OK, 0 rows affected (0.00 sec)

こうすると、ソースではINSERTした順番が num = 1, num = 2の順番だけど、コミットされた順番が num = 2, num = 1の順番なのでレプリカでINSERTが走るのは num = 2, num = 1の順になる。

よって、PKもないので暗黙の行IDがズレて、ORDER BY無しのSELECTで比較するとこんな感じになる。

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num
1
2
# server: 1:
num
2
1

ここで、ALTER TABLEでAUTO_INCREMENTなPKを足すじゃろ？

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

AUTO_INCREMENTの値はその時の暗黙の行IDの順番に依存して振られていくので、ソースとレプリカでズレる。

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
2       2
# server: 1:
num     id
2       1
1       2

当然、この状態で DELETE FROM d1.t1 WHERE id = 2 なんてやるともう取り返しがつかない。numが数字だからわかりにくいけど、emailとかだともう致命的にダメだってことが想像できる。

master [localhost] {msandbox} ((none)) > DELETE FROM d1.t1 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
# server: 1:
num     id
2       1

レプリケーションとしては正常(ALTER TABLEはステートメント形式で伝達されてそれぞれのノードで再計算されるし、 id = 2は行ベースレプリケーションでもPKしか比較しないのでそれぞれでid = 2の行が消える)なので、気を付けないとこのデータ不整合に気が付けない。

$ ./check_slaves
master
port: 25038
             File: mysql-bin.000001
         Position: 6184
Executed_Gtid_Set:
slave # 1
port: 25039
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 6184
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 6184
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:

俺も同僚から教えてもらったんですが、 MySQL Bugs: #108229: Exists gaps when add a auto_increment column の原因ってこれじゃないかなと思います。まる。

【2022/08/25 12:01】

限りなくユニークに近いカラムがあって、LOCK TABLE WRITE (= ほかのスレッドは読み書きできない）を取っても良いなら、多少安全にやる方法はある。

master [localhost] {msandbox} ((none)) > LOCK TABLE d1.t1 WRITE;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ORDER BY num;  ### numが限りなくユニークに近いことが必要だけれども
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


$ ./use_all -e "SELECT * FROM d1.t1"
# master
num     id
1       1
2       2
# server: 1:
num     id
1       1
2       2

https://gist.github.com/yoku0825/9482455b42cb2cdfaf52288792441b12

登録: 投稿 ( Atom )

GA

2022/10/10

MySQLは実はWindowsのdatadirをそのままLinuxに持って行っても動く(8.0では一ひねり必要)

2022/09/12

MySQL 8.0.30でもrpl_semi_sync_master_enabledとrpl_semi_sync_source_enabledは同じものではない

TL;DR

2022/08/30

MySQL on Windowsで8.0.20とそれ以降のmysqld.exeが--skip-grant-tablesでクラッシュすることがある

TL;DR

2022/08/25

PKの無いテーブルに、後からAUTO_INCREMENT PRIMARY KEYなカラムを足すときの罠

TL;DR