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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump, set-gtid-purged

[19 Jan 2023 10:52] Przemyslaw Malkowski
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.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.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.
[19 Jan 2023 15:43] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report. 

[23 Jan 2023 14:33] Yura Sorokin
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.
[22 Feb 2023 19:20] Philip Olson
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).
[31 May 2023 2:51] Philip Olson
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.
[31 May 2023 3:36] Philip Olson
Posted by developer:
Note: several related documentation changes were committed today that hopefully help clarify the situation.