MySQL Bugs: #91915: --set-gtid-purged has incorrect behavior with doc

Bug #91915	--set-gtid-purged has incorrect behavior with doc
Submitted:	7 Aug 2018 8:57	Modified:	7 Aug 2018 10:46
Reporter:	liu shun	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqlpump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.7.22, 5.7.23	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
As doc says:
The --set-gtid-purged option has the following effect on binary logging when the dump file is reloaded:
    --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.
    --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.
    --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON). 

mysqlpump with --set-gtid-purged=off, still has "SET @@SESSION.SQL_LOG_BIN=0" in output.

How to repeat:
1.dump test database for example:
[apps@10.132.30.63(Unknow) tmp]$ /apps/svr/mysql57/bin/mysqlpump -uroot -p'***********' -S/tmp/mysql3306.sock test --default-parallelism=4  --set-gtid-purged=off --single-transaction --users  --result-file=test.sql --log-error-file=test.log 

2.check dump file.
[apps@10.132.30.63(Unknow) tmp]$ more test.sql 
-- Dump created by MySQL pump utility, version: 5.7.22, linux-glibc2.12 (x86_64)
-- Dump start time: Tue Aug  7 16:20:27 2018
-- Server version: 5.7.22

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;

Suggested fix:
..

..

Hello liu shun,

Thank you for the report.
Observed this with 5.7.23.

Thanks,
Umesh

在mysql8.0.16&mysql8.0.18 也存在这个问题，手动清除日志中关于sql_log_bin的设置，可以正常使用。

Still an issue with MySQL 8.0.22.
Note that it works fine with mysqldump.

Can you please fix this bug already?

2023 and i was bitten by this, mysql 8.0.35. I went crazy for 3 weeks because i was mounting a new innodb cluster and when i restored backup dump, 3 databases go out of sync silently without errors... Guess what? Was that damn SQL_LOG_BIN= 0; and I call mysqlpump with --set-gtid-purged-off...

And i would consider this more than non-critical, the mysqlpump is not working as intended and this would break (and it does) a replication so fast that you would't known what punch you.

The patch will correct the behavior. It will only print the disable blinlog query before the GTID_PURGED.

Attachment: fix_bug_91915.patch (application/octet-stream, text), 898 bytes.