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:
None 
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
Description:
This is a bug related to Bug #51378.

When creating a unique index that contains partial segment (column) through "create index" or "alter table add index", it will be rejected from promoting to primary index. On the other hand, such primary index (on partial segment/column) can be created through "alter table add primary key" and "create table primary key" interfaces. Need to understand this different treatment from different interfaces.

Discussed with Georgi Kodinov, and we decided file a bug report to follow up.

How to repeat:
# Create a table contains a BLOB column
create table t1 (
        `col1` int not null,
        `col2` blob not null,
        `col3` time not null) engine = innodb;

# Create following unique indexes on 'col1' and 'col2(31)'
# of the table, the index would not be treated as primary
# key by MySQL because it indexes only first 31 bytes of col2.
# Thus it contains "partial segment", and will not be
# upgraded to primary index.

create unique index idx on t1(`col1`,`col2`(31));

# However, we can add the primary key through alter table interfaces
alter table t1 add primary key idx3(`col1`,`col2`(31));

# Or we can create such primary key through create table interfaces
create table t2 (
        `col1` int not null,
        `col2` blob not null,
        `col3` time not null, primary key(`col1`,`col2`(31))) engine = innodb;
[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.