mysqldump --innodb-optimize-keys can generate invalid table definitions

[Dan Rogers]

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.

[Raghavendra D Prabhu (raghavendra-prabhu)]

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.

[Dan Rogers (drogers-l)]

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

[Alexey Kopytov (akopytov)]

Dan,

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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