MySQLでstatement-based replicationを使っている場合のtrigger, function, procedureの扱い

row-basedなreplicationの場合はreplicationの対象が文字通りrow=dataであるため特にtrigger, function, procedureの動作について懸念点はないと思われます。でも、MySQLで既に多くの人が使っているであろうstatement-basedなreplicationの場合はどうなるの？masterだけで実行されるの？それとも両方で実行されるの？両方で実行されるとしたらデータの整合性とかはどうなるの？

これらの疑問への答えはだいたい以下のドキュメントに書いてあるようですが、日本語版は「お前は何を言っているんだ」という部分が多々あり、英語版を読んでもあまり実際の動作がイメージできなかったので確認を兼ねて自分で試すことにしました。

• http://dev.mysql.com/doc/refman/5.1/en/replication-features-triggers.html
• http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

環境は手元のOS Xの mysql5.1.63 (with MySQL::Sandbox) です。

master [localhost] {msandbox} (test) > select @@version;
+------------+
| @@version  |
+------------+
| 5.1.63-log |
+------------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.01 sec)

trigger編

以下のようなtableがあると思いねえ。test2, test3も同じスキーマだと思いねえ。

| test1 | CREATE TABLE `test1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `blood_type` enum('A','B','O','AB') DEFAULT 'A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

以下のようにtest2, test3には予めデータを入れておきます

master [localhost] {msandbox} (test) > SELECT * FROM test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  1 | foo  | A          |
+----+------+------------+
1 rows in set (0.00 sec)

master [localhost] {msandbox} (test) > SELECT * FROM test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  1 | foo  | A          |
+----+------+------------+
1 rows in set (0.00 sec)

master, slaveで異なるtriggerを用意するため、masterでcreate -> slaveでdrop -> slaveでcreateという手順を取ります

master:

CREATE TRIGGER after_insert_test1 AFTER INSERT ON test1 FOR EACH ROW DELETE FROM test2;

slave:

DROP TRIGGER after_insert_test1;
CREATE TRIGGER after_insert_test1 AFTER INSERT ON test1 FOR EACH ROW DELETE FROM test3;

さて、ここで以下のSQLをmasterに流すとどうなるか。

insert into test1 (name, blood_type) values ("foo", "AB");

1. masterでのみtriggerが発動してtest2のデータが消える。masterにおけるtest2への変更がslaveに伝播され、test2のデータが消える。
2. masterでtriggerが発動してtest2のデータが消え、slaveでtriggerが発動してtest3のデータが消える。masterにおけるtest2への変更はslaveに伝播されない。
3. masterでtriggerが発動してtest2のデータが消え、slaveでtriggerが発動してtest3のデータが消える。masterにおけるtest2への変更はslaveに伝播され、slaveにおいてもtest2のデータが消える。

statement-basedなreplicationでは「master, slaveの両方でtriggerが発動する（かつ、trigger内のステートメントはレプリケーションされない）」ため、正解は2のようです。

master:

master [localhost] {msandbox} (test) > select * from test2;
Empty set (0.00 sec)

master [localhost] {msandbox} (test) > SELECT * FROM test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  1 | foo  | A          |
+----+------+------------+
1 rows in set (0.00 sec)

slave:

slave1 [localhost] {msandbox} (test) > SELECT * FROM test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  1 | foo  | A          |
+----+------+------------+
1 rows in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from test3;
Empty set (0.00 sec)

function編

trigger編で入れたデータやらtriggerは消した上で、以下のようにtest2, test3には予めデータを入れておきます。
idの値が色々アレしてるのは気にしない方向で。色々あったのです。

master [localhost] {msandbox} (test) > select * from test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  8 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  3 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

さっきのtriggerと同じようなことをするfunctionをmaster, slaveのそれぞれに定義します。
MySQLのfunctionは基本的にその中でデータに対する操作を行うためのものではないようですが、ここでは実験のために敢えてそういうfunctionを定義しています。WHERE句なしのDELETEの結果は常に同じになるはずなので、DETERMINISTICといえるのかしらん？この辺は適当です。とにかくここではレプリケーションできればOK。

master:

delimiter //
CREATE FUNCTION f1 (id INT) RETURNS INT DETERMINISTIC BEGIN DELETE FROM test2; RETURN 1; END//
delimiter ;

slave:

DROP FUNCTION f1;
delimiter //
CREATE FUNCTION f1 (id INT) RETURNS INT DETERMINISTIC BEGIN DELETE FROM test3; RETURN 1; END//
delimiter ;

以下のようなSQLをmasterにて投げます

select f1(1);

結果は…

master:

master [localhost] {msandbox} (test) > select * from test2;
Empty set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  3 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

slave:

slave1 [localhost] {msandbox} (test) > select * from test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  8 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from test3;
Empty set (0.00 sec)

triggerと同じ挙動のようですね。

procedure編

function編で入れたデータやらfunctionは消した上で、以下のようにtest2, test3には予めデータを入れておきます。

master [localhost] {msandbox} (test) > select * from test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  9 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  4 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

以下略

master:

CREATE PROCEDURE p1 (IN id INT) DELETE FROM test2;

slave:

DROP PROCEDURE p1;
CREATE PROCEDURE p1 (IN id INT) DELETE FROM test3;

masterでこのSQLを以下略

CALL p1(1);

結果は

master:

master [localhost] {msandbox} (test) > select * from test2;
Empty set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  4 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

slave:

slave1 [localhost] {msandbox} (test) > select * from test2;
Empty set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  4 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

procedureのみ動作が異なり、一番初めに示したドキュメントの "Stored procedure calls are logged at the statement level rather than at the CALL level. That is, the server does not log the CALL statement, it logs those statements within the procedure that actually execute. As a result, the same changes that occur on the master will be observed on slave servers. This prevents problems that could result from a procedure having different execution paths on different machines." とあるように、procedureのCALL自体は伝播されず、その中で発行されたステートメントだけがレプリケーションされるもようです。

trigger + procedure編

最後におまけで、triggerの中でprocedureを実行しているような場合はどうなるか。

(略)

master [localhost] {msandbox} (test) > select * from test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
| 10 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  5 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

(略)

master:

CREATE PROCEDURE p1 (IN id INT) DELETE FROM test2;
CREATE TRIGGER after_insert_test1 AFTER INSERT ON test1 FOR EACH ROW CALL p1(1);

slave:

DROP PROCEDURE p1;
CREATE PROCEDURE p1 (IN id INT) DELETE FROM test3;

# triggerの定義は同じなので作りなおす必要なし

(略)

insert into test1 (name) values ("bar");

結果

master:

master [localhost] {msandbox} (test) > select * from test2;
Empty set (0.00 sec)

master [localhost] {msandbox} (test) > select * from test3;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
|  5 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

slave:

slave1 [localhost] {msandbox} (test) > select * from test2;
+----+------+------------+
| id | name | blood_type |
+----+------+------------+
| 10 | bar  | A          |
+----+------+------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select * from test3;
Empty set (0.00 sec)

triggerは両側で実行され、trigger内のステートメントはprocedure含めてレプリケーションされていないようですね。

これ以外にも組み合わせは多数あると思いますが、めんどくさいからMySQLでそんなフクザツなことをしている人はあまり居ないだろうということで、これ以上の検証はやめにしました。

まとめ

少なくともmysql5.1.63においては、

• triggerの実行はレプリケーションされ、master/slaveのそれぞれで実行される。trigger内で実行されたステートメントはレプリケーションされない。
• functionの実行はレプリケーションされ、master/slaveのそれぞれで実行される。function内で実行されたステートメントはレプリケーションされない。
• procedureのCALLはレプリケーションされない。procedure内で実行されたステートメントがレプリケーションされる。

という挙動のようです。誤認があれば指摘を頂けるとありがたいです。

triggerとfunctionはmaster/slaveの双方で実行される以上、definerに指定されたユーザがそもそも居なかったり、userのgrantが不適切な場合はおかしげなことになると思われます。（というか、なりました）