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

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

    XMLWordPrintable

    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

        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