Merge lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation into lp:mysql-server/5.1

Proposed by Sergei Glushchenko
Status: Needs review
Proposed branch: lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation
Merge into: lp:mysql-server/5.1
Diff against target: 1508 lines (+1160/-2)
20 files modified
client/client_priv.h (+1/-0)
client/mysqldump.c (+223/-0)
mysql-test/r/percona_innodb_expand_fast_index_creation.result (+67/-0)
mysql-test/r/percona_mysqldump_innodb_optimize_keys.result (+367/-0)
mysql-test/suite/innodb_plugin/r/percona_bug_999147.result (+8/-0)
mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt (+1/-0)
mysql-test/suite/innodb_plugin/t/percona_bug_999147.test (+29/-0)
mysql-test/t/percona_innodb_expand_fast_index_creation.test (+46/-0)
mysql-test/t/percona_mysqldump_innodb_optimize_keys.test (+187/-0)
sql/mysqld.cc (+9/-1)
sql/set_var.cc (+4/-0)
sql/sql_class.h (+2/-0)
sql/sql_lex.cc (+4/-0)
sql/sql_lex.h (+8/-0)
sql/sql_table.cc (+171/-0)
storage/innodb_plugin/handler/ha_innodb.cc (+15/-0)
storage/innodb_plugin/handler/handler0alter.cc (+2/-0)
storage/innodb_plugin/include/ha_prototypes.h (+11/-0)
storage/innodb_plugin/row/row0merge.c (+4/-0)
storage/innodb_plugin/row/row0mysql.c (+1/-1)
To merge this branch: bzr merge lp:~sergei.glushchenko/mysql-server/expand_fast_index_creation
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) Approve
Review via email: mp+113247@code.launchpad.net

Description of the change

Patch expand_fast_index_creation.patch
Fix for bug #999147.

To post a comment you must log in.
Revision history for this message
Alexey Kopytov (akopytov) :
review: Approve

Unmerged revisions

3733. By Sergei Glushchenko

