MySQL Bugs: #51546: unique index cannot upgrade to primary index if contains partial segment/column

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

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;

Sorry, but can you explain in details how one can check that UNIQUE index is NOT used as primary key by InnoDB?

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.

Thank you for the feedback.

Verified as described.