バイナリログ出力形式と GTID と一時テーブルの仕様変更について

2019年11月15日

この記事は最終更新から5年以上経過しています。内容が古くなっている可能性があります。

はじめに

MySQL においてグローバルトランザクション識別子(以後 GTID)を使用してレプリケーションを構築することや、その機能を前提とした MySQL InnoDB Cluster の普及など、最近は GTID が気軽に使用されてきているように感じます。
今回は MySQL 8.0.13 において変更された GTID の仕様変更から、バイナリログの出力形式(binlog_format) と一時テーブルの出力がどのように変化してきているかについて調査をおこないます。

内容

GTID ベースレプリケーションを利用する際の制約

GTID を利用する際には、主に以下のような制約があることが一般的に知られています。なお、その他の内容については「17.1.3.6 Restrictions on Replication with GTIDs」に詳しく記述されています。

MyISAM などの非トランザクションストレージエンジンを含む更新と InnoDB などのトランザクションストレージエンジンを含む更新は同一のトランザクション内で実行できません

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.innodb VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test.myisam VALUES(1);
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.innodb VALUES(1);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test.myisam VALUES(1);

ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

CREATE TABLE … SELECT は実行できません

mysql> CREATE TABLE test.t2 SELECT * FROM test.t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

1 2	mysql> CREATE TABLE test.t2 SELECT * FROM test.t1; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

CREATE TEMPORARY TABLE や DROP TEMPORARY TABLE は、トランザクション、プロシージャ、関数、およびトリガー内で使用できません

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE test.tmp1(id INT);
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.  These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE test.tmp1(id INT);

ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

今回はこのうち、CREATE/DROP TEMPORARY TABLE に関する仕様が変更となりました。

MySQL 8.0.13 からの変更点

引用 : Changes in MySQL 8.0.13 (2018-10-22, General Availability)

From MySQL 8.0.13, this restriction has been removed when binlog_format is set to ROW or MIXED. With row-based logging in use, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements can now be used inside transactions, procedures, functions, or triggers
(…)
When binlog_format is set to ROW or MIXED, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not written to the binary log and are therefore not replicated to slaves.

これまでは、GTID の使用時にトランザクション内で実行できなかった CREATE/DROP TEMPORARY TABLE が、binlog_format = STATEMENT 以外の場合は許可されるように変更されました。

また、後半部分の説明では、binlog_format が ROW あるいは MIXED の場合、CREATE/DROP TEMPORARY TABLE がバイナリログに出力されない旨の内容が書かれていますが、ここで、一時テーブルに関する以下の挙動との不一致が気になりました。

レプリケーションにおける一時テーブルの挙動

引用 : 【MySQL 5.7 版】16.4.1.29 Replication and Temporary Tables

The discussion in the following paragraphs does not apply when binlog_format=ROW because, in that case, temporary tables are not replicated; this means that there are never any temporary tables on the slave to be lost in the event of an unplanned shutdown by the slave. The remainder of this section applies only when using statement-based or mixed-format replication.

一時テーブルがバイナリログに出力されるかどうかの問題を聞くと、上記の内容が思い浮かびます。MySQL 5.7 までは、一時テーブルの内容がバイナリログに記録されないのは binlog_format = ROW のときのみでした。そのため、MIXED の場合は一時テーブルの更新がバイナリログに出力されてしまい、レプリケーションスレーブを停止する場合には Slave_open_temp_tables の値を気に掛ける必要がありました。

MySQL 8.0 からの変更点

引用 : 【MySQL 8.0 版】17.5.1.30 Replication and Temporary Tables

In MySQL 8.0, when binlog_format is set to ROW or MIXED, statements that exclusively use temporary tables are not logged on the master, and therefore the temporary tables are not replicated.

MySQL 8.0 からは、binlog_format が ROW あるいは MIXED の場合に、一時テーブルがバイナリログに出力されないように変更されているようです。MIXED は安全でないステートメントを透過的に ROW ベースで出力してくれるフォーマットのはずなので、むしろ今までが仕様に合っていなかったと言えるかもしれません。

バイナリログにおける CREATE/DROP TEMPORARY TABLE の出力

