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

LP #1081016: mysqldump --innodb-optimize-keys may produce invalid SQL with explicitly named FK constraints

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Alexey Kopytov last update 31-05-2013 08:07:56

      Given the following table:

      CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `a` (`a`),
      CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1

      mysqldump --innodb-optimize-keys generates the following SQL:

      CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      /*!40101 SET character_set_client = @saved_cs_client */;

      LOCK TABLES `t2` WRITE;
      /*!40000 ALTER TABLE `t2` DISABLE KEYS */;
      ALTER TABLE `t2` ADD KEY `a` (`a`);
      /*!40000 ALTER TABLE `t2` ENABLE KEYS */;
      UNLOCK TABLES;

      which fails on import when trying to execute ALTER TABLE with:

      ERROR 1280 (42000): Incorrect index name 'a'

      The key detail here is that the table definition contains an explicitly named FK constrain, and its name matches the foreign key name.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  lpjirasync lpjirasync (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: