MySQL Bugs: #72679: Undocumented behavior when dumping mysql database using mysqldump utility

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

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.

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 */;

File attached but hidden from public.

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

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

We are also experiencing this when GTID mode is enabled. We are using MySQL 5.6.19.