MySQL Bugs: #57323: myisam corruption with insert ignore and invalid spatial data

Bug #57323	myisam corruption with insert ignore and invalid spatial data
Submitted:	7 Oct 2010 17:50	Modified:	21 Jun 2011 18:51
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.1.53, 5.5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	corruption, spatial

Description:
spatial corruption happen too easily.  the testcase shows a minimal error, but larger datasets actually leave the table's indexes unusable and they have to be repaired.

mysql> insert ignore into `t1` set
    -> `a` = geomfromtext("point(-6 0)"),
    -> `b` = geomfromtext("error");
ERROR 126 (HY000): Incorrect key file for table './db/t1.MYI'; try to repair it

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` linestring not null,`b` geometry not null,
spatial key (`a`),spatial key (`b`)) engine=myisam;

insert into `t1` set
`b` = geomfromtext("point(1 2)"),
`a` = geomfromtext("point(1 2)");

insert ignore into `t1` set
`a` = geomfromtext("point(-6 0)"),
`b` = geomfromtext("error");

Suggested fix:
fix the error handling logic.

Verified on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> drop table if exists `t1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1` (`a` linestring not null,`b` geometry not null,
    -> spatial key (`a`),spatial key (`b`)) engine=myisam;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into `t1` set
    -> `b` = geomfromtext("point(1 2)"),
    -> `a` = geomfromtext("point(1 2)");
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert ignore into `t1` set
    -> `a` = geomfromtext("point(-6 0)"),
    -> `b` = geomfromtext("error");
ERROR 126 (HY000): Incorrect key file for table './test/t1.MYI'; try to repair it

Noted in 5.1.59, 5.5.16 changelogs.

For MyISAM tables, attempts to insert incorrect data into an index
GEOMETRY column could result in table corruption.

CHANGESET - http://lists.mysql.com/commits/138792

Correction: Noted in 5.5.15, not 5.5.16.