Bug #54606 innodb fast alter table + pack_keys=0 prevents adding new indexes
Submitted: 18 Jun 2010 7:32 Modified: 7 Oct 2010 23:07
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.48, plugin 1.0.9, plugin 1.1.1, 5.5.5 OS:Any
Assigned to: Magne Mæhre
Tags: Incorrect index name, old_alter_table, regression
Triage: Triaged: D3 (Medium)

[18 Jun 2010 7:32] Shane Bester
Description:
testcase output, it makes no sense why the 'create index' statements fail:

mysql>  create table `t1`(
    ->  `a` int,`b` char(9) charset latin1,`c` int,key (`b`)
    -> ) engine=innodb pack_keys=0;
Query OK, 0 rows affected (0.16 sec)

mysql> create index `a` on `t1`(`a`);
ERROR 1280 (42000): Incorrect index name 'b'
mysql> create index `c` on `t1`(`c`);
ERROR 1280 (42000): Incorrect index name 'b'

workaround is to "set old_alter_table=1" or to never define pack_keys for innodb tables.  Not a recent regression, 5.1.46 had same behaviour.

How to repeat:
#start mysqld with the innodb plugin:

set old_alter_table=0;
drop table if exists `t1`;
create table `t1`(`a` int,`b` char(9) charset latin1,`c` int,key (`b`)
) engine=innodb pack_keys=0;
create index `a` on `t1`(`a`);
create index `c` on `t1`(`c`);
[18 Jun 2010 7:42] Valerii Kravchuk
Sorry, but this is what I get with mysql-trunk:

valeriy-kravchuks-macbook-pro:trunk 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.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set old_alter_table=0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.07 sec)

mysql> create table `t1`(`a` int,`b` char(9) charset latin1,`c` int,key (`b`)
    -> ) engine=innodb pack_keys=0;
Query OK, 0 rows affected (0.30 sec)

mysql> create index `a` on `t1`(`a`);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index `c` on `t1`(`c`);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> exit
Bye
valeriy-kravchuks-macbook-pro:trunk openxs$ tail var/macbook-pro.err 

100618 10:40:11 [Note] Plugin 'FEDERATED' is disabled.
100618 10:40:11 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
100618 10:40:11  InnoDB: highest supported file format is Barracuda.
100618 10:40:11 InnoDB Plugin 1.0.6 started; log sequence number 7244030
100618 10:40:11 [Note] Event Scheduler: Loaded 0 events
100618 10:40:11 [Note] /Users/openxs/dbs/trunk/libexec/mysqld: ready for connections.
Version: '5.5.5-m3-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

What am I doing wrong? Maybe it is a regression in plugin after 1.0.6.
[18 Jun 2010 7:58] Shane Bester
i started 5.1.48 server like this on windows:

mysqld-debug --console --skip-gr --skip-na 
--ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.dll 
--innodb_file_per_table 
--innodb-file-format=barracuda 
--innodb-lock-wait-timeout=1
[18 Jun 2010 8:35] Valerii Kravchuk
OK, after upgrade of mysql-trunk to current I was able to repeat this:

valeriy-kravchuks-macbook-pro:trunk 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.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set old_alter_table=0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.36 sec)

mysql> create table `t1`(`a` int,`b` char(9) charset latin1,`c` int,key (`b`)
    -> ) engine=innodb pack_keys=0;
Query OK, 0 rows affected (0.33 sec)

mysql> create index `a` on `t1`(`a`);
ERROR 1280 (42000): Incorrect index name 'b'
mysql> create index `c` on `t1`(`c`);
ERROR 1280 (42000): Incorrect index name 'b'
mysql> exit
Bye
valeriy-kravchuks-macbook-pro:trunk openxs$ tail var/macbook-pro.err 100618 11:17:51 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
100618 11:17:51  InnoDB: highest supported file format is Barracuda.
InnoDB: 127 rollback segment(s) active.
100618 11:17:52 InnoDB 1.1.1 started; log sequence number 7254971
100618 11:17:52 [Note] Event Scheduler: Loaded 0 events
100618 11:17:52 [Note] /Users/openxs/dbs/trunk/libexec/mysqld: ready for connections.
Version: '5.5.5-m3-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

So, it is related to plugin version used.
[24 Jun 2010 10:18] Marko Mäkelä
This looks like a small bug in innobase_check_index_keys(), invoked by ha_innobase::add_index().
[24 Jun 2010 11:18] Marko Mäkelä
Sorry, this looks like a bug outside InnoDB, likely in the compare_tables() function invoked by mysql_alter_table(). I tried with the following test:

SET old_alter_table=0;
CREATE TABLE bug54606(a INT,b CHAR(9),c INT,KEY (b)) ENGINE=InnoDB PACK_KEYS=0;
CREATE INDEX a ON bug54606(a);
CREATE INDEX c ON bug54606(c);
DROP TABLE bug54606;

The statement CREATE INDEX a ON bug54606(a) causes ha_innobase::add_index() to be invoked with num_of_keys==2, with key_info containing (b,a).
[7 Sep 2010 12:56] 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/117706

3501 Magne Mahre	2010-09-07
      Bug #54606 innodb fast alter table + pack_keys=0 prevents 
                 adding new indexes
      
      A fast alter table requires that the existing (old) table
      and indices are unchanged (i.e only new indices can be
      added).  To verify this, the layout and flags of the old
      table/indices are compared for equality with the new.
      
      The PACK_KEYS option is a no-op in InnoDB, but the flag
      exists, and is used in the table compare.  We need to
      check this (table) option flag before deciding whether an 
      index should be packed or not.  If the table has
      explicitly set PACK_KEYS to 0, the created indices should
      not be marked as packed/packable.
[16 Sep 2010 10:54] 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/118375

3511 Magne Mahre	2010-09-16
      Bug #54606 innodb fast alter table + pack_keys=0 prevents 
                 adding new indexes
      
      A fast alter table requires that the existing (old) table
      and indices are unchanged (i.e only new indices can be
      added).  To verify this, the layout and flags of the old
      table/indices are compared for equality with the new.
      
      The PACK_KEYS option is a no-op in InnoDB, but the flag
      exists, and is used in the table compare.  We need to
      check this (table) option flag before deciding whether an 
      index should be packed or not.  If the table has
      explicitly set PACK_KEYS to 0, the created indices should
      not be marked as packed/packable.
[28 Sep 2010 15:38] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[1 Oct 2010 17:43] Paul Dubois
Noted in 5.5.7, 5.6.1 changelogs.

Setting the PACK_KEYS=0 table option for an InnoDB table prevented
new indexes from being added to the table.
[7 Oct 2010 23:07] Paul Dubois
Noted in 5.1.52 changelog.
[1 Nov 2010 19:00] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)