MySQL Bugs: #22985: Checksum sometimes returns different value in 5.0 than it would in 4.1

Bug #22985	Checksum sometimes returns different value in 5.0 than it would in 4.1
Submitted:	4 Oct 2006 14:27	Modified:	18 Dec 2006 18:42
Reporter:	Olof Tjerngen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20a-max	OS:	Linux (Linux)
Assigned to:	Chad MILLER	CPU Architecture:	Any
Tags:	4.1, 5.0, bfsm_2006_11_02, checksum table

Description:
The same table when copied (using mysqlhotcopy) to a 5.0 installation and a 4.1 installation can give different result when running checksum table. It does not happen on every table, only a small subset.

From the 4.1 install:
mysql> checksum table test;
+-----------+-----------+
| Table     | Checksum  |
+-----------+-----------+
| test.test | 252773406 |
+-----------+-----------+
1 row in set (0.29 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.19    |
+-----------+
1 row in set (0.00 sec)

And here's from the other machine with 5.0:

mysql> checksum table test;
+-----------+------------+
| Table     | Checksum   |
+-----------+------------+
| test.test | 1200280873 |
+-----------+------------+
1 row in set (0.43 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.20a-max |
+-------------+
1 row in set (0.00 sec) 

Noticed when trying to compare two a master and slave, one with 5.0 and another with 4.1.

The binary table files were created on version 3.23 and have not been dumped/restored when upgrading.
The checksum is not stored in the table. "checksum table x quick" gives NULL.

I have not tried to do a mysqldump and then reloading that into both 4.1 and 5.0 .
Out of 20 tested tables, 3 gave different result on 5.0 and 4.1, the only thing they had in common is that they had more than 10 field, and a mix of float, int, varchar, text.

Repair table doesn't give any hint.

How to repeat:
Use my example table (available on request!):
- Copy the table to a 4.1 installation and a 5.0 installation.
- Run "checksum table" on both.
- See if the resulting checksum is the same on both installs or different.

Suggested fix:
Make it return the same result when using the same binary table file - verifying replication between 4.0 and 5.0 is difficult without it.

Thank you for a problem report. Please, send SHOR CREATE TABLE and SHOW TABLE STATUS results for your table (as private comment, if you want).

You should not use tables binary copied from 4.x.y in 5.0.x at all, especially if they have VARCHAR columns. Please, try to execute no-op ALTER TABLE (like ALTER TABLE test ENGINE=MyISAM) or any ALTER TABLE to get a table with version 10, instead of 9, then check that checksums again and inform about the results.

I did a "mysqldump -c -u root -p test test_table > test_table.sql" on the 4.1 machine.
Then I loaded the test_table into both the 4.1 and the 5.0 machine (with "mysql -u root -p test < test_table.sql" on each machine).

The version is now 9 och 4.1 and 10 on 5.0. The checksum is still different.

Output from 4.1:
mysql> show table status like 'test_table'\G
*************************** 1. row ***************************
           Name: test_table
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 29788
 Avg_row_length: 165
    Data_length: 4918584
Max_data_length: 4294967295
   Index_length: 2751488
      Data_free: 0
 Auto_increment: 29873
    Create_time: 2006-10-05 12:36:36
    Update_time: 2006-10-05 12:36:38
     Check_time: 2006-10-05 12:36:39
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> checksum table test_table\G
*************************** 1. row ***************************
   Table: test.test_table
Checksum: 1232380398
1 row in set (0.25 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.20    |
+-----------+
1 row in set (0.00 sec)

Output from 5.0:
mysql> show table status like 'test_table'\G
*************************** 1. row ***************************
           Name: test_table
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 29788
 Avg_row_length: 164
    Data_length: 4886644
Max_data_length: 281474976710655
   Index_length: 3161088
      Data_free: 0
 Auto_increment: 29873
    Create_time: 2006-04-05 02:30:11
    Update_time: 2006-04-05 02:30:14
     Check_time: 2006-04-05 02:30:15
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> checksum table test_table\G
*************************** 1. row ***************************
   Table: test.test_table
Checksum: 4268823047
1 row in set (0.32 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.20a-max |
+-------------+
1 row in set (0.00 sec)

Possibly related:

- Table w/ INT field results in same table CHECKSUM between 4.1 and 5.0.
- Table w/ VARCHAR field results in different table CHECKSUM between 4.1 and 5.0.

4.1.21 VARCHAR

create table foo (foo varchar(20));
insert into foo values ('foo');
checksum table foo;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.foo | 1078210171 |
+----------+------------+

5.0.26 VARCHAR

create table foo (foo varchar(20));
insert into foo values ('foo');
checksum table foo;
+----------+-----------+
| Table    | Checksum  |
+----------+-----------+
| test.foo | 885981883 |
+----------+-----------+

4.1.21 INT

create table bar (bar int);
insert into bar values (1);
checksum table bar;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.bar | 3459908756 |
+----------+------------+

5.0.26 INT

create table bar (bar int);
insert into bar values (1);
checksum table bar;
+----------+------------+
| Table    | Checksum   |
+----------+------------+
| test.bar | 3459908756 |
+----------+------------+

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have updated the description for CHECKSUM TABLE to add this paragraph:

The checksum value depends on the table row format. If the row format
changes, the checksum will change. For example, the storage format
for VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is 
upgraded to MySQL 5.0, the checksum value may change.