Bug #72679 Undocumented behavior when dumping mysql database using mysqldump utility
Submitted: 19 May 2014 12:14 Modified: 17 Jul 2014 20:27
Reporter: Van Stokes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6.17, 5.6.21 OS:Any
Assigned to: CPU Architecture:Any
Tags: binlog, MySQL, mysqldump, regression, replication, sql_log_bin
Triage: Needs Triage: D2 (Serious)

[19 May 2014 12:14] Van Stokes
Description:
OS: Ubuntu 14.04 LTS(x86_64)
Sever: MySQL 5.6.17 (x86_64) (Ubuntu distro)
Client: mysqldump  Ver 10.13 Distrib 5.6.17, for debian-linux-gnu (x86_64)

When using mysqldump and dumping just the mysql database with no additional command line parameters, the mysqldump utility adds SET @@SESSION.SQL_LOG_BIN= 0; to the output. However, if you add addition database command line parameters, this behavior is not present.

We are unsure what the correct behavior is suppose to be because the documentation does not mention this behavior. We suspect it is an undocumented restriction.

How to repeat:
$ mysqldump --host=ahost -p mysql > mysql.sql

Notice that the following lines are present at the beginning of the file:
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

HOWEVER, if you add one or more command line database parameter options, this behavior is not present (i.e. the SET @@SESSION.SQL_LOG_BIN= 0; is not present):

$ mysqldump --host=ahost -p --set-gtid-purged=OFF --flush-privileges mysql > mysql.sql

Notice that the previous "SET" lines are missing from the beginning of the file.

Suggested fix:
Verify the behavior.
Update the documentation to explain the behavior.
[19 May 2014 14:56] Miguel Solorzano
I couldn't repeat. Please provide you my.cnf. Thanks.

-- MySQL dump 10.13  Distrib 5.6.17, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version	5.6.17-0ubuntu0.14.04.1-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 */;
[19 May 2014 17:04] Van Stokes
File attached but hidden from public.
[19 May 2014 17:07] Van Stokes
-- MySQL dump 10.13  Distrib 5.6.17, for debian-linux-gnu (x86_64)
--
-- Host: ll2    Database: mysql
-- ------------------------------------------------------
-- Server version 5.6.17-1+deb.sury.org~precise+1-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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--
[17 Jul 2014 20:27] Sveta Smirnova
Thank you for the feedback.

Verified as described. To repeat this bug one has to have binary log turned ON. I simply started MTR as "./mtr --start-dirty rpl_bug73065" where option files for rpl_bug73065 have: "--gtid-mode=on --enforce-gtid-consistency=true --log_slave_updates" Bug is repeatable not only with mysql database, but with any other. Not exists in 5.5, there is no documentation at http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
[26 Mar 2015 19:12] Mike Lee
We are also experiencing this when GTID mode is enabled. We are using MySQL 5.6.19.