MySQL Bugs: #109700: Fix for #33630199 in 8.0.32 not reflected in the documentation and release notes

Bug #109700	Fix for #33630199 in 8.0.32 not reflected in the documentation and release notes
Submitted:	19 Jan 2023 10:52	Modified:	31 May 2023 2:51
Reporter:	Przemyslaw Malkowski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump, set-gtid-purged

Description:
In the documentation: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_set-gtid-purged we can find a warning, quoting:

"Using this option with the --single-transaction option can lead to inconsistencies in the output. If --set-gtid-purged=ON is required, it can be used with --lock-all-tables, but this can prevent parallel queries while mysqldump is being run."

While in 8.0.32 source code we can find a fix for Bug #33630199, which addresses the problem described as follows:

"Dumping data using mysqldump with --single-transaction and
--set-gtid-purged=ON, while on the other session, INSERT
queries are being sent, the dump file generated has inconsistent
data. That is, the backup data has the gtids but not the
corresponding data." 

Commit reference: https://github.com/mysql/mysql-server/commit/022e73ba6976b984658a1c2652178cd4b81aec28

This changes the mysqldump behavior when --single-transaction and
--set-gtid-purged=ON is used, from previous (8.0.31):

2023-01-19T09:30:28.779920Z        23 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2023-01-19T09:30:28.779960Z        23 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-01-19T09:30:28.780050Z        23 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2023-01-19T09:30:28.782992Z        23 Query     SELECT @@GLOBAL.GTID_EXECUTED
2023-01-19T09:30:28.783065Z        23 Query     UNLOCK TABLES

To mysqldump 8.0.32 behavior:

2023-01-19T09:16:32.392912Z        20 Query     FLUSH TABLES
2023-01-19T09:16:32.473719Z        20 Query     FLUSH TABLES WITH READ LOCK
2023-01-19T09:16:32.473836Z        20 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2023-01-19T09:16:32.473902Z        20 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2023-01-19T09:16:32.474038Z        20 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2023-01-19T09:16:32.479419Z        20 Query     SELECT @@GLOBAL.GTID_EXECUTED
2023-01-19T09:16:32.479565Z        20 Query     UNLOCK TABLES

This change is not reflected in the release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html

Also, I assume the fix also makes the documentation warning obsolete, or at least the change should be reflected there as well?

Moreover, bug #105761 seems to be a duplicate of #33630199, but it wasn't updated at all...

How to repeat:
Analyze the related bug notes and 8.0.32 code changes.

Suggested fix:
Update the relevant documentation.

Hello Przemyslaw,

Thank you for the report. 

Thanks,
Umesh

This also duplicates https://bugs.mysql.com/bug.php?id=109685
where the author claims that this may not be just a documentation bug because after the 8.0.32 patch, the generated dump will include FTWRL that will require additional privileges that were not necessary before.

Posted by developer:
 
Thank you, this is an interesting situation. The related mysqldump documentation deserves attention and an update, as you described.

Related, the increased privilege requirement for --single-transaction added in 8.0.32 was reduced in 8.0.33 to situations where both gtid_mode = ON (default is OFF) and --set-gtid-purged = AUTO|ON (default is AUTO).

Posted by developer:
 
The 8.0.32 release notes were updated in February to include the following:

  Limitation: This fix adds a requirement for the RELOAD privilege when using --single-transaction to execute FLUSH TABLES WITH READ LOCK; the MySQL team is investigating a solution.

Unfortunately this bug report wasn't closed at the time, sorry for the delay.

The bug regression was fixed in 8.0.33.

Posted by developer:
 
Note: several related documentation changes were committed today that hopefully help clarify the situation.