Is there any reason why identical tables in MySQL 5.0 might have different checksums, and if so, is there any way to fix this?
I have two replicated databases that inexplicably have a different checksum. The first time this happened it disappeared before I could properly replicate it and ask this question, and now it's happened for the second time in the same week.
When I manually check the tables, they are exactly the same, i.e. the checksums of every row correspond identically, the table structure is identical, and they're running on the same version of MySQL (5.0.96-community-nt-log), more over the DB replication (Slave_IO_Running and Slave_SQL_Running) reports normal operation.
How can the checksums possibly be different?
Database A:
MySQL [libertyintelligence]> select md5(concat(LS_MapAssetLinkId,LS_Map,LS_Asset,LS_X,LS_Y,LS_MinZoom,LS_MaxZoom,LS_ZOrder)) from ls_mapassetlink;
+----------------------------------+
| md5(...) |
+----------------------------------+
| c3c060c7c514ac3e410b50c0eb0f63f9 |
| a97a117c63f9a2472b96e517c303e42f |
| 946dacb76c8e2919d1ce791211da660c |
| 112a10fe77558beca686ec2c9281ce7d |
| 271b8de08f13252ff0e58ac20590c9ce |
| 837e88605f4a7b1e0f2a78d40bf8ce1f |
+----------------------------------+
6 rows in set (0.00 sec)
MySQL [libertyintelligence]> checksum table ls_mapassetlink;
+-------------------------------------+------------+
| Table | Checksum |
+-------------------------------------+------------+
| libertyintelligence.ls_mapassetlink | 1670286008 |
+-------------------------------------+------------+
1 row in set (0.00 sec)
Database B:
MySQL [libertyintelligence]> select md5(concat(LS_MapAssetLinkId,LS_Map,LS_Asset,LS_X,LS_Y,LS_MinZoom,LS_MaxZoom,LS_ZOrder)) from ls_mapassetlink;
+----------------------------------+
| md5(...) |
+----------------------------------+
| c3c060c7c514ac3e410b50c0eb0f63f9 |
| a97a117c63f9a2472b96e517c303e42f |
| 946dacb76c8e2919d1ce791211da660c |
| 112a10fe77558beca686ec2c9281ce7d |
| 271b8de08f13252ff0e58ac20590c9ce |
| 837e88605f4a7b1e0f2a78d40bf8ce1f |
+----------------------------------+
6 rows in set (0.00 sec)
MySQL [libertyintelligence]> checksum table ls_mapassetlink;
+-------------------------------------+-----------+
| Table | Checksum |
+-------------------------------------+-----------+
| libertyintelligence.ls_mapassetlink | 123437249 |
+-------------------------------------+-----------+
Or specifically, one DB shows the table checksum as 1670286008 while the other as 123437249, when the data shows me that the tables are identical.
Both my co-workers and I have checked this independently (independently connected to the DB, independently checked, etc.), and are both stumped. The only reason I can find that checksums might be different are if I was using different DB versions, but these are the same version too.
Table schema The schema is identical on both systems:
CREATE TABLE `ls_mapassetlink` (
`LS_MapAssetLinkId` bigint(20) unsigned NOT NULL auto_increment,
`LS_Map` bigint(20) unsigned NOT NULL,
`LS_Asset` bigint(20) unsigned NOT NULL,
`LS_X` double NOT NULL,
`LS_Y` double NOT NULL,
`LS_MinZoom` int(11) NOT NULL,
`LS_MaxZoom` int(11) NOT NULL,
`LS_ZOrder` int(11) NOT NULL,
PRIMARY KEY (`LS_MapAssetLinkId`),
KEY `INDEX_MapAssetLink_Map` (`LS_Map`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
