MySQL Bugs: #68314: mysqldump --set-gtid-purged=AUTO does not detect if mysqld has GTIDs

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

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.

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.

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'

See also (likely dup): BUG#68775.

Move to Documenting stage as requested.

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.'