Bug #37007 Maria: different checksum for MyISAM table depending on CHECKSUM=0|1
Submitted: 27 May 2008 14:36 Modified: 5 Sep 2008 16:31
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Maria storage engine Severity:S3 (Non-critical)
Version:5.1-maria OS:Any
Assigned to: Michael Widenius CPU Architecture:Any

[27 May 2008 14:36] Guilhem Bichot
Description:
Specific of MyISAM in Maria tree.
In this tree, CHECKSUM TABLE was changed regarding how it treats NULL values.
The --old option is supposed to enable the old (5.1) CHECKSUM TABLE.
But:
./mtr --mem --start-and-exit innodb --mysqld=--old
mysql> drop table if exists t; create table t(a int null) engine=myisam checksum=0;insert into t values(null);checksum table t;
| test.t | 205926545 |
mysql> drop table if exists t; create table t(a int null) engine=myisam checksum=1;insert into t values(null);checksum table t;
| test.t | 4278190080 | # UUUUH
mysql> drop table if exists t; create table t(a int null) engine=innodb checksum=0;insert into t values(null);checksum table t;
| test.t | 205926545 | # ok

Running without --old, MyISAM gives same checksum as InnoDB, both with CHECKSUM=0|1 : 4278190080.

How to repeat:
run queries above.

Suggested fix:
mi_checksum.c has to handle --old like mysql_checksum_table() does I guess
[28 Jun 2008 9:10] Michael Widenius
Ths is not a bug.

The --old flag only affects how CHECK TABLE ... EXTENDED works.

All engines with checksum are supposed to use the new way to calculate checksums, independent of the--old flag.

Ie, the old way to calculate checksums are deprecated and one should move away from it ASAP. We also don't want to have two ways to calculate checksums in the MyISAM and Maria code.  (In the MyISAM code we do have two ways to be able to support old checksum for old tables).

For those that want to check that tables are identical when moving from 5.1 to 5.1-maria or 6.0 should on their new installation run mysqld with --old and use checksum table xxx extended if they want to verify the checksum against an 5.0 installation.

If the above logic will not work, another way to do this would be to split the HA_HAS_CHECKSUM flag to two flags: HA_HAS_CHECKSUM and HA_HAS_CHECKSUM_NEW. Then we could decide to automaticly use the MySQL level checksum if the table supports a different checksum method than indicated by --old
[28 Jun 2008 12:00] Michael Widenius
After some thinking, I implemented the last option in my previous comments

checksum table now works as follows:

If you run mysqld --old option, then:
  - If table is myisam with live old checksum it returns the live checksum
  - If we use QUICK option return NULL
  - Calculate checksum by looping over all row and calculate it the 5.0 way

If you run mysqld without --old, then:
  - If table is myisam with live new checksum it returns the live checksum
  - If we use QUICK option return NULL
  - Calculate checksum by looping over all row and calculate it the new way

In addition I added that check table detects if we are using MyISAM tables with VARCHAR columns and ROW_FORMAT=fixed and row CHECKSUM enabled and reports that these must be repaired. This is because in the past these tables gave different checksums if you did run with or without EXTENDED and you also got different checksum for different storage engines. This is bug is fixed in the new myisam code but you need to do an REPAIR or ALTER table to enable the new checksum code for the table.
[30 Jun 2008 18:00] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:02] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:03] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:04] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:06] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:08] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:10] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:12] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:13] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:14] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:16] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:17] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:17] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:28] Bugs System
Pushed into 5.1.25-maria-alpha
[30 Jun 2008 18:37] Bugs System
Pushed into 5.1.25-maria-alpha
[5 Sep 2008 16:31] MC Brown
The following note has been added to the 5.1.25-maria changelog: 

Important Note: The behavior of CHECKSUM TABLE has been modified so that the checksum operates consistently within all en- 
gines by default, unless you use the --old option. 
If you run mysqld --old option, then: 
• If table is MyISAM with an old-style checksum (5.0), then CHECKSUM TABLE returns the 5.0 checksum. 
• If the QUICK option is specified to CHECKSUM TABLE, NULL is returned instead. 
• When the EXTENDED option is specified, the checksum is calculated by looping over all the rows and calculates the checksum 
using the 5.0 algorithm. 
If you run mysqld without the --old option, then: 
• If the table is MyISAM with the new-style live checksum (5.1), then the live checksum is returned. 
• If the QUICK option to OPTIMIZE TBLE, NULL is returned. 
• When the EXTENDED option is specified, the checksum is calculated by looping over all the rows and calculating the checksum 
using the 5.1 algorithm. 
In addition, if you run CHECKSUM TABLE on MyISAM tables with VARCHAR columns, using ROW_FORMAT=fixed and row- 
level checksums enabled, then a warning will be raised that the table must be repaired using REPAIR TABLE to build the new 
checksum. 
To ensure that tables moved from a MySQL 5.1 environment to MySQL 5.1-maria, or MySQL 6.0 environment, you should run 
mysqld with the --old and run CHECKSUM TABLE ... EXTENDED to compare using the 5.0 algorithm.