MySQL Bugs: #3067: Repairing a table reenables "disabled keys" but it should not

Bug #3067	Repairing a table reenables "disabled keys" but it should not
Submitted:	4 Mar 2004 16:04	Modified:	22 Mar 2004 23:43
Reporter:	Tim Bunce	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.18-standard-log	OS:	Solaris (Solaris)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Repairing a table doesn't honor "disabled keys"

How to repeat:
CREATE TABLE foo (bat int PRIMARY KEY);
ALTER TABLE foo DISABLE KEYS;
-- crash the table --
-- repair the table --
-- index is now being used again ---

I can't easily test if using "REPAIR TABLE foo" triggers this because mysql 4.1.1 "show keys from foo" doesn't indicate if the index is disabled or not.

I notice this problem at ValueClick where inserts into a multimillion row table became very slow after the table was (auto) repaired after a crash. Inserts were 10 times slower until we realised the cause of problem.

Suggested fix:
Modify repair code so that disabled indices are left disabled and *not* repaired as there's not point in doing so.

Fix on dev-bugs
Date:
2004-03-15
Subj: 
Fix for bug#3067: Repairing a table reenables "disabled keys" but it should not

First i should say that testcase is not correct.
DISABLE KEYS has no effect with the PRIMARY KEY.

True, DISABLE KEYS has no effect with the PRIMARY KEY, I'd forgotten that when I wrote the test case and, as I said, I wasn't able to test it.

The bug does exist though.

tested this one with latest 4.0 and 4.1
works ok...

I tried this on my Linux:

mysql> create table foo(bar int primary key, bar_s varchar(10), key sk(bar_s));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values(1, 'one');
Query OK, 1 row affected (0.00 sec)

mysql> alter table foo disable keys;
Query OK, 0 rows affected (0.00 sec)

mysql> repair table foo;
+----------+--------+----------+----------+
| Table    | Op     | Msg_type | Msg_text |
+----------+--------+----------+----------+
| test.foo | repair | status   | OK       |
+----------+--------+----------+----------+
1 row in set (0.02 sec)

mysql> show keys from foo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| foo   |          0 | PRIMARY  |            1 | bar         | A         |           1 |     NULL | NULL   |      | BTREE      |          |
| foo   |          1 | sk       |            1 | bar_s       | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
2 rows in set (0.00 sec)

I've just duplicated the problem using "repair table test extended;"
(a basic "repair table test;" or ""repair table test quick;" didn't).

If you can't duplicate that I'll send a script.

bk commit - 4.1 tree (hf:1.1710)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html