Merge lp:~akopytov/percona-server/bugs-1039536-1081003-5.5 into lp:percona-server/5.5

Proposed by Alexey Kopytov on 2012-11-20
Status: Merged
Approved by: Laurynas Biveinis on 2012-11-30
Approved revision: 352
Merged at revision: 365
Proposed branch: lp:~akopytov/percona-server/bugs-1039536-1081003-5.5
Merge into: lp:percona-server/5.5
Diff against target: 1449 lines (+108/-589)
3 files modified
Percona-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)
To merge this branch: bzr merge lp:~akopytov/percona-server/bugs-1039536-1081003-5.5
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) 2012-11-20 Approve on 2012-11-30
Review via email: mp+135092@code.launchpad.net

Description of the change

Bug #1039536: mysqldump --innodb-optimize-keys can generate invalid
              table definitions
Bug #1081003: mysqldump --innodb-optimize-keys handles AUTO_INCREMENT
              columns inefficiently

The problem in bug #1039536 was that mysqldump --innodb-optimize-keys
did not handle composite indexes correctly when verifying if the
optimization is applicable with respect to AUTO_INCREMENT columns. When
an AUTO_INCREMENT column was encountered in the SHOW CREATE TABLE
output, the column was marked so that subsequent index specifications
involving that column would not be used in deferred index creation
mechanism, as MySQL does not allow creating tables with unindexed
AUTO_INCREMENT columns. However, the code checking if an index
specification involves a previously marked AUTO_INCREMENT column failed
to handle composite keys correctly, so those keys were optimized away
resulting in an invalid table definition.

A closely related problem in bug #1081003 was that even in cases where
indexes with AUTO_INCREMENT columns where correctly detected, mysqldump
prevented all such keys from optimization, even though it is sufficient
to skip just one (e.g. the first one).

Fixed by refactoring the AUTO_INCREMENT handling code in mysqldump
--innodb-optimize-keys to:

- process composite keys correctly

- prevent only the first key indexing an AUTO_INCREMENT columns from
  optimization

- use a simple pointer instead of the hash table to keep track of
  AUTO_INCREMENT column with a simple pointer, as the server only allows
  one such column per table anyway

To post a comment you must log in.

Same minor comments as for 5.1.

