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
を使用する際はご注意ください。