Bug #60778 myisamchk -n corrupts the spatial index
Submitted: 6 Apr 2011 12:05 Modified: 6 Apr 2011 13:04
Reporter: Vsevolod Novikov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1.55, 5.5.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: corruption, filesort, myisam, MyISAMchk, spatial

[6 Apr 2011 12:05] Vsevolod Novikov
Description:
The myisamchk table repair with filesort corrupts spatial index

How to repeat:
1. Create a test table:
CREATE TABLE `t1` (
 `line` linestring NOT NULL,
 `kind` enum('po','pp','rr','dr','rd','ts','cl') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'po',
 `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 SPATIAL KEY `line` (`line`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. Insert some number of records:
INSERT INTO t1 (name, kind, line) VALUES
  ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
  ("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")),
  ("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")),
  ("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")),
  ("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")),
  ("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")),
  ("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")),
  ("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")),
  ("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")),
  ("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")),
  ("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")),
  ("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")),
  ("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")),
  ("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")),
  ("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")),
  ("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")),
  ("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")),
  ("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")),
  ("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")),
  ("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")),
  ("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")),
  ("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")),
  ("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")),
  ("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
  ("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")),
  ("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")),
  ("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")),
  ("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")),
  ("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)"));

INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;

3. Stop the mysql server
4. Go to the mysql data directory and force repair of the t1 table using filesort:
myisamchk -vvvvv --key-buffer-size=1073741824 --read-buffer-size=1048576 --write-buffer-size=1048576 --sort-buffer-size=10737418240 -r -n --myisam-block-size=16384 --quick t1
5. Start the mysql server
6. Try to insert a record to the table:
INSERT INTO t1 (name, kind, line) VALUES
  ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)"));

The result is:
#126 - Incorrect key file for table './test/t1.MYI'; try to repair it
[6 Apr 2011 12:17] Vsevolod Novikov
This issue looks like a duplicate of (suspended) Bug #46946, but adds a well-defined reproduce code.
[6 Apr 2011 13:04] Valeriy Kravchuk
Verified also with current mysql-5.5 from bzr on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading 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 4
Server version: 5.5.12-debug Source distribution

Copyright (c) 2000, 2010, 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> drop table t1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `t1` (
    ->  `line` linestring NOT NULL,
    ->  `kind` enum('po','pp','rr','dr','rd','ts','cl') COLLATE utf8_unicode_ci NOT NULL DEFAULT
    -> 'po',
    ->  `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->  SPATIAL KEY `line` (`line`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (name, kind, line) VALUES
    ->   ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
    ->   ("Aadassiye", "pp", GeomFromText("POINT(35.816667 36.216667)")),
    ->   ("Aadbel", "pp", GeomFromText("POINT(34.533333 36.100000)")),
    ->   ("Aadchit", "pp", GeomFromText("POINT(33.347222 35.423611)")),
    ->   ("Aadchite", "pp", GeomFromText("POINT(33.347222 35.423611)")),
    ->   ("Aadchit el Qoussair", "pp", GeomFromText("POINT(33.283333 35.483333)")),
    ->   ("Aaddaye", "pp", GeomFromText("POINT(36.716667 40.833333)")),
    ->   ("'Aadeissa", "pp", GeomFromText("POINT(32.823889 35.698889)")),
    ->   ("Aaderup", "pp", GeomFromText("POINT(55.216667 11.766667)")),
    ->   ("Qalaat Aades", "pp", GeomFromText("POINT(33.503333 35.377500)")),
    ->   ("A ad'ino", "pp", GeomFromText("POINT(54.812222 38.209167)")),
    ->   ("Aadi Noia", "pp", GeomFromText("POINT(13.800000 39.833333)")),
    ->   ("Aad La Macta", "pp", GeomFromText("POINT(35.779444 -0.129167)")),
    ->   ("Aadland", "pp", GeomFromText("POINT(60.366667 5.483333)")),
    ->   ("Aadliye", "pp", GeomFromText("POINT(33.366667 36.333333)")),
    ->   ("Aadloun", "pp", GeomFromText("POINT(33.403889 35.273889)")),
    ->   ("Aadma", "pp", GeomFromText("POINT(58.798333 22.663889)")),
    ->   ("Aadma Asundus", "pp", GeomFromText("POINT(58.798333 22.663889)")),
    ->   ("Aadmoun", "pp", GeomFromText("POINT(34.150000 35.650000)")),
    ->   ("Aadneram", "pp", GeomFromText("POINT(59.016667 6.933333)")),
    ->   ("Aadneskaar", "pp", GeomFromText("POINT(58.083333 6.983333)")),
    ->   ("Aadorf", "pp", GeomFromText("POINT(47.483333 8.900000)")),
    ->   ("Aadorp", "pp", GeomFromText("POINT(52.366667 6.633333)")),
    ->   ("Aadouane", "pp", GeomFromText("POINT(32.816667 35.983333)")),
    ->   ("Aadoui", "pp", GeomFromText("POINT(34.450000 35.983333)")),
    ->   ("Aadouiye", "pp", GeomFromText("POINT(34.583333 36.183333)")),
    ->   ("Aadouss", "pp", GeomFromText("POINT(33.512500 35.601389)")),
    ->   ("Aadra", "pp", GeomFromText("POINT(33.616667 36.500000)")),
    ->   ("Aadzi", "pp", GeomFromText("POINT(38.100000 64.850000)"));
Query OK, 29 rows affected (0.06 sec)
Records: 29  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 29 rows affected (0.01 sec)
Records: 29  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 58 rows affected (0.00 sec)
Records: 58  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 116 rows affected (0.01 sec)
Records: 116  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 232 rows affected (0.01 sec)
Records: 232  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 464 rows affected (0.02 sec)
Records: 464  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 928 rows affected (0.04 sec)
Records: 928  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 1856 rows affected (0.09 sec)
Records: 1856  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 3712 rows affected (0.20 sec)
Records: 3712  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 7424 rows affected (0.43 sec)
Records: 7424  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT * FROM t1;
Query OK, 14848 rows affected (0.85 sec)
Records: 14848  Duplicates: 0  Warnings: 0

mysql> exit
Bye
macbook-pro:5.5 openxs$ bin/mysqladmin -uroot shutdown
110406 15:59:45 mysqld_safe mysqld from pid file /Users/openxs/dbs/5.5/data/macbook-pro.pid ended
[1]+  Done                    bin/mysqld_safe
macbook-pro:5.5 openxs$ cd data/test
macbook-pro:test openxs$ ../../bin/myisamchk -vvvvv --key-buffer-size=1073741824 --read-buffer-size=1048576 --write-buffer-size=1048576 --sort-buffer-size=10737418240 -r -n --myisam-block-size=16384 --quick t1
Warning: option 'sort_buffer_size': unsigned value 10737418240 adjusted to 4294967295
- check record delete-chain
- recovering (with sort) MyISAM-table 't1'
Data records: 29696
- Fixing index 1
  - Searching for keys, allocating buffer for 67790 keys
  - Dumping 29696 keys
macbook-pro:test openxs$ cd -
/Users/openxs/dbs/5.5
macbook-pro:5.5 openxs$ bin/mysqld_safe &
[1] 28685
macbook-pro:5.5 openxs$ 110406 16:01:32 mysqld_safe Logging to '/Users/openxs/dbs/5.5/data/macbook-pro.err'.
chown: /Users/openxs/dbs/5.5/data/macbook-pro.err: Operation not permitted
110406 16:01:32 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.5/data

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.12-debug Source distribution

Copyright (c) 2000, 2010, 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> INSERT INTO t1 (name, kind, line) VALUES
    ->   ("Aadaouane", "pp", GeomFromText("POINT(32.816667 35.983333)"));
ERROR 126 (HY000): Incorrect key file for table './test/t1.MYI'; try to repair it