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:
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.6.9 OS:Any
Assigned to: CPU Architecture:Any

[5 Jan 2013 20:55] Giuseppe Maxia
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
[8 Jan 2013 11:14] Nuno Carvalho
Hi Giuseppe,

Please use
show global variables like 'gtid_executed';

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.

[8 Jan 2013 12:10] Giuseppe Maxia
Thanks. Using "SHOW GLOBAL VARIABLES", is shows that GTID_EXECUTED is not empty.
[10 Jan 2013 12:42] Jon Stephens
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

[10 Jan 2013 12:43] Jon Stephens
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.