1 # name : innodb_expand_fast_index_creation.patch
4 # Expands the applicability of InnoDB fast index creation to mysqldump,
5 # ALTER TABLE and OPTIMIZE TABLE.
8 --- a/client/client_priv.h
9 +++ b/client/client_priv.h
11 OPT_NO_REMOVE_EOL_CARRET,
14 + OPT_INNODB_OPTIMIZE_KEYS,
18 --- a/client/mysqldump.c
19 +++ b/client/mysqldump.c
26 #include "client_priv.h"
30 static my_bool server_supports_sql_no_fcache= FALSE;
32 +static my_bool opt_innodb_optimize_keys= FALSE;
35 Dynamic_string wrapper functions. In this file use these
36 wrappers, they will terminate the process if there is
41 +LIST *skipped_keys_list;
43 static struct my_option my_long_options[] =
45 {"all-databases", 'A',
47 "in dump produced with --dump-slave.", &opt_include_master_host_port,
48 &opt_include_master_host_port, 0, GET_BOOL, NO_ARG,
50 + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS,
51 + "Use InnoDB fast index creation by creating secondary indexes after "
52 + "dumping the data.",
53 + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG,
55 {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",
56 &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
58 @@ -2240,6 +2250,189 @@
62 + Parse the specified key definition string and check if the key indexes
63 + any of the columns from ignored_columns.
65 +static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
67 + char *leftp, *rightp;
69 + if ((leftp = strchr(keydef, '(')) &&
70 + (rightp = strchr(leftp, ')')) &&
71 + rightp > leftp + 3 && /* (`...`) */
73 + rightp[-1] == '`' &&
74 + my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))
82 + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string
83 + and store them into a temporary list to be used later.
86 + skip_secondary_keys()
87 + create_str SHOW CREATE TABLE output
88 + has_pk TRUE, if the table has PRIMARY KEY
89 + (or UNIQUE key on non-nullable columns)
94 + Stores all lines starting with "KEY" or "UNIQUE KEY"
95 + into skipped_keys_list and removes them from the input string.
96 + Ignoring FOREIGN KEYS constraints when creating the table is ok, because
97 + mysqldump sets foreign_key_checks to 0 anyway.
100 +static void skip_secondary_keys(char *create_str, my_bool has_pk)
102 + char *ptr, *strend;
103 + char *last_comma = NULL;
104 + HASH ignored_columns;
105 + my_bool pk_processed= FALSE;
107 + if (my_hash_init(&ignored_columns, charset_info, 16, 0, 0,
108 + (my_hash_get_key) get_table_key, my_free, 0))
111 + strend= create_str + strlen(create_str);
116 + char *tmp, *orig_ptr, c;
117 + my_bool is_unique= FALSE;
120 + /* Skip leading whitespace */
121 + while (*ptr && my_isspace(charset_info, *ptr))
124 + /* Read the next line */
125 + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++);
128 + *tmp= '\0'; /* so strstr() only processes the current line */
130 + /* Is it a secondary index definition? */
132 + (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
133 + (pk_processed || !has_pk)) ||
134 + !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) &&
135 + !contains_ignored_column(&ignored_columns, ptr))
137 + char *data, *end= tmp - 1;
139 + /* Remove the trailing comma */
142 + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE));
143 + skipped_keys_list= list_cons(data, skipped_keys_list);
145 + memmove(orig_ptr, tmp + 1, strend - tmp);
147 + strend-= tmp + 1 - ptr;
149 + /* Remove the comma on the previos line */
150 + if (last_comma != NULL)
159 + if (last_comma != NULL && *ptr != ')')
162 + It's not the last line of CREATE TABLE, so we have skipped a key
163 + definition. We have to restore the last removed comma.
168 + if ((has_pk && is_unique && !pk_processed) ||
169 + !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
170 + pk_processed= TRUE;
172 + if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
175 + If a secondary key is defined on this column later,
176 + it cannot be skipped, as CREATE TABLE would fail on import.
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))))
190 + if (tmp[-1] == ',')
191 + last_comma= tmp - 1;
192 + ptr= (*tmp == '\0') ? tmp : tmp + 1;
196 + my_hash_free(&ignored_columns);
200 + Check if the table has a primary key defined either explicitly or
201 + implicitly (i.e. a unique key on non-nullable columns).
204 + my_bool has_primary_key(const char *table_name)
206 + table_name quoted table name
208 + RETURNS TRUE if the table has a primary key
213 +static my_bool has_primary_key(const char *table_name)
215 + MYSQL_RES *res= NULL;
217 + char query_buff[QUERY_LENGTH];
218 + my_bool has_pk= TRUE;
220 + my_snprintf(query_buff, sizeof(query_buff),
221 + "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE "
222 + "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND "
223 + "COLUMN_KEY='PRI'", table_name);
224 + if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) ||
225 + !(row= mysql_fetch_row(res)))
227 + fprintf(stderr, "Warning: Couldn't determine if table %s has a "
228 + "primary key (%s). "
229 + "--innodb-optimize-keys may work inefficiently.\n",
230 + table_name, mysql_error(mysql));
234 + has_pk= atoi(row[0]) > 0;
238 + mysql_free_result(res);
245 get_table_structure -- retrievs database structure, prints out corresponding
246 CREATE statement and fills out insert_pat if the table is the type we will
248 @@ -2276,6 +2469,7 @@
252 + my_bool has_pk= FALSE;
253 DBUG_ENTER("get_table_structure");
254 DBUG_PRINT("enter", ("db: %s table: %s", db, table));
256 @@ -2317,6 +2511,9 @@
257 result_table= quote_name(table, table_buff, 1);
258 opt_quoted_table= quote_name(table, table_buff2, 0);
260 + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
261 + has_pk= has_primary_key(table);
263 if (opt_order_by_primary)
264 order_by= primary_key_fields(result_table);
266 @@ -2480,6 +2677,9 @@
268 row= mysql_fetch_row(result);
270 + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
271 + skip_secondary_keys(row[1], has_pk);
273 fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
274 "/*!40101 SET @saved_cs_client = @@character_set_client */;\n"
275 "/*!40101 SET character_set_client = utf8 */;\n"
276 @@ -3574,6 +3774,27 @@
280 + /* Perform delayed secondary index creation for --innodb-optimize-keys */
281 + if (skipped_keys_list)
284 + skipped_keys_list= list_reverse(skipped_keys_list);
285 + fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table);
286 + for (keys= list_length(skipped_keys_list); keys > 0; keys--)
288 + LIST *node= skipped_keys_list;
289 + char *def= node->data;
291 + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n");
293 + skipped_keys_list= list_delete(skipped_keys_list, node);
298 + DBUG_ASSERT(skipped_keys_list == NULL);
301 /* Moved enable keys to before unlock per bug 15977 */
302 if (opt_disable_keys)
305 +++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result
308 +# Test the --innodb-optimize-keys option.
310 +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
311 +######################################
313 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
314 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
315 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
316 +/*!40101 SET NAMES utf8 */;
317 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
318 +/*!40103 SET TIME_ZONE='+00:00' */;
319 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
320 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
321 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
322 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
323 +DROP TABLE IF EXISTS `t1`;
324 +/*!40101 SET @saved_cs_client = @@character_set_client */;
325 +/*!40101 SET character_set_client = utf8 */;
327 + `a` int(11) NOT NULL,
328 + `b` int(11) DEFAULT NULL,
331 +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
332 +/*!40101 SET character_set_client = @saved_cs_client */;
334 +LOCK TABLES `t1` WRITE;
335 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
336 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
338 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
340 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
341 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
342 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
343 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
344 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
345 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
346 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
348 +######################################
350 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
351 +INSERT INTO t2 VALUES (0), (1), (2);
353 +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
354 +a INT, b VARCHAR(255), c DECIMAL(10,3),
356 +UNIQUE KEY uniq(c,a),
357 +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
359 +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
360 +######################################
362 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
363 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
364 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
365 +/*!40101 SET NAMES utf8 */;
366 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
367 +/*!40103 SET TIME_ZONE='+00:00' */;
368 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
369 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
370 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
371 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
372 +DROP TABLE IF EXISTS `t1`;
373 +/*!40101 SET @saved_cs_client = @@character_set_client */;
374 +/*!40101 SET character_set_client = utf8 */;
376 + `id` int(11) NOT NULL AUTO_INCREMENT,
377 + `a` int(11) DEFAULT NULL,
378 + `b` varchar(255) DEFAULT NULL,
379 + `c` decimal(10,3) DEFAULT NULL,
380 + PRIMARY KEY (`id`),
381 + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE
382 +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
383 +/*!40101 SET character_set_client = @saved_cs_client */;
385 +LOCK TABLES `t1` WRITE;
386 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
387 +INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200);
388 +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`);
389 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
391 +DROP TABLE IF EXISTS `t2`;
392 +/*!40101 SET @saved_cs_client = @@character_set_client */;
393 +/*!40101 SET character_set_client = utf8 */;
395 + `a` int(11) NOT NULL,
397 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
398 +/*!40101 SET character_set_client = @saved_cs_client */;
400 +LOCK TABLES `t2` WRITE;
401 +/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
402 +INSERT INTO `t2` VALUES (0),(1),(2);
403 +/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
405 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
407 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
408 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
409 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
410 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
411 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
412 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
413 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
415 +######################################
418 +id INT NOT NULL AUTO_INCREMENT,
422 +id INT NOT NULL AUTO_INCREMENT,
425 +INSERT INTO t1 VALUES (), (), ();
426 +INSERT INTO t2 VALUES (), (), ();
427 +######################################
429 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
430 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
431 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
432 +/*!40101 SET NAMES utf8 */;
433 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
434 +/*!40103 SET TIME_ZONE='+00:00' */;
435 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
436 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
437 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
438 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
439 +DROP TABLE IF EXISTS `t1`;
440 +/*!40101 SET @saved_cs_client = @@character_set_client */;
441 +/*!40101 SET character_set_client = utf8 */;
443 + `id` int(11) NOT NULL AUTO_INCREMENT,
445 +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
446 +/*!40101 SET character_set_client = @saved_cs_client */;
448 +LOCK TABLES `t1` WRITE;
449 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
450 +INSERT INTO `t1` VALUES (1),(2),(3);
451 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
453 +DROP TABLE IF EXISTS `t2`;
454 +/*!40101 SET @saved_cs_client = @@character_set_client */;
455 +/*!40101 SET character_set_client = utf8 */;
457 + `id` int(11) NOT NULL AUTO_INCREMENT,
458 + UNIQUE KEY `id` (`id`)
459 +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
460 +/*!40101 SET character_set_client = @saved_cs_client */;
462 +LOCK TABLES `t2` WRITE;
463 +/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
464 +INSERT INTO `t2` VALUES (1),(2),(3);
465 +/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
467 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
469 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
470 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
471 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
472 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
473 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
474 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
475 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
477 +######################################
481 +UNIQUE KEY (a)) ENGINE=InnoDB;
485 +UNIQUE KEY (a,b)) ENGINE=InnoDB;
489 +UNIQUE KEY (a,b)) ENGINE=InnoDB;
494 +UNIQUE KEY(b)) ENGINE=InnoDB;
495 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
496 +TABLE_SCHEMA=DATABASE() AND
501 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
502 +TABLE_SCHEMA=DATABASE() AND
507 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
508 +TABLE_SCHEMA=DATABASE() AND
513 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
514 +TABLE_SCHEMA=DATABASE() AND
519 +INSERT INTO t1 VALUES (1), (2), (3);
520 +INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
521 +INSERT INTO t3 SELECT * FROM t2;
522 +INSERT INTO t4 SELECT * FROM t2;
523 +######################################
525 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
526 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
527 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
528 +/*!40101 SET NAMES utf8 */;
529 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
530 +/*!40103 SET TIME_ZONE='+00:00' */;
531 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
532 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
533 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
534 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
535 +DROP TABLE IF EXISTS `t1`;
536 +/*!40101 SET @saved_cs_client = @@character_set_client */;
537 +/*!40101 SET character_set_client = utf8 */;
539 + `a` int(11) NOT NULL,
540 + UNIQUE KEY `a` (`a`)
541 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
542 +/*!40101 SET character_set_client = @saved_cs_client */;
544 +LOCK TABLES `t1` WRITE;
545 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
546 +INSERT INTO `t1` VALUES (1),(2),(3);
547 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
549 +DROP TABLE IF EXISTS `t2`;
550 +/*!40101 SET @saved_cs_client = @@character_set_client */;
551 +/*!40101 SET character_set_client = utf8 */;
553 + `a` int(11) NOT NULL,
554 + `b` int(11) NOT NULL,
555 + UNIQUE KEY `a` (`a`,`b`)
556 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
557 +/*!40101 SET character_set_client = @saved_cs_client */;
559 +LOCK TABLES `t2` WRITE;
560 +/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
561 +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
562 +/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
564 +DROP TABLE IF EXISTS `t3`;
565 +/*!40101 SET @saved_cs_client = @@character_set_client */;
566 +/*!40101 SET character_set_client = utf8 */;
568 + `a` int(11) DEFAULT NULL,
569 + `b` int(11) DEFAULT NULL
570 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
571 +/*!40101 SET character_set_client = @saved_cs_client */;
573 +LOCK TABLES `t3` WRITE;
574 +/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
575 +INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
576 +ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
577 +/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
579 +DROP TABLE IF EXISTS `t4`;
580 +/*!40101 SET @saved_cs_client = @@character_set_client */;
581 +/*!40101 SET character_set_client = utf8 */;
583 + `a` int(11) NOT NULL,
584 + `b` int(11) NOT NULL,
585 + PRIMARY KEY (`a`,`b`)
586 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
587 +/*!40101 SET character_set_client = @saved_cs_client */;
589 +LOCK TABLES `t4` WRITE;
590 +/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
591 +INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
592 +ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
593 +/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
595 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
597 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
598 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
599 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
600 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
601 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
602 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
603 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
605 +######################################
606 +DROP TABLE t1, t2, t3, t4;
608 +id INT NOT NULL PRIMARY KEY
611 +id INT NOT NULL AUTO_INCREMENT,
615 +FOREIGN KEY (a) REFERENCES t2 (id)
617 +INSERT INTO t1 VALUES (1), (2), (3);
618 +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
619 +######################################
621 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
622 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
623 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
624 +/*!40101 SET NAMES utf8 */;
625 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
626 +/*!40103 SET TIME_ZONE='+00:00' */;
627 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
628 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
629 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
630 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
631 +DROP TABLE IF EXISTS `t1`;
632 +/*!40101 SET @saved_cs_client = @@character_set_client */;
633 +/*!40101 SET character_set_client = utf8 */;
635 + `id` int(11) NOT NULL,
637 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
638 +/*!40101 SET character_set_client = @saved_cs_client */;
640 +LOCK TABLES `t1` WRITE;
641 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
642 +INSERT INTO `t1` VALUES (1),(2),(3);
643 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
645 +DROP TABLE IF EXISTS `t2`;
646 +/*!40101 SET @saved_cs_client = @@character_set_client */;
647 +/*!40101 SET character_set_client = utf8 */;
649 + `id` int(11) NOT NULL AUTO_INCREMENT,
650 + `a` int(11) NOT NULL,
651 + PRIMARY KEY (`id`),
652 + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
653 +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
654 +/*!40101 SET character_set_client = @saved_cs_client */;
656 +LOCK TABLES `t2` WRITE;
657 +/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
658 +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
659 +ALTER TABLE `t2` ADD KEY `a` (`a`);
660 +/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
662 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
664 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
665 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
666 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
667 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
668 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
669 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
670 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
672 +######################################
675 +++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
677 +# Embedded server doesn't support external clients
678 +--source include/not_embedded.inc
680 +# Fast index creation is only available in InnoDB plugin
681 +--source include/have_innodb.inc
683 +# Save the initial number of concurrent sessions
684 +--source include/count_sessions.inc
687 +--echo # Test the --innodb-optimize-keys option.
690 +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
692 +# First test that the option has no effect on non-InnoDB tables
694 +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
696 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
698 +--echo ######################################
700 +--echo ######################################
707 +# Check that for InnoDB tables secondary keys are created after the data is
708 +# dumped but foreign ones are left in CREATE TABLE
710 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
711 +INSERT INTO t2 VALUES (0), (1), (2);
714 + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
715 + a INT, b VARCHAR(255), c DECIMAL(10,3),
717 + UNIQUE KEY uniq(c,a),
718 + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
721 +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
723 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
725 +--echo ######################################
727 +--echo ######################################
729 +# Check that the resulting dump can be imported back
731 +--exec $MYSQL test < $file
737 +########################################################################
738 +# Bug #812179: AUTO_INCREMENT columns must be skipped by the
739 +# --innodb-optimize-keys optimization in mysqldump
740 +########################################################################
743 + id INT NOT NULL AUTO_INCREMENT,
748 + id INT NOT NULL AUTO_INCREMENT,
752 +INSERT INTO t1 VALUES (), (), ();
753 +INSERT INTO t2 VALUES (), (), ();
755 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
757 +--echo ######################################
759 +--echo ######################################
761 +# Check that the resulting dump can be imported back
763 +--exec $MYSQL test < $file
769 +########################################################################
770 +# Bug #851674: --innodb-optimize-keys does not work correctly with table
771 +# without PRIMARY KEY
772 +########################################################################
776 + UNIQUE KEY (a)) ENGINE=InnoDB;
781 + UNIQUE KEY (a,b)) ENGINE=InnoDB;
786 + UNIQUE KEY (a,b)) ENGINE=InnoDB;
792 + UNIQUE KEY(b)) ENGINE=InnoDB;
794 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
795 + TABLE_SCHEMA=DATABASE() AND
796 + TABLE_NAME='t1' AND
798 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
799 + TABLE_SCHEMA=DATABASE() AND
800 + TABLE_NAME='t2' AND
802 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
803 + TABLE_SCHEMA=DATABASE() AND
804 + TABLE_NAME='t3' AND
806 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
807 + TABLE_SCHEMA=DATABASE() AND
808 + TABLE_NAME='t4' AND
811 +INSERT INTO t1 VALUES (1), (2), (3);
812 +INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
813 +INSERT INTO t3 SELECT * FROM t2;
814 +INSERT INTO t4 SELECT * FROM t2;
816 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
818 +--echo ######################################
820 +--echo ######################################
822 +# Check that the resulting dump can be imported back
824 +--exec $MYSQL test < $file
828 +DROP TABLE t1, t2, t3, t4;
830 +########################################################################
831 +# Bug #859078: --innodb-optimize-keys should ignore foreign keys
832 +########################################################################
835 + id INT NOT NULL PRIMARY KEY
839 + id INT NOT NULL AUTO_INCREMENT,
843 + FOREIGN KEY (a) REFERENCES t2 (id)
846 +INSERT INTO t1 VALUES (1), (2), (3);
847 +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
849 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
851 +--echo ######################################
853 +--echo ######################################
855 +# Check that the resulting dump can be imported back
857 +--exec $MYSQL test < $file
863 +# Wait till we reached the initial number of concurrent sessions
864 +--source include/wait_until_count_sessions.inc
867 @@ -1638,6 +1638,9 @@
868 alter_list(rhs.alter_list, mem_root),
869 key_list(rhs.key_list, mem_root),
870 create_list(rhs.create_list, mem_root),
871 + delayed_key_list(rhs.delayed_key_list, mem_root),
872 + delayed_key_info(rhs.delayed_key_info),
873 + delayed_key_count(rhs.delayed_key_count),
875 keys_onoff(rhs.keys_onoff),
876 tablespace_op(rhs.tablespace_op),
877 @@ -1660,6 +1663,7 @@
878 list_copy_and_replace_each_value(alter_list, mem_root);
879 list_copy_and_replace_each_value(key_list, mem_root);
880 list_copy_and_replace_each_value(create_list, mem_root);
881 + list_copy_and_replace_each_value(delayed_key_list, mem_root);
882 /* partition_names are not deeply copied currently */
887 @@ -1014,6 +1014,9 @@
888 List<Alter_column> alter_list;
890 List<Create_field> create_list;
891 + List<Key> delayed_key_list;
892 + KEY *delayed_key_info;
893 + uint delayed_key_count;
895 enum enum_enable_or_disable keys_onoff;
896 enum tablespace_op_type tablespace_op;
897 @@ -1025,6 +1028,8 @@
901 + delayed_key_info(NULL),
902 + delayed_key_count(0),
904 keys_onoff(LEAVE_AS_IS),
905 tablespace_op(NO_TABLESPACE_OP),
906 @@ -1040,6 +1045,9 @@
910 + delayed_key_list.empty();
911 + delayed_key_info= NULL;
912 + delayed_key_count= 0;
914 keys_onoff= LEAVE_AS_IS;
915 tablespace_op= NO_TABLESPACE_OP;
916 --- a/sql/sql_table.cc
917 +++ b/sql/sql_table.cc
918 @@ -3220,6 +3220,14 @@
919 if (!*key_info_buffer || ! key_part_info)
920 DBUG_RETURN(TRUE); // Out of memory
922 + List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list);
923 + alter_info->delayed_key_count= 0;
924 + if (alter_info->delayed_key_list.elements > 0)
926 + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) *
930 key_iterator.rewind();
932 for (; (key=key_iterator++) ; key_number++)
933 @@ -3638,6 +3646,22 @@
934 key_info->comment.str= key->key_create_info.comment.str;
937 + if (alter_info->delayed_key_list.elements > 0)
941 + delayed_key_iterator.rewind();
942 + while ((delayed_key= delayed_key_iterator++))
944 + if (delayed_key == key)
946 + alter_info->delayed_key_info[alter_info->delayed_key_count++]=
955 if (!unique_key && !primary_key &&
956 @@ -5261,6 +5285,10 @@
957 List<Create_field> new_create_list;
958 /* New key definitions are added here */
959 List<Key> new_key_list;
960 + /* List with secondary keys which should be created after copying the data */
961 + List<Key> delayed_key_list;
962 + /* Foreign key list returned by handler::get_foreign_key_list() */
963 + List<FOREIGN_KEY_INFO> f_key_list;
964 List_iterator<Alter_drop> drop_it(alter_info->drop_list);
965 List_iterator<Create_field> def_it(alter_info->create_list);
966 List_iterator<Alter_column> alter_it(alter_info->alter_list);
967 @@ -5273,6 +5301,7 @@
968 uint used_fields= create_info->used_fields;
969 KEY *key_info=table->key_info;
971 + bool skip_secondary;
973 DBUG_ENTER("mysql_prepare_alter_table");
975 @@ -5462,7 +5491,26 @@
977 Collect all keys which isn't in drop list. Add only those
978 for which some fields exists.
981 + We also store secondary keys in delayed_key_list to make use of
982 + the InnoDB fast index creation. The following conditions must be
985 + - fast_index_creation is enabled for the current session
986 + - expand_fast_index_creation is enabled for the current session;
987 + - we are going to create an InnoDB table (this is checked later when the
988 + target engine is known);
989 + - the key most be a non-UNIQUE one;
990 + - there are no foreign keys. This can be optimized later to exclude only
991 + those keys which are a part of foreign key constraints. Currently we
992 + simply disable this optimization for all keys if there are any foreign
993 + key constraints in the table.
996 + skip_secondary= thd->variables.online_alter_index &&
997 + thd->variables.expand_fast_index_creation &&
998 + !table->file->get_foreign_key_list(thd, &f_key_list) &&
999 + f_key_list.elements == 0;
1001 for (uint i=0 ; i < table->s->keys ; i++,key_info++)
1003 @@ -5579,6 +5627,8 @@
1004 test(key_info->flags & HA_GENERATED_KEY),
1006 new_key_list.push_back(key);
1007 + if (skip_secondary && key_type == Key::MULTIPLE)
1008 + delayed_key_list.push_back(key);
1012 @@ -5586,7 +5636,21 @@
1013 while ((key=key_it++)) // Add new keys
1015 if (key->type != Key::FOREIGN_KEY)
1017 new_key_list.push_back(key);
1018 + if (skip_secondary && key->type == Key::MULTIPLE)
1019 + delayed_key_list.push_back(key);
1021 + else if (skip_secondary)
1024 + We are adding a foreign key so disable the secondary keys
1027 + skip_secondary= FALSE;
1028 + delayed_key_list.empty();
1031 if (key->name.str &&
1032 !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
1034 @@ -5635,12 +5699,104 @@
1036 alter_info->create_list.swap(new_create_list);
1037 alter_info->key_list.swap(new_key_list);
1038 + alter_info->delayed_key_list.swap(delayed_key_list);
1045 + Temporarily remove secondary keys previously stored in
1046 + alter_info->delayed_key_info.
1049 +remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
1051 + uint *key_numbers;
1052 + uint key_counter= 0;
1055 + DBUG_ENTER("remove_secondary_keys");
1056 + DBUG_ASSERT(alter_info->delayed_key_count > 0);
1058 + key_numbers= (uint *) thd->alloc(sizeof(uint) *
1059 + alter_info->delayed_key_count);
1060 + for (i= 0; i < alter_info->delayed_key_count; i++)
1062 + KEY *key= alter_info->delayed_key_info + i;
1065 + for (j= 0; j < table->s->keys; j++)
1067 + if (!strcmp(table->key_info[j].name, key->name))
1069 + key_numbers[key_counter++]= j;
1075 + DBUG_ASSERT(key_counter == alter_info->delayed_key_count);
1077 + if ((error= table->file->prepare_drop_index(table, key_numbers,
1079 + (error= table->file->final_drop_index(table)))
1081 + table->file->print_error(error, MYF(0));
1084 + DBUG_RETURN(error);
1088 + Restore secondary keys previously removed in remove_secondary_keys.
1092 +restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
1096 + DBUG_ENTER("restore_secondary_keys");
1097 + DBUG_ASSERT(alter_info->delayed_key_count > 0);
1099 + thd_proc_info(thd, "restoring secondary keys");
1101 + /* Fix the key parts */
1102 + for (i= 0; i < alter_info->delayed_key_count; i++)
1104 + KEY *key = alter_info->delayed_key_info + i;
1105 + KEY_PART_INFO *key_part;
1106 + KEY_PART_INFO *part_end;
1108 + part_end= key->key_part + key->key_parts;
1109 + for (key_part= key->key_part; key_part < part_end; key_part++)
1110 + key_part->field= table->field[key_part->fieldnr];
1112 + handler_add_index *add;
1113 + if ((error= table->file->add_index(table, alter_info->delayed_key_info,
1114 + alter_info->delayed_key_count, &add)))
1117 + Exchange the key_info for the error message. If we exchange
1118 + key number by key name in the message later, we need correct info.
1120 + KEY *save_key_info= table->key_info;
1121 + table->key_info= alter_info->delayed_key_info;
1122 + table->file->print_error(error, MYF(0));
1123 + table->key_info= save_key_info;
1125 + DBUG_RETURN(error);
1127 + if ((error= table->file->final_add_index(add, true)))
1129 + table->file->print_error(error, MYF(0));
1132 + DBUG_RETURN(error);
1139 @@ -6434,19 +6590,38 @@
1141 if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
1144 + Check if we can temporarily remove secondary indexes from the table
1145 + before copying the data and recreate them later to utilize InnoDB fast
1147 + TODO: is there a better way to check for InnoDB?
1149 + bool optimize_keys= (alter_info->delayed_key_count > 0) &&
1150 + !my_strcasecmp(system_charset_info,
1151 + new_table->file->table_type(), "InnoDB");
1152 /* We don't want update TIMESTAMP fields during ALTER TABLE. */
1153 new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
1154 new_table->next_number_field=new_table->found_next_number_field;
1156 thd_proc_info(thd, "copy to tmp table");
1157 DBUG_EXECUTE_IF("abort_copy_table", {
1158 my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
1159 goto err_new_table_cleanup;
1162 + if (optimize_keys)
1164 + /* ignore the error */
1165 + error= remove_secondary_keys(thd, new_table, alter_info);
1168 error= copy_data_between_tables(table, new_table,
1169 alter_info->create_list, ignore,
1170 order_num, order, &copied, &deleted,
1171 alter_info->keys_onoff,
1172 alter_info->error_if_not_empty);
1173 + if (!error && optimize_keys)
1174 + error= restore_secondary_keys(thd, new_table, alter_info);
1179 +++ b/mysql-test/r/percona_innodb_expand_fast_index_creation.result
1182 +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1183 +a CHAR(1) NOT NULL,
1184 +b CHAR(36) NOT NULL) ENGINE=InnoDB;
1185 +INSERT INTO t1(a,b) VALUES ('a','b');
1186 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1187 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1188 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1189 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1190 +ALTER TABLE t1 ADD KEY (a);
1192 +info: Records: 0 Duplicates: 0 Warnings: 0
1193 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
1214 +ALTER TABLE t1 DROP KEY a;
1215 +SET expand_fast_index_creation = 1;
1216 +SELECT @@expand_fast_index_creation;
1217 +@@expand_fast_index_creation
1219 +ALTER TABLE t1 ADD KEY (a);
1221 +info: Records: 0 Duplicates: 0 Warnings: 0
1222 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
1242 +Extra Using where; Using join buffer
1243 +SET expand_fast_index_creation = 0;
1246 +++ b/mysql-test/t/percona_innodb_expand_fast_index_creation.test
1248 +--source include/have_innodb.inc
1250 +########################################################################
1251 +# Bug #857590: Fast index creation does not update index statistics
1252 +########################################################################
1255 + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1256 + a CHAR(1) NOT NULL,
1257 + b CHAR(36) NOT NULL) ENGINE=InnoDB;
1259 +INSERT INTO t1(a,b) VALUES ('a','b');
1260 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1261 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1262 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1263 +INSERT INTO t1(a,b) SELECT a,b FROM t1;
1265 +# Check that fast index creation is used
1267 +ALTER TABLE t1 ADD KEY (a);
1270 +# The default (wrong) plan due to bogus statistics
1272 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
1273 +--horizontal_results
1275 +ALTER TABLE t1 DROP KEY a;
1277 +SET expand_fast_index_creation = 1;
1278 +SELECT @@expand_fast_index_creation;
1280 +# Check that stats are updated with the option enabled
1283 +ALTER TABLE t1 ADD KEY (a);
1287 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
1288 +--horizontal_results
1290 +SET expand_fast_index_creation = 0;
1293 --- a/storage/innobase/row/row0merge.c
1294 +++ b/storage/innobase/row/row0merge.c
1296 #include "log0log.h"
1297 #include "ut0sort.h"
1298 #include "handler0alter.h"
1299 +#include "ha_prototypes.h"
1301 /* Ignore posix_fadvise() on those platforms where it does not exist */
1303 @@ -2673,6 +2674,9 @@
1307 + if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd))
1308 + dict_update_statistics(new_table, FALSE, TRUE);
1311 row_merge_file_destroy_low(tmpfd);
1313 --- a/sql/sql_class.h
1314 +++ b/sql/sql_class.h
1317 double long_query_time_double;
1319 + my_bool expand_fast_index_creation;
1323 --- a/sql/sys_vars.cc
1324 +++ b/sql/sys_vars.cc
1325 @@ -783,6 +783,14 @@
1326 ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update));
1329 +static Sys_var_mybool Sys_expand_fast_index_creation(
1330 + "expand_fast_index_creation",
1331 + "Enable/disable improvements to the InnoDB fast index creation "
1332 + "functionality. Has no effect when fast index creation is disabled with "
1333 + "the fast-index-creation option",
1334 + SESSION_VAR(expand_fast_index_creation), CMD_LINE(OPT_ARG),
1337 static Sys_var_ulong Sys_expire_logs_days(
1339 "If non-zero, binary logs will be purged after expire_logs_days "
1340 --- a/storage/innobase/handler/ha_innodb.cc
1341 +++ b/storage/innobase/handler/ha_innodb.cc
1342 @@ -999,6 +999,19 @@
1343 return(THDVAR((THD*) thd, flush_log_at_trx_commit));
1346 +/******************************************************************//**
1347 +Returns true if expand_fast_index_creation is enabled for the current
1349 +@return the value of the server's expand_fast_index_creation variable */
1350 +extern "C" UNIV_INTERN
1352 +thd_expand_fast_index_creation(
1353 +/*================================*/
1356 + return((ibool) (((THD*) thd)->variables.expand_fast_index_creation));
1359 /********************************************************************//**
1360 Obtain the InnoDB transaction of a MySQL thread.
1361 @return reference to transaction pointer */
1362 --- a/storage/innobase/include/ha_prototypes.h
1363 +++ b/storage/innobase/include/ha_prototypes.h
1364 @@ -303,4 +303,15 @@
1365 innobase_get_lower_case_table_names(void);
1366 /*=====================================*/
1368 +/******************************************************************//**
1369 +Returns true if innodb_expand_fast_index_creation is enabled for the current
1371 +@return the value of the server's innodb_expand_fast_index_creation variable */
1374 +thd_expand_fast_index_creation(
1375 +/*==================*/
1376 + void* thd); /*!< in: thread handle (THD*) */
1381 +++ b/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result
1383 +SELECT @@global.expand_fast_index_creation;
1384 +@@global.expand_fast_index_creation
1386 +SELECT @@local.expand_fast_index_creation;
1387 +@@local.expand_fast_index_creation
1390 +++ b/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test
1392 +SELECT @@global.expand_fast_index_creation;
1393 +SELECT @@local.expand_fast_index_creation;
1394 --- a/mysql-test/r/mysqld--help-notwin.result
1395 +++ b/mysql-test/r/mysqld--help-notwin.result
1396 @@ -140,6 +140,10 @@
1397 and DISABLED (keep the event scheduler completely
1398 deactivated, it cannot be activated run-time)
1399 -T, --exit-info[=#] Used for debugging. Use at your own risk.
1400 + --expand-fast-index-creation
1401 + Enable/disable improvements to the InnoDB fast index
1402 + creation functionality. Has no effect when fast index
1403 + creation is disabled with the fast-index-creation option
1404 --expire-logs-days=#
1405 If non-zero, binary logs will be purged after
1406 expire_logs_days days; possible purges happen at startup
1408 div-precision-increment 4
1409 engine-condition-pushdown TRUE
1411 +expand-fast-index-creation FALSE
1413 external-locking FALSE
1415 --- a/mysql-test/r/mysqld--help-win.result
1416 +++ b/mysql-test/r/mysqld--help-win.result
1417 @@ -140,6 +140,10 @@
1418 and DISABLED (keep the event scheduler completely
1419 deactivated, it cannot be activated run-time)
1420 -T, --exit-info[=#] Used for debugging. Use at your own risk.
1421 + --expand-fast-index-creation
1422 + Enable/disable improvements to InnoDB fast index creation
1423 + functionality. Has no effect when fast index creation is
1424 + disabled with the fast-index-creation option
1425 --expire-logs-days=#
1426 If non-zero, binary logs will be purged after
1427 expire_logs_days days; possible purges happen at startup
1429 div-precision-increment 4
1430 engine-condition-pushdown TRUE
1432 +expand-fast-index-creation FALSE
1434 external-locking FALSE