MySQL 5.5.22 と 5.5.24 のステートメントベースレプリケーションの変更点

MySQL

MySQL 5.5.22 と 5.5.24 でステートメントベースのレプリケーションで気になる変更点があったので調べてみました。どちらも binlog_format = statement でテストしています。


MySQL 5.5.22

Statements that wrote to tables with AUTO_INCREMENT columns based on an unordered SELECT from another table could lead to the master and the slave going out of sync, as the order in which the rows are retrieved from the table may differ between them. Such statements include any INSERT ... SELECT, REPLACE ... SELECT, or CREATE TABLE ... SELECT statement. Such statements are now marked as unsafe for statement-based replication, which causes the execution of one to throw a warning, and forces the statement to be logged using the row-based format if the logging format is MIXED. (Bug #11758263, Bug #50440)


まず、次のようなテーブルを作成します。

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT, KEY(b));
CREATE TABLE t3 (a INT, b INT AUTO_INCREMENT, KEY(b));


以下のようなSQLを実行すると、ログに警告が表示されます。

INSERT INTO t2(a) SELECT * FROM t1;
REPLACE INTO t3(a) SELECT * FROM t1;
CREATE TABLE t4 (a INT, b INT AUTO_INCREMENT, KEY(b)) SELECT * FROM t1;


警告の内容は以下の通りです。SELECT の結果の順番はマスターとスレーブで異なる可能性があるため、AUTO_INCREMENT による連番がマスターとスレーブで異なるかもしれないからです(MySQL Bugs: #50440: mark [INSERT|REPLACE|CREATE]...SELECT unsafe if updated table has autoinc column)。

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: INSERT INTO t2(a) SELECT * FROM t1


REPLACE の場合は追加で下記も表示されます。これは以前からあったものです。

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. Statement: REPLACE INTO t3(a) SELECT * FROM t1

MySQL 5.5.24

INSERT ON DUPLICATE KEY UPDATE is now marked as unsafe for statement-based replication if the target table has more than one primary or unique key. For more information, see Section 16.1.2.3, “Determination of Safe and Unsafe Statements in Binary Logging”.

まず、次のようなテーブルを作成します。

CREATE TABLE x1
(
  a int not null,
  b int not null,
  c int not null,
  primary key (a),
  unique key (b)
);


以下のようなSQLを実行すると、ログに警告が表示されます。

INSERT INTO x1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;


警告の内容は以下の通りです。テーブルに主キーやユニークキーが複数あると、検査の順番が保証されていないため、どちらが先に検査されるかによって更新される行が変わってしまうらしいです(MySQL Bugs: #58637: Mark INSERT...ON DUPLICATE KEY UPDATE unsafe when there is more than one key)。

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO x1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1


例えば、↑のテーブルに下記のように行が挿入されている場合・・・

INSERT INTO x1 (a,b,c) VALUES (1,1,1);
INSERT INTO x1 (a,b,c) VALUES (2,2,2);


このSQLでどちらの行が更新されるかは 不定 ということです。

INSERT INTO x1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;