MySQL Bugs: #108206: Dump utility does not chunk deterministically, which breaks differential backups

Bug #108206	Dump utility does not chunk deterministically, which breaks differential backups
Submitted:	20 Aug 2022 21:04	Modified:	22 Aug 2022 12:01
Reporter:	Steve Drangus	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Shell Dump & Load	Severity:	S3 (Non-critical)
Version:	mysqlsh Ver 8.0.29 for Linux on x86_64	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86
Tags:	Backup, DETERMINISTIC, differential incremental image, dump, mysqlsh, shell

Description:
The dump utility produces chunked file output which differ between dumps even when no rows are changed. The number of rows dumped per chunk changes between runs, as does the file size. This causes all files to checksum differently even when none of the rows have actually changed in a table. This is problematic for very large databases where the goal is to transfer a little data to the backup server as possible (i.e. a differential backup), because none of the files can be skipped. 

In my case, almost all (out of 2TB compressed) data is historical and unchanged between dumps. But with the current behaviour all of it has to be transferred with each backup run because the chunks don't checksum between runs. The dump is being ordered by an autoindexing key, so if the chunks were split consistently by number of rows, it would be fine. But the row count per chunk is inconsistent, hence the problem.

How to repeat:
Make two separate dumps of a large table using chunking, without any changes being made to the table. Checksum the resulting files between dumps.

Suggested fix:
Split chunks consistently by number of rows.

Hi Mr. Drangus,

Thank you for your bug report.

However, we do need a fully repeatable test case, in the form of the SQL file containing all the necessary commands, including the table with its contents.

We would also like you to test your script on 8.0.30, because it has a lot of improvement in the category of dumping and loading. Read further on .....

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php 

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.