review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'Percona-Server/client/mysqldump.c'
2--- Percona-Server/client/mysqldump.c 2012-10-17 03:47:45 +0000
3+++ Percona-Server/client/mysqldump.c 2012-11-20 09:38:30 +0000
4@@ -84,6 +84,13 @@
5 #define IGNORE_DATA 0x01 /* don't dump data for this table */
6 #define IGNORE_INSERT_DELAYED 0x02 /* table doesn't support INSERT DELAYED */
7
8+typedef enum {
9+ KEY_TYPE_NONE,
10+ KEY_TYPE_PRIMARY,
11+ KEY_TYPE_UNIQUE,
12+ KEY_TYPE_NON_UNIQUE
13+} key_type_t;
14+
15 /* general_log or slow_log tables under mysql database */
16 static inline my_bool general_log_or_slow_log_tables(const char *db,
17 const char *table)
18@@ -2454,20 +2461,62 @@
19 }
20
21 /*
22- Parse the specified key definition string and check if the key indexes
23- any of the columns from ignored_columns.
24+ Parse the specified key definition string and check if the key contains an
25+ AUTO_INCREMENT column as the first key part. We only check for the first key
26+ part, because unlike MyISAM, InnoDB does not allow the AUTO_INCREMENT column
27+ as a secondary key column, i.e. the AUTO_INCREMENT column would not be
28+ considered indexed for such key specification.
29 */
30-static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
31+static my_bool contains_autoinc_column(const char *autoinc_column,
32+ const char *keydef,
33+ key_type_t type)
34 {
35- char *leftp, *rightp;
36-
37- if ((leftp = strchr(keydef, '(')) &&
38- (rightp = strchr(leftp, ')')) &&
39- rightp > leftp + 3 && /* (`...`) */
40- leftp[1] == '`' &&
41- rightp[-1] == '`' &&
42- my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))
43- return TRUE;
44+ char *from, *to;
45+ uint idnum;
46+
47+ DBUG_ASSERT(type != KEY_TYPE_NONE);
48+
49+ if (autoinc_column == NULL || !(from= strchr(keydef, '`')))
50+ return FALSE;
51+
52+ to= from;
53+ idnum= 0;
54+
55+ while ((to= strchr(to + 1, '`')))
56+ {
57+ /*
58+ Double backticks represent a backtick in identifier, rather than a quote
59+ character.
60+ */
61+ if (to[1] == '`')
62+ {
63+ to++;
64+ continue;
65+ }
66+
67+ if (to <= from + 1)
68+ break; /* Broken key definition */
69+
70+ idnum++;
71+
72+ /*
73+ Skip the check if it's the first identifier and we are processing a
74+ secondary key.
75+ */
76+ if ((type == KEY_TYPE_PRIMARY || idnum != 1) &&
77+ !strncmp(autoinc_column, from + 1, to - from - 1))
78+ return TRUE;
79+
80+ /*
81+ Check only the first (for PRIMARY KEY) or the second (for secondary keys)
82+ quoted identifier.
83+ */
84+ if ((idnum == 1 + test(type != KEY_TYPE_PRIMARY)) ||
85+ !(from= strchr(to + 1, '`')))
86+ break;
87+
88+ to= from;
89+ }
90
91 return FALSE;
92 }
93@@ -2495,13 +2544,11 @@
94 static void skip_secondary_keys(char *create_str, my_bool has_pk)
95 {
96 char *ptr, *strend;
97- char *last_comma = NULL;
98- HASH ignored_columns;
99+ char *last_comma= NULL;
100 my_bool pk_processed= FALSE;
101-
102- if (my_hash_init(&ignored_columns, charset_info, 16, 0, 0,
103- (my_hash_get_key) get_table_key, my_free, 0))
104- exit(EX_EOM);
105+ char *autoinc_column= NULL;
106+ my_bool has_autoinc= FALSE;
107+ key_type_t type;
108
109 strend= create_str + strlen(create_str);
110
111@@ -2509,7 +2556,6 @@
112 while (*ptr)
113 {
114 char *tmp, *orig_ptr, c;
115- my_bool is_unique= FALSE;
116
117 orig_ptr= ptr;
118 /* Skip leading whitespace */
119@@ -2522,12 +2568,22 @@
120 c= *tmp;
121 *tmp= '\0'; /* so strstr() only processes the current line */
122
123+ if (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1))
124+ type= KEY_TYPE_UNIQUE;
125+ else if (!strncmp(ptr, "KEY ", sizeof("KEY ") - 1))
126+ type= KEY_TYPE_NON_UNIQUE;
127+ else if (!strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
128+ type= KEY_TYPE_PRIMARY;
129+ else
130+ type= KEY_TYPE_NONE;
131+
132+ has_autoinc= (type != KEY_TYPE_NONE) ?
133+ contains_autoinc_column(autoinc_column, ptr, type) : FALSE;
134+
135 /* Is it a secondary index definition? */
136 if (c == '\n' &&
137- (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
138- (pk_processed || !has_pk)) ||
139- !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) &&
140- !contains_ignored_column(&ignored_columns, ptr))
141+ ((type == KEY_TYPE_UNIQUE && (pk_processed || !has_pk)) ||
142+ type == KEY_TYPE_NON_UNIQUE) && !has_autoinc)
143 {
144 char *data, *end= tmp - 1;
145
146@@ -2560,23 +2616,41 @@
147 *last_comma= ',';
148 }
149
150- if ((has_pk && is_unique && !pk_processed) ||
151- !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
152+ /*
153+ If we are skipping a key which indexes an AUTO_INCREMENT column, it is
154+ safe to optimize all subsequent keys, i.e. we should not be checking for
155+ that column anymore.
156+ */
157+ if (type != KEY_TYPE_NONE && has_autoinc)
158+ {
159+ DBUG_ASSERT(autoinc_column != NULL);
160+
161+ my_free(autoinc_column);
162+ autoinc_column= NULL;
163+ }
164+
165+ if ((has_pk && type == KEY_TYPE_UNIQUE && !pk_processed) ||
166+ type == KEY_TYPE_PRIMARY)
167 pk_processed= TRUE;
168
169 if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
170 {
171 /*
172- If a secondary key is defined on this column later,
173- it cannot be skipped, as CREATE TABLE would fail on import.
174+ The first secondary key defined on this column later cannot be
175+ skipped, as CREATE TABLE would fail on import. Unless there is a
176+ PRIMARY KEY and it indexes that column.
177 */
178- for (end= ptr + 1; *end != '`' && *end != '\0'; end++);
179- if (*end == '`' && end > ptr + 1 &&
180- my_hash_insert(&ignored_columns,
181- (uchar *) my_strndup(ptr + 1,
182- end - ptr - 1, MYF(0))))
183+ for (end= ptr + 1;
184+ /* Skip double backticks as they are a part of identifier */
185+ *end != '\0' && (*end != '`' || end[1] == '`');
186+ end++)
187+ /* empty */;
188+
189+ if (*end == '`' && end > ptr + 1)
190 {
191- exit(EX_EOM);
192+ DBUG_ASSERT(autoinc_column == NULL);
193+
194+ autoinc_column= my_strndup(ptr + 1, end - ptr - 1, MYF(MY_FAE));
195 }
196 }
197
198@@ -2588,7 +2662,7 @@
199 }
200 }
201
202- my_hash_free(&ignored_columns);
203+ my_free(autoinc_column);
204 }
205
206 /*
207
208=== added file 'Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result'
209--- Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 1970-01-01 00:00:00 +0000
210+++ Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2012-11-20 09:38:30 +0000
211@@ -0,0 +1,441 @@
212+#
213+# Test the --innodb-optimize-keys option.
214+#
215+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
216+######################################
217+
218+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
219+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
220+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
221+/*!40101 SET NAMES utf8 */;
222+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
223+/*!40103 SET TIME_ZONE='+00:00' */;
224+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
225+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
226+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
227+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
228+DROP TABLE IF EXISTS `t1`;
229+/*!40101 SET @saved_cs_client = @@character_set_client */;
230+/*!40101 SET character_set_client = utf8 */;
231+CREATE TABLE `t1` (
232+ `a` int(11) NOT NULL,
233+ `b` int(11) DEFAULT NULL,
234+ PRIMARY KEY (`a`),
235+ KEY `b` (`b`)
236+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
237+/*!40101 SET character_set_client = @saved_cs_client */;
238+
239+LOCK TABLES `t1` WRITE;
240+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
241+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
242+UNLOCK TABLES;
243+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
244+
245+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
246+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
247+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
248+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
249+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
250+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
251+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
252+
253+######################################
254+DROP TABLE t1;
255+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
256+INSERT INTO t2 VALUES (0), (1), (2);
257+CREATE TABLE t1 (
258+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
259+a INT, b VARCHAR(255), c DECIMAL(10,3),
260+KEY (b),
261+UNIQUE KEY uniq(c,a),
262+FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
263+) ENGINE=InnoDB;
264+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
265+######################################
266+
267+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
268+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
269+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
270+/*!40101 SET NAMES utf8 */;
271+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
272+/*!40103 SET TIME_ZONE='+00:00' */;
273+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
274+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
275+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
276+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
277+DROP TABLE IF EXISTS `t1`;
278+/*!40101 SET @saved_cs_client = @@character_set_client */;
279+/*!40101 SET character_set_client = utf8 */;
280+CREATE TABLE `t1` (
281+ `id` int(11) NOT NULL AUTO_INCREMENT,
282+ `a` int(11) DEFAULT NULL,
283+ `b` varchar(255) DEFAULT NULL,
284+ `c` decimal(10,3) DEFAULT NULL,
285+ PRIMARY KEY (`id`),
286+ CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE
287+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
288+/*!40101 SET character_set_client = @saved_cs_client */;
289+
290+LOCK TABLES `t1` WRITE;
291+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
292+INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200);
293+ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`);
294+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
295+UNLOCK TABLES;
296+DROP TABLE IF EXISTS `t2`;
297+/*!40101 SET @saved_cs_client = @@character_set_client */;
298+/*!40101 SET character_set_client = utf8 */;
299+CREATE TABLE `t2` (
300+ `a` int(11) NOT NULL,
301+ PRIMARY KEY (`a`)
302+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
303+/*!40101 SET character_set_client = @saved_cs_client */;
304+
305+LOCK TABLES `t2` WRITE;
306+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
307+INSERT INTO `t2` VALUES (0),(1),(2);
308+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
309+UNLOCK TABLES;
310+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
311+
312+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
313+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
314+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
315+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
316+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
317+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
318+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
319+
320+######################################
321+DROP TABLE t1, t2;
322+CREATE TABLE t1 (
323+id INT NOT NULL AUTO_INCREMENT,
324+KEY (id)
325+) ENGINE=InnoDB;
326+CREATE TABLE t2 (
327+id INT NOT NULL AUTO_INCREMENT,
328+UNIQUE KEY (id)
329+) ENGINE=InnoDB;
330+INSERT INTO t1 VALUES (), (), ();
331+INSERT INTO t2 VALUES (), (), ();
332+######################################
333+
334+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
335+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
336+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
337+/*!40101 SET NAMES utf8 */;
338+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
339+/*!40103 SET TIME_ZONE='+00:00' */;
340+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
341+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
342+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
343+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
344+DROP TABLE IF EXISTS `t1`;
345+/*!40101 SET @saved_cs_client = @@character_set_client */;
346+/*!40101 SET character_set_client = utf8 */;
347+CREATE TABLE `t1` (
348+ `id` int(11) NOT NULL AUTO_INCREMENT,
349+ KEY `id` (`id`)
350+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
351+/*!40101 SET character_set_client = @saved_cs_client */;
352+
353+LOCK TABLES `t1` WRITE;
354+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
355+INSERT INTO `t1` VALUES (1),(2),(3);
356+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
357+UNLOCK TABLES;
358+DROP TABLE IF EXISTS `t2`;
359+/*!40101 SET @saved_cs_client = @@character_set_client */;
360+/*!40101 SET character_set_client = utf8 */;
361+CREATE TABLE `t2` (
362+ `id` int(11) NOT NULL AUTO_INCREMENT,
363+ UNIQUE KEY `id` (`id`)
364+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
365+/*!40101 SET character_set_client = @saved_cs_client */;
366+
367+LOCK TABLES `t2` WRITE;
368+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
369+INSERT INTO `t2` VALUES (1),(2),(3);
370+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
371+UNLOCK TABLES;
372+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
373+
374+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
375+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
376+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
377+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
378+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
379+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
380+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
381+
382+######################################
383+DROP TABLE t1, t2;
384+CREATE TABLE t1 (
385+a INT NOT NULL,
386+UNIQUE KEY (a)) ENGINE=InnoDB;
387+CREATE TABLE t2 (
388+a INT NOT NULL,
389+b INT NOT NULL,
390+UNIQUE KEY (a,b)) ENGINE=InnoDB;
391+CREATE TABLE t3 (
392+a INT,
393+b INT,
394+UNIQUE KEY (a,b)) ENGINE=InnoDB;
395+CREATE TABLE t4 (
396+a INT NOT NULL,
397+b INT NOT NULL,
398+PRIMARY KEY (a,b),
399+UNIQUE KEY(b)) ENGINE=InnoDB;
400+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
401+TABLE_SCHEMA=DATABASE() AND
402+TABLE_NAME='t1' AND
403+COLUMN_KEY='PRI';
404+COUNT(*)
405+1
406+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
407+TABLE_SCHEMA=DATABASE() AND
408+TABLE_NAME='t2' AND
409+COLUMN_KEY='PRI';
410+COUNT(*)
411+2
412+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
413+TABLE_SCHEMA=DATABASE() AND
414+TABLE_NAME='t3' AND
415+COLUMN_KEY='PRI';
416+COUNT(*)
417+0
418+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
419+TABLE_SCHEMA=DATABASE() AND
420+TABLE_NAME='t4' AND
421+COLUMN_KEY='PRI';
422+COUNT(*)
423+2
424+INSERT INTO t1 VALUES (1), (2), (3);
425+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
426+INSERT INTO t3 SELECT * FROM t2;
427+INSERT INTO t4 SELECT * FROM t2;
428+######################################
429+
430+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
431+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
432+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
433+/*!40101 SET NAMES utf8 */;
434+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
435+/*!40103 SET TIME_ZONE='+00:00' */;
436+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
437+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
438+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
439+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
440+DROP TABLE IF EXISTS `t1`;
441+/*!40101 SET @saved_cs_client = @@character_set_client */;
442+/*!40101 SET character_set_client = utf8 */;
443+CREATE TABLE `t1` (
444+ `a` int(11) NOT NULL,
445+ UNIQUE KEY `a` (`a`)
446+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
447+/*!40101 SET character_set_client = @saved_cs_client */;
448+
449+LOCK TABLES `t1` WRITE;
450+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
451+INSERT INTO `t1` VALUES (1),(2),(3);
452+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
453+UNLOCK TABLES;
454+DROP TABLE IF EXISTS `t2`;
455+/*!40101 SET @saved_cs_client = @@character_set_client */;
456+/*!40101 SET character_set_client = utf8 */;
457+CREATE TABLE `t2` (
458+ `a` int(11) NOT NULL,
459+ `b` int(11) NOT NULL,
460+ UNIQUE KEY `a` (`a`,`b`)
461+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
462+/*!40101 SET character_set_client = @saved_cs_client */;
463+
464+LOCK TABLES `t2` WRITE;
465+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
466+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
467+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
468+UNLOCK TABLES;
469+DROP TABLE IF EXISTS `t3`;
470+/*!40101 SET @saved_cs_client = @@character_set_client */;
471+/*!40101 SET character_set_client = utf8 */;
472+CREATE TABLE `t3` (
473+ `a` int(11) DEFAULT NULL,
474+ `b` int(11) DEFAULT NULL
475+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
476+/*!40101 SET character_set_client = @saved_cs_client */;
477+
478+LOCK TABLES `t3` WRITE;
479+/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
480+INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
481+ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
482+/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
483+UNLOCK TABLES;
484+DROP TABLE IF EXISTS `t4`;
485+/*!40101 SET @saved_cs_client = @@character_set_client */;
486+/*!40101 SET character_set_client = utf8 */;
487+CREATE TABLE `t4` (
488+ `a` int(11) NOT NULL,
489+ `b` int(11) NOT NULL,
490+ PRIMARY KEY (`a`,`b`)
491+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
492+/*!40101 SET character_set_client = @saved_cs_client */;
493+
494+LOCK TABLES `t4` WRITE;
495+/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
496+INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
497+ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
498+/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
499+UNLOCK TABLES;
500+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
501+
502+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
503+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
504+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
505+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
506+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
507+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
508+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
509+
510+######################################
511+DROP TABLE t1, t2, t3, t4;
512+CREATE TABLE t1 (
513+id INT NOT NULL PRIMARY KEY
514+) ENGINE=InnoDB;
515+CREATE TABLE t2 (
516+id INT NOT NULL AUTO_INCREMENT,
517+a INT NOT NULL,
518+PRIMARY KEY (id),
519+KEY (a),
520+FOREIGN KEY (a) REFERENCES t2 (id)
521+) ENGINE=InnoDB;
522+INSERT INTO t1 VALUES (1), (2), (3);
523+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
524+######################################
525+
526+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
527+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
528+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
529+/*!40101 SET NAMES utf8 */;
530+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
531+/*!40103 SET TIME_ZONE='+00:00' */;
532+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
533+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
534+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
535+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
536+DROP TABLE IF EXISTS `t1`;
537+/*!40101 SET @saved_cs_client = @@character_set_client */;
538+/*!40101 SET character_set_client = utf8 */;
539+CREATE TABLE `t1` (
540+ `id` int(11) NOT NULL,
541+ PRIMARY KEY (`id`)
542+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
543+/*!40101 SET character_set_client = @saved_cs_client */;
544+
545+LOCK TABLES `t1` WRITE;
546+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
547+INSERT INTO `t1` VALUES (1),(2),(3);
548+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
549+UNLOCK TABLES;
550+DROP TABLE IF EXISTS `t2`;
551+/*!40101 SET @saved_cs_client = @@character_set_client */;
552+/*!40101 SET character_set_client = utf8 */;
553+CREATE TABLE `t2` (
554+ `id` int(11) NOT NULL AUTO_INCREMENT,
555+ `a` int(11) NOT NULL,
556+ PRIMARY KEY (`id`),
557+ CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
558+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
559+/*!40101 SET character_set_client = @saved_cs_client */;
560+
561+LOCK TABLES `t2` WRITE;
562+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
563+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
564+ALTER TABLE `t2` ADD KEY `a` (`a`);
565+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
566+UNLOCK TABLES;
567+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
568+
569+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
570+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
571+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
572+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
573+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
574+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
575+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
576+
577+######################################
578+DROP TABLE t1, t2;
579+CREATE TABLE t1 (
580+id INT NOT NULL AUTO_INCREMENT,
581+uid INT NOT NULL,
582+`id``` INT NOT NULL,
583+```id` INT NOT NULL,
584+# The following ones may be skipped and used in ALTER TABLE later
585+KEY k1 (```id`, id),
586+KEY k2 (```id`, `id```),
587+# The following one should be kept in CREATE TABLE
588+KEY k3 (id, uid),
589+# The following one may be skipped again
590+KEY k4 (id, `id```)
591+) ENGINE=InnoDB;
592+CREATE TABLE t2 (
593+id INT NOT NULL AUTO_INCREMENT,
594+PRIMARY KEY (id),
595+KEY k1 (id),
596+KEY k2 (id)
597+) ENGINE=InnoDB;
598+######################################
599+
600+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
601+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
602+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
603+/*!40101 SET NAMES utf8 */;
604+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
605+/*!40103 SET TIME_ZONE='+00:00' */;
606+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
607+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
608+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
609+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
610+DROP TABLE IF EXISTS `t1`;
611+/*!40101 SET @saved_cs_client = @@character_set_client */;
612+/*!40101 SET character_set_client = utf8 */;
613+CREATE TABLE `t1` (
614+ `id` int(11) NOT NULL AUTO_INCREMENT,
615+ `uid` int(11) NOT NULL,
616+ `id``` int(11) NOT NULL,
617+ ```id` int(11) NOT NULL,
618+ KEY `k3` (`id`,`uid`)
619+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
620+/*!40101 SET character_set_client = @saved_cs_client */;
621+
622+LOCK TABLES `t1` WRITE;
623+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
624+ALTER TABLE `t1` ADD KEY `k1` (```id`,`id`), ADD KEY `k2` (```id`,`id```), ADD KEY `k4` (`id`,`id```);
625+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
626+UNLOCK TABLES;
627+DROP TABLE IF EXISTS `t2`;
628+/*!40101 SET @saved_cs_client = @@character_set_client */;
629+/*!40101 SET character_set_client = utf8 */;
630+CREATE TABLE `t2` (
631+ `id` int(11) NOT NULL AUTO_INCREMENT,
632+ PRIMARY KEY (`id`)
633+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
634+/*!40101 SET character_set_client = @saved_cs_client */;
635+
636+LOCK TABLES `t2` WRITE;
637+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
638+ALTER TABLE `t2` ADD KEY `k1` (`id`), ADD KEY `k2` (`id`);
639+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
640+UNLOCK TABLES;
641+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
642+
643+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
644+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
645+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
646+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
647+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
648+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
649+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
650+
651+######################################
652+DROP TABLE t1, t2;
653
654=== removed file 'Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result'
655--- Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2012-04-18 23:26:09 +0000
656+++ Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 1970-01-01 00:00:00 +0000
657@@ -1,367 +0,0 @@
658-#
659-# Test the --innodb-optimize-keys option.
660-#
661-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
662-######################################
663-
664-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
665-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
666-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
667-/*!40101 SET NAMES utf8 */;
668-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
669-/*!40103 SET TIME_ZONE='+00:00' */;
670-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
671-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
672-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
673-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
674-DROP TABLE IF EXISTS `t1`;
675-/*!40101 SET @saved_cs_client = @@character_set_client */;
676-/*!40101 SET character_set_client = utf8 */;
677-CREATE TABLE `t1` (
678- `a` int(11) NOT NULL,
679- `b` int(11) DEFAULT NULL,
680- PRIMARY KEY (`a`),
681- KEY `b` (`b`)
682-) ENGINE=MyISAM DEFAULT CHARSET=latin1;
683-/*!40101 SET character_set_client = @saved_cs_client */;
684-
685-LOCK TABLES `t1` WRITE;
686-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
687-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
688-UNLOCK TABLES;
689-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
690-
691-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
692-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
693-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
694-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
695-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
696-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
697-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
698-
699-######################################
700-DROP TABLE t1;
701-CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
702-INSERT INTO t2 VALUES (0), (1), (2);
703-CREATE TABLE t1 (
704-id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
705-a INT, b VARCHAR(255), c DECIMAL(10,3),
706-KEY (b),
707-UNIQUE KEY uniq(c,a),
708-FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
709-) ENGINE=InnoDB;
710-INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
711-######################################
712-
713-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
714-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
715-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
716-/*!40101 SET NAMES utf8 */;
717-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
718-/*!40103 SET TIME_ZONE='+00:00' */;
719-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
720-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
721-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
722-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
723-DROP TABLE IF EXISTS `t1`;
724-/*!40101 SET @saved_cs_client = @@character_set_client */;
725-/*!40101 SET character_set_client = utf8 */;
726-CREATE TABLE `t1` (
727- `id` int(11) NOT NULL AUTO_INCREMENT,
728- `a` int(11) DEFAULT NULL,
729- `b` varchar(255) DEFAULT NULL,
730- `c` decimal(10,3) DEFAULT NULL,
731- PRIMARY KEY (`id`),
732- CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE
733-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
734-/*!40101 SET character_set_client = @saved_cs_client */;
735-
736-LOCK TABLES `t1` WRITE;
737-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
738-INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200);
739-ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`);
740-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
741-UNLOCK TABLES;
742-DROP TABLE IF EXISTS `t2`;
743-/*!40101 SET @saved_cs_client = @@character_set_client */;
744-/*!40101 SET character_set_client = utf8 */;
745-CREATE TABLE `t2` (
746- `a` int(11) NOT NULL,
747- PRIMARY KEY (`a`)
748-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
749-/*!40101 SET character_set_client = @saved_cs_client */;
750-
751-LOCK TABLES `t2` WRITE;
752-/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
753-INSERT INTO `t2` VALUES (0),(1),(2);
754-/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
755-UNLOCK TABLES;
756-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
757-
758-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
759-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
760-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
761-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
762-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
763-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
764-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
765-
766-######################################
767-DROP TABLE t1, t2;
768-CREATE TABLE t1 (
769-id INT NOT NULL AUTO_INCREMENT,
770-KEY (id)
771-) ENGINE=InnoDB;
772-CREATE TABLE t2 (
773-id INT NOT NULL AUTO_INCREMENT,
774-UNIQUE KEY (id)
775-) ENGINE=InnoDB;
776-INSERT INTO t1 VALUES (), (), ();
777-INSERT INTO t2 VALUES (), (), ();
778-######################################
779-
780-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
781-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
782-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
783-/*!40101 SET NAMES utf8 */;
784-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
785-/*!40103 SET TIME_ZONE='+00:00' */;
786-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
787-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
788-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
789-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
790-DROP TABLE IF EXISTS `t1`;
791-/*!40101 SET @saved_cs_client = @@character_set_client */;
792-/*!40101 SET character_set_client = utf8 */;
793-CREATE TABLE `t1` (
794- `id` int(11) NOT NULL AUTO_INCREMENT,
795- KEY `id` (`id`)
796-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
797-/*!40101 SET character_set_client = @saved_cs_client */;
798-
799-LOCK TABLES `t1` WRITE;
800-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
801-INSERT INTO `t1` VALUES (1),(2),(3);
802-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
803-UNLOCK TABLES;
804-DROP TABLE IF EXISTS `t2`;
805-/*!40101 SET @saved_cs_client = @@character_set_client */;
806-/*!40101 SET character_set_client = utf8 */;
807-CREATE TABLE `t2` (
808- `id` int(11) NOT NULL AUTO_INCREMENT,
809- UNIQUE KEY `id` (`id`)
810-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
811-/*!40101 SET character_set_client = @saved_cs_client */;
812-
813-LOCK TABLES `t2` WRITE;
814-/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
815-INSERT INTO `t2` VALUES (1),(2),(3);
816-/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
817-UNLOCK TABLES;
818-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
819-
820-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
821-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
822-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
823-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
824-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
825-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
826-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
827-
828-######################################
829-DROP TABLE t1, t2;
830-CREATE TABLE t1 (
831-a INT NOT NULL,
832-UNIQUE KEY (a)) ENGINE=InnoDB;
833-CREATE TABLE t2 (
834-a INT NOT NULL,
835-b INT NOT NULL,
836-UNIQUE KEY (a,b)) ENGINE=InnoDB;
837-CREATE TABLE t3 (
838-a INT,
839-b INT,
840-UNIQUE KEY (a,b)) ENGINE=InnoDB;
841-CREATE TABLE t4 (
842-a INT NOT NULL,
843-b INT NOT NULL,
844-PRIMARY KEY (a,b),
845-UNIQUE KEY(b)) ENGINE=InnoDB;
846-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
847-TABLE_SCHEMA=DATABASE() AND
848-TABLE_NAME='t1' AND
849-COLUMN_KEY='PRI';
850-COUNT(*)
851-1
852-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
853-TABLE_SCHEMA=DATABASE() AND
854-TABLE_NAME='t2' AND
855-COLUMN_KEY='PRI';
856-COUNT(*)
857-2
858-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
859-TABLE_SCHEMA=DATABASE() AND
860-TABLE_NAME='t3' AND
861-COLUMN_KEY='PRI';
862-COUNT(*)
863-0
864-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
865-TABLE_SCHEMA=DATABASE() AND
866-TABLE_NAME='t4' AND
867-COLUMN_KEY='PRI';
868-COUNT(*)
869-2
870-INSERT INTO t1 VALUES (1), (2), (3);
871-INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
872-INSERT INTO t3 SELECT * FROM t2;
873-INSERT INTO t4 SELECT * FROM t2;
874-######################################
875-
876-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
877-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
878-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
879-/*!40101 SET NAMES utf8 */;
880-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
881-/*!40103 SET TIME_ZONE='+00:00' */;
882-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
883-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
884-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
885-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
886-DROP TABLE IF EXISTS `t1`;
887-/*!40101 SET @saved_cs_client = @@character_set_client */;
888-/*!40101 SET character_set_client = utf8 */;
889-CREATE TABLE `t1` (
890- `a` int(11) NOT NULL,
891- UNIQUE KEY `a` (`a`)
892-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
893-/*!40101 SET character_set_client = @saved_cs_client */;
894-
895-LOCK TABLES `t1` WRITE;
896-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
897-INSERT INTO `t1` VALUES (1),(2),(3);
898-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
899-UNLOCK TABLES;
900-DROP TABLE IF EXISTS `t2`;
901-/*!40101 SET @saved_cs_client = @@character_set_client */;
902-/*!40101 SET character_set_client = utf8 */;
903-CREATE TABLE `t2` (
904- `a` int(11) NOT NULL,
905- `b` int(11) NOT NULL,
906- UNIQUE KEY `a` (`a`,`b`)
907-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
908-/*!40101 SET character_set_client = @saved_cs_client */;
909-
910-LOCK TABLES `t2` WRITE;
911-/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
912-INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
913-/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
914-UNLOCK TABLES;
915-DROP TABLE IF EXISTS `t3`;
916-/*!40101 SET @saved_cs_client = @@character_set_client */;
917-/*!40101 SET character_set_client = utf8 */;
918-CREATE TABLE `t3` (
919- `a` int(11) DEFAULT NULL,
920- `b` int(11) DEFAULT NULL
921-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
922-/*!40101 SET character_set_client = @saved_cs_client */;
923-
924-LOCK TABLES `t3` WRITE;
925-/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
926-INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
927-ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
928-/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
929-UNLOCK TABLES;
930-DROP TABLE IF EXISTS `t4`;
931-/*!40101 SET @saved_cs_client = @@character_set_client */;
932-/*!40101 SET character_set_client = utf8 */;
933-CREATE TABLE `t4` (
934- `a` int(11) NOT NULL,
935- `b` int(11) NOT NULL,
936- PRIMARY KEY (`a`,`b`)
937-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
938-/*!40101 SET character_set_client = @saved_cs_client */;
939-
940-LOCK TABLES `t4` WRITE;
941-/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
942-INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
943-ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
944-/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
945-UNLOCK TABLES;
946-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
947-
948-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
949-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
950-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
951-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
952-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
953-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
954-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
955-
956-######################################
957-DROP TABLE t1, t2, t3, t4;
958-CREATE TABLE t1 (
959-id INT NOT NULL PRIMARY KEY
960-) ENGINE=InnoDB;
961-CREATE TABLE t2 (
962-id INT NOT NULL AUTO_INCREMENT,
963-a INT NOT NULL,
964-PRIMARY KEY (id),
965-KEY (a),
966-FOREIGN KEY (a) REFERENCES t2 (id)
967-) ENGINE=InnoDB;
968-INSERT INTO t1 VALUES (1), (2), (3);
969-INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
970-######################################
971-
972-/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
973-/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
974-/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
975-/*!40101 SET NAMES utf8 */;
976-/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
977-/*!40103 SET TIME_ZONE='+00:00' */;
978-/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
979-/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
980-/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
981-/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
982-DROP TABLE IF EXISTS `t1`;
983-/*!40101 SET @saved_cs_client = @@character_set_client */;
984-/*!40101 SET character_set_client = utf8 */;
985-CREATE TABLE `t1` (
986- `id` int(11) NOT NULL,
987- PRIMARY KEY (`id`)
988-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
989-/*!40101 SET character_set_client = @saved_cs_client */;
990-
991-LOCK TABLES `t1` WRITE;
992-/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
993-INSERT INTO `t1` VALUES (1),(2),(3);
994-/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
995-UNLOCK TABLES;
996-DROP TABLE IF EXISTS `t2`;
997-/*!40101 SET @saved_cs_client = @@character_set_client */;
998-/*!40101 SET character_set_client = utf8 */;
999-CREATE TABLE `t2` (
1000- `id` int(11) NOT NULL AUTO_INCREMENT,
1001- `a` int(11) NOT NULL,
1002- PRIMARY KEY (`id`),
1003- CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
1004-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
1005-/*!40101 SET character_set_client = @saved_cs_client */;
1006-
1007-LOCK TABLES `t2` WRITE;
1008-/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
1009-INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
1010-ALTER TABLE `t2` ADD KEY `a` (`a`);
1011-/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
1012-UNLOCK TABLES;
1013-/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1014-
1015-/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1016-/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1017-/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1018-/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1019-/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1020-/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1021-/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1022-
1023-######################################
1024-DROP TABLE t1, t2;
1025
1026=== added file 'Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test'
1027--- Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 1970-01-01 00:00:00 +0000
1028+++ Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2012-11-20 09:38:30 +0000
1029@@ -0,0 +1,227 @@
1030+# Embedded server doesn't support external clients
1031+--source include/not_embedded.inc
1032+
1033+# Fast index creation is only available in InnoDB plugin
1034+--source include/have_innodb.inc
1035+
1036+# Save the initial number of concurrent sessions
1037+--source include/count_sessions.inc
1038+
1039+--echo #
1040+--echo # Test the --innodb-optimize-keys option.
1041+--echo #
1042+
1043+--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
1044+
1045+# First test that the option has no effect on non-InnoDB tables
1046+
1047+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
1048+
1049+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
1050+
1051+--echo ######################################
1052+--cat_file $file
1053+--echo ######################################
1054+
1055+--remove_file $file
1056+
1057+DROP TABLE t1;
1058+
1059+# Check that for InnoDB tables secondary keys are created after the data is
1060+# dumped but foreign ones are left in CREATE TABLE
1061+
1062+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
1063+INSERT INTO t2 VALUES (0), (1), (2);
1064+
1065+CREATE TABLE t1 (
1066+ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1067+ a INT, b VARCHAR(255), c DECIMAL(10,3),
1068+ KEY (b),
1069+ UNIQUE KEY uniq(c,a),
1070+ FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
1071+) ENGINE=InnoDB;
1072+
1073+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
1074+
1075+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1076+
1077+--echo ######################################
1078+--cat_file $file
1079+--echo ######################################
1080+
1081+# Check that the resulting dump can be imported back
1082+
1083+--exec $MYSQL test < $file
1084+
1085+--remove_file $file
1086+
1087+DROP TABLE t1, t2;
1088+
1089+########################################################################
1090+# Bug #812179: AUTO_INCREMENT columns must be skipped by the
1091+# --innodb-optimize-keys optimization in mysqldump
1092+########################################################################
1093+
1094+CREATE TABLE t1 (
1095+ id INT NOT NULL AUTO_INCREMENT,
1096+ KEY (id)
1097+) ENGINE=InnoDB;
1098+
1099+CREATE TABLE t2 (
1100+ id INT NOT NULL AUTO_INCREMENT,
1101+ UNIQUE KEY (id)
1102+) ENGINE=InnoDB;
1103+
1104+INSERT INTO t1 VALUES (), (), ();
1105+INSERT INTO t2 VALUES (), (), ();
1106+
1107+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1108+
1109+--echo ######################################
1110+--cat_file $file
1111+--echo ######################################
1112+
1113+# Check that the resulting dump can be imported back
1114+
1115+--exec $MYSQL test < $file
1116+
1117+--remove_file $file
1118+
1119+DROP TABLE t1, t2;
1120+
1121+########################################################################
1122+# Bug #851674: --innodb-optimize-keys does not work correctly with table
1123+# without PRIMARY KEY
1124+########################################################################
1125+
1126+CREATE TABLE t1 (
1127+ a INT NOT NULL,
1128+ UNIQUE KEY (a)) ENGINE=InnoDB;
1129+
1130+CREATE TABLE t2 (
1131+ a INT NOT NULL,
1132+ b INT NOT NULL,
1133+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
1134+
1135+CREATE TABLE t3 (
1136+ a INT,
1137+ b INT,
1138+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
1139+
1140+CREATE TABLE t4 (
1141+ a INT NOT NULL,
1142+ b INT NOT NULL,
1143+ PRIMARY KEY (a,b),
1144+ UNIQUE KEY(b)) ENGINE=InnoDB;
1145+
1146+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1147+ TABLE_SCHEMA=DATABASE() AND
1148+ TABLE_NAME='t1' AND
1149+ COLUMN_KEY='PRI';
1150+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1151+ TABLE_SCHEMA=DATABASE() AND
1152+ TABLE_NAME='t2' AND
1153+ COLUMN_KEY='PRI';
1154+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1155+ TABLE_SCHEMA=DATABASE() AND
1156+ TABLE_NAME='t3' AND
1157+ COLUMN_KEY='PRI';
1158+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1159+ TABLE_SCHEMA=DATABASE() AND
1160+ TABLE_NAME='t4' AND
1161+ COLUMN_KEY='PRI';
1162+
1163+INSERT INTO t1 VALUES (1), (2), (3);
1164+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
1165+INSERT INTO t3 SELECT * FROM t2;
1166+INSERT INTO t4 SELECT * FROM t2;
1167+
1168+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
1169+
1170+--echo ######################################
1171+--cat_file $file
1172+--echo ######################################
1173+
1174+# Check that the resulting dump can be imported back
1175+
1176+--exec $MYSQL test < $file
1177+
1178+--remove_file $file
1179+
1180+DROP TABLE t1, t2, t3, t4;
1181+
1182+########################################################################
1183+# Bug #859078: --innodb-optimize-keys should ignore foreign keys
1184+########################################################################
1185+
1186+CREATE TABLE t1 (
1187+ id INT NOT NULL PRIMARY KEY
1188+) ENGINE=InnoDB;
1189+
1190+CREATE TABLE t2 (
1191+ id INT NOT NULL AUTO_INCREMENT,
1192+ a INT NOT NULL,
1193+ PRIMARY KEY (id),
1194+ KEY (a),
1195+ FOREIGN KEY (a) REFERENCES t2 (id)
1196+) ENGINE=InnoDB;
1197+
1198+INSERT INTO t1 VALUES (1), (2), (3);
1199+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
1200+
1201+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1202+
1203+--echo ######################################
1204+--cat_file $file
1205+--echo ######################################
1206+
1207+# Check that the resulting dump can be imported back
1208+
1209+--exec $MYSQL test < $file
1210+
1211+--remove_file $file
1212+
1213+DROP TABLE t1, t2;
1214+
1215+########################################################################
1216+# Bug #1039536: mysqldump --innodb-optimize-keys can generate invalid table
1217+# definitions
1218+########################################################################
1219+
1220+CREATE TABLE t1 (
1221+ id INT NOT NULL AUTO_INCREMENT,
1222+ uid INT NOT NULL,
1223+ `id``` INT NOT NULL,
1224+ ```id` INT NOT NULL,
1225+ # The following ones may be skipped and used in ALTER TABLE later
1226+ KEY k1 (```id`, id),
1227+ KEY k2 (```id`, `id```),
1228+ # The following one should be kept in CREATE TABLE
1229+ KEY k3 (id, uid),
1230+ # The following one may be skipped again
1231+ KEY k4 (id, `id```)
1232+) ENGINE=InnoDB;
1233+
1234+CREATE TABLE t2 (
1235+ id INT NOT NULL AUTO_INCREMENT,
1236+ PRIMARY KEY (id),
1237+ KEY k1 (id),
1238+ KEY k2 (id)
1239+) ENGINE=InnoDB;
1240+
1241+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1242+
1243+--echo ######################################
1244+--cat_file $file
1245+--echo ######################################
1246+
1247+# Check that the resulting dump can be imported back
1248+
1249+--exec $MYSQL test < $file
1250+
1251+--remove_file $file
1252+
1253+DROP TABLE t1, t2;
1254+
1255+# Wait till we reached the initial number of concurrent sessions
1256+--source include/wait_until_count_sessions.inc
1257
1258=== removed file 'Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test'
1259--- Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2012-04-18 23:26:09 +0000
1260+++ Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 1970-01-01 00:00:00 +0000
1261@@ -1,188 +0,0 @@
1262-# Embedded server doesn't support external clients
1263---source include/not_embedded.inc
1264-
1265-# Fast index creation is only available in InnoDB plugin
1266---source include/have_innodb.inc
1267-
1268-# Save the initial number of concurrent sessions
1269---source include/count_sessions.inc
1270-
1271---echo #
1272---echo # Test the --innodb-optimize-keys option.
1273---echo #
1274-
1275---let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
1276-
1277-# First test that the option has no effect on non-InnoDB tables
1278-
1279-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
1280-
1281---exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
1282-
1283---echo ######################################
1284---cat_file $file
1285---echo ######################################
1286-
1287---remove_file $file
1288-
1289-DROP TABLE t1;
1290-
1291-
1292-# Check that for InnoDB tables secondary keys are created after the data is
1293-# dumped but foreign ones are left in CREATE TABLE
1294-
1295-CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
1296-INSERT INTO t2 VALUES (0), (1), (2);
1297-
1298-CREATE TABLE t1 (
1299- id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1300- a INT, b VARCHAR(255), c DECIMAL(10,3),
1301- KEY (b),
1302- UNIQUE KEY uniq(c,a),
1303- FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
1304-) ENGINE=InnoDB;
1305-
1306-INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
1307-
1308---exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1309-
1310---echo ######################################
1311---cat_file $file
1312---echo ######################################
1313-
1314-# Check that the resulting dump can be imported back
1315-
1316---exec $MYSQL test < $file
1317-
1318---remove_file $file
1319-
1320-DROP TABLE t1, t2;
1321-
1322-########################################################################
1323-# Bug #812179: AUTO_INCREMENT columns must be skipped by the
1324-# --innodb-optimize-keys optimization in mysqldump
1325-########################################################################
1326-
1327-CREATE TABLE t1 (
1328- id INT NOT NULL AUTO_INCREMENT,
1329- KEY (id)
1330-) ENGINE=InnoDB;
1331-
1332-CREATE TABLE t2 (
1333- id INT NOT NULL AUTO_INCREMENT,
1334- UNIQUE KEY (id)
1335-) ENGINE=InnoDB;
1336-
1337-INSERT INTO t1 VALUES (), (), ();
1338-INSERT INTO t2 VALUES (), (), ();
1339-
1340---exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1341-
1342---echo ######################################
1343---cat_file $file
1344---echo ######################################
1345-
1346-# Check that the resulting dump can be imported back
1347-
1348---exec $MYSQL test < $file
1349-
1350---remove_file $file
1351-
1352-DROP TABLE t1, t2;
1353-
1354-########################################################################
1355-# Bug #851674: --innodb-optimize-keys does not work correctly with table
1356-# without PRIMARY KEY
1357-########################################################################
1358-
1359-CREATE TABLE t1 (
1360- a INT NOT NULL,
1361- UNIQUE KEY (a)) ENGINE=InnoDB;
1362-
1363-CREATE TABLE t2 (
1364- a INT NOT NULL,
1365- b INT NOT NULL,
1366- UNIQUE KEY (a,b)) ENGINE=InnoDB;
1367-
1368-CREATE TABLE t3 (
1369- a INT,
1370- b INT,
1371- UNIQUE KEY (a,b)) ENGINE=InnoDB;
1372-
1373-CREATE TABLE t4 (
1374- a INT NOT NULL,
1375- b INT NOT NULL,
1376- PRIMARY KEY (a,b),
1377- UNIQUE KEY(b)) ENGINE=InnoDB;
1378-
1379-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1380- TABLE_SCHEMA=DATABASE() AND
1381- TABLE_NAME='t1' AND
1382- COLUMN_KEY='PRI';
1383-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1384- TABLE_SCHEMA=DATABASE() AND
1385- TABLE_NAME='t2' AND
1386- COLUMN_KEY='PRI';
1387-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1388- TABLE_SCHEMA=DATABASE() AND
1389- TABLE_NAME='t3' AND
1390- COLUMN_KEY='PRI';
1391-SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
1392- TABLE_SCHEMA=DATABASE() AND
1393- TABLE_NAME='t4' AND
1394- COLUMN_KEY='PRI';
1395-
1396-INSERT INTO t1 VALUES (1), (2), (3);
1397-INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
1398-INSERT INTO t3 SELECT * FROM t2;
1399-INSERT INTO t4 SELECT * FROM t2;
1400-
1401---exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
1402-
1403---echo ######################################
1404---cat_file $file
1405---echo ######################################
1406-
1407-# Check that the resulting dump can be imported back
1408-
1409---exec $MYSQL test < $file
1410-
1411---remove_file $file
1412-
1413-DROP TABLE t1, t2, t3, t4;
1414-
1415-########################################################################
1416-# Bug #859078: --innodb-optimize-keys should ignore foreign keys
1417-########################################################################
1418-
1419-CREATE TABLE t1 (
1420- id INT NOT NULL PRIMARY KEY
1421-) ENGINE=InnoDB;
1422-
1423-CREATE TABLE t2 (
1424- id INT NOT NULL AUTO_INCREMENT,
1425- a INT NOT NULL,
1426- PRIMARY KEY (id),
1427- KEY (a),
1428- FOREIGN KEY (a) REFERENCES t2 (id)
1429-) ENGINE=InnoDB;
1430-
1431-INSERT INTO t1 VALUES (1), (2), (3);
1432-INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
1433-
1434---exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1435-
1436---echo ######################################
1437---cat_file $file
1438---echo ######################################
1439-
1440-# Check that the resulting dump can be imported back
1441-
1442---exec $MYSQL test < $file
1443-
1444---remove_file $file
1445-
1446-DROP TABLE t1, t2;
1447-
1448-# Wait till we reached the initial number of concurrent sessions
1449---source include/wait_until_count_sessions.inc

Subscribers

People subscribed via source and target branches