MySQL Bugs: #49034: Unique index ordering problem at InnoDB Plugin with fast index creation

Bug #49034	Unique index ordering problem at InnoDB Plugin with fast index creation
Submitted:	24 Nov 2009 15:08	Modified:	2 Dec 2009 7:41
Reporter:	Yasufumi Kinoshita	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.41 + 1.0.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Contribution

Description:
Ryan's comment for Bug #44571:

case #2 is not fixed yet at 5.1.41 + 1.0.5

[5 May 6:53] Ryan Huddleston 
...
Case #2 "index error in error log"

CREATE TABLE `ruleacts` (
  `rule_key` int(11) NOT NULL DEFAULT '0',
  `seq` smallint(6) NOT NULL DEFAULT '0',
  `action` smallint(6) NOT NULL DEFAULT '0',
  `arg_id` smallint(6) DEFAULT NULL,
  `else_ind` TINYINT NOT NULL,
  KEY `ruleacts$arg_id` (`arg_id`)
) ENGINE=InnoDB; 
ALTER TABLE ruleacts ADD UNIQUE ruleacts$r$e$seq$act$a_id
(rule_key,else_ind,seq,action,arg_id) ;

The above results in the error:

090504 21:47:03 [ERROR] Index ruleacts$arg_id of rightnow/ruleacts has 2 columns unique
inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files
from different installations? See
http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

How to repeat:
As his comment.

Suggested fix:
I will attach patch for InnoDB Plugin

Align index order in dict to mysqld

Attachment: innodb_plugin_for_case2.patch (text/x-diff), 1.27 KiB.

Thank you for the bug report.

miguel@quetzal3:~$ dbs/5.1/libexec/mysqld --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so
091124 13:16:25 [Note] Plugin 'FEDERATED' is disabled.
091124 13:16:25 [Note] Plugin 'ndbcluster' is disabled.
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
091124 13:16:25  InnoDB: highest supported file format is Barracuda.
091124 13:16:26 InnoDB Plugin 1.0.5 started; log sequence number 51377
091124 13:16:26 [Note] Event Scheduler: Loaded 0 events
091124 13:16:26 [Note] dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.42-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
091124 13:17:21 [ERROR] Index ruleacts$arg_id of test/ruleacts has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.ht

Sorry, the my patch is not enough.
And this is duplicated of Bug #47622

  - UNIQUE keys where all column are NOT NULL
  - UNIQUE keys that don't contain partial segments
  - Other UNIQUE keys
   are not distinguished in InnoDB.

The patch should be more complex...

The reason that mysql sort the indexes in mysql_prepare_create_table()is to prepare for an alter table rebuild operation, where indexes on the table could be rebuilt. So ordering index before rebuild would make sense (we don't want to rebuild cluster index after nonclustered index etc.)

In our case, we are not really rebuilding the table. We are in fact overloading alter table interfaces for new index creation. And in this particular case, a single index is created. So re-sort of all existing indexes does not help, and would complicated things by making the order out of sync. And it does not make sense reordering all existing indexes while we are not going to rebuild them.

Any manipulations for in-memory metadata without fixing its on-disk representation would be incorrect. For example, a special fix in dict_index_add_to_cache() for special ordering would be non-persistent, a reboot of server would revert the ordering back to the order for rows inserted in sys_indexes. 

For create index (FIC) case (alter add index), the more appropriate fix would be the alter table interface not to resort all index (if we are not going to rebuild all of them).

Work will be done under the umbrella of bug #47622, the fix will resolve the issue mentioned in the CR. This CR can be marked as dup of 47622.

In summary, we accommodate this request by de-coupling the metadata sequence in cache and those on disk for innodb. Each time we open the table, and load index into memory, we check and make additional effort (if necessary) to sync up the innodb index sequence along with the sequence from mysql.