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

LP #1233841: mysqldump --innodb-optimize-keys produces incorrect CREATE TABLE statement for partitioned tables

    Details

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

      Description

      **Reported in Launchpad by Rene' Cannao' last update 30-03-2016 12:06:45

      mysqldump --innodb-optimize-keys generates incorrect CREATE TABLE statement for partitioned tables.

      Here an example:

      mysql [localhost]

      {msandbox} (test) > SELECT @@version;
      --------------------
      | @@version |
      --------------------
      | 5.5.33-rel31.1-log |
      --------------------
      1 row in set (0.00 sec)

      mysql [localhost] {msandbox}

      ((none)) > use test
      Database changed
      mysql [localhost]

      {msandbox} (test) > CREATE TABLE tb1 (id INT NOT NULL AUTO_INCREMENT, created datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id, created), c1 INT, c2 INT, INDEX (c1), INDEX(c2))
      -> ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
      -> PARTITION BY RANGE (TO_DAYS(created))
      -> (PARTITION p83 VALUES LESS THAN (735484) ENGINE = InnoDB,
      -> PARTITION p84 VALUES LESS THAN (735491) ENGINE = InnoDB,
      -> PARTITION p85 VALUES LESS THAN (735498) ENGINE = InnoDB
      -> );
      Query OK, 0 rows affected (0.01 sec)


      mysql [localhost] {msandbox}

      (test) > SHOW CREATE TABLE tb1\G

                                                          • 1. row ***************************
                                                            Table: tb1
                                                            Create Table: CREATE TABLE `tb1` (
                                                            `id` int(11) NOT NULL AUTO_INCREMENT,
                                                            `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                                                            `c1` int(11) DEFAULT NULL,
                                                            `c2` int(11) DEFAULT NULL,
                                                            PRIMARY KEY (`id`,`created`),
                                                            KEY `c1` (`c1`),
                                                            KEY `c2` (`c2`)
                                                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
                                                            /*!50100 PARTITION BY RANGE (TO_DAYS(created))
                                                            (PARTITION p83 VALUES LESS THAN (735484) ENGINE = InnoDB,
                                                            PARTITION p84 VALUES LESS THAN (735491) ENGINE = InnoDB,
                                                            PARTITION p85 VALUES LESS THAN (735498) ENGINE = InnoDB) */
                                                            1 row in set (0.00 sec)

      [rene@db-bkup msb_5_5_33]$ ./my sqldump --innodb-optimize-keys test tb1
      – MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)

      -- Host: localhost Database: test
      – ------------------------------------------------------
      – Server version 5.5.33-rel31.1-log

      /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
      /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
      /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
      /*!40101 SET NAMES utf8 */;
      /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
      /*!40103 SET TIME_ZONE='+00:00' */;
      /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
      /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
      /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
      /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


      -- Table structure for table `tb1`

      DROP TABLE IF EXISTS `tb1`;
      /*!40101 SET @saved_cs_client = @@character_set_client */;
      /*!40101 SET character_set_client = utf8 */;
      CREATE TABLE `tb1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `c1` int(11) DEFAULT NULL,
      `c2` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`,`created`),
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
      /*!50100 PARTITION BY RANGE (TO_DAYS(created))
      (PARTITION p83 VALUES LESS THAN (735484) ENGINE = InnoDB,
      PARTITION p84 VALUES LESS THAN (735491) ENGINE = InnoDB,
      PARTITION p85 VALUES LESS THAN (735498) ENGINE = InnoDB) */;
      /*!40101 SET character_set_client = @saved_cs_client */;


      -- Dumping data for table `tb1`

      LOCK TABLES `tb1` WRITE;
      /*!40000 ALTER TABLE `tb1` DISABLE KEYS */;
      ALTER TABLE `tb1` ADD KEY `c1` (`c1`), ADD KEY `c2` (`c2`);
      /*!40000 ALTER TABLE `tb1` ENABLE KEYS */;
      UNLOCK TABLES;
      /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

      /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
      /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
      /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
      /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
      /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
      /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

      – Dump completed on 2013-10-01 13:14:35

      The CREATE TABLE statement has a syntax error here (extra comma) :

      PRIMARY KEY (`id`,`created`),
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

      =============================

      mysqldump --innodb-optimize-keys works correctly once partitioning is removed :

      mysql [localhost]

      {msandbox} (test) > ALTER TABLE tb1 REMOVE PARTITIONING;
      Query OK, 0 rows affected (0.03 sec)
      Records: 0 Duplicates: 0 Warnings: 0

      mysql [localhost] {msandbox}

      (test) > SHOW CREATE TABLE tb1\G

                                                          • 1. row ***************************
                                                            Table: tb1
                                                            Create Table: CREATE TABLE `tb1` (
                                                            `id` int(11) NOT NULL AUTO_INCREMENT,
                                                            `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
                                                            `c1` int(11) DEFAULT NULL,
                                                            `c2` int(11) DEFAULT NULL,
                                                            PRIMARY KEY (`id`,`created`),
                                                            KEY `c1` (`c1`),
                                                            KEY `c2` (`c2`)
                                                            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
                                                            1 row in set (0.00 sec)
                                                            mysql [localhost] {msandbox}

                                                            (test) > \q
                                                            Bye

      [rene@db-bkup msb_5_5_33]$ ./my sqldump --innodb-optimize-keys test tb1
      – MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)

      -- Host: localhost Database: test
      – ------------------------------------------------------
      – Server version 5.5.33-rel31.1-log

      /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
      /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
      /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
      /*!40101 SET NAMES utf8 */;
      /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
      /*!40103 SET TIME_ZONE='+00:00' */;
      /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
      /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
      /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
      /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


      -- Table structure for table `tb1`

      DROP TABLE IF EXISTS `tb1`;
      /*!40101 SET @saved_cs_client = @@character_set_client */;
      /*!40101 SET character_set_client = utf8 */;
      CREATE TABLE `tb1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      `c1` int(11) DEFAULT NULL,
      `c2` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`,`created`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;
      /*!40101 SET character_set_client = @saved_cs_client */;


      -- Dumping data for table `tb1`

      LOCK TABLES `tb1` WRITE;
      /*!40000 ALTER TABLE `tb1` DISABLE KEYS */;
      ALTER TABLE `tb1` ADD KEY `c1` (`c1`), ADD KEY `c2` (`c2`);
      /*!40000 ALTER TABLE `tb1` ENABLE KEYS */;
      UNLOCK TABLES;
      /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

      /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
      /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
      /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
      /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
      /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
      /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

      – Dump completed on 2013-10-01 13:19:31

        Smart Checklist

          Attachments

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: