mysqldump --innodb-optimize-keys can generate invalid table definitions
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Low
|
Alexey Kopytov | ||
5.1 |
Fix Released
|
Low
|
Alexey Kopytov | ||
5.5 |
Fix Released
|
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-
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-
Related branches
- Laurynas Biveinis (community): Approve
-
Diff: 339 lines (+222/-35)3 files modifiedPercona-Server/client/mysqldump.c (+108/-35)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+74/-0)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+40/-0)
- Laurynas Biveinis (community): Approve
-
Diff: 1449 lines (+108/-589)3 files modifiedPercona-Server/client/mysqldump.c (+108/-34)
Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+0/-367)
Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+0/-188)
Changed in percona-server: | |
importance: | Undecided → Low |
status: | New → Triaged |
tags: | added: contribution |
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' Server/ client/ mysqldump. c 2012-08-07 06:10:00 +0000 Server/ client/ mysqldump. c 2012-08-22 20:37:54 +0000 ignored_ column( HASH *ignored_columns, char *keydef) search( ignored_ columns, (uchar *) leftp + 2, rightp - leftp - 3)) strchr( keydef, '(')))) search( ignored_ columns, (uchar *) token, strlen(token)))
--- Percona-
+++ Percona-
@@ -2457,17 +2457,18 @@
*/
static my_bool contains_
{
- char *leftp, *rightp;
-
- if ((leftp = strchr(keydef, '(')) &&
- (rightp = strchr(leftp, ')')) &&
- rightp > leftp + 3 && /* (`...`) */
- leftp[1] == '`' &&
- rightp[-1] == '`' &&
- my_hash_
- return TRUE;
-
- return FALSE;
+ char *leftp, *token;
+ my_bool ret = FALSE;
+
+ if (!(leftp = strdup(
+ return FALSE;
+
+ if ((token = strtok(leftp, "()`,")) != NULL)
+ if (my_hash_
+ ret = TRUE;
+
+ free(leftp);
+ return ret;
}
Also, tested it.