新特性解读 | MySQL 8.0 对 GTID 的限制解除

CREATE TABLE ... SELECT statements. CREATE TABLE ... SELECT
 （https://dev.mysql.com/doc/refman/5.7/en/create-table-select.html）statements are not allowed when using GTID-based replication. When binlog_format
 （https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format）is set to STATEMENT, a CREATE TABLE ... SELECT
statement is recorded in the binary log as one transaction with one GTID, but if ROW format is used, the statement is recorded as two transactions with two GTIDs. If a source used STATEMENT format and a replica used ROW format, the replica would be unable to handle the transaction correctly, therefore the CREATE TABLE ... SELECT
statement is disallowed with GTIDs to prevent this scenario.

Temporary tables. CREATE TEMPORARY TABLE
 （https://dev.mysql.com/doc/refman/5.7/en/create-table.html）and DROP TEMPORARY TABLE
 （https://dev.mysql.com/doc/refman/5.7/en/drop-table.html）statements are not supported inside transactions, procedures, functions, and triggers when using GTIDs (that is, when the enforce_gtid_consistency
 （https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency）system variable is set to ON
). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1
 （https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_autocommit）.

我们先来看下在 MySQL 5.7 下这一行为对事务的影响以及如何通过变通的方法绕过这些限制。

1. create table ... select ... : 这条语句本身是懒人写法，语义上分别属于两个隐式事务（一条 DDL 语句，一条 DML 语句）。但在 GTID 开启后，单个语句只能给它分配一个 GTID 事务号，如果强制使用，会直接报语句违反 GTID 一致性。比如下面例子：直接执行这条语句就会报错。

   mysql:ytt:5.7.34-log> create table trans1(id int primary key, log_date date);
   Query OK, 0 rows affected (0.03 sec)
   
   <mysql:ytt:5.7.34-log> insert trans1 values (1,'2022-01-02');
   Query OK, 1 row affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> create table trans2 as select * from trans1;
   ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
复制

需要注意的是拆分后第一条 DDL 语句的后续工作，是延迟建立索引还是根本不需要索引？如果是延迟建立索引，那很简单，使用 MySQL 的 create table ... like ... 语法就行。虽然 create table ... like ... 语法是直接克隆原表，索引也是立即创建，不过最终目标是一致的。示例如下：

<mysql:ytt:5.7.34-log> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql:ytt:5.7.34-log> create table trans2 like trans1;
Query OK, 0 rows affected (0.02 sec)

<mysql:ytt:5.7.34-log> insert trans2 select * from trans1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
复制

   <mysql:ytt:5.7.34-log> show binlog events in 'mysql-bin.000001'\G
   *************************** 1. row ***************************
     ...
   *************************** 3. row ***************************
      Log_name: mysql-bin.000001
           Pos: 154
    Event_type: Gtid
     Server_id: 100
   End_log_pos: 219
          Info: SET @@SESSION.GTID_NEXT= '00020135-1111-1111-1111-111111111111:1'
   *************************** 4. row ***************************
      Log_name: mysql-bin.000001
           Pos: 219
    Event_type: Query
     Server_id: 100
   End_log_pos: 316
          Info: use `ytt`; create table trans2 like trans1
   *************************** 5. row ***************************
      Log_name: mysql-bin.000001
           Pos: 316
    Event_type: Gtid
     Server_id: 100
   End_log_pos: 381
          Info: SET @@SESSION.GTID_NEXT= '00020135-1111-1111-1111-111111111111:2'
   *************************** 6. row ***************************
      Log_name: mysql-bin.000001
           Pos: 381
    Event_type: Query
     Server_id: 100
   End_log_pos: 452
          Info: BEGIN
   *************************** 7. row ***************************
      Log_name: mysql-bin.000001
           Pos: 452
    Event_type: Table_map
     Server_id: 100
   End_log_pos: 501
          Info: table_id: 112 (ytt.trans2)
   *************************** 8. row ***************************
      Log_name: mysql-bin.000001
           Pos: 501
    Event_type: Write_rows
     Server_id: 100
   End_log_pos: 552
          Info: table_id: 112 flags: STMT_END_F
   *************************** 9. row ***************************
      Log_name: mysql-bin.000001
           Pos: 552
    Event_type: Xid
     Server_id: 100
   End_log_pos: 583
          Info: COMMIT * xid=54 */
   9 rows in set (0.00 sec)
复制

如果是后一种，只需要复制表结构和数据，不要索引，那也可以用 create table ... like ... 语法创建好表结构，完了手工删除表索引。如果表比较多，可以写个简单脚本对索引批量删除。

