MySQL Bugs: #77393: Failure to generate GTID leads to inconsistency

Bug #77393	Failure to generate GTID leads to inconsistency
Submitted:	17 Jun 2015 16:58	Modified:	22 Mar 2016 21:47
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.25, 5.6.26, 5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	exhausted, GTID

Description:
If generating a GTID for a transaction fails, the transaction is not written to the binary log but still gets committed, which potentially leads to master/slave data inconsistency.

How to repeat:
mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> RESET MASTER;
Query OK, 0 rows affected (0.07 sec)

mysql> SET GLOBAL gtid_purged = CONCAT(@@GLOBAL.server_uuid, ':1-9223372036854775805');
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (2);
ERROR 1775 (HY000): Impossible to generate Global Transaction Identifier: the integer component reached the maximal value. Restart the server with a new server_uuid.
mysql> SELECT * FROM t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

Ensure transaction is rolled back on failure to generate a GTID (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: gtid-rollback-on-flush-failure.patch (application/octet-stream, text), 1.98 KiB.

Hello Davi,

Thank you for the report and contribution.
Confirmed this with 5.6.26/5.7.8 builds.

Thanks,
Umesh

// 5.6.26

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.26/mysql-test: ./mtr --mysqld='--gtid_mode=ON' --mysqld='--log-bin' --mysqld='--log-slave-updates' --mysqld='--enforce-gtid-consistency' 77393
Logging: ./mtr  --mysqld=--gtid_mode=ON --mysqld=--log-bin --mysqld=--log-slave-updates --mysqld=--enforce-gtid-consistency 77393
2015-06-18 08:00:46 0 [Note] /export/umesh/server/binaries/mysql-5.6.26/bin/mysqld (mysqld 5.6.26-enterprise-commercial-advanced) starting as process 4262 ...
2015-06-18 08:00:46 4262 [Note] Plugin 'FEDERATED' is disabled.
2015-06-18 08:00:46 4262 [Note] Binlog end
2015-06-18 08:00:46 4262 [Note] Shutting down plugin 'CSV'
2015-06-18 08:00:46 4262 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.26
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.6.26/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
#
# Ensure transaction is rolled back on failure to generate a GTID
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
RESET MASTER;
SET GLOBAL gtid_purged = CONCAT(@@GLOBAL.server_uuid, ':1-9223372036854775805');
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
ERROR HY000: Impossible to generate Global Transaction Identifier: the integer component reached the maximal value. Restart the server with a new server_uuid.
SELECT * FROM t1;
a
1
2
RESET MASTER;
SET GLOBAL gtid_purged = '';
DROP TABLE t1;
RESET MASTER;

// 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8/mysql-test: ./mtr --mysqld='--gtid_mode=ON' --mysqld='--log-bin' --mysqld='--log-slave-updates' --mysqld='--enforce-gtid-consistency' 77393
Logging: ./mtr  --mysqld=--gtid_mode=ON --mysqld=--log-bin --mysqld=--log-slave-updates --mysqld=--enforce-gtid-consistency 77393
MySQL Version 5.7.8
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/binaries/mysql-5.7.8/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
#
# Ensure transaction is rolled back on failure to generate a GTID
#
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
RESET MASTER;
SET GLOBAL gtid_purged = CONCAT(@@GLOBAL.server_uuid, ':1-9223372036854775805');
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
ERROR HY000: Impossible to generate Global Transaction Identifier: the integer component reached the maximal value. Restart the server with a new server_uuid.
SELECT * FROM t1;
a
1
2
RESET MASTER;
SET GLOBAL gtid_purged = '';
DROP TABLE t1;
RESET MASTER;

Thanks for your feedback. This has been fixed in upcoming versions and the following was added to the 5.6.29 and 5.7.11 change logs:
If generating a GTID for a transaction fails, the transaction is not written to the binary log but still gets committed. Although running out of GTIDs is a rare situation, if it did occur an error was written to the binary log as a sync stage error. With binlog_error_action=ABORT_SERVER, the server aborts on such an error, avoiding data inconsistency. When binlog_error_action=IGNORE_ERROR, the server continues binary logging after such an error, potentially leading to data inconsistency between the master and the slave. The fix changes the error to be correctly logged as a flush stage error.

Bug #80138 marked as duplicate of this

This wasn't properly fixed, reported Bug#80828.