MySQL Bugs: #54606: innodb fast alter table + pack_keys=0 prevents adding new indexes

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	CPU Architecture:	Any
Tags:	Incorrect index name, old_alter_table, regression

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`);

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.

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

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.

This looks like a small bug in innobase_check_index_keys(), invoked by ha_innobase::add_index().

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).

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.

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.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)

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)

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.

Noted in 5.1.52 changelog.

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)