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.
7 diff -ruN a/client/client_priv.h b/client/client_priv.h
8 --- a/client/client_priv.h 2011-04-11 08:57:20.000000000 +0400
9 +++ b/client/client_priv.h 2011-04-11 08:57:21.000000000 +0400
11 OPT_NO_REMOVE_EOL_CARRET,
14 + OPT_INNODB_OPTIMIZE_KEYS,
18 diff -ruN a/client/mysqldump.c b/client/mysqldump.c
19 --- a/client/mysqldump.c 2011-04-11 08:57:17.000000000 +0400
20 +++ b/client/mysqldump.c 2011-04-11 08:57:21.000000000 +0400
27 #include "client_priv.h"
31 static my_bool server_supports_sql_no_fcache= FALSE;
33 +static my_bool opt_innodb_optimize_keys= FALSE;
36 Dynamic_string wrapper functions. In this file use these
37 wrappers, they will terminate the process if there is
42 +LIST *skipped_keys_list;
44 static struct my_option my_long_options[] =
46 {"all-databases", 'A',
48 "in dump produced with --dump-slave.", &opt_include_master_host_port,
49 &opt_include_master_host_port, 0, GET_BOOL, NO_ARG,
51 + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS,
52 + "Use InnoDB fast index creation by creating secondary indexes after "
53 + "dumping the data.",
54 + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG,
56 {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",
57 &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
59 @@ -2236,6 +2246,77 @@
63 + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string
64 + and store them into a temporary list to be used later.
67 + skip_secondary_keys()
68 + create_str SHOW CREATE TABLE output
73 + Stores all lines starting with "KEY" or "UNIQUE KEY" or "CONSTRAINT"
74 + into skipped_keys_list and removes them from the input string.
75 + Ignoring FOREIGN KEYS constraints when creating the table is ok, because
76 + mysqldump sets foreign_key_checks to 0 anyway.
79 +static void skip_secondary_keys(char *create_str)
82 + char *last_comma = NULL;
84 + strend= create_str + strlen(create_str);
89 + char *tmp, *orig_ptr;
92 + /* Skip leading whitespace */
93 + while (*ptr && my_isspace(charset_info, *ptr))
96 + /* Read the next line */
97 + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++);
99 + /* Is it a secondary index definition? */
100 + if (*tmp == '\n' &&
101 + (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1) ||
102 + !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) ||
103 + !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1)))
105 + char *data, *end= tmp - 1;
107 + /* Remove the trailing comma */
110 + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE));
111 + skipped_keys_list= list_cons(data, skipped_keys_list);
113 + memmove(orig_ptr, tmp + 1, strend - tmp);
115 + strend-= tmp + 1 - ptr;
117 + /* Remove the comma on the previos line */
118 + if (last_comma != NULL)
126 + if (tmp[-1] == ',')
127 + last_comma= tmp - 1;
128 + ptr= (*tmp == '\0') ? tmp : tmp + 1;
134 get_table_structure -- retrievs database structure, prints out corresponding
135 CREATE statement and fills out insert_pat if the table is the type we will
137 @@ -2476,6 +2557,9 @@
139 row= mysql_fetch_row(result);
141 + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
142 + skip_secondary_keys(row[1]);
144 fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
145 "/*!40101 SET @saved_cs_client = @@character_set_client */;\n"
146 "/*!40101 SET character_set_client = utf8 */;\n"
147 @@ -3570,6 +3654,27 @@
151 + /* Perform delayed secondary index creation for --innodb-optimize-keys */
152 + if (skipped_keys_list)
155 + skipped_keys_list= list_reverse(skipped_keys_list);
156 + fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table);
157 + for (keys= list_length(skipped_keys_list); keys > 0; keys--)
159 + LIST *node= skipped_keys_list;
160 + char *def= node->data;
162 + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n");
164 + skipped_keys_list= list_delete(skipped_keys_list, node);
169 + DBUG_ASSERT(skipped_keys_list == NULL);
172 /* Moved enable keys to before unlock per bug 15977 */
173 if (opt_disable_keys)
175 diff -ruN /dev/null b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result
176 --- /dev/null 1970-01-01 00:00:00.000000000 +0000
177 +++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2011-04-11 08:57:21.000000000 +0400
180 +# Test the --innodb-optimize-keys option.
182 +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
183 +######################################
185 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
186 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
187 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
188 +/*!40101 SET NAMES utf8 */;
189 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
190 +/*!40103 SET TIME_ZONE='+00:00' */;
191 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
192 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
193 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
194 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
195 +DROP TABLE IF EXISTS `t1`;
196 +/*!40101 SET @saved_cs_client = @@character_set_client */;
197 +/*!40101 SET character_set_client = utf8 */;
199 + `a` int(11) NOT NULL,
200 + `b` int(11) DEFAULT NULL,
203 +) ENGINE=MyISAM DEFAULT CHARSET=latin1;
204 +/*!40101 SET character_set_client = @saved_cs_client */;
206 +LOCK TABLES `t1` WRITE;
207 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
208 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
210 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
212 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
213 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
214 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
215 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
216 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
217 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
218 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
220 +######################################
222 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
223 +INSERT INTO t2 VALUES (0), (1), (2);
225 +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
226 +a INT, b VARCHAR(255), c DECIMAL(10,3),
228 +UNIQUE KEY uniq(c,a),
229 +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
231 +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
232 +######################################
234 +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
235 +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
236 +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
237 +/*!40101 SET NAMES utf8 */;
238 +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
239 +/*!40103 SET TIME_ZONE='+00:00' */;
240 +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
241 +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
242 +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
243 +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
244 +DROP TABLE IF EXISTS `t1`;
245 +/*!40101 SET @saved_cs_client = @@character_set_client */;
246 +/*!40101 SET character_set_client = utf8 */;
248 + `id` int(11) NOT NULL AUTO_INCREMENT,
249 + `a` int(11) DEFAULT NULL,
250 + `b` varchar(255) DEFAULT NULL,
251 + `c` decimal(10,3) DEFAULT NULL,
253 +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
254 +/*!40101 SET character_set_client = @saved_cs_client */;
256 +LOCK TABLES `t1` WRITE;
257 +/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
258 +INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200);
259 +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`), ADD CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE;
260 +/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
262 +DROP TABLE IF EXISTS `t2`;
263 +/*!40101 SET @saved_cs_client = @@character_set_client */;
264 +/*!40101 SET character_set_client = utf8 */;
266 + `a` int(11) NOT NULL,
268 +) ENGINE=InnoDB DEFAULT CHARSET=latin1;
269 +/*!40101 SET character_set_client = @saved_cs_client */;
271 +LOCK TABLES `t2` WRITE;
272 +/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
273 +INSERT INTO `t2` VALUES (0),(1),(2);
274 +/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
276 +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
278 +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
279 +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
280 +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
281 +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
282 +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
283 +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
284 +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
286 +######################################
288 diff -ruN a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
289 --- a/mysql-test/suite/innodb/r/innodb.result 2011-03-31 17:36:17.000000000 +0400
290 +++ b/mysql-test/suite/innodb/r/innodb.result 2011-04-11 23:26:45.000000000 +0400
291 @@ -1673,7 +1673,7 @@
293 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
294 variable_value - @innodb_rows_inserted_orig
297 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
298 variable_value - @innodb_rows_updated_orig
300 diff -ruN a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test
301 --- a/mysql-test/suite/innodb/t/innodb-index.test 2011-03-31 17:36:17.000000000 +0400
302 +++ b/mysql-test/suite/innodb/t/innodb-index.test 2011-04-11 08:57:21.000000000 +0400
304 show create table t1;
305 --error ER_MULTIPLE_PRI_KEY
306 alter table t1 add primary key (c);
307 +# Suppress the error log messages occuring on duplicate key error
308 +# during ALTER TABLE when using fast index creation
310 +call mtr.add_suppression("Cannot find index PRIMARY in InnoDB index translation table.");
313 alter table t1 drop primary key, add primary key (b);
314 create unique index c on t1 (c);
315 diff -ruN a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test
316 --- a/mysql-test/suite/innodb/t/innodb.test 2011-03-31 17:36:17.000000000 +0400
317 +++ b/mysql-test/suite/innodb/t/innodb.test 2011-04-11 08:57:21.000000000 +0400
320 -- source include/have_innodb.inc
322 +# Suppress the error log message occuring on duplicate key error
323 +# during ALTER TABLE when using fast index creation
325 +call mtr.add_suppression("Cannot find index v_2 in InnoDB index translation table.");
328 let $MYSQLD_DATADIR= `select @@datadir`;
330 # Save the original values of some variables in order to be able to
331 diff -ruN /dev/null b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
332 --- /dev/null 1970-01-01 00:00:00.000000000 +0000
333 +++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2011-04-11 08:57:21.000000000 +0400
335 +# Embedded server doesn't support external clients
336 +--source include/not_embedded.inc
338 +# Fast index creation is only available in InnoDB plugin
339 +--source include/have_innodb.inc
341 +# Save the initial number of concurrent sessions
342 +--source include/count_sessions.inc
345 +--echo # Test the --innodb-optimize-keys option.
348 +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
350 +# First test that the option has no effect on non-InnoDB tables
352 +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
354 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
356 +--echo ######################################
358 +--echo ######################################
365 +# Check that for InnoDB tables secondary and foreign keys are created
366 +# after the data is dumped
368 +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
369 +INSERT INTO t2 VALUES (0), (1), (2);
372 + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
373 + a INT, b VARCHAR(255), c DECIMAL(10,3),
375 + UNIQUE KEY uniq(c,a),
376 + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
379 +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
381 +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
383 +--echo ######################################
385 +--echo ######################################
387 +# Check that the resulting dump can be imported back
389 +--exec $MYSQL test < $file
395 +# Wait till we reached the initial number of concurrent sessions
396 +--source include/wait_until_count_sessions.inc
397 diff -ruN a/sql/sql_lex.cc b/sql/sql_lex.cc
398 --- a/sql/sql_lex.cc 2011-04-11 08:57:17.000000000 +0400
399 +++ b/sql/sql_lex.cc 2011-04-11 08:57:21.000000000 +0400
400 @@ -1630,6 +1630,9 @@
401 alter_list(rhs.alter_list, mem_root),
402 key_list(rhs.key_list, mem_root),
403 create_list(rhs.create_list, mem_root),
404 + delayed_key_list(rhs.delayed_key_list, mem_root),
405 + delayed_key_info(rhs.delayed_key_info),
406 + delayed_key_count(rhs.delayed_key_count),
408 keys_onoff(rhs.keys_onoff),
409 tablespace_op(rhs.tablespace_op),
410 @@ -1652,6 +1655,7 @@
411 list_copy_and_replace_each_value(alter_list, mem_root);
412 list_copy_and_replace_each_value(key_list, mem_root);
413 list_copy_and_replace_each_value(create_list, mem_root);
414 + list_copy_and_replace_each_value(delayed_key_list, mem_root);
415 /* partition_names are not deeply copied currently */
418 diff -ruN a/sql/sql_lex.h b/sql/sql_lex.h
419 --- a/sql/sql_lex.h 2011-04-11 08:57:19.000000000 +0400
420 +++ b/sql/sql_lex.h 2011-04-11 08:57:21.000000000 +0400
421 @@ -1003,6 +1003,9 @@
422 List<Alter_column> alter_list;
424 List<Create_field> create_list;
425 + List<Key> delayed_key_list;
426 + KEY *delayed_key_info;
427 + uint delayed_key_count;
429 enum enum_enable_or_disable keys_onoff;
430 enum tablespace_op_type tablespace_op;
431 @@ -1014,6 +1017,8 @@
435 + delayed_key_info(NULL),
436 + delayed_key_count(0),
438 keys_onoff(LEAVE_AS_IS),
439 tablespace_op(NO_TABLESPACE_OP),
440 @@ -1029,6 +1034,9 @@
444 + delayed_key_list.empty();
445 + delayed_key_info= NULL;
446 + delayed_key_count= 0;
448 keys_onoff= LEAVE_AS_IS;
449 tablespace_op= NO_TABLESPACE_OP;
450 diff -ruN a/sql/sql_table.cc b/sql/sql_table.cc
451 --- a/sql/sql_table.cc 2011-04-11 08:56:57.000000000 +0400
452 +++ b/sql/sql_table.cc 2011-04-11 23:30:02.000000000 +0400
453 @@ -2773,7 +2773,7 @@
454 file The handler for the new table.
455 key_info_buffer OUT An array of KEY structs for the indexes.
456 key_count OUT The number of elements in the array.
457 - select_field_count The number of fields coming from a select table.
458 + select_field_count The number of fields coming from a select table.
461 Prepares the table and key structures for table creation.
462 @@ -3119,7 +3119,6 @@
467 List_iterator<Key> key_iterator(alter_info->key_list);
468 List_iterator<Key> key_iterator2(alter_info->key_list);
469 uint key_parts=0, fk_key_count=0;
470 @@ -3217,6 +3216,14 @@
471 if (!*key_info_buffer || ! key_part_info)
472 DBUG_RETURN(TRUE); // Out of memory
474 + List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list);
475 + alter_info->delayed_key_count= 0;
476 + if (alter_info->delayed_key_list.elements > 0)
478 + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) *
482 key_iterator.rewind();
484 for (; (key=key_iterator++) ; key_number++)
485 @@ -3635,6 +3642,22 @@
486 key_info->comment.str= key->key_create_info.comment.str;
489 + if (alter_info->delayed_key_list.elements > 0)
493 + delayed_key_iterator.rewind();
494 + while ((delayed_key= delayed_key_iterator++))
496 + if (delayed_key == key)
498 + alter_info->delayed_key_info[alter_info->delayed_key_count++]=
507 if (!unique_key && !primary_key &&
508 @@ -5247,6 +5270,10 @@
509 List<Create_field> new_create_list;
510 /* New key definitions are added here */
511 List<Key> new_key_list;
512 + /* List with secondary keys which should be created after copying the data */
513 + List<Key> delayed_key_list;
514 + /* Foreign key list returned by handler::get_foreign_key_list() */
515 + List<FOREIGN_KEY_INFO> f_key_list;
516 List_iterator<Alter_drop> drop_it(alter_info->drop_list);
517 List_iterator<Create_field> def_it(alter_info->create_list);
518 List_iterator<Alter_column> alter_it(alter_info->alter_list);
519 @@ -5259,6 +5286,7 @@
520 uint used_fields= create_info->used_fields;
521 KEY *key_info=table->key_info;
523 + bool skip_secondary;
525 DBUG_ENTER("mysql_prepare_alter_table");
527 @@ -5286,6 +5314,7 @@
528 char *tablespace= static_cast<char *>(thd->alloc(FN_LEN + 1));
530 Regular alter table of disk stored table (no tablespace/storage change)
535 @@ -5436,7 +5465,23 @@
537 Collect all keys which isn't in drop list. Add only those
538 for which some fields exists.
541 + We also store secondary keys in delayed_key_list to make use of
542 + the InnoDB fast index creation. The following conditions must be
545 + - we are going to create an InnoDB table (this is checked later when the
546 + target engine is known);
547 + - the key most be a non-UNIQUE one;
548 + - there are no foreign keys. This can be optimized later to exclude only
549 + those keys which are a part of foreign key constraints. Currently we
550 + simply disable this optimization for all keys if there are any foreign
551 + key constraints in the table.
555 + !table->file->get_foreign_key_list(thd, &f_key_list) &&
556 + f_key_list.elements == 0;
558 for (uint i=0 ; i < table->s->keys ; i++,key_info++)
560 @@ -5553,6 +5598,8 @@
561 test(key_info->flags & HA_GENERATED_KEY),
563 new_key_list.push_back(key);
564 + if (skip_secondary && key_type == Key::MULTIPLE)
565 + delayed_key_list.push_back(key);
569 @@ -5560,7 +5607,21 @@
570 while ((key=key_it++)) // Add new keys
572 if (key->type != Key::FOREIGN_KEY)
574 new_key_list.push_back(key);
575 + if (skip_secondary && key->type == Key::MULTIPLE)
576 + delayed_key_list.push_back(key);
578 + else if (skip_secondary)
581 + We are adding a foreign key so disable the secondary keys
584 + skip_secondary= FALSE;
585 + delayed_key_list.empty();
589 !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
591 @@ -5609,12 +5670,100 @@
593 alter_info->create_list.swap(new_create_list);
594 alter_info->key_list.swap(new_key_list);
595 + alter_info->delayed_key_list.swap(delayed_key_list);
602 + Temporarily remove secondary keys previously stored in
603 + alter_info->delayed_key_info.
606 +remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
609 + uint key_counter= 0;
612 + DBUG_ENTER("remove_secondary_keys");
613 + DBUG_ASSERT(alter_info->delayed_key_count > 0);
615 + key_numbers= (uint *) thd->alloc(sizeof(uint) *
616 + alter_info->delayed_key_count);
617 + for (i= 0; i < alter_info->delayed_key_count; i++)
619 + KEY *key= alter_info->delayed_key_info + i;
622 + for (j= 0; j < table->s->keys; j++)
624 + if (!strcmp(table->key_info[j].name, key->name))
626 + key_numbers[key_counter++]= j;
632 + DBUG_ASSERT(key_counter == alter_info->delayed_key_count);
634 + if ((error= table->file->prepare_drop_index(table, key_numbers,
636 + (error= table->file->final_drop_index(table)))
638 + table->file->print_error(error, MYF(0));
641 + DBUG_RETURN(error);
645 + Restore secondary keys previously removed in remove_secondary_keys.
649 +restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
653 + DBUG_ENTER("restore_secondary_keys");
654 + DBUG_ASSERT(alter_info->delayed_key_count > 0);
656 + thd_proc_info(thd, "restoring secondary keys");
658 + /* Fix the key parts */
659 + for (i= 0; i < alter_info->delayed_key_count; i++)
661 + KEY *key = alter_info->delayed_key_info + i;
662 + KEY_PART_INFO *key_part;
663 + KEY_PART_INFO *part_end;
665 + part_end= key->key_part + key->key_parts;
666 + for (key_part= key->key_part; key_part < part_end; key_part++)
667 + key_part->field= table->field[key_part->fieldnr];
670 + if ((error= table->file->add_index(table, alter_info->delayed_key_info,
671 + alter_info->delayed_key_count)))
674 + Exchange the key_info for the error message. If we exchange
675 + key number by key name in the message later, we need correct info.
677 + KEY *save_key_info= table->key_info;
678 + table->key_info= alter_info->delayed_key_info;
679 + table->file->print_error(error, MYF(0));
680 + table->key_info= save_key_info;
682 + DBUG_RETURN(error);
692 @@ -6405,19 +6554,38 @@
694 if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
697 + Check if we can temporarily remove secondary indexes from the table
698 + before copying the data and recreate them later to utilize InnoDB fast
700 + TODO: is there a better way to check for InnoDB?
702 + bool optimize_keys= (alter_info->delayed_key_count > 0) &&
703 + !my_strcasecmp(system_charset_info,
704 + new_table->file->table_type(), "InnoDB");
705 /* We don't want update TIMESTAMP fields during ALTER TABLE. */
706 new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
707 new_table->next_number_field=new_table->found_next_number_field;
709 thd_proc_info(thd, "copy to tmp table");
710 DBUG_EXECUTE_IF("abort_copy_table", {
711 my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
712 goto err_new_table_cleanup;
717 + /* ignore the error */
718 + error= remove_secondary_keys(thd, new_table, alter_info);
721 error= copy_data_between_tables(table, new_table,
722 alter_info->create_list, ignore,
723 order_num, order, &copied, &deleted,
724 alter_info->keys_onoff,
725 alter_info->error_if_not_empty);
726 + if (!error && optimize_keys)
727 + error= restore_secondary_keys(thd, new_table, alter_info);