| 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: | |
| 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: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)
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.