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