Bug #21888 Query on GEOMETRY field using PointFromWKB() results in lost connection
Submitted: 28 Aug 2006 23:43 Modified: 23 Oct 2006 14:26
Reporter: Peter Brodersen (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.1.12-beta-log, 5.0.24-max-log\4.1BK OS:Linux (linux\windows)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: crash, Geometry

[28 Aug 2006 23:43] Peter Brodersen
Description:
A simple query where a field of type geometry is queried makes the server drop the connection (and possibly crashes the current server child process).

The crash only seem to occur when zero-valued coordinates are present in the table.

How to repeat:
CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));

ERROR 2013 (HY000): Lost connection to MySQL server during query
[29 Aug 2006 0:54] Miguel Solorzano
Call Stack 5.1.12-beta-nt on Windows 2003 32-bits

Attachment: bt-windows-21888.txt (text/plain), 2.46 KiB.

[29 Aug 2006 0:56] Miguel Solorzano
Call stack attached:

D:\mysql\bin>mysqladmin -uroot create db1

D:\mysql\bin>mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[29 Aug 2006 1:02] Miguel Solorzano
Back Trace 5.0.25BK on Suse 10 32-bits

Attachment: bt-linux-21888.txt (text/plain), 11.74 KiB.

[29 Aug 2006 1:08] Miguel Solorzano
Thank you for the bug report. I was able to repeat.
Back trace attached for 5.0.25BK Linux.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
NSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
INSERT INQuery OK, 0 rows affected (0.02 sec)

TO gemysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
ALUES (PointFromWKB(POINT(1,0)));
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));Query OK, 1 row affected (0.04 sec)

INmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
O geom (foo) VALUES (PointQuery OK, 1 row affected (0.01 sec)

Frommysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
0,0)));
SELECT 1 FROM geom Query OK, 1 row affected (0.01 sec)

WHEmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
intFromWKB(POINT(0,0)Query OK, 1 row affected (0.01 sec)

);mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

-----------------------------------------------------------------
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE geom (foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) );
INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
IQuery OK, 0 rows affected (0.02 sec)

NSEmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,1)));
T INTO geom (foo) VALUES (PointFromWQuery OK, 1 row affected (0.02 sec)

KBmysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(1,0)));
Query OK, 1 row affected (0.00 sec)

Imysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,1)));
IQuery OK, 1 row affected (0.00 sec)

mysql> INSERT INTO geom (foo) VALUES (PointFromWKB(POINT(0,0)));
SEQuery OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM geom WHERE foo != PointFromWKB(POINT(0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[15 Sep 2006 15:11] 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/12040

ChangeSet@1.2528, 2006-09-15 20:28:21+05:00, holyfoot@mysql.com +3 -0
  bug #21888 (Query on GEOMETRY crashes server)
[27 Sep 2006 7:48] Alexander Barkov
Looks ok to push.
[29 Sep 2006 12:28] 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/12837

ChangeSet@1.2573, 2006-09-29 17:56:02+05:00, holyfoot@mysql.com +3 -0
  bug #21888 (Query on GEOMETRY field crashes the server)
  
  RTree keys are really different from BTree and need specific
  paramters to be set by optimizer to work.
  Sometimes optimizer doesn't set those properly.
  Here we decided just to add code to check that the parameters
  are correct. Hope to fix optimizer sometimes.
[21 Oct 2006 9:09] Georgi Kodinov
Pushed in 4.1.22/5.0.27/5.1.13-beta
[23 Oct 2006 14:26] Paul Dubois
Noted in 4.1.22, 5.0.27, 5.1.13 changelogs.

The optimizer sometimes mishandled R-tree indexes for GEOMETRY data types,
resulting in a server crash.