Shulou(Shulou.com)06/01 Report--

In the same transaction (statement), the changes of transaction and non-transaction data tables cannot be involved at the same time, which will result in a transaction corresponding to multiple GTID, violating the one-to-one correspondence principle between transaction and GTID.

[root@mysql.sock] [db1] > show create table T2\ G

* * 1. Row *

Table: t2

Create Table: CREATE TABLE `t2` (

`id`int (11) NOT NULL AUTO_INCREMENT

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

[root@mysql.sock] [db1] > start transaction

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock] [db1] > insert into T1 select null

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

[root@mysql.sock] [db1] > insert into T2 select null

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.

[root@mysql.sock] [db1] > commit

Query OK, 0 rows affected (0.01 sec)

Cannot use CREATE TABLE... SELECT statement.

[root@mysql.sock] [db1] > create table T2 select * from T2

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE. SELECT.

CREATE TEMPORARY TABLE, and DROP TEMPORARY TABLE statements cannot be used in transactions, stored procedures, functions, and triggers.

[root@mysql.sock] [db1] > start transaction

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock] [db1] > create temporary table T4 (id int auto_increment primary key)

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.

[root@mysql.sock] [db1] > rollback

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock] [db1] > create temporary table T4 (id int auto_increment primary key)

Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock] [db1] > desc T4

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

+-+ +

1 row in set (0.00 sec)

[root@mysql.sock] [db1] > drop temporary table T4

Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock] [db1] > desc T4

ERROR 1146 (42S02): Table 'db1.t4' doesn't exist

Turn on the-- enforce-gtid-consistency parameter to prevent the execution of statements that violate GTID-based replication principles.

Skip the execution of the transaction, the parameter sql_slave_skip_counter no longer works, and you need to skip it by injecting an empty transaction as follows.

SET GTID_NEXT='aaa-bbb-ccc-ddd:N'

BEGIN

COMMIT

SET GTID_NEXT='AUTOMATIC'

The IGNORE_SERVER_IDS in the CHANGE MASTER TO statement is no longer working, and transactions that have been played back are skipped automatically.

When you use mysqldump to export data on an instance with GTID enabled, @ @ SESSION.SQL_LOG_BIN= 0 is set in the backup file, and no binary log is recorded when you use the backup file to import data to the target instance.

When you use mysql_upgrade to upgrade the MySQL version in place on an instance with GTID enabled, you can either write a binary log (this is also the default behavior of mysql_upgrade, which is not enabled-- write-binlog).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.