Bug #68314 mysqldump --set-gtid-purged=AUTO does not detect if mysqld has GTIDs
Submitted: 8 Feb 2013 13:37 Modified: 21 May 2013 22:28
Reporter: Leandro Morgado Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.10 OS:Any
Assigned to: Ritheesh Vedire CPU Architecture:Any

[8 Feb 2013 13:37] Leandro Morgado
Description:
When using 5.6.10 mysqldump against older GA versions (5.1, 5.5), it breaks with the following error: 

mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

WORKAROUND: force mysqldump to not use GTIDs with:
shell> mysqldump ... --set-gtid-purged=OFF

How to repeat:
shell> mysqldump --version
mysqldump  Ver 10.13 Distrib 5.6.10, for linux-glibc2.5 (x86_64)

shell> mysqldump -h127.0.0.1 -P5163 -A -uroot -pmsandbox --set-gtid-purged=AUTO
Warning: Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 5.6.10, for linux-glibc2.5 (x86_64)
--
-- Host: 127.0.0.1    Database: 
-- ------------------------------------------------------
-- Server version	5.1.63-enterprise-commercial-advanced

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193) 

shell> mysqldump -d mysql -uroot -p
Enter password: 
-- MySQL dump 10.13  Distrib 5.6.10, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version	5.5.14-enterprise-commercial-advanced-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Couldn't execute 'SELECT @@GTID_MODE': Unknown system variable 'GTID_MODE' (1193)

Suggested fix:
--set-gtid-purged=AUTO should detect if mysqld server being dumped has GTIDs enabled or not

Note that AUTO is the default value for --set-gtid-purged.
[25 Feb 2013 10:28] Van Stokes
Plus one. We get the same error when trying to dump from servers that haven't been upgraded to 5.6 yet. The dump fails.
[26 Feb 2013 4:49] Ritheesh Vedire
1. 5.6.5 introduced GTIDs. mysqldump 5.6 was made                             
          compatible  to use GTIDs                                                   
        2. In versions < 5.6, where GTIDs are not                            
          even defined, mysqldump 5.6 fails as it cannot                             
          execute 'SELECT @@GTID_MODE'                                               
        3.  using --set-gtid-purged=AUTO, should detect whether                        
          the server has GTIDs enabled or not.  However, this                        
          option works only for 5.6 versions.   

WORKAROUND: if you are using mysqldump 5.6 to backup mysql-5.5  and old database
           versions, use --set-gtid-purged=OFF option.

Fix:  THe fix is to check for the server version before executing 'select @@gtid_mode'
[26 Mar 2013 23:54] Luis Manuel Oliveira Soares
See also (likely dup): BUG#68775.
[21 May 2013 22:23] Daniel So
Move to Documenting stage as requested.
[21 May 2013 22:28] Daniel So
Added change log entry for MySQL 5.6.12 and 5.7.2:

'When using "mysqldump" to back up a database created with MySQL 5.6.4 or
an earlier version, setting "--set-gtid-purged=AUTO" caused the backup to
fail, because pre-5.6.5 versions of MySQL did not support GTIDs, and it
could not be determined if GTIDs were enabled for the database. This fix
makes sure "mysqldump" does not attempt to output a "SET
@@global.gtid.purged" statement when backing up any pre-5.6.5 databases.'