第40回　カラムのデータ型の異なる行ベースレプリケーションの挙動

MySQL 5.7以降からbinlog_formatオプションのデフォルトがROWになりました。MySQL5.6とそれ以前ではステートメントベースレプリケーション（SBR）がデフォルトでしたが、MySQL5.7以降からは行ベースレプリケーション（RBR）がデフォルトになったということです。SBRとRBRの違いについては第34回　行ベースレプリケーションの遅延についてをご確認ください。

今回は、slave_type_conversionsオプションによるカラムのデータ型の異なるRBRの挙動について紹介します。

slave_type_conversionsオプションとは

このオプションはRBRを使用するときにスレーブに対して設定するもので、カラムの型変換モードを制御します。マニュアルはslave_type_conversionsになります。デフォルトは''（空文字）であり、マスターとスレーブの間のカラムの型変換をしません。つまり、デフォルト設定のRBRではマスターとスレーブのカラムのデータ型が全て同じでなくてはならないということです。

では、マスターとスレーブ間でカラムのデータ型が異なるとどのような挙動になるか確認してみましょう。

マスターの設定

RBRの設定であることを確認します。

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

スレーブの設定

slave_type_conversionsがデフォルトの''（空文字）であることを確認します。

mysql> select @@slave_type_conversions;

+--------------------------+
| @@slave_type_conversions |
+--------------------------+
|                          |
+--------------------------+

マスターで`t0`テーブルを作成

