Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #1039536: mysqldump --innodb-optimize-keys can generate invalid table definitions

Description

**Reported in Launchpad by Dan Rogers last update 13-12-2012 01:03:56

Given the following table definition:

CREATE TABLE `chatroom_ban_list` (
`crid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT '0',
`admin_uid` int(11) NOT NULL DEFAULT '0',
`modified` int(11) NOT NULL DEFAULT '0',
KEY `crid_uid` (`crid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysqldump --innodb-optimize-keys will generate the following CREATE TABLE:

CREATE TABLE `chatroom_ban_list` (
`crid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT '0',
`admin_uid` int(11) NOT NULL DEFAULT '0',
`modified` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Which fails with the following error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Admittedly, the original table designed is flawed, but MySQL accepts it, so --innodb-optimize-keys should probably make sure that compound keys aren't the only keys on auto increment columns before moving them out of the CREATE TABLE statement.

Environment

None

Smart Checklist

Activity

lpjirasync January 22, 2018 at 4:57 PM

**Comment from Launchpad by: Alexey Kopytov on: 19-11-2012 16:13:30

Dan,

Sorry, this has somehow slipped through the cracks. I'm going to commit a fix for this bug soon.

lpjirasync January 22, 2018 at 4:57 PM

**Comment from Launchpad by: Dan Rogers on: 13-11-2012 18:08:12

It's been three months. Any movement on this?

lpjirasync January 22, 2018 at 4:57 PM

**Comment from Launchpad by: Raghavendra D Prabhu on: 22-08-2012 20:43:56

Confirmed.

This happens because contains_ignored_column doesn't handle composite keys, so the hash search for keys which may be AUTO_INCREMENT doesn't return positive if the key is composite (when the hash already contains the column which is AUTO_INCREMENT).

This is the fix:

=== modified file 'Percona-Server/client/mysqldump.c'
— Percona-Server/client/mysqldump.c 2012-08-07 06:10:00 +0000
+++ Percona-Server/client/mysqldump.c 2012-08-22 20:37:54 +0000
@@ -2457,17 +2457,18 @@
*/
static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
{

  • char *leftp, *rightp;
    -

  • if ((leftp = strchr(keydef, '(')) &&

  • (rightp = strchr(leftp, ')')) &&

  • rightp > leftp + 3 && /* (`...`) */

  • leftp[1] == '`' &&

  • rightp[-1] == '`' &&

  • my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))

  • return TRUE;
    -

  • return FALSE;
    + char *leftp, *token;
    + my_bool ret = FALSE;
    +
    + if (!(leftp = strdup(strchr(keydef, '('))))
    + return FALSE;
    +
    + if ((token = strtok(leftp, "()`,")) != NULL)
    + if (my_hash_search(ignored_columns, (uchar *) token, strlen(token)))
    + ret = TRUE;
    +
    + free(leftp);
    + return ret;
    }

Also, tested it.

Done

Details

Assignee

Reporter

Labels

Priority

Smart Checklist

Created January 22, 2018 at 4:56 PM
Updated January 22, 2018 at 4:57 PM
Resolved January 22, 2018 at 4:56 PM