Bug #88003 REPAIR TABLE for MyISAM table repeatedly causes index corruption
Submitted: 5 Oct 2017 22:08 Modified: 7 Mar 2018 17:15
Reporter: Jesper wisborg Krogh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[5 Oct 2017 22:08] Jesper wisborg Krogh
Description:
After a REPAIR TABLE attempting to run a query using a secondary index causes an empty result even if the query should return several rows. A CHECK TABLE shows there is index corruption. REPAIR TABLE fixes claims to work, but same result applies.

Doing an INSERT ... SELECT into a new table (so uses MyISAM's optimization where the index is created at the end using the sort algorithm) also causes index corruption in the new table.

How to repeat:
The expected result of the query is:

mysql> SELECT id FROM t1 WHERE val = 53088155 AND id = 257289609;
+-----------+
| id        |
+-----------+
| 257289609 |
| 257293063 |
| 257296529 |
+-----------+
3 rows in set (0.15 sec)

Actual result:

mysql> SELECT id FROM t1 WHERE val = 53088155;
Empty set (0.00 sec)

mysql> SELECT id FROM t1 WHERE val = 53088155 AND id = 257289609;
+-----------+
| id        |
+-----------+
| 257289609 |
+-----------+
1 row in set (0.00 sec)

mysql> CHECK TABLE t1;
+--------+-------+----------+------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                 |
+--------+-------+----------+------------------------------------------+
| db1.t1 | check | error    | Key in wrong position at page 6167783424 |
| db1.t1 | check | error    | Corrupt                                  |
+--------+-------+----------+------------------------------------------+
2 rows in set (1 min 17.19 sec)

mysql> SET myisam_sort_buffer_size = 15 * 1024 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> REPAIR TABLE t1;
+--------+--------+----------+---------------------------------------------------+
| Table  | Op     | Msg_type | Msg_text                                          |
+--------+--------+----------+---------------------------------------------------+
| db1.t1 | repair | warning  | Number of rows changed from 74953439 to 600081305 |
| db1.t1 | repair | status   | OK                                                |
+--------+--------+----------+---------------------------------------------------+
2 rows in set (22 min 24.97 sec)

mysql> SELECT id FROM t1 WHERE val = 53088155;
Empty set (0.00 sec)

mysql> SELECT id FROM t1 WHERE val = 53088155 AND id = 257289609;
+-----------+
| id        |
+-----------+
| 257289609 |
+-----------+
1 row in set (0.07 sec)

mysql> CHECK TABLE t1;
+--------+-------+----------+------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                 |
+--------+-------+----------+------------------------------------------+
| db1.t1 | check | error    | Key in wrong position at page 6121517056 |
| db1.t1 | check | error    | Corrupt                                  |
+--------+-------+----------+------------------------------------------+
2 rows in set (1 min 26.98 sec)

mysql> SELECT @@global.key_buffer_size;
+--------------------------+
| @@global.key_buffer_size |
+--------------------------+
|                  8388608 |
+--------------------------+
1 row in set (0.01 sec)

mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.48 sec)

mysql> CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 600081305 rows affected (32 min 26.12 sec)
Records: 600081305  Duplicates: 0  Warnings: 0

mysql> SELECT id FROM t2 WHERE val = 53088155;
Empty set (0.13 sec)

mysql> SELECT id FROM t2 WHERE val = 53088155 AND id = 257289609;
+-----------+
| id        |
+-----------+
| 257289609 |
+-----------+
1 row in set (0.14 sec)
[6 Oct 2017 7:51] Shane Bester
One other corruption bug I know of (probably not same as this, but surely will affect anybody else using MyISAM).

https://bugs.mysql.com/bug.php?id=83467
[23 Oct 2017 0:29] Jesper wisborg Krogh
Posted by developer:
 
If I reduce myisam_sort_buffer_size to the default (8M) then I can no longer reproduce. There is some overhead though. For the test table with 600 million rows, two integer columns, a primary key on one of the columns, and a non-unique secondary index on the other, the overhead is between six (populating the table using INSERT ... SELECT or LOAD DATA INFILE) and ten minutes (repairing the table).
[7 Mar 2018 17:14] Paul Dubois
Posted by developer:
 
Fixed in 5.7.22, 8.0.5.

MyISAM index corruption could occur for bulk-insert and table-repair
operations that involve the repair-by-sorting algorithm and many
(more than 450 million) rows.