mysql> CREATE TABLE `t0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

スレーブでカラム`col`のvarchar(5) から varchar(10)に変更

mysql> alter table t0 modify col varchar(10);

これで、マスターのカラムcolの型はvarchar(5)でスレーブのカラムcolの型はvarchar(10)という状態になります。ここでマスターで更新すると、カラムの型が違うためエラーが発生してレプリケーションが停止します。

マスターで更新

mysql> insert into t0(col) values ('ABCDE');

スレーブでレプリケーションエラー

Last_SQL_Error: Column 1 of table 'aa.t0' cannot be converted from type 'varchar(5)' to type 'varchar(10)'

slave_type_conversionsオプションにモードを設定することでこれを回避することができます。有効なモードは以下のようになります。

モード	内容	有効なバージョン
ALL_LOSSY	不可逆変換を許可します	MySQL5.5以降
ALL_NON_LOSSY	非不可逆変換を許可します	MySQL5.5以降
ALL_SIGNED	昇格される整数型を符号付き値として扱います	MySQL5.6.13以降
ALL_UNSIGNED	昇格される整数型を符号なし値として扱います	MySQL5.6.13以降
''(空文字)	すべての変換を許可しません	MySQL5.5以降

このオプションは動的に変更可能ですが、レプリケーションの再起動が必要です（STOP SLAVEとSTART SLAVE⁠）⁠。

以下、各モードについて説明します。

ALL_LOSSY

このモードはスレーブのカラムの型がマスターのカラムの型よりも小さい場合、データを切り捨ててしまうかもしれない変換（不可逆変換）を許可します。Int型とBigint型や、varchar型とchar型などの互換性のある型であれば、レプリケーションされたデータをスレーブの型に合わせてデータを適合するということです。

たとえば、マスターがvarchar(20)でスレーブがvarchar(10)だった場合に、20文字のデータがレプリケーションされたときは10文字目までは格納して、それ以降は切り捨てて登録されます。マスターとスレーブ間でデータの不整合が起こる可能性のあるモードです。

ALL_LOSSYの動作

前述で使用したテーブルを使って試してみます。スレーブの型をvarchar(1)に変更しています。

スレーブで設定

mysql> select @@slave_type_conversions;

+--------------------------+
| @@slave_type_conversions |
+--------------------------+
| ALL_LOSSY                |
+--------------------------+

mysql> alter table t0 modify col varchar(1);
Query OK, 0 rows affected (0.00 sec)

マスターで更新

mysql> insert into t0(col) values ('ABCDE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | ABCDE|
+----+------+
1 rows in set (0.00 sec)

スレーブで確認

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | A    |
+----+------+
1 rows in set (0.00 sec)

このように、マスターでは更新した内容が、スレーブの型のvarchar(1)に切り詰められて登録されています。

ALL_NON_LOSSY

このモードは前述と同様に、互換性のある型であれば、スレーブのカラムの型がマスターのカラムの型よりも大きい場合の変換（非不可逆変換）を許可します。

たとえば、マスターがvarchar(5)でスレーブがvarchar(200)だった場合に、エラーにならずにレプリケーションされます。マスターとスレーブでデータは同じものになります。

ALL_NON_LOSSYの動作

こちらも前述で使用したテーブルを使って試してみます。スレーブの型をvarchar(200)に変更しています。

スレーブで設定

mysql> select @@slave_type_conversions;

+--------------------------+
| @@slave_type_conversions |
+--------------------------+
| ALL_NON_LOSSY            |
+--------------------------+

mysql> alter table t0 modify col varchar(200);
Query OK, 0 rows affected (0.00 sec)

マスターで更新

mysql> insert into t0(col) values ('ABCDE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | ABCDE|
+----+------+
1 rows in set (0.00 sec)

スレーブで確認

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | ABCDE|
+----+------+
1 rows in set (0.00 sec)

エラーなく登録されていることがわかります。

以下のようにカンマ区切りで複数のモードを設定することも可能です。

mysql> set global slave_type_conversions='ALL_LOSSY,ALL_NON_LOSSY';

この2つのモード組み合わせると、スレーブの型がマスターの型よりも大小関わらずエラーなくレプリケーション可能です。

また、この2つのモードは型変換をチェックする際に転送されてきたデータ内容は見ていません。カラムのデータ型のみ比較してチェックします。

ALL_SIGNEDとALL_UNSIGNED

続いて、これらのモードはtinyint型からsmallint型へ、smallint型からint型へなどの昇格する整数の型変換を行うときに、ALL_SIGNEDは符号付きとして値を扱い、ALL_UNSIGNEDは符号なしとして値を扱います。指定がない場合はALL_SIGNEDがデフォルトで動作します。前述のALL_LOSSY、ALL_NON_LOSSY、またはその両方が設定されていなければ効果はありません。

たとえば、マスターでtinyint型のカラムを作成してスレーブでsmallint型に変換した場合、それぞれのモードどのような値になるか、結果は以下の表のようになります。

モード	マスターの値	スレーブの値
ALL_SIGNED	-128	-128
ALL_SIGNED	127	127
ALL_UNSIGNED	-128	128
ALL_UNSIGNED	127	127

このように、ALL_UNSIGNEDの場合は符号なしとして扱うため、値が変わります。これらのモードが追加されたのは、MySQL5.6.13より前は符号の情報が保持されなかったため、昇格する整数の型変換に対して予期せぬ値が入るバグがあったためです。

ちなみに、この2つのモード組み合わせると、ALL_SIGNED、ALL_UNSIGNEDの順に評価されます。

ALL_LOSSYとSTRICT_TRANS_TABLESの関係

sql_modeオプションにSTRICT_TRANS_TABLESというモードがあります。これを有効にしていると、更新するデータ内容がカラムの型より大きい場合、データを切り捨てて登録するのを防ぎます。たとえば、varchar(2)のカラムに対してABCという文字列を挿入しようとすると、エラーとなり更新できません。

mysql>  insert into t1 ( strict_col ) values ( 'ABC');
ERROR 1406 (22001): Data too long for column 'strict_col' at row 1

では、もしスレーブでデータの切り捨てを許さないSTRICT_TRANS_TABLESが有効、かつデータの切り捨てを許すALL_LOSSYが設定されていて、レプリケーションされてきたデータが切り捨てなくては登録できないデータであった場合どうなるでしょうか。

答えは、ALL_LOSSYが優先されて、スレーブがデータを切り捨てて登録します。

ちなみに、slave_type_conversionsオプションはRBRにのみ影響するオプションなので、SBRの場合はSTRICT_TRANS_TABLESの影響により、エラーが発生してレプリケーションが停止します。

こちらも前述のテーブルを使用して試してみましょう。

スレーブの設定

varchar(2)へ変更します。

mysql> select @@slave_type_conversions;

+--------------------------+
| @@slave_type_conversions |
+--------------------------+
| ALL_LOSSY                |
+--------------------------+　

mysql> set global sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t0 modify col varchar(2);
Query OK, 0 rows affected (0.00 sec)

マスターの設定

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

マスターで更新

mysql> insert into t0(col) values ('ABCDE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | ABCDE|
+----+------+
1 rows in set (0.00 sec)

スレーブで確認

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | AB   |
+----+------+
1 rows in set (0.00 sec)

このようにエラーはなく、データが切り捨てられて登録されてました。

続いて、SBRで試してみます。マスターでSBRになるように変更して実行します。

スレーブの設定

varchar(2)へ変更します。

mysql> select @@slave_type_conversions;

+--------------------------+
| @@slave_type_conversions |
+--------------------------+
| ALL_LOSSY                |
+--------------------------+

mysql> set global sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t0 modify col varchar(2);
Query OK, 0 rows affected (0.00 sec)

マスターの設定

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+

マスターで更新

mysql> insert into t0(col) values ('ABCDE');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t0;
+----+------+
| id | col  |
+----+------+
|  1 | ABCDE|
+----+------+
1 rows in set (0.00 sec)

スレーブで確認

mysql> show slave status¥G

Last_Error: Error 'Data too long for column 'col' at row 1' on query. Default database: 'aa'. Query: 'insert into t0(col) values ('ABCDE')'

このように、STRICT_TRANS_TABLESの影響により、エラーが発生してレプリケーションが停止します。

まとめ

今回は、slave_type_conversionsオプションによるテーブル定義の異なるRBRの挙動について紹介しました。これを使用すれば、カラムの変更などのレプリケーションの遅延が発生するAlter Table文を、第30回　InnoDBオンラインDDLについてで紹介したレプリケーションの遅延を防ぐ方法で実行も可能です。

ただし、互換性のあるデータ型のみ対応していることと、マスターとスレーブでデータの不整合が起こりうるALL_LOSSYを使用する際はご注意ください。

slave_type_conversionsオプションとは

マスターの設定

スレーブの設定

マスターでt0テーブルを作成

スレーブでカラムcolのvarchar(5) から varchar(10)に変更

マスターで更新

スレーブでレプリケーションエラー

ALL_LOSSY

ALL_LOSSYの動作

スレーブで設定

マスターで更新

スレーブで確認

ALL_NON_LOSSY

ALL_NON_LOSSYの動作

スレーブで設定

マスターで更新

スレーブで確認

ALL_SIGNEDとALL_UNSIGNED

ALL_LOSSYとSTRICT_TRANS_TABLESの関係

スレーブの設定

マスターの設定

マスターで更新

スレーブで確認

スレーブの設定

マスターの設定

マスターで更新

スレーブで確認

まとめ

マスターで`t0`テーブルを作成

スレーブでカラム`col`のvarchar(5) から varchar(10)に変更