port expand_fast_index_creation patch

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'client/client_priv.h'
2--- client/client_priv.h 2011-07-03 15:47:37 +0000
3+++ client/client_priv.h 2012-07-03 16:14:26 +0000
4@@ -94,5 +94,6 @@
5 OPT_WRITE_BINLOG, OPT_DUMP_DATE,
6 OPT_FIRST_SLAVE,
7 OPT_ALL,
8+ OPT_INNODB_OPTIMIZE_KEYS,
9 OPT_MAX_CLIENT_OPTION
10 };
11
12=== modified file 'client/mysqldump.c'
13--- client/mysqldump.c 2012-05-07 11:16:44 +0000
14+++ client/mysqldump.c 2012-07-03 16:14:26 +0000
15@@ -47,6 +47,7 @@
16 #include <m_ctype.h>
17 #include <hash.h>
18 #include <stdarg.h>
19+#include <my_list.h>
20
21 #include "client_priv.h"
22 #include "mysql.h"
23@@ -148,6 +149,8 @@
24 #endif
25 static uint opt_protocol= 0;
26
27+static my_bool opt_innodb_optimize_keys= FALSE;
28+
29 /*
30 Dynamic_string wrapper functions. In this file use these
31 wrappers, they will terminate the process if there is
32@@ -193,6 +196,8 @@
33
34 HASH ignore_table;
35
36+LIST *skipped_keys_list;
37+
38 static struct my_option my_long_options[] =
39 {
40 {"all", OPT_ALL, "Deprecated. Use --create-options instead.",
41@@ -339,6 +344,11 @@
42 "be specified with both database and table names, e.g., "
43 "--ignore-table=database.table.",
44 0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
45+ {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS,
46+ "Use InnoDB fast index creation by creating secondary indexes after "
47+ "dumping the data.",
48+ &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG,
49+ 0, 0, 0, 0, 0, 0},
50 {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",
51 &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
52 0, 0},
53@@ -2433,6 +2443,190 @@
54 }
55
56 /*
57+ Parse the specified key definition string and check if the key indexes
58+ any of the columns from ignored_columns.
59+*/
60+static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
61+{
62+ char *leftp, *rightp;
63+
64+ if ((leftp = strchr(keydef, '(')) &&
65+ (rightp = strchr(leftp, ')')) &&
66+ rightp > leftp + 3 && /* (`...`) */
67+ leftp[1] == '`' &&
68+ rightp[-1] == '`' &&
69+ my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))
70+ return TRUE;
71+
72+ return FALSE;
73+}
74+
75+
76+/*
77+ Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string
78+ and store them into a temporary list to be used later.
79+
80+ SYNOPSIS
81+ skip_secondary_keys()
82+ create_str SHOW CREATE TABLE output
83+ has_pk TRUE, if the table has PRIMARY KEY
84+ (or UNIQUE key on non-nullable columns)
85+
86+
87+ DESCRIPTION
88+
89+ Stores all lines starting with "KEY" or "UNIQUE KEY"
90+ into skipped_keys_list and removes them from the input string.
91+ Ignoring FOREIGN KEYS constraints when creating the table is ok, because
92+ mysqldump sets foreign_key_checks to 0 anyway.
93+*/
94+
95+static void skip_secondary_keys(char *create_str, my_bool has_pk)
96+{
97+ char *ptr, *strend;
98+ char *last_comma = NULL;
99+ HASH ignored_columns;
100+ my_bool pk_processed= FALSE;
101+
102+ if (hash_init(&ignored_columns, charset_info, 16, 0, 0,
103+ (hash_get_key) get_table_key,
104+ (hash_free_key) free_table_ent, 0))
105+ exit(EX_EOM);
106+
107+ strend= create_str + strlen(create_str);
108+
109+ ptr= create_str;
110+ while (*ptr)
111+ {
112+ char *tmp, *orig_ptr, c;
113+ my_bool is_unique= FALSE;
114+
115+ orig_ptr= ptr;
116+ /* Skip leading whitespace */
117+ while (*ptr && my_isspace(charset_info, *ptr))
118+ ptr++;
119+
120+ /* Read the next line */
121+ for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++);
122+
123+ c= *tmp;
124+ *tmp= '\0'; /* so strstr() only processes the current line */
125+
126+ /* Is it a secondary index definition? */
127+ if (c == '\n' &&
128+ (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
129+ (pk_processed || !has_pk)) ||
130+ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) &&
131+ !contains_ignored_column(&ignored_columns, ptr))
132+ {
133+ char *data, *end= tmp - 1;
134+
135+ /* Remove the trailing comma */
136+ if (*end == ',')
137+ end--;
138+ data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE));
139+ skipped_keys_list= list_cons(data, skipped_keys_list);
140+
141+ memmove(orig_ptr, tmp + 1, strend - tmp);
142+ ptr= orig_ptr;
143+ strend-= tmp + 1 - ptr;
144+
145+ /* Remove the comma on the previos line */
146+ if (last_comma != NULL)
147+ {
148+ *last_comma= ' ';
149+ }
150+ }
151+ else
152+ {
153+ char *end;
154+
155+ if (last_comma != NULL && *ptr != ')')
156+ {
157+ /*
158+ It's not the last line of CREATE TABLE, so we have skipped a key
159+ definition. We have to restore the last removed comma.
160+ */
161+ *last_comma= ',';
162+ }
163+
164+ if ((has_pk && is_unique && !pk_processed) ||
165+ !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
166+ pk_processed= TRUE;
167+
168+ if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
169+ {
170+ /*
171+ If a secondary key is defined on this column later,
172+ it cannot be skipped, as CREATE TABLE would fail on import.
173+ */
174+ for (end= ptr + 1; *end != '`' && *end != '\0'; end++);
175+ if (*end == '`' && end > ptr + 1 &&
176+ my_hash_insert(&ignored_columns,
177+ (uchar *) my_strndup(ptr + 1,
178+ end - ptr - 1, MYF(0))))
179+ {
180+ exit(EX_EOM);
181+ }
182+ }
183+
184+ *tmp= c;
185+
186+ if (tmp[-1] == ',')
187+ last_comma= tmp - 1;
188+ ptr= (*tmp == '\0') ? tmp : tmp + 1;
189+ }
190+ }
191+
192+ my_hash_free(&ignored_columns);
193+}
194+
195+/*
196+ Check if the table has a primary key defined either explicitly or
197+ implicitly (i.e. a unique key on non-nullable columns).
198+
199+ SYNOPSIS
200+ my_bool has_primary_key(const char *table_name)
201+
202+ table_name quoted table name
203+
204+ RETURNS TRUE if the table has a primary key
205+
206+ DESCRIPTION
207+*/
208+
209+static my_bool has_primary_key(const char *table_name)
210+{
211+ MYSQL_RES *res= NULL;
212+ MYSQL_ROW row;
213+ char query_buff[QUERY_LENGTH];
214+ my_bool has_pk= TRUE;
215+
216+ my_snprintf(query_buff, sizeof(query_buff),
217+ "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE "
218+ "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND "
219+ "COLUMN_KEY='PRI'", table_name);
220+ if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) ||
221+ !(row= mysql_fetch_row(res)))
222+ {
223+ fprintf(stderr, "Warning: Couldn't determine if table %s has a "
224+ "primary key (%s). "
225+ "--innodb-optimize-keys may work inefficiently.\n",
226+ table_name, mysql_error(mysql));
227+ goto cleanup;
228+ }
229+
230+ has_pk= atoi(row[0]) > 0;
231+
232+cleanup:
233+ if (res)
234+ mysql_free_result(res);
235+
236+ return has_pk;
237+}
238+
239+
240+/*
241 get_table_structure -- retrievs database structure, prints out corresponding
242 CREATE statement and fills out insert_pat if the table is the type we will
243 be dumping.
244@@ -2470,6 +2664,7 @@
245 my_bool is_log_table;
246 MYSQL_RES *result;
247 MYSQL_ROW row;
248+ my_bool has_pk= FALSE;
249 DBUG_ENTER("get_table_structure");
250 DBUG_PRINT("enter", ("db: %s table: %s", db, table));
251
252@@ -2511,6 +2706,9 @@
253 result_table= quote_name(table, table_buff, 1);
254 opt_quoted_table= quote_name(table, table_buff2, 0);
255
256+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
257+ has_pk= has_primary_key(table);
258+
259 if (opt_order_by_primary)
260 order_by= primary_key_fields(result_table);
261
262@@ -2672,6 +2870,10 @@
263 is_log_table= general_log_or_slow_log_tables(db, table);
264 if (is_log_table)
265 row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */
266+
267+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
268+ skip_secondary_keys(row[1], has_pk);
269+
270 if (opt_compatible_mode & 3)
271 {
272 fprintf(sql_file,
273@@ -3788,6 +3990,27 @@
274 goto err;
275 }
276
277+ /* Perform delayed secondary index creation for --innodb-optimize-keys */
278+ if (skipped_keys_list)
279+ {
280+ uint keys;
281+ skipped_keys_list= list_reverse(skipped_keys_list);
282+ fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table);
283+ for (keys= list_length(skipped_keys_list); keys > 0; keys--)
284+ {
285+ LIST *node= skipped_keys_list;
286+ char *def= node->data;
287+
288+ fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n");
289+
290+ skipped_keys_list= list_delete(skipped_keys_list, node);
291+ my_free(def, MYF(0));
292+ my_free(node, MYF(0));
293+ }
294+
295+ DBUG_ASSERT(skipped_keys_list == NULL);
296+ }
297+
298 /* Moved enable keys to before unlock per bug 15977 */
299 if (opt_disable_keys)
300 {
301
302=== added file 'mysql-test/r/percona_innodb_expand_fast_index_creation.result'
303--- mysql-test/r/percona_innodb_expand_fast_index_creation.result 1970-01-01 00:00:00 +0000
304+++ mysql-test/r/percona_innodb_expand_fast_index_creation.result 2012-07-03 16:14:26 +0000
305@@ -0,0 +1,67 @@
306+SELECT @@expand_fast_index_creation;
307+@@expand_fast_index_creation
308+0
309+CREATE TABLE t1(
310+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
311+a CHAR(1) NOT NULL,
312+b CHAR(36) NOT NULL) ENGINE=InnoDB;
313+INSERT INTO t1(a,b) VALUES ('a','b');
314+INSERT INTO t1(a,b) SELECT a,b FROM t1;
315+INSERT INTO t1(a,b) SELECT a,b FROM t1;
316+INSERT INTO t1(a,b) SELECT a,b FROM t1;
317+INSERT INTO t1(a,b) SELECT a,b FROM t1;
318+ALTER TABLE t1 ADD KEY (a);
319+affected rows: 0
320+info: Records: 0 Duplicates: 0 Warnings: 0
321+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
322+id 1
323+select_type SIMPLE
324+table t1
325+type ALL
326+possible_keys a
327+key NULL
328+key_len NULL
329+ref NULL
330+rows 16
331+Extra
332+id 1
333+select_type SIMPLE
334+table t2
335+type ref
336+possible_keys a
337+key a
338+key_len 1
339+ref test.t1.a
340+rows 1
341+Extra Using where
342+ALTER TABLE t1 DROP KEY a;
343+SET expand_fast_index_creation = 1;
344+SELECT @@expand_fast_index_creation;
345+@@expand_fast_index_creation
346+1
347+ALTER TABLE t1 ADD KEY (a);
348+affected rows: 0
349+info: Records: 0 Duplicates: 0 Warnings: 0
350+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
351+id 1
352+select_type SIMPLE
353+table t1
354+type ALL
355+possible_keys a
356+key NULL
357+key_len NULL
358+ref NULL
359+rows 16
360+Extra
361+id 1
362+select_type SIMPLE
363+table t2
364+type ALL
365+possible_keys a
366+key NULL
367+key_len NULL
368+ref NULL
369+rows 16
370+Extra Using where; Using join buffer
371+SET expand_fast_index_creation = 0;
372+DROP TABLE t1;
373
374=== added file 'mysql-test/r/percona_mysqldump_innodb_optimize_keys.result'
375--- mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 1970-01-01 00:00:00 +0000
376+++ mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2012-07-03 16:14:26 +0000
377@@ -0,0 +1,367 @@
378+#
379+# Test the --innodb-optimize-keys option.
380+#
381+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
382+######################################
383+
384+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
385+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
386+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
387+/*!40101 SET NAMES utf8 */;
388+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
389+/*!40103 SET TIME_ZONE='+00:00' */;
390+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
391+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
392+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
393+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
394+DROP TABLE IF EXISTS `t1`;
395+/*!40101 SET @saved_cs_client = @@character_set_client */;
396+/*!40101 SET character_set_client = utf8 */;
397+CREATE TABLE `t1` (
398+ `a` int(11) NOT NULL,
399+ `b` int(11) DEFAULT NULL,
400+ PRIMARY KEY (`a`),
401+ KEY `b` (`b`)
402+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
403+/*!40101 SET character_set_client = @saved_cs_client */;
404+
405+LOCK TABLES `t1` WRITE;
406+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
407+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
408+UNLOCK TABLES;
409+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
410+
411+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
412+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
413+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
414+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
415+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
416+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
417+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
418+
419+######################################
420+DROP TABLE t1;
421+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
422+INSERT INTO t2 VALUES (0), (1), (2);
423+CREATE TABLE t1 (
424+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
425+a INT, b VARCHAR(255), c DECIMAL(10,3),
426+KEY (b),
427+UNIQUE KEY uniq(c,a),
428+FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
429+) ENGINE=InnoDB;
430+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
431+######################################
432+
433+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
434+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
435+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
436+/*!40101 SET NAMES utf8 */;
437+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
438+/*!40103 SET TIME_ZONE='+00:00' */;
439+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
440+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
441+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
442+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
443+DROP TABLE IF EXISTS `t1`;
444+/*!40101 SET @saved_cs_client = @@character_set_client */;
445+/*!40101 SET character_set_client = utf8 */;
446+CREATE TABLE `t1` (
447+ `id` int(11) NOT NULL AUTO_INCREMENT,
448+ `a` int(11) DEFAULT NULL,
449+ `b` varchar(255) DEFAULT NULL,
450+ `c` decimal(10,3) DEFAULT NULL,
451+ PRIMARY KEY (`id`),
452+ CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE
453+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
454+/*!40101 SET character_set_client = @saved_cs_client */;
455+
456+LOCK TABLES `t1` WRITE;
457+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
458+INSERT INTO `t1` VALUES (1,0,'0','0.000'),(2,1,'1','1.100'),(3,2,'2','2.200');
459+ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`);
460+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
461+UNLOCK TABLES;
462+DROP TABLE IF EXISTS `t2`;
463+/*!40101 SET @saved_cs_client = @@character_set_client */;
464+/*!40101 SET character_set_client = utf8 */;
465+CREATE TABLE `t2` (
466+ `a` int(11) NOT NULL,
467+ PRIMARY KEY (`a`)
468+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
469+/*!40101 SET character_set_client = @saved_cs_client */;
470+
471+LOCK TABLES `t2` WRITE;
472+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
473+INSERT INTO `t2` VALUES (0),(1),(2);
474+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
475+UNLOCK TABLES;
476+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
477+
478+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
479+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
480+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
481+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
482+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
483+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
484+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
485+
486+######################################
487+DROP TABLE t1, t2;
488+CREATE TABLE t1 (
489+id INT NOT NULL AUTO_INCREMENT,
490+KEY (id)
491+) ENGINE=InnoDB;
492+CREATE TABLE t2 (
493+id INT NOT NULL AUTO_INCREMENT,
494+UNIQUE KEY (id)
495+) ENGINE=InnoDB;
496+INSERT INTO t1 VALUES (), (), ();
497+INSERT INTO t2 VALUES (), (), ();
498+######################################
499+
500+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
501+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
502+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
503+/*!40101 SET NAMES utf8 */;
504+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
505+/*!40103 SET TIME_ZONE='+00:00' */;
506+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
507+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
508+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
509+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
510+DROP TABLE IF EXISTS `t1`;
511+/*!40101 SET @saved_cs_client = @@character_set_client */;
512+/*!40101 SET character_set_client = utf8 */;
513+CREATE TABLE `t1` (
514+ `id` int(11) NOT NULL AUTO_INCREMENT,
515+ KEY `id` (`id`)
516+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
517+/*!40101 SET character_set_client = @saved_cs_client */;
518+
519+LOCK TABLES `t1` WRITE;
520+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
521+INSERT INTO `t1` VALUES (1),(2),(3);
522+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
523+UNLOCK TABLES;
524+DROP TABLE IF EXISTS `t2`;
525+/*!40101 SET @saved_cs_client = @@character_set_client */;
526+/*!40101 SET character_set_client = utf8 */;
527+CREATE TABLE `t2` (
528+ `id` int(11) NOT NULL AUTO_INCREMENT,
529+ UNIQUE KEY `id` (`id`)
530+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
531+/*!40101 SET character_set_client = @saved_cs_client */;
532+
533+LOCK TABLES `t2` WRITE;
534+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
535+INSERT INTO `t2` VALUES (1),(2),(3);
536+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
537+UNLOCK TABLES;
538+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
539+
540+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
541+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
542+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
543+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
544+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
545+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
546+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
547+
548+######################################
549+DROP TABLE t1, t2;
550+CREATE TABLE t1 (
551+a INT NOT NULL,
552+UNIQUE KEY (a)) ENGINE=InnoDB;
553+CREATE TABLE t2 (
554+a INT NOT NULL,
555+b INT NOT NULL,
556+UNIQUE KEY (a,b)) ENGINE=InnoDB;
557+CREATE TABLE t3 (
558+a INT,
559+b INT,
560+UNIQUE KEY (a,b)) ENGINE=InnoDB;
561+CREATE TABLE t4 (
562+a INT NOT NULL,
563+b INT NOT NULL,
564+PRIMARY KEY (a,b),
565+UNIQUE KEY(b)) ENGINE=InnoDB;
566+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
567+TABLE_SCHEMA=DATABASE() AND
568+TABLE_NAME='t1' AND
569+COLUMN_KEY='PRI';
570+COUNT(*)
571+1
572+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
573+TABLE_SCHEMA=DATABASE() AND
574+TABLE_NAME='t2' AND
575+COLUMN_KEY='PRI';
576+COUNT(*)
577+2
578+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
579+TABLE_SCHEMA=DATABASE() AND
580+TABLE_NAME='t3' AND
581+COLUMN_KEY='PRI';
582+COUNT(*)
583+0
584+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
585+TABLE_SCHEMA=DATABASE() AND
586+TABLE_NAME='t4' AND
587+COLUMN_KEY='PRI';
588+COUNT(*)
589+2
590+INSERT INTO t1 VALUES (1), (2), (3);
591+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
592+INSERT INTO t3 SELECT * FROM t2;
593+INSERT INTO t4 SELECT * FROM t2;
594+######################################
595+
596+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
597+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
598+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
599+/*!40101 SET NAMES utf8 */;
600+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
601+/*!40103 SET TIME_ZONE='+00:00' */;
602+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
603+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
604+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
605+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
606+DROP TABLE IF EXISTS `t1`;
607+/*!40101 SET @saved_cs_client = @@character_set_client */;
608+/*!40101 SET character_set_client = utf8 */;
609+CREATE TABLE `t1` (
610+ `a` int(11) NOT NULL,
611+ UNIQUE KEY `a` (`a`)
612+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
613+/*!40101 SET character_set_client = @saved_cs_client */;
614+
615+LOCK TABLES `t1` WRITE;
616+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
617+INSERT INTO `t1` VALUES (1),(2),(3);
618+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
619+UNLOCK TABLES;
620+DROP TABLE IF EXISTS `t2`;
621+/*!40101 SET @saved_cs_client = @@character_set_client */;
622+/*!40101 SET character_set_client = utf8 */;
623+CREATE TABLE `t2` (
624+ `a` int(11) NOT NULL,
625+ `b` int(11) NOT NULL,
626+ UNIQUE KEY `a` (`a`,`b`)
627+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
628+/*!40101 SET character_set_client = @saved_cs_client */;
629+
630+LOCK TABLES `t2` WRITE;
631+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
632+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
633+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
634+UNLOCK TABLES;
635+DROP TABLE IF EXISTS `t3`;
636+/*!40101 SET @saved_cs_client = @@character_set_client */;
637+/*!40101 SET character_set_client = utf8 */;
638+CREATE TABLE `t3` (
639+ `a` int(11) DEFAULT NULL,
640+ `b` int(11) DEFAULT NULL
641+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
642+/*!40101 SET character_set_client = @saved_cs_client */;
643+
644+LOCK TABLES `t3` WRITE;
645+/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
646+INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
647+ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
648+/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
649+UNLOCK TABLES;
650+DROP TABLE IF EXISTS `t4`;
651+/*!40101 SET @saved_cs_client = @@character_set_client */;
652+/*!40101 SET character_set_client = utf8 */;
653+CREATE TABLE `t4` (
654+ `a` int(11) NOT NULL,
655+ `b` int(11) NOT NULL,
656+ PRIMARY KEY (`a`,`b`)
657+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
658+/*!40101 SET character_set_client = @saved_cs_client */;
659+
660+LOCK TABLES `t4` WRITE;
661+/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
662+INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
663+ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
664+/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
665+UNLOCK TABLES;
666+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
667+
668+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
669+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
670+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
671+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
672+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
673+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
674+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
675+
676+######################################
677+DROP TABLE t1, t2, t3, t4;
678+CREATE TABLE t1 (
679+id INT NOT NULL PRIMARY KEY
680+) ENGINE=InnoDB;
681+CREATE TABLE t2 (
682+id INT NOT NULL AUTO_INCREMENT,
683+a INT NOT NULL,
684+PRIMARY KEY (id),
685+KEY (a),
686+FOREIGN KEY (a) REFERENCES t2 (id)
687+) ENGINE=InnoDB;
688+INSERT INTO t1 VALUES (1), (2), (3);
689+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
690+######################################
691+
692+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
693+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
694+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
695+/*!40101 SET NAMES utf8 */;
696+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
697+/*!40103 SET TIME_ZONE='+00:00' */;
698+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
699+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
700+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
701+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
702+DROP TABLE IF EXISTS `t1`;
703+/*!40101 SET @saved_cs_client = @@character_set_client */;
704+/*!40101 SET character_set_client = utf8 */;
705+CREATE TABLE `t1` (
706+ `id` int(11) NOT NULL,
707+ PRIMARY KEY (`id`)
708+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
709+/*!40101 SET character_set_client = @saved_cs_client */;
710+
711+LOCK TABLES `t1` WRITE;
712+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
713+INSERT INTO `t1` VALUES (1),(2),(3);
714+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
715+UNLOCK TABLES;
716+DROP TABLE IF EXISTS `t2`;
717+/*!40101 SET @saved_cs_client = @@character_set_client */;
718+/*!40101 SET character_set_client = utf8 */;
719+CREATE TABLE `t2` (
720+ `id` int(11) NOT NULL AUTO_INCREMENT,
721+ `a` int(11) NOT NULL,
722+ PRIMARY KEY (`id`),
723+ CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
724+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
725+/*!40101 SET character_set_client = @saved_cs_client */;
726+
727+LOCK TABLES `t2` WRITE;
728+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
729+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
730+ALTER TABLE `t2` ADD KEY `a` (`a`);
731+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
732+UNLOCK TABLES;
733+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
734+
735+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
736+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
737+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
738+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
739+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
740+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
741+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
742+
743+######################################
744+DROP TABLE t1, t2;
745
746=== added file 'mysql-test/suite/innodb_plugin/r/percona_bug_999147.result'
747--- mysql-test/suite/innodb_plugin/r/percona_bug_999147.result 1970-01-01 00:00:00 +0000
748+++ mysql-test/suite/innodb_plugin/r/percona_bug_999147.result 2012-07-03 16:14:26 +0000
749@@ -0,0 +1,8 @@
750+DROP TABLE IF EXISTS t1;
751+SET SESSION expand_fast_index_creation=ON;
752+CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB;
753+SET debug="+d,crash_innodb_add_index_after";
754+ALTER TABLE t1 ADD INDEX (b);
755+ERROR HY000: Lost connection to MySQL server during query
756+SHOW TABLES;
757+Tables_in_test
758
759=== added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt'
760--- mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt 1970-01-01 00:00:00 +0000
761+++ mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt 2012-07-03 16:14:26 +0000
762@@ -0,0 +1,1 @@
763+--skip-stack-trace --skip-core-file --innodb-file-per-table=1
764
765=== added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147.test'
766--- mysql-test/suite/innodb_plugin/t/percona_bug_999147.test 1970-01-01 00:00:00 +0000
767+++ mysql-test/suite/innodb_plugin/t/percona_bug_999147.test 2012-07-03 16:14:26 +0000
768@@ -0,0 +1,29 @@
769+# Test for Percona Server bug 999147 (A crash that leaves behind an
770+# InnoDB temporary table with indexes results in an unbootable server)
771+# https://bugs.launchpad.net/percona-server/+bug/999147
772+
773+-- source include/not_embedded.inc
774+-- source include/not_valgrind.inc
775+-- source include/not_crashrep.inc
776+-- source include/have_debug.inc
777+-- source include/have_innodb_plugin.inc
778+
779+--disable_warnings
780+DROP TABLE IF EXISTS t1;
781+--enable_warnings
782+
783+SET SESSION expand_fast_index_creation=ON;
784+
785+CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB;
786+
787+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
788+
789+SET debug="+d,crash_innodb_add_index_after";
790+--error 2013
791+ALTER TABLE t1 ADD INDEX (b);
792+
793+--enable_reconnect
794+
795+--source include/wait_until_connected_again.inc
796+
797+SHOW TABLES;
798
799=== added file 'mysql-test/t/percona_innodb_expand_fast_index_creation.test'
800--- mysql-test/t/percona_innodb_expand_fast_index_creation.test 1970-01-01 00:00:00 +0000
801+++ mysql-test/t/percona_innodb_expand_fast_index_creation.test 2012-07-03 16:14:26 +0000
802@@ -0,0 +1,46 @@
803+--source include/have_innodb_plugin.inc
804+
805+SELECT @@expand_fast_index_creation;
806+
807+########################################################################
808+# Bug #857590: Fast index creation does not update index statistics
809+########################################################################
810+
811+CREATE TABLE t1(
812+ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
813+ a CHAR(1) NOT NULL,
814+ b CHAR(36) NOT NULL) ENGINE=InnoDB;
815+
816+INSERT INTO t1(a,b) VALUES ('a','b');
817+INSERT INTO t1(a,b) SELECT a,b FROM t1;
818+INSERT INTO t1(a,b) SELECT a,b FROM t1;
819+INSERT INTO t1(a,b) SELECT a,b FROM t1;
820+INSERT INTO t1(a,b) SELECT a,b FROM t1;
821+
822+# Check that fast index creation is used
823+--enable_info
824+ALTER TABLE t1 ADD KEY (a);
825+--disable_info
826+
827+# The default (wrong) plan due to bogus statistics
828+--vertical_results
829+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
830+--horizontal_results
831+
832+ALTER TABLE t1 DROP KEY a;
833+
834+SET expand_fast_index_creation = 1;
835+SELECT @@expand_fast_index_creation;
836+
837+# Check that stats are updated with the option enabled
838+
839+--enable_info
840+ALTER TABLE t1 ADD KEY (a);
841+--disable_info
842+--vertical_results
843+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
844+--horizontal_results
845+
846+SET expand_fast_index_creation = 0;
847+
848+DROP TABLE t1;
849
850=== added file 'mysql-test/t/percona_mysqldump_innodb_optimize_keys.test'
851--- mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 1970-01-01 00:00:00 +0000
852+++ mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2012-07-03 16:14:26 +0000
853@@ -0,0 +1,187 @@
854+# Embedded server doesn't support external clients
855+--source include/not_embedded.inc
856+
857+# Fast index creation is only available in InnoDB plugin
858+--source include/have_innodb_plugin.inc
859+
860+# Save the initial number of concurrent sessions
861+--source include/count_sessions.inc
862+
863+--echo #
864+--echo # Test the --innodb-optimize-keys option.
865+--echo #
866+
867+--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
868+
869+# First test that the option has no effect on non-InnoDB tables
870+
871+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
872+
873+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
874+
875+--echo ######################################
876+--cat_file $file
877+--echo ######################################
878+
879+--remove_file $file
880+
881+DROP TABLE t1;
882+
883+# Check that for InnoDB tables secondary keys are created after the data is
884+# dumped but foreign ones are left in CREATE TABLE
885+
886+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
887+INSERT INTO t2 VALUES (0), (1), (2);
888+
889+CREATE TABLE t1 (
890+ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
891+ a INT, b VARCHAR(255), c DECIMAL(10,3),
892+ KEY (b),
893+ UNIQUE KEY uniq(c,a),
894+ FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
895+) ENGINE=InnoDB;
896+
897+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
898+
899+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
900+
901+--echo ######################################
902+--cat_file $file
903+--echo ######################################
904+
905+# Check that the resulting dump can be imported back
906+
907+--exec $MYSQL test < $file
908+
909+--remove_file $file
910+
911+DROP TABLE t1, t2;
912+
913+########################################################################
914+# Bug #812179: AUTO_INCREMENT columns must be skipped by the
915+# --innodb-optimize-keys optimization in mysqldump
916+########################################################################
917+
918+CREATE TABLE t1 (
919+ id INT NOT NULL AUTO_INCREMENT,
920+ KEY (id)
921+) ENGINE=InnoDB;
922+
923+CREATE TABLE t2 (
924+ id INT NOT NULL AUTO_INCREMENT,
925+ UNIQUE KEY (id)
926+) ENGINE=InnoDB;
927+
928+INSERT INTO t1 VALUES (), (), ();
929+INSERT INTO t2 VALUES (), (), ();
930+
931+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
932+
933+--echo ######################################
934+--cat_file $file
935+--echo ######################################
936+
937+# Check that the resulting dump can be imported back
938+
939+--exec $MYSQL test < $file
940+
941+--remove_file $file
942+
943+DROP TABLE t1, t2;
944+
945+########################################################################
946+# Bug #851674: --innodb-optimize-keys does not work correctly with table
947+# without PRIMARY KEY
948+########################################################################
949+
950+CREATE TABLE t1 (
951+ a INT NOT NULL,
952+ UNIQUE KEY (a)) ENGINE=InnoDB;
953+
954+CREATE TABLE t2 (
955+ a INT NOT NULL,
956+ b INT NOT NULL,
957+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
958+
959+CREATE TABLE t3 (
960+ a INT,
961+ b INT,
962+ UNIQUE KEY (a,b)) ENGINE=InnoDB;
963+
964+CREATE TABLE t4 (
965+ a INT NOT NULL,
966+ b INT NOT NULL,
967+ PRIMARY KEY (a,b),
968+ UNIQUE KEY(b)) ENGINE=InnoDB;
969+
970+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
971+ TABLE_SCHEMA=DATABASE() AND
972+ TABLE_NAME='t1' AND
973+ COLUMN_KEY='PRI';
974+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
975+ TABLE_SCHEMA=DATABASE() AND
976+ TABLE_NAME='t2' AND
977+ COLUMN_KEY='PRI';
978+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
979+ TABLE_SCHEMA=DATABASE() AND
980+ TABLE_NAME='t3' AND
981+ COLUMN_KEY='PRI';
982+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
983+ TABLE_SCHEMA=DATABASE() AND
984+ TABLE_NAME='t4' AND
985+ COLUMN_KEY='PRI';
986+
987+INSERT INTO t1 VALUES (1), (2), (3);
988+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
989+INSERT INTO t3 SELECT * FROM t2;
990+INSERT INTO t4 SELECT * FROM t2;
991+
992+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
993+
994+--echo ######################################
995+--cat_file $file
996+--echo ######################################
997+
998+# Check that the resulting dump can be imported back
999+
1000+--exec $MYSQL test < $file
1001+
1002+--remove_file $file
1003+
1004+DROP TABLE t1, t2, t3, t4;
1005+
1006+########################################################################
1007+# Bug #859078: --innodb-optimize-keys should ignore foreign keys
1008+########################################################################
1009+
1010+CREATE TABLE t1 (
1011+ id INT NOT NULL PRIMARY KEY
1012+) ENGINE=InnoDB;
1013+
1014+CREATE TABLE t2 (
1015+ id INT NOT NULL AUTO_INCREMENT,
1016+ a INT NOT NULL,
1017+ PRIMARY KEY (id),
1018+ KEY (a),
1019+ FOREIGN KEY (a) REFERENCES t2 (id)
1020+) ENGINE=InnoDB;
1021+
1022+INSERT INTO t1 VALUES (1), (2), (3);
1023+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
1024+
1025+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
1026+
1027+--echo ######################################
1028+--cat_file $file
1029+--echo ######################################
1030+
1031+# Check that the resulting dump can be imported back
1032+
1033+--exec $MYSQL test < $file
1034+
1035+--remove_file $file
1036+
1037+DROP TABLE t1, t2;
1038+
1039+# Wait till we reached the initial number of concurrent sessions
1040+--source include/wait_until_count_sessions.inc
1041
1042=== modified file 'sql/mysqld.cc'
1043--- sql/mysqld.cc 2012-03-08 15:16:53 +0000
1044+++ sql/mysqld.cc 2012-07-03 16:14:26 +0000
1045@@ -5667,7 +5667,8 @@
1046 OPT_IGNORE_BUILTIN_INNODB,
1047 OPT_BINLOG_DIRECT_NON_TRANS_UPDATE,
1048 OPT_DEFAULT_CHARACTER_SET_OLD,
1049- OPT_MAX_LONG_DATA_SIZE
1050+ OPT_MAX_LONG_DATA_SIZE,
1051+ OPT_EXPAND_FAST_INDEX_CREATION
1052 };
1053
1054
1055@@ -5896,6 +5897,13 @@
1056 each time the SQL thread starts.",
1057 &opt_init_slave, &opt_init_slave, 0, GET_STR_ALLOC,
1058 REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
1059+ {"expand-fast-index-creation", OPT_EXPAND_FAST_INDEX_CREATION,
1060+ "Enable/disable improvements to the InnoDB fast index creation functionality. "
1061+ "Has no effect when fast index creation is disabled with the "
1062+ "fast-index-creation option",
1063+ &global_system_variables.expand_fast_index_creation,
1064+ &max_system_variables.expand_fast_index_creation,
1065+ 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, NULL},
1066 {"language", 'L',
1067 "Client error messages in given language. May be given as a full path.",
1068 &language_ptr, &language_ptr, 0, GET_STR, REQUIRED_ARG,
1069
1070=== modified file 'sql/set_var.cc'
1071--- sql/set_var.cc 2011-07-03 15:47:37 +0000
1072+++ sql/set_var.cc 2012-07-03 16:14:26 +0000
1073@@ -921,6 +921,10 @@
1074 SHOW_LONGLONG,
1075 get_myisam_mmap_size);
1076
1077+static sys_var_thd_bool
1078+sys_expand_fast_index_creation(&vars,
1079+ "expand_fast_index_creation", &SV::expand_fast_index_creation);
1080+
1081
1082 bool sys_var::check(THD *thd, set_var *var)
1083 {
1084
1085=== modified file 'sql/sql_class.h'
1086--- sql/sql_class.h 2012-04-20 16:41:20 +0000
1087+++ sql/sql_class.h 2012-07-03 16:14:26 +0000
1088@@ -404,6 +404,8 @@
1089 DATE_TIME_FORMAT *datetime_format;
1090 DATE_TIME_FORMAT *time_format;
1091 my_bool sysdate_is_now;
1092+
1093+ my_bool expand_fast_index_creation;
1094 };
1095
1096
1097
1098=== modified file 'sql/sql_lex.cc'
1099--- sql/sql_lex.cc 2012-04-18 11:14:05 +0000
1100+++ sql/sql_lex.cc 2012-07-03 16:14:26 +0000
1101@@ -1498,6 +1498,9 @@
1102 alter_list(rhs.alter_list, mem_root),
1103 key_list(rhs.key_list, mem_root),
1104 create_list(rhs.create_list, mem_root),
1105+ delayed_key_list(rhs.delayed_key_list, mem_root),
1106+ delayed_key_info(rhs.delayed_key_info),
1107+ delayed_key_count(rhs.delayed_key_count),
1108 flags(rhs.flags),
1109 keys_onoff(rhs.keys_onoff),
1110 tablespace_op(rhs.tablespace_op),
1111@@ -1520,6 +1523,7 @@
1112 list_copy_and_replace_each_value(alter_list, mem_root);
1113 list_copy_and_replace_each_value(key_list, mem_root);
1114 list_copy_and_replace_each_value(create_list, mem_root);
1115+ list_copy_and_replace_each_value(delayed_key_list, mem_root);
1116 /* partition_names are not deeply copied currently */
1117 }
1118
1119
1120=== modified file 'sql/sql_lex.h'
1121--- sql/sql_lex.h 2012-04-18 11:14:05 +0000
1122+++ sql/sql_lex.h 2012-07-03 16:14:26 +0000
1123@@ -911,6 +911,9 @@
1124 List<Alter_column> alter_list;
1125 List<Key> key_list;
1126 List<Create_field> create_list;
1127+ List<Key> delayed_key_list;
1128+ KEY *delayed_key_info;
1129+ uint delayed_key_count;
1130 uint flags;
1131 enum enum_enable_or_disable keys_onoff;
1132 enum tablespace_op_type tablespace_op;
1133@@ -922,6 +925,8 @@
1134
1135
1136 Alter_info() :
1137+ delayed_key_info(NULL),
1138+ delayed_key_count(0),
1139 flags(0),
1140 keys_onoff(LEAVE_AS_IS),
1141 tablespace_op(NO_TABLESPACE_OP),
1142@@ -937,6 +942,9 @@
1143 alter_list.empty();
1144 key_list.empty();
1145 create_list.empty();
1146+ delayed_key_list.empty();
1147+ delayed_key_info= NULL;
1148+ delayed_key_count= 0;
1149 flags= 0;
1150 keys_onoff= LEAVE_AS_IS;
1151 tablespace_op= NO_TABLESPACE_OP;
1152
1153=== modified file 'sql/sql_table.cc'
1154--- sql/sql_table.cc 2012-02-15 16:21:38 +0000
1155+++ sql/sql_table.cc 2012-07-03 16:14:26 +0000
1156@@ -3016,6 +3016,14 @@
1157 if (!*key_info_buffer || ! key_part_info)
1158 DBUG_RETURN(TRUE); // Out of memory
1159
1160+ List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list);
1161+ alter_info->delayed_key_count= 0;
1162+ if (alter_info->delayed_key_list.elements > 0)
1163+ {
1164+ alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) *
1165+ (*key_count));
1166+ }
1167+
1168 key_iterator.rewind();
1169 key_number=0;
1170 for (; (key=key_iterator++) ; key_number++)
1171@@ -3394,6 +3402,23 @@
1172 my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length);
1173 DBUG_RETURN(TRUE);
1174 }
1175+
1176+ if (alter_info->delayed_key_list.elements > 0)
1177+ {
1178+ Key *delayed_key;
1179+
1180+ delayed_key_iterator.rewind();
1181+ while ((delayed_key= delayed_key_iterator++))
1182+ {
1183+ if (delayed_key == key)
1184+ {
1185+ alter_info->delayed_key_info[alter_info->delayed_key_count++]=
1186+ *key_info;
1187+ break;
1188+ }
1189+ }
1190+ }
1191+
1192 key_info++;
1193 }
1194 if (!unique_key && !primary_key &&
1195@@ -6099,6 +6124,10 @@
1196 List<Create_field> new_create_list;
1197 /* New key definitions are added here */
1198 List<Key> new_key_list;
1199+ /* List with secondary keys which should be created after copying the data */
1200+ List<Key> delayed_key_list;
1201+ /* Foreign key list returned by handler::get_foreign_key_list() */
1202+ List<FOREIGN_KEY_INFO> f_key_list;
1203 List_iterator<Alter_drop> drop_it(alter_info->drop_list);
1204 List_iterator<Create_field> def_it(alter_info->create_list);
1205 List_iterator<Alter_column> alter_it(alter_info->alter_list);
1206@@ -6111,6 +6140,7 @@
1207 uint used_fields= create_info->used_fields;
1208 KEY *key_info=table->key_info;
1209 bool rc= TRUE;
1210+ bool skip_secondary;
1211
1212 DBUG_ENTER("mysql_prepare_alter_table");
1213
1214@@ -6305,8 +6335,26 @@
1215 /*
1216 Collect all keys which isn't in drop list. Add only those
1217 for which some fields exists.
1218+
1219+ We also store secondary keys in delayed_key_list to make use of
1220+ the InnoDB fast index creation. The following conditions must be
1221+ met:
1222+
1223+ - fast_index_creation is enabled for the current session
1224+ - expand_fast_index_creation is enabled for the current session;
1225+ - we are going to create an InnoDB table (this is checked later when the
1226+ target engine is known);
1227+ - the key most be a non-UNIQUE one;
1228+ - there are no foreign keys. This can be optimized later to exclude only
1229+ those keys which are a part of foreign key constraints. Currently we
1230+ simply disable this optimization for all keys if there are any foreign
1231+ key constraints in the table.
1232 */
1233
1234+ skip_secondary= thd->variables.expand_fast_index_creation &&
1235+ !table->file->get_foreign_key_list(thd, &f_key_list) &&
1236+ f_key_list.elements == 0;
1237+
1238 for (uint i=0 ; i < table->s->keys ; i++,key_info++)
1239 {
1240 char *key_name= key_info->name;
1241@@ -6408,6 +6456,8 @@
1242 test(key_info->flags & HA_GENERATED_KEY),
1243 key_parts);
1244 new_key_list.push_back(key);
1245+ if (skip_secondary && key_type == Key::MULTIPLE)
1246+ delayed_key_list.push_back(key);
1247 }
1248 }
1249 {
1250@@ -6415,7 +6465,21 @@
1251 while ((key=key_it++)) // Add new keys
1252 {
1253 if (key->type != Key::FOREIGN_KEY)
1254+ {
1255 new_key_list.push_back(key);
1256+ if (skip_secondary && key->type == Key::MULTIPLE)
1257+ delayed_key_list.push_back(key);
1258+ }
1259+ else if (skip_secondary)
1260+ {
1261+ /*
1262+ We are adding a foreign key so disable the secondary keys
1263+ optimization.
1264+ */
1265+ skip_secondary= FALSE;
1266+ delayed_key_list.empty();
1267+ }
1268+
1269 if (key->name &&
1270 !my_strcasecmp(system_charset_info,key->name,primary_key_name))
1271 {
1272@@ -6464,12 +6528,100 @@
1273 rc= FALSE;
1274 alter_info->create_list.swap(new_create_list);
1275 alter_info->key_list.swap(new_key_list);
1276+ alter_info->delayed_key_list.swap(delayed_key_list);
1277 err:
1278 DBUG_RETURN(rc);
1279 }
1280
1281
1282 /*
1283+ Temporarily remove secondary keys previously stored in
1284+ alter_info->delayed_key_info.
1285+*/
1286+static int
1287+remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
1288+{
1289+ uint *key_numbers;
1290+ uint key_counter= 0;
1291+ uint i;
1292+ int error;
1293+ DBUG_ENTER("remove_secondary_keys");
1294+ DBUG_ASSERT(alter_info->delayed_key_count > 0);
1295+
1296+ key_numbers= (uint *) thd->alloc(sizeof(uint) *
1297+ alter_info->delayed_key_count);
1298+ for (i= 0; i < alter_info->delayed_key_count; i++)
1299+ {
1300+ KEY *key= alter_info->delayed_key_info + i;
1301+ uint j;
1302+
1303+ for (j= 0; j < table->s->keys; j++)
1304+ {
1305+ if (!strcmp(table->key_info[j].name, key->name))
1306+ {
1307+ key_numbers[key_counter++]= j;
1308+ break;
1309+ }
1310+ }
1311+ }
1312+
1313+ DBUG_ASSERT(key_counter == alter_info->delayed_key_count);
1314+
1315+ if ((error= table->file->prepare_drop_index(table, key_numbers,
1316+ key_counter)) ||
1317+ (error= table->file->final_drop_index(table)))
1318+ {
1319+ table->file->print_error(error, MYF(0));
1320+ }
1321+
1322+ DBUG_RETURN(error);
1323+}
1324+
1325+/*
1326+ Restore secondary keys previously removed in remove_secondary_keys.
1327+*/
1328+
1329+static int
1330+restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
1331+{
1332+ uint i;
1333+ int error;
1334+ DBUG_ENTER("restore_secondary_keys");
1335+ DBUG_ASSERT(alter_info->delayed_key_count > 0);
1336+
1337+ thd_proc_info(thd, "restoring secondary keys");
1338+
1339+ /* Fix the key parts */
1340+ for (i= 0; i < alter_info->delayed_key_count; i++)
1341+ {
1342+ KEY *key = alter_info->delayed_key_info + i;
1343+ KEY_PART_INFO *key_part;
1344+ KEY_PART_INFO *part_end;
1345+
1346+ part_end= key->key_part + key->key_parts;
1347+ for (key_part= key->key_part; key_part < part_end; key_part++)
1348+ key_part->field= table->field[key_part->fieldnr];
1349+ }
1350+
1351+ if ((error= table->file->add_index(table, alter_info->delayed_key_info,
1352+ alter_info->delayed_key_count)))
1353+ {
1354+ /*
1355+ Exchange the key_info for the error message. If we exchange
1356+ key number by key name in the message later, we need correct info.
1357+ */
1358+ KEY *save_key_info= table->key_info;
1359+ table->key_info= alter_info->delayed_key_info;
1360+ table->file->print_error(error, MYF(0));
1361+ table->key_info= save_key_info;
1362+
1363+ DBUG_RETURN(error);
1364+ }
1365+
1366+ DBUG_RETURN(0);
1367+}
1368+
1369+/*
1370 Alter table
1371
1372 SYNOPSIS
1373@@ -7305,15 +7457,34 @@
1374 */
1375 if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
1376 {
1377+ /*
1378+ Check if we can temporarily remove secondary indexes from the table
1379+ before copying the data and recreate them later to utilize InnoDB fast
1380+ index creation.
1381+ TODO: is there a better way to check for InnoDB?
1382+ */
1383+ bool optimize_keys= (alter_info->delayed_key_count > 0) &&
1384+ !my_strcasecmp(system_charset_info,
1385+ new_table->file->table_type(), "InnoDB");
1386 /* We don't want update TIMESTAMP fields during ALTER TABLE. */
1387 new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
1388 new_table->next_number_field=new_table->found_next_number_field;
1389+
1390 thd_proc_info(thd, "copy to tmp table");
1391+
1392+ if (optimize_keys)
1393+ {
1394+ /* ignore the error */
1395+ error= remove_secondary_keys(thd, new_table, alter_info);
1396+ }
1397+
1398 error= copy_data_between_tables(table, new_table,
1399 alter_info->create_list, ignore,
1400 order_num, order, &copied, &deleted,
1401 alter_info->keys_onoff,
1402 alter_info->error_if_not_empty);
1403+ if (!error && optimize_keys)
1404+ error= restore_secondary_keys(thd, new_table, alter_info);
1405 }
1406 else
1407 {
1408
1409=== modified file 'storage/innodb_plugin/handler/ha_innodb.cc'
1410--- storage/innodb_plugin/handler/ha_innodb.cc 2012-05-08 05:19:14 +0000
1411+++ storage/innodb_plugin/handler/ha_innodb.cc 2012-07-03 16:14:26 +0000
1412@@ -43,6 +43,8 @@
1413 #pragma implementation // gcc: Class implementation
1414 #endif
1415
1416+#define MYSQL_SERVER
1417+
1418 #include <mysql_priv.h>
1419
1420 #include <m_ctype.h>
1421@@ -682,6 +684,19 @@
1422 }
1423
1424 /******************************************************************//**
1425+Returns true if expand_fast_index_creation is enabled for the current
1426+session.
1427+@return the value of the server's expand_fast_index_creation variable */
1428+extern "C"
1429+ibool
1430+thd_expand_fast_index_creation(
1431+/*================================*/
1432+ void* thd)
1433+{
1434+ return((ibool) (((THD*) thd)->variables.expand_fast_index_creation));
1435+}
1436+
1437+/******************************************************************//**
1438 Returns true if the thread supports XA,
1439 global value of innodb_supports_xa if thd is NULL.
1440 @return true if thd has XA support */
1441
1442=== modified file 'storage/innodb_plugin/handler/handler0alter.cc'
1443--- storage/innodb_plugin/handler/handler0alter.cc 2012-02-28 12:00:00 +0000
1444+++ storage/innodb_plugin/handler/handler0alter.cc 2012-07-03 16:14:26 +0000
1445@@ -813,6 +813,8 @@
1446 innodb_table, indexed_table,
1447 index, num_of_idx, table);
1448
1449+ DBUG_EXECUTE_IF("crash_innodb_add_index_after", DBUG_SUICIDE(););
1450+
1451 error_handling:
1452 /* After an error, remove all those index definitions from the
1453 dictionary which were defined. */
1454
1455=== modified file 'storage/innodb_plugin/include/ha_prototypes.h'
1456--- storage/innodb_plugin/include/ha_prototypes.h 2010-05-14 13:08:15 +0000
1457+++ storage/innodb_plugin/include/ha_prototypes.h 2012-07-03 16:14:26 +0000
1458@@ -268,4 +268,15 @@
1459 void* thd); /*!< in: thread handle (THD*), or NULL to query
1460 the global innodb_lock_wait_timeout */
1461
1462+/******************************************************************//**
1463+Returns true if innodb_expand_fast_index_creation is enabled for the current
1464+session.
1465+@return the value of the server's innodb_expand_fast_index_creation variable */
1466+
1467+ibool
1468+thd_expand_fast_index_creation(
1469+/*==================*/
1470+ void* thd); /*!< in: thread handle (THD*) */
1471+
1472+
1473 #endif
1474
1475=== modified file 'storage/innodb_plugin/row/row0merge.c'
1476--- storage/innodb_plugin/row/row0merge.c 2012-02-10 03:09:12 +0000
1477+++ storage/innodb_plugin/row/row0merge.c 2012-07-03 16:14:26 +0000
1478@@ -56,6 +56,7 @@
1479 #include "log0log.h"
1480 #include "ut0sort.h"
1481 #include "handler0alter.h"
1482+#include "ha_prototypes.h"
1483
1484 #ifdef UNIV_DEBUG
1485 /** Set these in order ot enable debug printout. */
1486@@ -2640,6 +2641,9 @@
1487 }
1488 }
1489
1490+ if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd))
1491+ dict_update_statistics(new_table, FALSE);
1492+
1493 func_exit:
1494 close(tmpfd);
1495
1496
1497=== modified file 'storage/innodb_plugin/row/row0mysql.c'
1498--- storage/innodb_plugin/row/row0mysql.c 2012-03-08 15:16:53 +0000
1499+++ storage/innodb_plugin/row/row0mysql.c 2012-07-03 16:14:26 +0000
1500@@ -3512,7 +3512,7 @@
1501 btr_pcur_store_position(&pcur, &mtr);
1502 btr_pcur_commit_specify_mtr(&pcur, &mtr);
1503
1504- table = dict_load_table(table_name);
1505+ table = dict_table_get_low(table_name);
1506
1507 if (table) {
1508 row_drop_table_for_mysql(table_name, trx, FALSE);