| 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: | |
| 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: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
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