MySQL Bugs: #60681: CHECKSUM TABLE returns 0 for partitioned table

Bug #60681	CHECKSUM TABLE returns 0 for partitioned table
Submitted:	28 Mar 2011 19:32	Modified:	25 Oct 2011 18:11
Reporter:	Aaron Brown	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.56	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a table with partitions is altered to add live checksumming, CHECKSUM TABLE returns 0.

I have reproduced this on MySQL versions 5.1.56 (from source on Fedora Linux), 5.1.51 (Windows XP installer), and 5.1.47 (Fedora Linux package).

The type of partition does not matter.  I got the same behavior with KEY, HASH, and LIST partitions, but I did not get it when I did not partition the table at all.

I tried the following to fix the table:

  OPTIMIZE TABLE t;
  SHOW TABLE STATUS LIKE 't';
  CHECK TABLE t MEDIUM;
  REPAIR TABLE t;

but CHECKSUM TABLE still returns 0.  As shown in the test case, though, CHECKSUM TABLE EXTENDED does return the correct value.

Possibly related bugs:

- http://bugs.mysql.com/bug.php?id=57188
- http://bugs.mysql.com/bug.php?id=12296

In bug 57188 (which is for the ARCHIVE engine), running SHOW TABLE STATUS fixes things.  In bug 12296 the problem was caused by a table from a previous MySQL version.

Thanks.

How to repeat:
CREATE TABLE t (
  i INT
) ENGINE=MyISAM
  PARTITION BY RANGE (i) (
    PARTITION pa VALUES LESS THAN (3),
    PARTITION pb VALUES LESS THAN (5),
    PARTITION prest VALUES LESS THAN MAXVALUE);

INSERT INTO t (i)
VALUES (1), (2), (3), (4), (5), (6);

CHECKSUM TABLE t; -- Returns 2653438147.

ALTER TABLE t CHECKSUM = 1;

CHECKSUM TABLE t EXTENDED; -- Returns 2653438147.

CHECKSUM TABLE t; -- Returns 0!

Suggested fix:
A workaround is to use

  CHECKSUM TABLE t EXTENDED;

instead of

  CHECKSUM TABLE t;

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.56-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t (
    ->   i INT
    -> ) ENGINE=MyISAM
    ->   PARTITION BY RANGE (i) (
    ->     PARTITION pa VALUES LESS THAN (3),
    ->     PARTITION pb VALUES LESS THAN (5),
    ->     PARTITION prest VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO t (i)
    -> VALUES (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CHECKSUM TABLE t;
+--------+------------+
| Table  | Checksum   |
+--------+------------+
| test.t | 2653438147 |
+--------+------------+
1 row in set (0.03 sec)

mysql> ALTER TABLE t CHECKSUM = 1;
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CHECKSUM TABLE t EXTENDED;
+--------+------------+
| Table  | Checksum   |
+--------+------------+
| test.t | 2653438147 |
+--------+------------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t;
+--------+----------+
| Table  | Checksum |
+--------+----------+
| test.t |        0 |
+--------+----------+
1 row in set (0.00 sec)

Compare to non-partitioned table:

mysql> CREATE TABLE t2 (
    ->   i INT
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t2 (i)
    -> VALUES (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CHECKSUM TABLE t2;
+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.t2 | 2653438147 |
+---------+------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t2 CHECKSUM = 1;
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CHECKSUM TABLE t2 EXTENDED;
+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.t2 | 2653438147 |
+---------+------------+
1 row in set (0.00 sec)

mysql> CHECKSUM TABLE t2;
+---------+------------+
| Table   | Checksum   |
+---------+------------+
| test.t2 | 2653438147 |
+---------+------------+
1 row in set (0.00 sec)

the handler function ::checksum is not implemented in ha_partition and table_flags is copied from the partitions handlers. Which results in it says it does checksum, but always returns 0. CHECKSUM EXTENDED is implemented in the SQL layer by reading all rows from the table and calculate the checksum.

ha_partition::checksum() needs to be implemented like:
uint ha_partition::checksum() const
{
  ha_checksum crc= 0;

  DBUG_ENTER("ha_partition::checksum");
  if ((table_flags() & HA_HAS_CHECKSUM))
  {
    handler **file= m_file;
    do
    {
      crc+= (*file)->checksum();
    } while (*(++file));
  }
  DBUG_RETURN(crc_sum);
}

Note for others skimming this: contrary to what I imply in my original report, the misbehavior happens whether checksumming is turned on right in the CREATE TABLE statement or later with ALTER TABLE.  (I thought I had checked this -- oops!  I only checked it just now because Mattias Jonsson's [31 Mar 9:45] comment sounded like the type of thing that would happen no matter when checksumming was turned on.)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in the 5.6.4 changelog as follows:

        CHECKSUM TABLE returned 0 for partitioned tables unless
        the statement was used with the EXTENDED option.

Also noted the issue under checksum-table (5.1-5.6). Closed.