Bug #79060 Cannot get geometry object from data you send to the GEOMETRY field

Submitted: 1 Nov 2015 20:00 Modified: 5 Nov 2015 19:59
Reporter: Benjamin Morel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: GIS Severity:S1 (Critical)
Version:5.7.9 OS:Fedora (22 x64)
Assigned to: CPU Architecture:Any
Tags: Geometry, gis

[1 Nov 2015 20:00] Benjamin Morel
Description:
I certain very specific cases (using JOIN and ORDER BY), retrieving a valid geometry using ST_AsBinary() fails with the following message:

> Cannot get geometry object from data you send to the GEOMETRY field

I've stripped down my app data to a maximum, and managed to reproduce it 100% with the script below.

When playing with the script, you'll notice that:

- Using ST_AsText() instead of ST_AsBinary() works as expected
- Removing INNER JOIN (try removing DealPeriod) works as expected
- Removing ORDER BY works as expected

So it looks like valid geometry data gets internally corrupted in some very specific cases.

This is a very blocking issue as our app just cannot work with MySQL 5.7 yet.

How to repeat:
DROP DATABASE IF EXISTS test_bug;
CREATE DATABASE test_bug;
USE test_bug;

DROP TABLE IF EXISTS Deal;
CREATE TABLE Deal (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  restaurantId int(10) unsigned NOT NULL,
  creationTime int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO Deal VALUES (1,1,1446379501);

DROP TABLE IF EXISTS DealPeriod;
CREATE TABLE DealPeriod (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  dealId int(10) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO DealPeriod VALUES (1,1);

DROP TABLE IF EXISTS Restaurant;
CREATE TABLE Restaurant (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  location point NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO Restaurant VALUES (1,ST_GeomFromText('POINT(55.2918681 -20.9388982)'));

SELECT ST_AsBinary(r1_.location)
FROM Deal d0_
INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
ORDER BY d0_.creationTime DESC;
[1 Nov 2015 22:21] MySQL Verification Team
Thank you for the bug report.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.10 Source distribution PULL: 2015-OCT-24

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > DROP DATABASE IF EXISTS test_bug;
Query OK, 3 rows affected (0.06 sec)

mysql 5.7 > CREATE DATABASE test_bug;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE test_bug;
Database changed
mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS Deal;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE Deal (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   restaurantId int(10) unsigned NOT NULL,
    ->   creationTime int(11) NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > INSERT INTO Deal VALUES (1,1,1446379501);
Query OK, 1 row affected (0.02 sec)

mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS DealPeriod;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE DealPeriod (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   dealId int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO DealPeriod VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS Restaurant;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE Restaurant (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   location point NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO Restaurant VALUES (1,ST_GeomFromText('POINT(55.2918681 -20.9388982)'));
Query OK, 1 row affected (0.01 sec)

mysql 5.7 >
mysql 5.7 > SELECT ST_AsBinary(r1_.location)
    -> FROM Deal d0_
    -> INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
    -> INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
    -> ORDER BY d0_.creationTime DESC;
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

mysql 5.7 > SELECT ST_AsText(r1_.location)
    -> FROM Deal d0_
    -> INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
    -> INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
    -> ORDER BY d0_.creationTime DESC;
+-------------------------------+
| ST_AsText(r1_.location)       |
+-------------------------------+
| POINT(55.2918681 -20.9388982) |
+-------------------------------+
1 row in set (0.00 sec)
[5 Nov 2015 15:19] Paul DuBois
Noted in 5.7.10, 5.8.0 changelogs.

ST_AsWKB() could cause a server exit if given invalid data.
[5 Nov 2015 19:25] Benjamin Morel
This is actually in the 5.7.9 changelog, so I don't think it is related (and the geometry data is valid here!)
[5 Nov 2015 19:32] Benjamin Morel
(closed by mistake)
[5 Nov 2015 19:51] MySQL Verification Team
I don't understood why you opened again when the bug was verified and commented the changelogs for the below version:

 [5 Nov 15:19] Paul Dubois

Noted in 5.7.10, 5.8.0 changelogs.
[5 Nov 2015 19:59] Benjamin Morel
@Paul I did not find what you mentioned in the 5.7.10 changelog:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-10.html

However, I did find it in the 5.7.9 changelog, which is the affected version:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html

So I thought this was a mistake on your part to think this was related to / duplicate of another bug.

The present bug has been Verified, but is now Closed. This is confusing to me; has it been fixed? Will the fix be included in 5.7.10?
[6 Nov 2015 13:59] Paul DuBois
Actually, this bug was related to another bug, but it fixes a different problem, so the previous changelog entry was in error. Here is the corrected version:

Queries that needed to store the result of ST_AsWKB() in a temporary
table could fail with an error message.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.