Bug #35570 CHECKSUM TABLE unreliable if LINESTRING field (same content / differen checksum)
Submitted: 26 Mar 2008 11:23 Modified: 18 Dec 2009 20:32
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S3 (Non-critical)
Version:5.1-bk/5.0 OS:Linux
Assigned to: Kristofer Pettersson CPU Architecture:Any

[26 Mar 2008 11:23] Guilhem Bichot
Description:
Using a table t1 with a LINESTRING column and one row in it.
Running "CHECKSUM TABLE t1" twice shows different checksums!
Table t2 is created with same statements as t1, different checksum.
Table t3 is created with CREATE SELECT * FROM t1, different checksum.
Table t4 is created with binary "cp" of t1, different checksum.
I have not test 5.0.

This is a bad bug: CHECKSUM TABLE can serve to verify master/slave synchronization (in this sense, it is a tool to fight bugs but it's buggy itself).
My humble opinion is that there is no workaround.

How to repeat:
CREATE TABLE t1 (
  line LINESTRING NOT NULL
) engine=myisam;

INSERT INTO t1 VALUES 
  (GeomFromText("POINT(32.816667 35.983333)"));

checksum table t1;
checksum table t1;

CREATE TABLE t2 (
  line LINESTRING NOT NULL
) engine=myisam;

INSERT INTO t2 VALUES 
  (GeomFromText("POINT(32.816667 35.983333)"));

checksum table t2;

CREATE TABLE t3 select * from t1;
checksum table t3;

flush table t1;
--system cp var/master-data/test/t1.frm var/master-data/test/t4.frm
--system cp var/master-data/test/t1.MYD var/master-data/test/t4.MYD
--system cp var/master-data/test/t1.MYI var/master-data/test/t4.MYI
checksum table t4;

=>all checksums are different. This is dependent on mysterious conditions; adding a CHECK TABLE here or there modifies checksums.

Suggested fix:
first fix that two consecutive CHECKSUM give same results.
[26 Mar 2008 11:31] Guilhem Bichot
Affects InnoDB and Maria too.
[26 Mar 2008 12:24] MySQL Verification Team
Thank you for the bug report.
[26 Mar 2008 17:20] Sergei Golubchik
This is essentially a duplicate of Bug#30041. With the difference that there's no workaround (in Bug#30041 one could've switch to using DECIMAL).

This should be fixed in 6.0
[26 Mar 2008 20:04] Sergei Golubchik
No, it's a different bug. The bug is in mysql_checksum_table():

	      if ((f->type() == MYSQL_TYPE_BLOB) ||
                  (f->type() == MYSQL_TYPE_VARCHAR))

should also check for geometry data
[26 Mar 2008 20:57] Guilhem Bichot
Thanks Serg; after this patch
--- 1.456/sql/sql_table.cc      2008-03-20 16:04:18 +01:00
+++ edited/../sql/sql_table.cc  2008-03-26 21:55:31 +01:00
@@ -7261,8 +7261,10 @@ bool mysql_checksum_table(THD *thd, TABL
               if (! thd->variables.old_mode &&
                   f->is_real_null(0))
                 continue;
-             if ((f->type() == MYSQL_TYPE_BLOB) ||
-                  (f->type() == MYSQL_TYPE_VARCHAR))
+              enum_field_types field_type= f->type();
+             if ((field_type == MYSQL_TYPE_BLOB) ||
+                  (field_type == MYSQL_TYPE_VARCHAR) ||
+                  (field_type == MYSQL_TYPE_GEOMETRY))
              {
                String tmp;
                f->val_str(&tmp);

the checksums are identical as expected.
[27 Mar 2008 9:09] Guilhem Bichot
will push once the important 5.1 ongoing merges are completed (in a few days).
[2 Apr 2008 16:37] 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/commits/44800

ChangeSet@1.2624, 2008-04-02 18:37:01+02:00, guilhem@gbichot4.local +3 -0
  Fix for BUG#35570 "CHECKSUM TABLE unreliable if LINESTRING field (same content / differen checksum)"
  This will be back-ported to 5.x trees but the work for R-tree logging critically needs this patch in Maria now.
[10 Apr 2008 14:50] 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/commits/45198

ChangeSet@1.2605, 2008-04-10 16:50:08+02:00, guilhem@gbichot4.local +3 -0
  Fix for BUG#35570 "CHECKSUM TABLE unreliable if LINESTRING field (same content/ differen checksum)"
  The problem was that checksum of GEOMETRY type used memory addresses
  in the computation, making it un-repeatable thus useless.
[10 Apr 2008 15:00] 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/commits/45200

ChangeSet@1.2573, 2008-04-10 17:00:03+02:00, guilhem@gbichot4.local +2 -0
  Manual merge from 5.0 of the test for the fix for BUG#35570 "CHECKSUM
  TABLE unreliable if LINESTRING field (same content/ differen checksum)"
[10 Apr 2008 17:53] Guilhem Bichot
queued to 5.0-opt,5.1-opt,6.0-opt
[28 May 2008 10:02] Bugs System
Pushed into 6.0.6-alpha
[30 May 2008 19:11] Paul DuBois
Noted in 6.0.6 changelog.

Different invocations of CHECKSUM TABLE could return different
results for a table containing columns with spatial data types.

Setting report to Patch queued pending push into 5.0.x and 5.1.x.
[1 Jul 2008 21:18] Marc ALFF
The code was pushed into bitkeeper mysql-5.0-opt and mysql-5.1-opt,
but was never merged to a main tree or any bazaar team tree.
Setting back to verified: the fix and/or test is missing from 5.0 and 5.1.
[18 Dec 2008 13:37] Guilhem Bichot
So testing and inspection of code as well as revision history shows that bugfix is in 6.0 but not in 5.1: in 5.1 I still get different checksums:
checksum table t1;
Table   Checksum
test.t1 3783522773
checksum table t1;
Table   Checksum
test.t1 2370095220
checksum table t2;
Table   Checksum
test.t2 2569580433
checksum table t3;
Table   Checksum
test.t3 3255057450
checksum table t4;
Table   Checksum
test.t4 2098957064
I asked Igor to explain what happened to my bugfix.

I'm de-assigning from me (yes, I fix bugs only once :( ). If someone wants to re-push the fix into 5.1, you can find the patch in this revision of 6.0:
sp1r-guilhem@gbichot4.local-20080402163701-47894
[6 Aug 2009 12:31] Guilhem Bichot
got the reason why it didn't end in 5.1. Fix is in 6.0 and thus in the 6.0-based azalea, case closed.
[21 Sep 2009 9:58] 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/commits/83870

3117 Kristofer Pettersson	2009-09-21
      Fix for BUG#35570 "CHECKSUM TABLE unreliable if LINESTRING field (same content/ differen
      checksum)"
      
      The problem was that checksum of GEOMETRY type used memory addresses
      in the computation, making it un-repeatable thus useless.
      (This patch is a backport from 6.0 branch)
     @ mysql-test/r/myisam.result
        test case for bug35570 that same tables give same checksums
     @ mysql-test/t/myisam.test
        test case for bug35570 that same tables give same checksums
     @ sql/sql_table.cc
        Type GEOMETRY is implemented on top of type BLOB, so, just like for BLOB,
        its 'field' contains pointers which it does not make sense to include in
        the checksum; it rather has to be converted to a string and then we can
        compute the checksum.
[30 Sep 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:jperkin@sun.com-20090921190349-0rw35ei70cvpdvw6) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 0:26] Paul DuBois
Noted in 6.0.14 changelog.

Setting report to NDI pending push into 5.1.x, 5.4.x.
[6 Oct 2009 9:00] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:joro@sun.com-20090923082712-i6jc8tuy0uk1eakt) (merge vers: 5.1.40) (pib:11)
[7 Oct 2009 0:56] Paul DuBois
Noted in 5.1.40 changelog.

Setting report to NDI pending push into 5.4.x.
[22 Oct 2009 7:08] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 20:02] Paul DuBois
Noted in 5.5.0 changelog.
[23 Oct 2009 3:05] Roel Van de Paar
Summary:

This bug was fixed in 5.1.40, 5.5.0, 6.0.14

Workaround: none (except upgrade)
[18 Dec 2009 10:36] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:51] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:06] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:21] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)