Description:
Columns in spatial indexes must be declared NOT NULL. However, it is possible to skip the requirement by inserting 'GEOMETRYCOLLECTION EMPTY', which will eventually lead to index corruption.
How to repeat:
-- 1. Create a table has one Spatial Index
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`path` geometry NOT NULL /*!80003 SRID 0 */,
PRIMARY KEY (`id`),
SPATIAL KEY `path` (`path`)
) ENGINE=InnoDB;
-- 2. At this time, Check Table is successful
check table t1;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| zxz_test.t1 | check | status | OK |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
-- 3. Insert 'GEOMETRYCOLLECTION EMPTY' into the geometry column
insert into t1(path) values(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
-- 4. Now, Check Table will fail
check table t1;
+-------------+-------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+-------------------------------------------------------+
| zxz_test.t1 | check | Warning | InnoDB: Index 'path' contains 0 entries, should be 1. |
| zxz_test.t1 | check | error | Corrupt |
+-------------+-------+----------+-------------------------------------------------------+
2 rows in set (0.00 sec)
Suggested fix:
Do not allow empty content to be inserted into Columns in spatial index.
Description: Columns in spatial indexes must be declared NOT NULL. However, it is possible to skip the requirement by inserting 'GEOMETRYCOLLECTION EMPTY', which will eventually lead to index corruption. How to repeat: -- 1. Create a table has one Spatial Index CREATE TABLE `t1` ( `id` int NOT NULL AUTO_INCREMENT, `path` geometry NOT NULL /*!80003 SRID 0 */, PRIMARY KEY (`id`), SPATIAL KEY `path` (`path`) ) ENGINE=InnoDB; -- 2. At this time, Check Table is successful check table t1; +-------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+-------+----------+----------+ | zxz_test.t1 | check | status | OK | +-------------+-------+----------+----------+ 1 row in set (0.00 sec) -- 3. Insert 'GEOMETRYCOLLECTION EMPTY' into the geometry column insert into t1(path) values(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')); -- 4. Now, Check Table will fail check table t1; +-------------+-------+----------+-------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------+-------+----------+-------------------------------------------------------+ | zxz_test.t1 | check | Warning | InnoDB: Index 'path' contains 0 entries, should be 1. | | zxz_test.t1 | check | error | Corrupt | +-------------+-------+----------+-------------------------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: Do not allow empty content to be inserted into Columns in spatial index.