MySQL Bugs: #12296: CHECKSUM TABLE reports 0 for the table

Bug #12296	CHECKSUM TABLE reports 0 for the table
Submitted:	1 Aug 2005 11:33	Modified:	28 Sep 2005 4:10
Reporter:	Victoria Reznichenko
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.1 from BK	OS:	Linux (linux)
Assigned to:	Ingo Strüwing	Target Version:

Description:
CHECKSUM TABLE reports 0 for non-empty table. OPTIMIZE TABLE or ALTER TABLE ...
CHECKSUM=1 fix the problem:

mysql> checksum table cluster_tree;
+-------------------+----------+
| Table             | Checksum |
+-------------------+----------+
| test.cluster_tree |        0 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> checksum table cluster_tree extended;
+-------------------+----------+
| Table             | Checksum |
+-------------------+----------+
| test.cluster_tree |        0 |
+-------------------+----------+
1 row in set (0.03 sec)

mysql> optimize table cluster_tree;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| test.cluster_tree | optimize | status   | OK       |
+-------------------+----------+----------+----------+
1 row in set (0.02 sec)

mysql> checksum table cluster_tree;
+-------------------+------------+
| Table             | Checksum   |
+-------------------+------------+
| test.cluster_tree | 1678527378 |
+-------------------+------------+
1 row in set (0.01 sec)

How to repeat:
- restore table
- run CHECKSUM TABLE cluster_tree
- run OPTIMIZE TABLE cluster_tree or ALTER TABLE cluster_tree CHECKSUM=1
- run CHECKSUM TABLE cluster_tree

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28150

This fix implies that checksums calculated with the new MySQL version may differ from
older checksums on the same table if there are deleted records in the table (which is
quite probable). 

If users stored the old checksum values to detect table changes, they should check the
tables before upgrading and calculate new values after upgrading. If necessary, measures
need to be taken that noone can change the tables in between.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28982

Pushed to 4.1.15 and 5.0.12.

Docs team, please observe my last comment to this bug report.

Documented in 5.0.12 and 4.1.15 changelogs:

  <listitem>
        <para>
          <literal>CHECKSUM TABLE</literal> command returned <literal>0</literal> for
tables with
          deleted rows. After upgrading, users who used stored checksum information to
detect table changes
          should rebuild their checksum data. (Bug #12296)
        </para>
      </listitem>