Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

innodb-optimize-keys fails when variable name is a prefix

Description

Hello!

I have founded a bug.

When I get show create table, I can see that:

CREATE TABLE `mytable` ( `note_id` int(5) NOT NULL AUTO_INCREMENT, `note` varchar(50) NOT NULL, `note_rus` varchar(50) NOT NULL, PRIMARY KEY (`note`), UNIQUE KEY `note_id` (`note_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3074 DEFAULT CHARSET=cp1251

Then I have mysqdump, and I can see in sql file that:

CREATE TABLE `runtime_note` ( `note_id` int(5) NOT NULL AUTO_INCREMENT, `note` varchar(50) NOT NULL, `note_rus` varchar(50) NOT NULL, PRIMARY KEY (`note`) ) ENGINE=InnoDB AUTO_INCREMENT=3074 DEFAULT CHARSET=cp1251;

In SQL dump I have lost UNIQUE KEY, but this key can be founded later:

 

... ALTER TABLE `runtime_note` ADD UNIQUE KEY `note_id` (`note_id`); ...

But, when I try to load this SQL dump to my a new MySQL instance, I have an error:

ERROR 1075 (42000) at line 34: Incorrect table definition; there can be only one auto column and it must be defined as a key

Error occurs only if a variable name (`note`) is a prefix of the name of the second variable (`note_id`) with AUTO_INCREMENT

Environment

None

Smart Checklist

Activity

Show:

Przemyslaw Skibinski July 3, 2018 at 9:53 AM

Error occurs only if a variable name (`note`) is a prefix of the name of the second variable (`note_id`) with AUTO_INCREMENT

goosev.pavel June 15, 2018 at 5:02 PM

Thanks for your answer. 

--innodb-optimize-keys=TRUE is a default option

now I understand the is the bug. Thank you.

Przemyslaw Skibinski June 15, 2018 at 10:41 AM

Paul, --innodb-optimize-keys=TRUE is a feature and you have found a bug in this feature

goosev.pavel June 14, 2018 at 11:06 AM

@Lauryunas

I am using 5.7.22-22-1.trusty

My coworkers told me about https://perconadev.atlassian.net/browse/PS-1125#icft=PS-1125, but I cannot understand, this a bug or a feature?

Przemyslaw Skibinski June 11, 2018 at 12:57 PM

I compiled 5.7 at da3de7a which is before https://perconadev.atlassian.net/browse/PS-1125#icft=PS-1125 / https://perconadev.atlassian.net/browse/PS-3863#icft=PS-3863 changes and it has the same issue.

Done

Details

Assignee

Reporter

Labels

Time tracking

1d logged

Fix versions

Affects versions

Priority

Smart Checklist

Created June 6, 2018 at 7:09 AM
Updated March 6, 2024 at 1:07 PM
Resolved July 3, 2018 at 9:52 AM
Configure

Flag notifications