| Bug #117036 | Spatial Index causes CHECK TABLE to fail | ||
|---|---|---|---|
| Submitted: | 23 Dec 2024 7:28 | Modified: | 23 Dec 2024 10:20 |
| Reporter: | Xizhe Zhang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
| Version: | 8.0, 8.0.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Dec 2024 10:20]
MySQL Verification Team
Hello Xizhe Zhang, Thank you for the report and feedback. regards, Umesh
[31 Dec 2024 5:43]
MySQL Verification Team
this looks like fixed in 8.0.42: Bug 37286473 - CHECK TABLE reports false corruption "ISSUE: - CHECK TABLE reports false corruption for Spatial Index, when there is "GEOMETRYCOLLECTION EMPTY" or a Geometry with MBR which covers the full range."
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.