Bug #24395 ALTER TABLE DISABLE KEYS doesn't work when modifying the table

• Submitted: 17 Nov 2006 15:16
• Modified: 14 Dec 2006 3:57
• Reporter: Andrey Hristov
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1, 5.0, 5.1
• OS: Linux (Linux)
• Assigned to: Andrey Hristov
• CPU Architecture: Any

[17 Nov 2006 15:16] Andrey Hristov

Description:
ALTER TABLE xyz DISABLE KEYS does work, tested a lot by our customers. ALTER TABLE xyz RENAME TO abc, DISABLE KEYS used to crash, fixed (bug#24219). There is another bug when handling a table and using DISABLE|ENABLE KEYS. It does not get executed or even the status of the indices is not preserved.
I suppose this problem exists also in 4.0, 4.1 and 5.0. Please try to verify on these versions.

See example:
andrey@lmy004:~> mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.13-beta-valgrind-max-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database alter_table;
Query OK, 1 row affected (0.03 sec)

mysql> use alter_table;
Database changed
mysql> create table abc (a int, index(a));
Query OK, 0 rows affected (0.12 sec)

mysql> show indexes from abc\G
*************************** 1. row ***************************
       Table: abc
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.08 sec)

mysql> alter table abc modify a int, disable keys;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from abc\G
*************************** 1. row ***************************
       Table: abc
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.07 sec)

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

mysql> show indexes from abc\G
*************************** 1. row ***************************
       Table: abc
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: disabled
1 row in set (0.08 sec)

How to repeat:
drop database if exists alter_table;
create database alter_table;
use alter_table;
create table abc (a int, index(a));
show indexes from abc\G
-- this doesn't disable the index
alter table abc modify a int, disable keys;
show indexes from abc\G

-- this however does
alter table abc disable keys;
show indexes from abc\G

drop table abc;
drop database alter_table;

[17 Nov 2006 15:55] Andrey Hristov

Verified by Shawn on 4.1 and 5.0

[17 Nov 2006 20:12] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15522

ChangeSet@1.2611, 2006-11-17 21:10:46+01:00, [e-mail] +3 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  To the documentor: ENABLE|DISABLE KEYS combined with another
  ALTER TABLE option, different than RENAME TO did nothing. Also, if
  the table had disabled keys and was modified then the end table was
  with enabled keys.

[23 Nov 2006 15:55] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15768

ChangeSet@1.2611, 2006-11-23 16:54:02+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.
  
  Note: The fix will probably automerge to 5.0 but in 5.1 there is optimization
        which tries to skip data copy operation if not needed. Then the fix will
        be a bit different - there could be no new_table.

[23 Nov 2006 16:06] Andrey Hristov

Back to "In progress". Just found a case when the patch doesn't work.

[23 Nov 2006 17:14] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15775

ChangeSet@1.2611, 2006-11-23 18:12:20+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.
  
  Note: The fix will probably automerge to 5.0 but in 5.1 there is optimization
        which tries to skip data copy operation if not needed. Then the fix will
        be a bit different - there could be no new_table.

[24 Nov 2006 11:15] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15793

ChangeSet@1.2611, 2006-11-24 12:13:41+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  (simplified patch)
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.
  
  Note: The fix will probably automerge to 5.0 but in 5.1 there is optimization
        which tries to skip data copy operation if not needed. Then the fix will
        be a bit different - there could be no new_table.

[24 Nov 2006 11:24] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15794

ChangeSet@1.2611, 2006-11-24 12:21:25+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  (simplified patch)
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.
  
  Note: The fix will probably automerge to 5.0 but in 5.1 there is optimization
        which tries to skip data copy operation if not needed. Then the fix will
        be a bit different - there could be no new_table.

[24 Nov 2006 16:44] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15809

ChangeSet@1.2611, 2006-11-24 17:42:55+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[24 Nov 2006 17:03] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15810

ChangeSet@1.2611, 2006-11-24 18:01:25+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[27 Nov 2006 11:30] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15838

ChangeSet@1.2611, 2006-11-27 12:28:44+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[27 Nov 2006 12:57] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15840

ChangeSet@1.2611, 2006-11-27 13:46:36+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
  
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
  
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[27 Nov 2006 16:19] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15860

ChangeSet@1.2558, 2006-11-27 17:16:46+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[27 Nov 2006 18:16] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15874

ChangeSet@1.2558, 2006-11-27 19:14:52+01:00, [e-mail] +6 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[27 Nov 2006 19:14] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15877

ChangeSet@1.2334, 2006-11-27 19:45:15+01:00, [e-mail] +6 -0
  Merge example.com:/work/bug24395-v2/my41
  into  example.com:/work/bug24395-v2/my50
  
  fix for bug#24395 merge
  MERGE: 1.1616.2847.4

[28 Nov 2006 13:28] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15951

ChangeSet@1.2558, 2006-11-28 14:21:28+01:00, [e-mail] +5 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[28 Nov 2006 13:36] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15952

ChangeSet@1.2558, 2006-11-28 14:34:31+01:00, [e-mail] +5 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[28 Nov 2006 15:24] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15973

ChangeSet@1.2558, 2006-11-28 16:19:12+01:00, [e-mail] +5 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[28 Nov 2006 17:16] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15993

ChangeSet@1.2558, 2006-11-28 18:14:18+01:00, [e-mail] +4 -0
  Fix for bug#24395:
  ALTER TABLE DISABLE KEYS doesn't work when modifying the table
    
  ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different
  than RENAME TO did nothing. Also, if the table had disabled keys
  and was ALTER-ed then the end table was with enabled keys.
    
  Fixed by checking whether the table had disabled keys and enabling them
  in the copied table.

[28 Nov 2006 17:49] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15999

ChangeSet@1.2334, 2006-11-28 18:47:47+01:00, [e-mail] +4 -0
  Merge example.com:/work/bug24395-v2/my41
  into  example.com:/work/bug24395-v2/my50
  
  fix for bug#24395 (5.0 merge)
  MERGE: 1.1616.2847.4

[30 Nov 2006 17:38] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16238

ChangeSet@1.2334, 2006-11-30 18:36:15+01:00, [e-mail] +4 -0
  Merge example.com:/work/bug24395-v2/my41
  into  example.com:/work/bug24395-v2/my50
  
  fix for bug#24395 merged into 5.0
  MERGE: 1.1616.2847.4

[30 Nov 2006 18:34] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16244

ChangeSet@1.2393, 2006-11-30 19:32:45+01:00, [e-mail] +4 -0
  Merge example.com:/work/bug24395-v2/my50
  into  example.com:/work/bug24395-v2/my51
  
  fix for bug#24395 , merge into 5.1
  MERGE: 1.1810.2320.7

[2 Dec 2006 20:46] Andrey Hristov

4.1-maint, 5.0-maint, 5.1-maint

[8 Dec 2006 16:46] Andrey Hristov

The fix will appear as part of 4.1.23 (if released), 5.0.32 (5.0.30 is already released) and 5.1.15

[14 Dec 2006 3:57] Jon Stephens

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix for 4.1.23, 5.0.32, and 5.1.15.