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)
