| Bug #73871 | CREATE SPATIAL INDEX fails to flag an error for invalid geometry data | ||
|---|---|---|---|
| Submitted: | 10 Sep 2014 8:00 | Modified: | 21 Apr 2015 13:11 |
| Reporter: | Marko Mäkelä | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Sep 2014 8:03]
Marko Mäkelä
Posted by developer: I think that we should handle this properly, if we intend to allow spatial indexes to be created on tables that were originally created in MySQL 5.6 or earlier. The geometry columns can contain any invalid data, and spatial indexes should not be allowed to be built on invalid data. The spatial index is like an additional constraint.
[21 Apr 2015 13:11]
Paul DuBois
Noted in 5.7.8, 5.8.0 changelogs. If a spatial column contained invalid spatial data, creating a SPATIAL index on the column failed to produce an error.
[4 Jun 2018 16:31]
MySQL Verification Team
https://bugs.mysql.com/bug.php?id=91105 marked as duplicate of this one.
Description: It is a known bug that MySQL does not validate the WKB data that is inserted into geometry columns. Internally, they are stored as strings, and the implicit default value is an empty string, which is not a valid WKB value. The InnoDB bug is that CREATE SPATIAL INDEX fails to check the validity of the data, and it could be creating a corrupted R-tree index as a result. There seems to be some error handling when modifying data after the index has been created. How to repeat: --source include/have_innodb.inc CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 GEOMETRY NOT NULL); INSERT INTO t1 VALUES(1,''); # Error: this should fail with ER_CANT_CREATE_GEOMETRY_OBJECT ALTER TABLE t1 ADD SPATIAL INDEX(c2), ALGORITHM=INPLACE; # Error: this should fail with ER_CANT_CREATE_GEOMETRY_OBJECT ALTER TABLE t1 ADD SPATIAL INDEX(c2), ALGORITHM=COPY; --error ER_CANT_CREATE_GEOMETRY_OBJECT INSERT INTO t1 VALUES(2,''); BEGIN; # Error: this should fail with ER_CANT_CREATE_GEOMETRY_OBJECT # (or the spatial index creation should not have succeeded in the first place) UPDATE t1 SET c1=3, c2=''; --error ER_CANT_CREATE_GEOMETRY_OBJECT UPDATE t1 SET c2='fail'; UPDATE t1 SET c2=GeomFromText('POINT(1000 1000)'); SELECT c1,HEX(c2) FROM t1; # Error: ROLLBACK updates back to c2='', making it invalid again! ROLLBACK; SELECT c1,HEX(c2) FROM t1; DROP TABLE t1; Suggested fix: Make the ADD SPATIAL INDEX fail, in both ALGORITHM=COPY and ALGORITHM=INPLACE. Ensure that we are handling all DML code paths correctly. We must reject invalid geometry values in INSERT and UPDATE. It would be good to add debug assertions to the ROLLBACK code path to prevent corruption.