1. 对于显式临时表的创建与删除：这样的 DDL 语句在 GTID 模式下也是禁止放在事务块里执行的（显式的 begin; commit; 或者存储过程、存储函数、触发器等大事务块）。直接在事务块里执行会报错：

   <mysql:ytt:5.7.34-log> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> create temporary table tmp(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.
复制

这种如何解决呢？官方也给出建议：把此类 DDL 语句放在事务块外面或者直接使用基于磁盘表的 DDL 语句来替代它。比如下面示例：在事务块外创建临时表，事务块内部引用临时表数据就行。

   <mysql:ytt:5.7.34-log> create temporary table tmp(id int,log_date date);
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> begin;
   Query OK, 0 rows affected (0.01 sec)
   
   <mysql:ytt:5.7.34-log> insert tmp values (100,'2022-10-21');
   Query OK, 1 row affected (0.01 sec)
   
   <mysql:ytt:5.7.34-log> insert trans1 select * from tmp;
   Query OK, 1 row affected (0.00 sec)
   Records: 1  Duplicates: 0  Warnings: 0
   
   <mysql:ytt:5.7.34-log> commit;
   Query OK, 0 rows affected (0.00 sec)
   
   <mysql:ytt:5.7.34-log> select * from trans1;
   +-----+------------+
   | id  | log_date   |
   +-----+------------+
   |   1 | 2022-01-02 |
   | 100 | 2022-10-21 |
   +-----+------------+
   2 rows in set (0.00 sec)
复制

MySQL 8.0 原生 DDL 原子性，所以连带就解除了这两个 GTID 的限制。

1. 对于 create table ... like ... 语句，在 MySQL 8.0 版本里只会生成一个 GTID 事务号，见下面 binlog 内容：0228ca56-db2f-11ec-83d3-080027951c4a:1 。

mysql:ytt:8.0.29>create table trans2 as select * from trans1;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

<mysql:ytt:8.0.29>show binlog events in 'binlog.000001'\G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 236
       Info: SET @@SESSION.GTID_NEXT= '0228ca56-db2f-11ec-83d3-080027951c4a:1'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 236
 Event_type: Query
  Server_id: 1
End_log_pos: 310
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000001
        Pos: 310
 Event_type: Query
  Server_id: 1
End_log_pos: 476
       Info: use `ytt`; CREATE TABLE `trans2` (
  `id` int NOT NULL,
  `log_date` date DEFAULT NULL
) START TRANSACTION
*************************** 6. row ***************************
   Log_name: binlog.000001
        Pos: 476
 Event_type: Table_map
  Server_id: 1
End_log_pos: 528
       Info: table_id: 349 (ytt.trans2)
*************************** 7. row ***************************
   Log_name: binlog.000001
        Pos: 528
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 571
       Info: table_id: 349 flags: STMT_END_F
*************************** 8. row ***************************
   Log_name: binlog.000001
        Pos: 571
 Event_type: Xid
  Server_id: 1
End_log_pos: 602
       Info: COMMIT * xid=8833 */
8 rows in set (0.00 sec)
复制

1. 对于事务块里有显式临时表的 DDL 语句，可以正常执行：

<mysql:ytt:8.0.29>reset master;
Query OK, 0 rows affected (0.02 sec)

<mysql:ytt:8.0.29>begin;
Query OK, 0 rows affected (0.01 sec)

<mysql:ytt:8.0.29>create temporary table tmp(a int,b date);
Query OK, 0 rows affected (0.00 sec)

<mysql:ytt:8.0.29>insert into tmp values (10,'2022-12-31');
Query OK, 1 row affected (0.00 sec)

<mysql:ytt:8.0.29>insert trans1 select * from tmp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

<mysql:ytt:8.0.29>commit;
Query OK, 0 rows affected (0.01 sec)

<mysql:ytt:8.0.29>table trans1;
+----+------------+
| id | log_date   |
+----+------------+
|  1 | 2022-07-07 |
| 10 | 2022-12-31 |
+----+------------+
2 rows in set (0.00 sec)
复制

这样生成的 GTID 事务号里（0228ca56-db2f-11ec-83d3-080027951c4a:1）只包含对磁盘表 trans1 的写入记录：

mysql:ytt:8.0.29>show binlog events in 'binlog.000001'\G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
   Log_name: binlog.000001
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 236
       Info: SET @@SESSION.GTID_NEXT= '0228ca56-db2f-11ec-83d3-080027951c4a:1'
*************************** 4. row ***************************
   Log_name: binlog.000001
        Pos: 236
 Event_type: Query
  Server_id: 1
End_log_pos: 310
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: binlog.000001
        Pos: 310
 Event_type: Table_map
  Server_id: 1
End_log_pos: 362
       Info: table_id: 405 (ytt.trans1)
*************************** 6. row ***************************
   Log_name: binlog.000001
        Pos: 362
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 405
       Info: table_id: 405 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: binlog.000001
        Pos: 405
 Event_type: Xid
  Server_id: 1
End_log_pos: 436
       Info: COMMIT /* xid=9374 */
7 rows in set (0.00 sec)
复制