| Bug #51378 | Init "ref_length" to correct value, in case an out of bound MySQL primary_key | ||
|---|---|---|---|
| Submitted: | 22 Feb 2010 9:57 | Modified: | 20 Jul 2010 16:11 |
| Reporter: | Jimmy Yang | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Jimmy Yang | CPU Architecture: | Any |
[22 Feb 2010 10:44]
Marko Mäkelä
This was forked from Bug #47622 follow-up.
[22 Feb 2010 13:02]
MySQL Verification Team
Thank you for the bug report.
[25 Feb 2010 13:57]
Jimmy Yang
We decided that unique index created through "create index" and "alter table add unique index" interfaces would not be treated as primary index for InnoDB either. This is consistent with that of MySQL and documented in the Manual: "CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead". This will address the mismatch error message printed described in the bug report
[25 Feb 2010 14:20]
Georgi Kodinov
Jimmy, This bug is a special case : the unique index is over a partial column. And these doesn't qualify for the UNIQUE->PRIMARY KEY upgrade (even when all the other conditions are met). The condition in the innodb code needs some extending to check for partial keys when promoting a UNIQUE index to PRIMARY KEY
[10 Mar 2010 9:20]
Jimmy Yang
------------------------------------------------------------------------ r6789 | jyang | 2010-03-10 01:18:18 -0800 (Wed, 10 Mar 2010) | 10 lines branches/zip: If a unique index is on a column prefix, such unique index cannot be upgrade to primary index even if there is no primary index already defined. Also fix possible corruption when initialize "ref_length" value in case there is a mismatch between MySQL and InnoDB primary key. Fix bug #51378: "Init 'ref_length' to correct value, in case an out of bound MySQL primary_key".
[15 Mar 2010 9:41]
Valeriy Kravchuk
Bug #52056 was marked as a duplicate of this one.
[1 Apr 2010 12:14]
Bugs System
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/104880 3526 Sergey Vojtovich 2010-04-01 Applying InnoDB snapshot, fixes BUG#51378. Detailed revision comments: r6789 | jyang | 2010-03-10 11:18:18 +0200 (Wed, 10 Mar 2010) | 10 lines branches/zip: If a unique index is on a column prefix, such unique index cannot be upgrade to primary index even if there is no primary index already defined. Also fix possible corruption when initialize "ref_length" value in case there is a mismatch between MySQL and InnoDB primary key. Fix bug #51378: "Init 'ref_length' to correct value, in case an out of bound MySQL primary_key". rb://262 approved by Marko.
[6 Apr 2010 7:57]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)
[28 Apr 2010 4:01]
James Day
The fix for this is in the InnoDB Plugin version 1.0.7 which was included with MySQL 4.1.46.
[28 Apr 2010 4:11]
James Day
The fix for this is in the InnoDB Plugin version 1.0.7 which was included with MySQL 5.1.46. Not 4.1.46.
[5 May 2010 15:07]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 5:54]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:23]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:51]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[15 Jun 2010 8:11]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:26]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:55]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:33]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:20]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[20 Jul 2010 16:11]
Paul DuBois
Noted in 5.1.46, 5.5.4 changelogs. A unique index on a column prefix could not be upgraded to a primary index even if there was no primary index already defined.
[20 Jul 2010 18:30]
James Day
Paul, I suggest changing from: A unique index on a column prefix could not be upgraded to a primary index even if there was no primary index already defined. To: If there wasn't already a primary key, InnoDB could attempt to use a unique key with a column prefix as a primary key if it was added with ALTER TABLE. Only keys without column prefixes are acceptable as primary keys.
Description: This is from an observation of a case reported by Shane. MySQL could pass MAX_KEY as primary_key value to InnoDB while InnoDB thinking there exists a primary key. A very simple repro: create table `t5`( `col1` int not null, `col2` blob not null, `col3` time not null)engine=innodb; create unique index `idx5` on `t5`(`col1`,`col2`(31)); 100222 10:12:06 [ERROR] Table ./test/t5 has a primary key in InnoDB data dictionary, but not in MySQL! it is deemed to be an MySQL bug: ========================= In fact, this seems to be a MySQL issue or its behavior change. There creates a unique index (idx5) on all non null column, so InnoDB has following assumption on MySQL: innobase_create_key_def() { /* If there is a UNIQUE INDEX consisting entirely of NOT NULL columns, MySQL will treat it as a PRIMARY KEY unless the table already has one. */ } So InnoDB treat this as a primary key. Unfortunately, MySQL somehow does not treat it as so, and set the primary_key still to be MAX_KEY, which means there is NO primary key defined. Thus the error message: (gdb) print table->s->primary_key $19 = 64 =================== However, for InnoDB, we could continue to initialize "ref_length" with this out of bound "primary_key" value: if (!row_table_got_default_clust_index(ib_table)) { if (primary_key >= MAX_KEY) { sql_print_error("Table %s has a primary key in InnoDB data " "dictionary, but not in MySQL!", name); } prebuilt->clust_index_was_generated = FALSE; /* MySQL allocates the buffer for ref. key_info->key_length includes space for all key columns + one byte for each column that may be NULL. ref_length must be as exact as possible to save space, because all row reference buffers are allocated based on ref_length. */ ref_length = table->key_info[primary_key].key_length; } A more appropriate fix here is to initialize it to our internal primary key length. And server can continue as long as MySQL does not provide the out of bound key number again. How to repeat: create table `t5`( `col1` int not null, `col2` blob not null, `col3` time not null)engine=innodb; create unique index `idx5` on `t5`(`col1`,`col2`(31)); Suggested fix: A more appropriate fix here is to initialize it to InnoDB internal primary key length. And server can continue as long as MySQL does not provide the out of bound key number again.