Bug #82159 mysql.gtid_executed is not updated. after restore.
Submitted: 8 Jul 2016 5:51 Modified: 9 Aug 2016 13:30
Reporter: ookido ookido Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.13 OS:CentOS
Assigned to: CPU Architecture:Any

[8 Jul 2016 5:51] ookido ookido
Description:
slave global variables gtid_executed is reversed. after restore.

Is this specification or bug?
you recommend to add ' --flush-logs' ?

How to repeat:
###
### MASTER
###

[root@MASTER ~]# mysqldump --all-databases -p --triggers --routines --events > db.dmp
Enter password:

[root@MASTER ~]# grep 'INSERT INTO `gtid_executed` VALUES' db.dmp
INSERT INTO `gtid_executed` VALUES ('XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN',1,10248);

[root@MASTER ~]# grep GLOBAL.GTID_PURGED db.dmp
SET @@GLOBAL.GTID_PURGED='XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669';

# mysql.gtid_executed and @@GLOBAL.GTID_PURGED have a diff.
# but MASTER is not a problem.
# It will be reflected in the automatic

###
### SLAVE
###

[root@SLAVE ~]# mysql -p < db.dmp

[root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;'
Enter password:
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN |              1 |        10248 |
+--------------------------------------+----------------+--------------+

[root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";'
Enter password:
+---------------+----------------------------------------------+
| Variable_name | Value                                        |
+---------------+----------------------------------------------+
| gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10669 |
+---------------+----------------------------------------------+

[root@SLAVE ~]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@SLAVE ~]#

[root@SLAVE ~]# mysql -p -e 'select * from mysql.gtid_executed;'
Enter password:
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN |              1 |        10248 |
+--------------------------------------+----------------+--------------+

[root@SLAVE ~]# mysql -p -e 'show global variables like "gtid_executed";'
Enter password:
+---------------+----------------------------------------------+
| Variable_name | Value                                        |
+---------------+----------------------------------------------+
| gtid_executed | XXXXXXXX-YYYY-ZZZZ-AAAA-NNNNNNNNNNNN:1-10248 |
+---------------+----------------------------------------------+

global variables gtid_executed is reversed.
but 10249-10669 has already been written.
Replication error occurs.

Suggested fix:
mysqldump --all-databases -p --triggers --routines --events  --flush-logs > db.dmp
[12 Jul 2016 19:45] Bryan Aldridge
Seems similar to http://bugs.mysql.com/bug.php?id=81692 which has been open over a month. It'd be great to get some sort of update on this.
[9 Aug 2016 13:30] Sinisa Milivojevic
Hi,

Thank you for your report. mysqldump, in the way that you have used it, and replication are two totally independent subjects. Mysqldump does not work as part of the replication. 

Hence, as you dumped mysql database, so was the table in question, when it was its turn. Table was dumped in the mode that it was in that nanosecond. Later, it might be changed by the replication process.

Hence, not a bug.