MySQL Bugs: #68038: mysqldump with GTID fails to restore with puzzling error

Bug #68038	mysqldump with GTID fails to restore with puzzling error
Submitted:	5 Jan 2013 20:55	Modified:	8 Jan 2013 12:10
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.6.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
using a complete dump of a MySQL slave with GTID enabled, I tried to reload the SQL produced by mysqldump

1) dump
mysqldump --all-databases --triggers --routines --events > slave.sql

2) restore
mysql -h SLAVE -p$SLAVE_PORT < slave.sql
ERROR 1840 (HY000) at line 24: GTID_PURGED can only be set when GTID_EXECUTED is empty.

mysql -h SLAVE -p$SLAVE_PORT -e "show variables like 'gtid_executed'";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+

GTID_EXECUTED looks empty.

Either the error message is wrong or GTID_EXECUTED status is not updated

How to repeat:
see above

Hi Giuseppe,

Please use
show global variables like 'gtid_executed';
instead.

gtid_executed has global and session scopes:
 global: contains a representation of the set of all transactions that are logged in the binary log;
 session: contains a representation of the set of transactions that are written to the cache in the current session.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_gtid_executed

Thanks. Using "SHOW GLOBAL VARIABLES", is shows that GTID_EXECUTED is not empty.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.6+, documented as follows in the 5.6.11 and 5.7.1 changelogs:

        In order to provision or to restore a server using GTIDs, it is
        possible to set @@GLOBAL.GTID_PURGED to a given GTID set
        specifying the transactions that were imported. This operation
        requires that @@GLOBAL.GTID_EXECUTED as well as
        @@GLOBAL.GTID_PURGED are empty, in order to avoid possible
        actions that would override server executed GTIDs. When this
        requirement was not met, the error message -GTID_PURGED can only
        be set when GTID_EXECUTED is empty- did not specify the scope of
        the affected variables. 

        In order to avoid confusion, error messages that refer to 
        variables relating to GTIDs now include the variable scope.

Closed.