Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  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

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              przemyslaw.skibinski Przemyslaw Skibinski
              Reporter:
              paulstrong Paul A. Gusev
              Votes:
              0 Vote for this issue
              Watchers:
              4 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