Bug #12296 CHECKSUM TABLE reports 0 for the table
Submitted: 1 Aug 2005 9:33 Modified: 28 Sep 2005 2:10
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 from BK OS:Linux (linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[1 Aug 2005 9:33] Victoria Reznichenko 
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
[11 Aug 2005 11:36] Bugs System 
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
[11 Aug 2005 11:54] Ingo Strüwing 
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.
[29 Aug 2005 15:08] Bugs System 
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
[30 Aug 2005 12:41] Ingo Strüwing 
Pushed to 4.1.15 and 5.0.12.

Docs team, please observe my last comment to this bug report.
[13 Sep 2005 22:36] Mike Hillyer 
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>