Bug #51546 | unique index cannot upgrade to primary index if contains partial segment/column | ||
---|---|---|---|
Submitted: | 26 Feb 2010 10:02 | Modified: | 2 Mar 2010 8:41 |
Reporter: | Jimmy Yang | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.0, 5.1, 5.5.99 + | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[26 Feb 2010 10:02]
Jimmy Yang
[26 Feb 2010 10:15]
Valeriy Kravchuk
Sorry, but can you explain in details how one can check that UNIQUE index is NOT used as primary key by InnoDB?
[1 Mar 2010 2:59]
Jimmy Yang
Hi, the easiest way to tell whether InnoDB has an external defined primary index is through monitor table output: mysql> create table innodb_table_monitor (a int) engine = innodb; If there is NO external primary index, InnoDB will create an internal primary index called "GEN_CLUST_INDEX". This index will always exist if InnoDB consider there is no external defined primary index. TABLE: name test/t1 ... INDEX: name GEN_CLUST_INDEX, id 0 15, fields 0/6, uniq 1, type 1 root page 50, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR col1 col2 col3 If an external primary index is added, InnoDB will drop "GEN_CLUST_INDEX" index (not create it during table rebuild) as part of process. In addition, in the repro for this bug, it will show following error message as: 100222 10:12:06 [ERROR] Table ./test/t5 has a primary key in InnoDB data dictionary, but not in MySQL! Since InnoDB treated the index on a partial segment as primary index, but not MySQL.
[2 Mar 2010 8:41]
Sveta Smirnova
Thank you for the feedback. Verified as described.