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

Bug #1039536 reported by Dan Rogers on 2012-08-21
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Server
Low
Alexey Kopytov
5.1
Low
Alexey Kopytov
5.5
Low
Alexey Kopytov

Bug Description

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.

Related branches

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.

Stewart Smith (stewart) on 2012-08-22
Changed in percona-server:
importance: Undecided → Low
status: New → Triaged
tags: added: contribution
Dan Rogers (drogers-l) wrote :

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

Alexey Kopytov (akopytov) wrote :

Dan,

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers