Bug #110758 mysqldump uses FLUSH TABLES without NO_WRITE_TO_BINLOG option

• Submitted: 21 Apr 2023 11:10
• Modified: 21 Apr 2023 12:59
• Reporter: Przemysław Budny
• Status: Not a Bug
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any

[21 Apr 2023 11:10] Przemysław Budny

Description:
mysqldump  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

When the option --set-gtid-purged is set to AUTO | ON 

2023-04-21T10:54:32.493364Z	2963430 Query	FLUSH TABLES
2023-04-21T10:54:32.564411Z	2963430 Query	FLUSH TABLES WITH READ LOCK

FLUSH TABLES is replicated which may cause replication delays as a replica is waiting for the command to be processed

On a replica:
>show slave status\G
Slave_SQL_Running_State: Waiting for table flush

How to repeat:
Set the replication chain with a primary and a secondary node.

Execute on the master node:
>create database test;
>create table test(id int);

on the replica node
>lock table test1 read;

on the master node:
>FLUSH TABLES;

on the replica node
>show slave status\G

The replica node has got the status:
Slave_SQL_Running_State: Waiting for table flush

Suggested fix:
Use NO_WRITE_TO_BINLOG for FLUSH TABLES:

FLUSH NO_WRITE_TO_BINLOG TABLES

[21 Apr 2023 11:12] Przemysław Budny

I used the option --single-transaction

[21 Apr 2023 12:59] MySQL Verification Team

Hi Mr. Budny,

Thank you for your bug report.

If you have read our Reference Manual, thoroughly, you would have learned that:

* you have used two incompatible options in the same command

* you have used the option that is forced to flush tables

* there are already options for the binary log in the mysqldump options .....

* many customers and users rely on all of the statements to be replicated

Hence, it is not a bug.