MySQL Bugs: #115952: discrepancy in checksum b/w source/target table after migration

Bug #115952	discrepancy in checksum b/w source/target table after migration
Submitted:	28 Aug 2024 13:32	Modified:	28 Feb 12:08
Reporter:	Manish Dudi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
-- I have migrated an InnoDB table from MySQL 8.0.37 to MySQL 8.0.37 using mysqldump and mydumper. 

Before the mysqldump is taken, ran CHECKSUM command to calculate checksum.

mysql> checksum table t1;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| migrate.t1 | 1642722862 |
+----------------+------------+
1 row in set (0.00 sec)

-- After the dump is imported to target MySQL 8.0.37 successfully, the checksum value changes for the same table:

mysql> checksum table t1;
+-----------------+------------+
| Table           | Checksum   |
+-----------------+------------+
| migrate1.t1 | 1798840458 |
+-----------------+------------+
1 row in set (0.00 sec)

-- We can see that there is difference in checksum. MySQL documentation mentions that if the checksums for two tables are different, then it is almost certain that the tables are different in some way.

How to repeat:
Table DDL:

CREATE TABLE `t1` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` int NOT NULL,
  `c3` int unsigned NOT NULL,
  `c4` int unsigned DEFAULT NULL,
  `c5` int NOT NULL,
  `c6` float DEFAULT '0',
  `c7` float DEFAULT '0',
  `c8` float DEFAULT '0',
  `c9` float DEFAULT '0',
  `c10` float DEFAULT '0',
  `c11` float DEFAULT '0',
  `c12` varchar(45) DEFAULT '0',
  `c13` float DEFAULT '0',
  `c14` varchar(256) DEFAULT 'COMMENTS COMING SOON ...!',
  `c15` varchar(45) DEFAULT NULL,
  `c16` int DEFAULT NULL,
  `c17` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `id_UNIQUE` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=25531 DEFAULT CHARSET=latin1;

-- The table has 2000 rows (the dump of table data is attached)
-- Ran check table on the table in question before dump:

mysql> check table events;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| migrate.t1 | check | status   | OK       |
+----------------+-------+----------+----------+
1 row in set (0.00 sec)

-- Took a dump using mysqldump and restored it to target MySQL 8.0.37.
-- Checksum on source:

mysql> checksum table t1;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| migrate.t1 | 1642722862 |
+----------------+------------+
1 row in set (0.00 sec)

-- Checksum on target:

mysql> checksum table t1;
+-----------------+------------+
| Table           | Checksum   |
+-----------------+------------+
| migrate1.t1 | 1798840458 |
+-----------------+------------+
1 row in set (0.00 sec)

-- After this, took another dump using mydumper and migrated the data.

--> After migration, used mydumper to take dump from source and target using --checksum-all parameter:

mydumper -h host_name -u admin -p -B migrate --checksum-all -o /home/ubuntu/targetmydumper.sql

**********************
SOURCE Checksum:
**********************

[`migrate`.`t1`]
real_table_name=t1
rows = 2000
data_checksum = 1642722862
schema_checksum = 5F0BB893
indexes_checksum = 75DC6AC1

[`migrate`]
schema_checksum = 95DC8DDE
# Finished dump at: 2024-08-25 09:03:06

**********************
target Checksum:
**********************

[`migrate`.`t1`]
real_table_name=t1
rows = 2000
data_checksum = 1798840458
schema_checksum = 5F0BB893
indexes_checksum = 75DC6AC1

[`migrate`]
schema_checksum = 95DC8DDE
# Finished dump at: 2024-08-25 09:06:10

--> We can observe that there is a difference in data_checksum (same as CHECKSUM TABLE) whereas the schema_checksum and indexes_checksum do match. Also, note that the number of rows do match as well. I have verified the data as well using 3rd party tools and the data rows match too.

--> Attached are the dumps taken from source (sourcemydumper.sql) and target (targetmydumper.sql) using mydumper along with checksum.

Suggested fix:
The checksum of the tables should be same before and after the table migration is done to same version.

We had this problem when introducing verifiable backups - the checksums were failing on some of our tables between the source and destination database servers.

It turns out the issue was the FLOAT data type.

As per the MySQL docs (https://dev.mysql.com/doc/refman/8.4/en/problems-with-float.html) "...if you attempt to create a replica by dumping table contents with mysqldump on the source and reloading the dump file into the replica, tables containing floating-point columns might differ between the two hosts"

While visually the float data looked identical, the internal representation of the data was different enough that the checksums were different.

I suspect the same issue applies here as there are multiple float-type fields in  your table structure.

We resolved it by migrating the columns over to DECIMAL which for our use case was better anyway, but I don't think there is a fix for those who require FLOAT fields.

> While visually the float data looked identical, the internal representation of the data was different enough that the checksums were different.

Interesting.  I am curious if this impacts replication, to the point of not being able to replicate an update or delete to a row with different FLOAT representation.

> the dump of table data is attached

I guess it is attached as a private comment.

If Aled hypothesis is true, it should be relatively easy to identify one row causing the problem by "binary searching" in the table.  Once you have such row, it would be interesting to test if an update to it in FULL RBR (or delete) breaks replication.

> Interesting.  I am curious if this impacts replication, to the point of not being able to replicate an update or delete to a row with different FLOAT representation.

While I have no information on this specifically, what I can say is that whenever restoring the data, the checksum would always be the same (whether it was back to the source server or to a different target server) - it would only ever differ from the original source database.

It's possible (and this is purely conjecture) that the original data was inserted on a previous MySQL version and over time the way FLOATs are represented have changed but existing data doesn't get changed - as such I would expect a replica to the same MySQL version/architecture would not experience these issues.

Hello Manish Dudi,

Thank you for the report and feedback.
My sincere apologies for the delay in getting back to you on this.
I tried to reproduce but observed that provided source dump has missing tables( logical dumps have events table but create schema has only t1 table create details). Request you to provide dump taken using 'mysqldump'  instead of mydumper(you may want to mark it as private after uploading). Thank you.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".