MySQL Bugs: #72392: inconsistent data in GTID (reset master, stop slave, start slave)

Bug #72392	inconsistent data in GTID (reset master, stop slave, start slave)
Submitted:	19 Apr 2014 12:01	Modified:	20 Feb 2015 18:15
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GTID, inconsistent data

Description:
When using GTID, in the function "request_dump", it says it will remove last_retrieved_gtid only if it is not part of executed_gtid_set.

But if we run 
  stop slave;
  reset master;
  start slave;
in the slave, this design will lead to an extra transaction on slave.

How to repeat:
#double (4,4) appear in the result of the follow test case.

--source include/have_gtid.inc
--source include/master-slave.inc
--let $rpl_skip_start_slave= 1
--source include/rpl_set_gtid_mode.inc

--connection slave
change master to master_auto_position=1;
start slave;

--connection master
create table t1 (a int, b int) engine=innodb;
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t1 values(4,4);

--sync_slave_with_master
--connection slave
select * from t1;

reset master;
stop slave;
start slave;

--sleep 1
--connection slave
select * from t1;

connection master;
drop table t1;
--sync_slave_with_master
--source include/rpl_end.inc

Suggested fix:
In fact, the design is to avoid partial transaction events in relay-log. So in this logic, another check can be involved: check whether there is a commit or xid event after the GTID_LOG_EVENT that related last_retrieved_gtid.

If yes, do not remove it from executed_set.

Hello Xiaobin,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

//

./mysql-test-run.pl --mysqld=--gtid-mode=on --mysqld=--enforce-gtid-consistency --mysqld=--log-slave-updates 72392
Logging: ./mysql-test-run.pl  --mysqld=--gtid-mode=on --mysqld=--enforce-gtid-consistency --mysqld=--log-slave-updates 72392
2014-04-23 04:25:12 32462 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-04-23 04:25:12 32462 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-04-23 04:25:12 32462 [Note] Plugin 'FEDERATED' is disabled.
2014-04-23 04:25:12 32462 [Note] Binlog end
2014-04-23 04:25:12 32462 [Note] Shutting down plugin 'MyISAM'
2014-04-23 04:25:12 32462 [Note] Shutting down plugin 'CSV'
MySQL Version 5.6.17
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/data/ushastry/server/mysql-5.6.17/mysql-test/var'...
Installing system database...
Using server port 38384

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

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

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
include/rpl_set_gtid_mode.inc
change master to master_auto_position=1;
start slave;
create table t1 (a int, b int) engine=innodb;
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t1 values(4,4);
select * from t1;
a       b
1       1
2       2
3       3
4       4
reset master;
stop slave;
start slave;
select * from t1;
a       b
1       1
2       2
3       3
4       4
4       4
drop table t1;
include/rpl_end.inc
main.72392                               [ pass ]  24860
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 24.860 of 38 seconds executing testcases

Completed: All 1 tests were successful.

// 5.7.5 is also shows extra row

]$ ./mysql-test-run.pl --mysqld=--gtid-mode=on --mysqld=--enforce-gtid-consistency --mysqld=--log-slave-updates 72392
Logging: ./mysql-test-run.pl  --mysqld=--gtid-mode=on --mysqld=--enforce-gtid-consistency --mysqld=--log-slave-updates 72392
MySQL Version 5.7.5
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/data/ushastry/server/mysql-5.7.5/mysql-test/var'...
Installing system database...

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

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

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
include/rpl_set_gtid_mode.inc
change master to master_auto_position=1;
start slave;
create table t1 (a int, b int) engine=innodb;
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t1 values(4,4);
select * from t1;
a       b
1       1
2       2
3       3
4       4
reset master;
stop slave;
start slave;
select * from t1;
a       b
1       1
2       2
3       3
4       4
4       4
drop table t1;
include/rpl_end.inc

Thanks for your feedback. The fix will be available in an upcoming release.

The following was added to the 5.7.6 changelog with commit 5243:
When using a GTID based replication slave with auto positioning enabled, there was a possibility that the last fully received transaction could be requested again by the slave I/O thread when the GTID of the transaction was not in the slave's gtid_executed set. This situation could occur for example if the SQL thread had not applied the transaction or a RESET MASTER statement was issued on the slave to clean up its gtid_executed set. The fix ensures that a GTID based replication slave using auto positioning does not ask for a fully received transaction twice, regardless of the slave's gtid_executed set, and it now only adds a GTID to the Retrieved_Gtid_Set when the whole transaction has been received.