🫣
[MySQL]slave_type_conversionsがデフォルト設定だとテーブル定義が異なる場合に行ベースレプリケーションが失敗する
TL;DR
- MySQL（※8.0） のバイナリログレプリケーションに関するお話
- 行ベースレプリケーションではソース・レプリカで異なるテーブル定義（カラムデータ型の差異）である場合、デフォルトでレプリケーションが失敗するような設定となっているため注意が必要
- 型変換モードを制御する slave_type_conversions というパラメータが存在するためこの値を変更することで柔軟に対応が可能
- 参考：17.5.1.9 ソースとレプリカで異なるテーブル定義を使用したレプリケーション
デモ手順
※前提として、Docker を用いたローカル環境を使用。
必要なファイルを準備する。
docker-compose.yaml
services:
mysql8.0-source:
image: mysql:8.0
container_name: mysql8.0-source
environment:
MYSQL_ROOT_PASSWORD: root
volumes:
- ./data/source:/var/lib/mysql
- ./sql/source:/docker-entrypoint-initdb.d
- ./config/source/my.cnf:/etc/mysql/my.cnf
ports:
- 3307:3306
mysql8.0-replica:
image: mysql:8.0
container_name: mysql8.0-replica
environment:
MYSQL_ROOT_PASSWORD: root
volumes:
- ./data/replica:/var/lib/mysql
- ./config/replica/my.cnf:/etc/mysql/my.cnf
ports:
- 3308:3306
config/source/my.cnf
[mysqld]
default_authentication_plugin = mysql_native_password
config/replica/my.cnf
[mysqld]
default_authentication_plugin = mysql_native_password
server_id = 2
※本件とは関係がないが、MySQL8.0 から デフォルトとなった caching_sha2_password を使うには少し手間がいるため省くために mysql_native_password を使用している（参考：MySQL8.0で新たに追加されているレプリケーション接続オプション）。
※レプリケーションにおいて各レプリカの server_id は一意にする必要があるため対応している（参考：17.1.2.2 レプリカ構成の設定）。
sql/source/init.sql
CREATE DATABASE IF NOT EXISTS repl_test;
use repl_test;
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(254) NOT NULL,
age INT NOT NULL
);
docker コンテナの立ち上げ。
% docker-compose up -d mysql8.0-source
[+] Running 1/2
⠙ Network 20240427_poc_mysql_replication_default Created 0.2s
✔ Container mysql8.0-source Started 0.1s
% docker-compose up -d mysql8.0-replica
[+] Running 1/1
✔ Container mysql8.0-replica Started
ソース DB からのデータ dump とレプリカへの流し込み。
% mysqldump -u root -proot -h 127.0.0.1 -P 3307 --all-databases > dbdump.db
% mysql -u root -proot -h 127.0.0.1 -P 3308 < dbdump.db
ソース DB でレプリケーション用 MySQL アカウントを作成。
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
バイナリログファイルとポジションの確認。
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 660 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
レプリカ DB でレプリケーション設定。
mysql> CHANGE REPLICATION SOURCE TO
-> SOURCE_HOST='mysql8.0-source',
-> SOURCE_USER='repl',
-> SOURCE_PASSWORD='repl',
-> SOURCE_LOG_FILE='binlog.000002',
-> SOURCE_LOG_POS=660;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
レプリケーションの開始。
mysql> START REPLICA;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: mysql8.0-source
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000002
Read_Source_Log_Pos: 660
Relay_Log_File: 2c481115371a-relay-bin.000002
Relay_Log_Pos: 323
Relay_Source_Log_File: binlog.000002
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 660
Relay_Log_Space: 540
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 6c8edb6c-0b67-11ef-9df6-0242c0a80002
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
レプリカ DB でテーブル定義の変更。
name カラムの 文字数制限を VARCHAR(254) -> VARCHAR(255) にする。
mysql> ALTER TABLE users MODIFY name VARCHAR(255) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
ソース DB でレコードを INSERT する。
mysql> INSERT INTO users (name, age) VALUES ('Bob', 20);
Query OK, 1 row affected (0.01 sec)
レプリカ DB でレプリケーションのステータスを確認する。
SQL スレッドでエラーが吐かれ、停止していることが分かる。
mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: mysql8.0-source
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000002
Read_Source_Log_Pos: 3297
Relay_Log_File: 2c481115371a-relay-bin.000002
Relay_Log_Pos: 2656
Relay_Source_Log_File: binlog.000002
Replica_IO_Running: Yes
Replica_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 13146
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000002, end_log_pos 3266. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Source_Log_Pos: 2993
Relay_Log_Space: 3177
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 13146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000002, end_log_pos 3266. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: 6c8edb6c-0b67-11ef-9df6-0242c0a80002
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 240506 06:30:10
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000002, end_log_pos 3266. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
SQL スレッドのエラーメッセージだけでは分かりづらいため上記メッセージに従い、performance_schema の replication_applier_status_by_worker を見てみる。
mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker WHERE WORKER_ID = 1\G;
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 13146
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000002, end_log_pos 3266; Column 1 of table 'repl_test.users' cannot be converted from type 'varchar(1016(bytes))' to type 'varchar(1020(bytes) utf8mb4)'
LAST_ERROR_TIMESTAMP: 2024-05-06 06:30:10.545443
LAST_APPLIED_TRANSACTION: ANONYMOUS
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-05-06 06:29:29.178554
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-05-06 06:29:29.178554
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2024-05-06 06:29:29.180308
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2024-05-06 06:29:29.188135
APPLYING_TRANSACTION: ANONYMOUS
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-05-06 06:30:10.539461
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-05-06 06:30:10.539461
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2024-05-06 06:30:10.545006
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
1 row in set (0.00 sec)
ERROR:
No query specified
Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000002, end_log_pos 3266; Column 1 of table 'repl_test.users' cannot be converted from type 'varchar(1016(bytes))' to type 'varchar(1020(bytes) utf8mb4)'
VARCHAR(254) -> VARCHAR(255) への変更により、カラムのデータが変換できない旨のエラーメッセージが吐かれている。
解決策
slave_type_conversions というパラメータがあるので、こちらの値を変えることで解決。デモ手順のようなケース（VARCHAR の文字数範囲が増える場合等）では ALL_NON_LOSSY に変更するとよさそう。ALL_NON_LOSSY はレプリカ側の定義の範囲（VARCHAR の文字数範囲等）がソース側の定義より広い場合にレプリケーションを許可するという設定になる。
このモードは、ソース値の切り捨てまたは特別処理を必要とない変換を許可します。すなわち、ターゲット型の範囲がソース型より広い変換を許可します。
前提として、レプリケーションの際に型変換は許容するがレプリカのデータ型に適合するために値を切り捨てる「不可逆変換」という挙動があり、これは ALL_LOSSY を設定することで実現できる。
例として INT 型を TINYINT 型へ変換することはできるが、TINYINT 型を INT 型へ変換することはできないと言った具合。
これは非不可逆変換が許可されることを暗示せず、不可逆変換を必要とするまたは変換をまったく必要としないケースのみが許可されることだけを暗示します。たとえば、このモードのみを有効にした場合、INT カラムが TINYINT に変換されること (不可逆変換) は許可されますが、TINYINT カラムが INT カラムに変換されること (非不可逆) は許可されません。
今回の解決策として設定した ALL_NON_LOSSY により VARTCHAR（254）-> VARCHAR(255)の変換を許容するようになったため、こういった挙動は「非不可逆変換」と捉えるのがよさそう。ALL_LOSSY,ALL_NON_LOSSY という値に設定すると不可逆変換かどうかに関わらず、全ての変換を許可するようになる。
このモードが設定されると、サポートされるすべての型変換が、不可逆変換かどうかにかかわらず、許可されます。
また、整数型の変換を取り扱う ALL_SIGNED 、ALL_UNSIGNED、ALL_SIGNED,ALL_UNSIGNED といった設定も存在する。
ALL_SIGNED
昇格される整数型を符号付き値として扱います (デフォルト動作)。
ALL_UNSIGNED
昇格される整数型を符号なし値として扱います。
ALL_SIGNED,ALL_UNSIGNED
昇格される整数型を、可能な場合符号付きとして、そうでない場合は符号なしとして扱います。
その他詳細については下記を参照。
17.5.1.9 ソースとレプリカで異なるテーブル定義を使用したレプリケーション
Discussion