バイナリログ上における一時テーブルの出力、特に DROP TEMPORARY TABLE の出力という観点のみで考えると、さらに問題は細分化します。

引用 : Changes in MySQL 5.7.25 (2019-01-21, General Availability)

Now, when a temporary table is created in a session, the binary logging format is tracked. The DROP TEMPORARY TABLE IF EXISTS statement is only logged at the end of the session if statement-based format was in effect when the temporary table was created, so the CREATE TEMPORARY TABLE statement was logged. If row-based or mixed-format binary logging was in use when the table was created, the DROP TEMPORARY TABLE IF EXISTS statement is not logged.

MySQL 5.7 においても、binlog_format=ROW であれば一時テーブルの更新内容についてはバイナリログに記録されていませんでしたが、DROP TEMPORARY TABLE あるいはセッションの切断時には、バイナリログに暗黙的に以下のような出力がおこなわれており、GTID も増加していました。

# at 259
#191111 19:00:22 server id 1  end_log_pos 375 CRC32 0xeec3d544  Query   thread_id=3     exec_time=0     error_code=0
(...)
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
(...)
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp1`
/*!*/;

# at 259

#191111 19:00:22 server id 1 end_log_pos 375 CRC32 0xeec3d544 Query thread_id=3 exec_time=0 error_code=0

(...)

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

(...)

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `tmp1`

/*!*/;

そのため MySQL 5.7.25 からは、binlog_format=ROW であれば、DROP TEMPORARY TABLE でもバイナリログに出力されないように変更がおこなわれているようです。
なお、上記のマニュアルでは MIXED も対象になっているような記載がありますが、MySQL 5.7 では MIXED の場合一時テーブルはバイナリログに出力されるはずのため、ドキュメント側の不整合である可能性があります。

検証

これらの、一時テーブルにおけるバイナリログ上の出力や GTID 使用時の挙動について、具体的にどのように挙動が変化しているか、簡単な検証をおこなってみました。

検証環境

検証環境は、GTID ベースレプリケーションを使用した 2 台構成で、それぞれ以下のバージョンにおいて、binlog_format を変更(STATEMENT, MIXED, ROW)しながら実施しています。

MySQL 5.7.24 (Bug#83003修正前)
MySQL 5.7.28 (最新版)
MySQL 8.0.12 (GTID の仕様変更前)
MySQL 8.0.18 (最新版)

検証手順

以下の項目について確認をおこないました。

1. バイナリログに一時テーブルの更新内容が出力されるかどうか

mysql> CREATE TEMPORARY TABLE test.tmp1(id INT);
mysql> INSERT INTO test.tmp1 VALUES(1);

1 2	mysql> CREATE TEMPORARY TABLE test.tmp1(id INT); mysql> INSERT INTO test.tmp1 VALUES(1);

2. バイナリログに「 DROP /!40005 TEMPORARY / TABLE (…)」が出力されるかどうか

(一時テーブルを作成したままセッションを切断)
mysql> quit
(あるいは、明示的に一時テーブルを削除)
mysql> DROP TEMPORARY TABLE test.tmp1;

(一時テーブルを作成したままセッションを切断)

mysql> quit

(あるいは、明示的に一時テーブルを削除)

mysql> DROP TEMPORARY TABLE test.tmp1;

3. トランザクション内で CREATE TEMPORARY TABLE が実行できるかどうか

mysql> BEGIN;
mysql> CREATE TEMPORARY TABLE test.tmp1(id INT);

1 2	mysql> BEGIN; mysql> CREATE TEMPORARY TABLE test.tmp1(id INT);

検証結果

上記の内容について検証したところ、以下のような結果になりました。

この表を確認すると、MySQL 8.0.12 まではバイナリログへの出力と GTID の制約が一貫していない印象を受けるのに対して、MySQL 8.0.18 では直感的な挙動になっていることがわかります。

まとめ

ここまで、バイナリログの出力形式と GTID と一時テーブルの挙動の推移について確認してきました。MySQL 8.0.13 で変更になった仕様変更は GTID の導入しやすさにもつながるため、是非 MySQL 5.7 にもバックポートしてほしい機能ですね。

よかったらシェアしてね！

URLをコピーしました！