Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4524

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

    Details

      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

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                przemyslaw.skibinski Przemyslaw Skibinski
                Reporter:
                paulstrong Paul A. Gusev
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day
                  1d