Percona Server moved to https://jira.percona.com/projects/PS

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

Bug #1233841 reported by Rene' Cannao' on 2013-10-01

This bug affects 1 person

	Status	Importance	Assigned to	Milestone
Percona Server moved to https://jira.percona.com/projects/PS	Fix Released	Medium	Patryk Pomykalski	Percona Server moved to https://jira.percona.com/projects/PS 5.6.15-63.0
5.1	Won't Fix	Medium	Unassigned
5.5	Fix Released	Medium	Patryk Pomykalski	Percona Server moved to https://jira.percona.com/projects/PS 5.5.35-33.0
5.6	Fix Released	Medium	Patryk Pomykalski	Percona Server moved to https://jira.percona.com/projects/PS 5.6.15-63.0

Bug Description

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

--
-- Table structure for table `tb1`
--

--
-- Dumping data for table `tb1`
--

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

Related branches

lp:~pomyks/percona-server/5.5-bug1233841

Merged into lp:percona-server/5.5 at revision 596

Alexey Kopytov (community): Approve on 2013-11-25

lp:~akopytov/percona-server/bug1233841-5.6

Merged into lp:percona-server/5.6 at revision 499

Alexey Kopytov (community): Approve on 2013-11-25

Revision history for this message

Valerii Kravchuk (valerii-kravchuk) wrote on 2013-10-02:

Download full text (3.4 KiB)

This is easy to verify:

[openxs@chief p5.5]$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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, 6 warnings (0.19 sec)

mysql> 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)

mysql> exit
Bye
[openxs@chief p5.5]$ bin/mysqldump --no-defaults --innodb-optimize-keys -uroot test tb1
-- MySQL dump 10.13 Distrib 5.5.32, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.5.32

--
-- Table structure for table `tb1`
--

This is easy to verify:

[openxs@chief p5.5]$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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, 6 warnings (0.19 sec)

mysql> 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)

mysql> exit
Bye
[openxs@chief p5.5]$ bin/mysqldump --no-defaults --innodb-optimize-keys -uroot test tb1
-- MySQL dump 10.13  Distrib 5.5.32, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.5.32

--
-- 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 */;
...

Revision history for this message

Valerii Kravchuk (valerii-kravchuk) wrote on 2013-10-02:

mysqldump utilities from PS 5.1.x and 5.6.x are also affected.

Yaning Zhu (yzhu) on 2013-11-18

information type:	Public → Public Security
information type:	Public Security → Private Security
information type:	Private Security → Public Security

Yaning Zhu (yzhu) on 2013-11-18

information type:

Public Security → Public

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-25:

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1426

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.