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

[Sergei Glushchenko]

Patch expand_fast_index_creation.patch
Fix for bug #999147.

[Alexey Kopytov (akopytov)]

review: Approve

Unmerged revisions

3733. By Sergei Glushchenko on 2012-06-29

port expand_fast_index_creation patch

=== modified file 'client/client_priv.h'
--- client/client_priv.h	2011-07-03 15:47:37 +0000
+++ client/client_priv.h	2012-07-03 16:14:26 +0000
@@ -94,5 +94,6 @@
   OPT_WRITE_BINLOG, OPT_DUMP_DATE,
   OPT_FIRST_SLAVE,
   OPT_ALL,
+  OPT_INNODB_OPTIMIZE_KEYS,
   OPT_MAX_CLIENT_OPTION
 };
 
=== modified file 'client/mysqldump.c'
--- client/mysqldump.c	2012-05-07 11:16:44 +0000
+++ client/mysqldump.c	2012-07-03 16:14:26 +0000
@@ -47,6 +47,7 @@
 #include <m_ctype.h>
 #include <hash.h>
 #include <stdarg.h>
+#include <my_list.h>
 
 #include "client_priv.h"
 #include "mysql.h"
@@ -148,6 +149,8 @@
 #endif
 static uint opt_protocol= 0;
 
+static my_bool opt_innodb_optimize_keys= FALSE;
+
 /*
 Dynamic_string wrapper functions. In this file use these
 wrappers, they will terminate the process if there is
@@ -193,6 +196,8 @@
 
 HASH ignore_table;
 
+LIST *skipped_keys_list;
+
 static struct my_option my_long_options[] =
 {
   {"all", OPT_ALL, "Deprecated. Use --create-options instead.",
@@ -339,6 +344,11 @@
    "be specified with both database and table names, e.g., "
    "--ignore-table=database.table.",
    0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+   {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS,
+    "Use InnoDB fast index creation by creating secondary indexes after "
+    "dumping the data.",
+    &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG,
+    0, 0, 0, 0, 0, 0},
   {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.",
    &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
    0, 0},
@@ -2433,6 +2443,190 @@
 }
 
 /*
+  Parse the specified key definition string and check if the key indexes
+  any of the columns from ignored_columns.
+*/
+static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
+{
+  char *leftp, *rightp;
+
+  if ((leftp = strchr(keydef, '(')) &&
+      (rightp = strchr(leftp, ')')) &&
+      rightp > leftp + 3 &&                      /* (`...`) */
+      leftp[1] == '`' &&
+      rightp[-1] == '`' &&
+      my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))
+    return TRUE;
+
+  return FALSE;
+}
+
+
+/*
+  Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string
+  and store them into a temporary list to be used later.
+
+  SYNOPSIS
+    skip_secondary_keys()
+    create_str                SHOW CREATE TABLE output
+    has_pk                    TRUE, if the table has PRIMARY KEY
+                              (or UNIQUE key on non-nullable columns)
+
+
+  DESCRIPTION
+
+    Stores all lines starting with "KEY" or "UNIQUE KEY"
+    into skipped_keys_list and removes them from the input string.
+    Ignoring FOREIGN KEYS constraints when creating the table is ok, because
+    mysqldump sets foreign_key_checks to 0 anyway.
+*/
+
+static void skip_secondary_keys(char *create_str, my_bool has_pk)
+{
+  char *ptr, *strend;
+  char *last_comma = NULL;
+  HASH ignored_columns;
+  my_bool pk_processed= FALSE;
+
+  if (hash_init(&ignored_columns, charset_info, 16, 0, 0,
+                (hash_get_key) get_table_key,
+                (hash_free_key) free_table_ent, 0))
+    exit(EX_EOM);
+
+  strend= create_str + strlen(create_str);
+
+  ptr= create_str;
+  while (*ptr)
+  {
+    char *tmp, *orig_ptr, c;
+    my_bool is_unique= FALSE;
+
+    orig_ptr= ptr;
+    /* Skip leading whitespace */
+    while (*ptr && my_isspace(charset_info, *ptr))
+      ptr++;
+
+    /* Read the next line */
+    for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++);
+
+    c= *tmp;
+    *tmp= '\0'; /* so strstr() only processes the current line */
+
+    /* Is it a secondary index definition? */
+    if (c == '\n' &&
+        (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
+          (pk_processed || !has_pk)) ||
+         !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) &&
+        !contains_ignored_column(&ignored_columns, ptr))
+    {
+      char *data, *end= tmp - 1;
+
+      /* Remove the trailing comma */
+      if (*end == ',')
+        end--;
+      data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE));
+      skipped_keys_list= list_cons(data, skipped_keys_list);
+
+      memmove(orig_ptr, tmp + 1, strend - tmp);
+      ptr= orig_ptr;
+      strend-= tmp + 1 - ptr;
+
+      /* Remove the comma on the previos line */
+      if (last_comma != NULL)
+      {
+        *last_comma= ' ';
+      }
+    }
+    else
+    {
+      char *end;
+
+      if (last_comma != NULL && *ptr != ')')
+      {
+        /*
+          It's not the last line of CREATE TABLE, so we have skipped a key
+          definition. We have to restore the last removed comma.
+        */
+        *last_comma= ',';
+      }
+
+      if ((has_pk && is_unique && !pk_processed) ||
+          !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
+        pk_processed= TRUE;
+
+      if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
+      {
+        /*
+          If a secondary key is defined on this column later,
+          it cannot be skipped, as CREATE TABLE would fail on import.
+        */
+        for (end= ptr + 1; *end != '`' && *end != '\0'; end++);
+        if (*end == '`' && end > ptr + 1 &&
+            my_hash_insert(&ignored_columns,
+                           (uchar *) my_strndup(ptr + 1,
+                                                end - ptr - 1, MYF(0))))
+        {
+          exit(EX_EOM);
+        }
+      }
+
+      *tmp= c;
+
+      if (tmp[-1] == ',')
+        last_comma= tmp - 1;
+      ptr= (*tmp == '\0') ? tmp : tmp + 1;
+    }
+  }
+
+  my_hash_free(&ignored_columns);
+}
+
+/*
+  Check if the table has a primary key defined either explicitly or
+  implicitly (i.e. a unique key on non-nullable columns).
+
+  SYNOPSIS
+    my_bool has_primary_key(const char *table_name)
+
+    table_name  quoted table name
+
+  RETURNS     TRUE if the table has a primary key
+
+  DESCRIPTION
+*/
+
+static my_bool has_primary_key(const char *table_name)
+{
+  MYSQL_RES  *res= NULL;
+  MYSQL_ROW  row;
+  char query_buff[QUERY_LENGTH];
+  my_bool has_pk= TRUE;
+
+  my_snprintf(query_buff, sizeof(query_buff),
+              "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE "
+              "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND "
+              "COLUMN_KEY='PRI'", table_name);
+  if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) ||
+      !(row= mysql_fetch_row(res)))
+  {
+    fprintf(stderr, "Warning: Couldn't determine if table %s has a "
+            "primary key (%s). "
+            "--innodb-optimize-keys may work inefficiently.\n",
+            table_name, mysql_error(mysql));
+    goto cleanup;
+  }
+
+  has_pk= atoi(row[0]) > 0;
+
+cleanup:
+  if (res)
+    mysql_free_result(res);
+
+  return has_pk;
+}
+
+
+/*
   get_table_structure -- retrievs database structure, prints out corresponding
   CREATE statement and fills out insert_pat if the table is the type we will
   be dumping.
@@ -2470,6 +2664,7 @@
   my_bool    is_log_table;
   MYSQL_RES  *result;
   MYSQL_ROW  row;
+  my_bool    has_pk= FALSE;
   DBUG_ENTER("get_table_structure");
   DBUG_PRINT("enter", ("db: %s  table: %s", db, table));
 
@@ -2511,6 +2706,9 @@
   result_table=     quote_name(table, table_buff, 1);
   opt_quoted_table= quote_name(table, table_buff2, 0);
 
+  if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
+    has_pk= has_primary_key(table);
+
   if (opt_order_by_primary)
     order_by= primary_key_fields(result_table);
 
@@ -2672,6 +2870,10 @@
       is_log_table= general_log_or_slow_log_tables(db, table);
       if (is_log_table)
         row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */
+
+      if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
+        skip_secondary_keys(row[1], has_pk);
+
       if (opt_compatible_mode & 3)
       {
         fprintf(sql_file,
@@ -3788,6 +3990,27 @@
       goto err;
     }
 
+    /* Perform delayed secondary index creation for --innodb-optimize-keys */
+    if (skipped_keys_list)
+    {
+      uint keys;
+      skipped_keys_list= list_reverse(skipped_keys_list);
+      fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table);
+      for (keys= list_length(skipped_keys_list); keys > 0; keys--)
+      {
+        LIST *node= skipped_keys_list;
+        char *def= node->data;
+
+        fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n");
+
+        skipped_keys_list= list_delete(skipped_keys_list, node);
+        my_free(def, MYF(0));
+        my_free(node, MYF(0));
+      }
+
+      DBUG_ASSERT(skipped_keys_list == NULL);
+    }
+
     /* Moved enable keys to before unlock per bug 15977 */
     if (opt_disable_keys)
     {
 
=== added file 'mysql-test/r/percona_innodb_expand_fast_index_creation.result'
--- mysql-test/r/percona_innodb_expand_fast_index_creation.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/percona_innodb_expand_fast_index_creation.result	2012-07-03 16:14:26 +0000
@@ -0,0 +1,67 @@
+SELECT @@expand_fast_index_creation;
+@@expand_fast_index_creation
+0
+CREATE TABLE t1(
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a CHAR(1) NOT NULL,
+b CHAR(36) NOT NULL) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES ('a','b');
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+ALTER TABLE t1 ADD KEY (a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
+id	1
+select_type	SIMPLE
+table	t1
+type	ALL
+possible_keys	a
+key	NULL
+key_len	NULL
+ref	NULL
+rows	16
+Extra	
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	a
+key	a
+key_len	1
+ref	test.t1.a
+rows	1
+Extra	Using where
+ALTER TABLE t1 DROP KEY a;
+SET expand_fast_index_creation = 1;
+SELECT @@expand_fast_index_creation;
+@@expand_fast_index_creation
+1
+ALTER TABLE t1 ADD KEY (a);
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
+id	1
+select_type	SIMPLE
+table	t1
+type	ALL
+possible_keys	a
+key	NULL
+key_len	NULL
+ref	NULL
+rows	16
+Extra	
+id	1
+select_type	SIMPLE
+table	t2
+type	ALL
+possible_keys	a
+key	NULL
+key_len	NULL
+ref	NULL
+rows	16
+Extra	Using where; Using join buffer
+SET expand_fast_index_creation = 0;
+DROP TABLE t1;
 
=== added file 'mysql-test/r/percona_mysqldump_innodb_optimize_keys.result'
--- mysql-test/r/percona_mysqldump_innodb_optimize_keys.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/percona_mysqldump_innodb_optimize_keys.result	2012-07-03 16:14:26 +0000
@@ -0,0 +1,367 @@
+#
+# Test the --innodb-optimize-keys option.
+#
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
+######################################
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+DROP TABLE IF EXISTS `t1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `b` (`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+######################################
+DROP TABLE t1;
+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (0), (1), (2);
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a INT, b VARCHAR(255), c DECIMAL(10,3),
+KEY (b),
+UNIQUE KEY uniq(c,a),
+FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
+) ENGINE=InnoDB;
+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
+######################################
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+DROP TABLE IF EXISTS `t1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `a` int(11) DEFAULT NULL,
+  `b` varchar(255) DEFAULT NULL,
+  `c` decimal(10,3) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+INSERT INTO `t1` VALUES (1,0,'0','0.000'),(2,1,'1','1.100'),(3,2,'2','2.200');
+ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`);
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t2` (
+  `a` int(11) NOT NULL,
+  PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t2` WRITE;
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+INSERT INTO `t2` VALUES (0),(1),(2);
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+######################################
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT,
+KEY (id)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INT NOT NULL AUTO_INCREMENT,
+UNIQUE KEY (id)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (), (), ();
+INSERT INTO t2 VALUES (), (), ();
+######################################
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+DROP TABLE IF EXISTS `t1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  KEY `id` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+INSERT INTO `t1` VALUES (1),(2),(3);
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t2` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  UNIQUE KEY `id` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t2` WRITE;
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+INSERT INTO `t2` VALUES (1),(2),(3);
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+######################################
+DROP TABLE t1, t2;
+CREATE TABLE t1 (
+a INT NOT NULL,
+UNIQUE KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t2 (
+a INT NOT NULL,
+b INT NOT NULL,
+UNIQUE KEY (a,b)) ENGINE=InnoDB;
+CREATE TABLE t3 (
+a INT,
+b INT,
+UNIQUE KEY (a,b)) ENGINE=InnoDB;
+CREATE TABLE t4 (
+a INT NOT NULL,
+b INT NOT NULL,
+PRIMARY KEY (a,b),
+UNIQUE KEY(b)) ENGINE=InnoDB;
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+TABLE_SCHEMA=DATABASE() AND
+TABLE_NAME='t1' AND
+COLUMN_KEY='PRI';
+COUNT(*)
+1
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+TABLE_SCHEMA=DATABASE() AND
+TABLE_NAME='t2' AND
+COLUMN_KEY='PRI';
+COUNT(*)
+2
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+TABLE_SCHEMA=DATABASE() AND
+TABLE_NAME='t3' AND
+COLUMN_KEY='PRI';
+COUNT(*)
+0
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+TABLE_SCHEMA=DATABASE() AND
+TABLE_NAME='t4' AND
+COLUMN_KEY='PRI';
+COUNT(*)
+2
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t4 SELECT * FROM t2;
+######################################
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+DROP TABLE IF EXISTS `t1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  UNIQUE KEY `a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+INSERT INTO `t1` VALUES (1),(2),(3);
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t2` (
+  `a` int(11) NOT NULL,
+  `b` int(11) NOT NULL,
+  UNIQUE KEY `a` (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t2` WRITE;
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t3`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t3` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL 
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t3` WRITE;
+/*!40000 ALTER TABLE `t3` DISABLE KEYS */;
+INSERT INTO `t3` VALUES (1,1),(2,2),(3,3);
+ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`);
+/*!40000 ALTER TABLE `t3` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t4`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t4` (
+  `a` int(11) NOT NULL,
+  `b` int(11) NOT NULL,
+  PRIMARY KEY (`a`,`b`) 
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t4` WRITE;
+/*!40000 ALTER TABLE `t4` DISABLE KEYS */;
+INSERT INTO `t4` VALUES (1,1),(2,2),(3,3);
+ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`);
+/*!40000 ALTER TABLE `t4` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+######################################
+DROP TABLE t1, t2, t3, t4;
+CREATE TABLE t1 (
+id INT NOT NULL PRIMARY KEY
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INT NOT NULL AUTO_INCREMENT,
+a INT NOT NULL,
+PRIMARY KEY (id),
+KEY (a),
+FOREIGN KEY (a) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
+######################################
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+DROP TABLE IF EXISTS `t1`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+INSERT INTO `t1` VALUES (1),(2),(3);
+/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
+UNLOCK TABLES;
+DROP TABLE IF EXISTS `t2`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t2` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `a` int(11) NOT NULL,
+  PRIMARY KEY (`id`),
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t2` WRITE;
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+INSERT INTO `t2` VALUES (1,1),(2,2),(3,3);
+ALTER TABLE `t2` ADD KEY `a` (`a`);
+/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+######################################
+DROP TABLE t1, t2;
 
=== added file 'mysql-test/suite/innodb_plugin/r/percona_bug_999147.result'
--- mysql-test/suite/innodb_plugin/r/percona_bug_999147.result	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/innodb_plugin/r/percona_bug_999147.result	2012-07-03 16:14:26 +0000
@@ -0,0 +1,8 @@
+DROP TABLE IF EXISTS t1;
+SET SESSION expand_fast_index_creation=ON;
+CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB;
+SET debug="+d,crash_innodb_add_index_after";
+ALTER TABLE t1 ADD INDEX (b);
+ERROR HY000: Lost connection to MySQL server during query
+SHOW TABLES;
+Tables_in_test
 
=== added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt'
--- mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/innodb_plugin/t/percona_bug_999147-master.opt	2012-07-03 16:14:26 +0000
@@ -0,0 +1,1 @@
+--skip-stack-trace --skip-core-file --innodb-file-per-table=1
 
=== added file 'mysql-test/suite/innodb_plugin/t/percona_bug_999147.test'
--- mysql-test/suite/innodb_plugin/t/percona_bug_999147.test	1970-01-01 00:00:00 +0000
+++ mysql-test/suite/innodb_plugin/t/percona_bug_999147.test	2012-07-03 16:14:26 +0000
@@ -0,0 +1,29 @@
+# Test for Percona Server bug 999147 (A crash that leaves behind an
+# InnoDB temporary table with indexes results in an unbootable server)
+# https://bugs.launchpad.net/percona-server/+bug/999147
+
+-- source include/not_embedded.inc
+-- source include/not_valgrind.inc
+-- source include/not_crashrep.inc
+-- source include/have_debug.inc
+-- source include/have_innodb_plugin.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+SET SESSION expand_fast_index_creation=ON;
+
+CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a)) ENGINE=InnoDB;
+
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+SET debug="+d,crash_innodb_add_index_after";
+--error 2013
+ALTER TABLE t1 ADD INDEX (b);
+
+--enable_reconnect
+
+--source include/wait_until_connected_again.inc
+
+SHOW TABLES;
 
=== added file 'mysql-test/t/percona_innodb_expand_fast_index_creation.test'
--- mysql-test/t/percona_innodb_expand_fast_index_creation.test	1970-01-01 00:00:00 +0000
+++ mysql-test/t/percona_innodb_expand_fast_index_creation.test	2012-07-03 16:14:26 +0000
@@ -0,0 +1,46 @@
+--source include/have_innodb_plugin.inc
+
+SELECT @@expand_fast_index_creation;
+
+########################################################################
+# Bug #857590: Fast index creation does not update index statistics
+########################################################################
+
+CREATE TABLE t1(
+       id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+       a CHAR(1) NOT NULL,
+       b CHAR(36) NOT NULL) ENGINE=InnoDB;
+
+INSERT INTO t1(a,b) VALUES ('a','b');
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+
+# Check that fast index creation is used
+--enable_info
+ALTER TABLE t1 ADD KEY (a);
+--disable_info
+
+# The default (wrong) plan due to bogus statistics
+--vertical_results
+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
+--horizontal_results
+
+ALTER TABLE t1 DROP KEY a;
+
+SET expand_fast_index_creation = 1;
+SELECT @@expand_fast_index_creation;
+
+# Check that stats are updated with the option enabled
+
+--enable_info
+ALTER TABLE t1 ADD KEY (a);
+--disable_info
+--vertical_results
+EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
+--horizontal_results
+
+SET expand_fast_index_creation = 0;
+
+DROP TABLE t1;
 
=== added file 'mysql-test/t/percona_mysqldump_innodb_optimize_keys.test'
--- mysql-test/t/percona_mysqldump_innodb_optimize_keys.test	1970-01-01 00:00:00 +0000
+++ mysql-test/t/percona_mysqldump_innodb_optimize_keys.test	2012-07-03 16:14:26 +0000
@@ -0,0 +1,187 @@
+# Embedded server doesn't support external clients
+--source include/not_embedded.inc
+
+# Fast index creation is only available in InnoDB plugin
+--source include/have_innodb_plugin.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+--echo #
+--echo # Test the --innodb-optimize-keys option.
+--echo #
+
+--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql
+
+# First test that the option has no effect on non-InnoDB tables
+
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM;
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file
+
+--echo ######################################
+--cat_file $file
+--echo ######################################
+
+--remove_file $file
+
+DROP TABLE t1;
+
+# Check that for InnoDB tables secondary keys are created after the data is
+# dumped but foreign ones are left in CREATE TABLE
+
+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (0), (1), (2);
+
+CREATE TABLE t1 (
+  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  a INT, b VARCHAR(255), c DECIMAL(10,3),
+  KEY (b),
+  UNIQUE KEY uniq(c,a),
+  FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE
+) ENGINE=InnoDB;
+
+INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2);
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
+
+--echo ######################################
+--cat_file $file
+--echo ######################################
+
+# Check that the resulting dump can be imported back
+
+--exec $MYSQL test < $file
+
+--remove_file $file
+
+DROP TABLE t1, t2;
+
+########################################################################
+# Bug #812179: AUTO_INCREMENT columns must be skipped by the
+#              --innodb-optimize-keys optimization in mysqldump
+########################################################################
+
+CREATE TABLE t1 (
+  id INT NOT NULL AUTO_INCREMENT,
+  KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+  id INT NOT NULL AUTO_INCREMENT,
+  UNIQUE KEY (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (), (), ();
+INSERT INTO t2 VALUES (), (), ();
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
+
+--echo ######################################
+--cat_file $file
+--echo ######################################
+
+# Check that the resulting dump can be imported back
+
+--exec $MYSQL test < $file
+
+--remove_file $file
+
+DROP TABLE t1, t2;
+
+########################################################################
+# Bug #851674: --innodb-optimize-keys does not work correctly with table
+#              without PRIMARY KEY
+########################################################################
+
+CREATE TABLE t1 (
+       a INT NOT NULL,
+       UNIQUE KEY (a)) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+       a INT NOT NULL,
+       b INT NOT NULL,
+       UNIQUE KEY (a,b)) ENGINE=InnoDB;
+
+CREATE TABLE t3 (
+       a INT,
+       b INT,
+       UNIQUE KEY (a,b)) ENGINE=InnoDB;
+
+CREATE TABLE t4 (
+       a INT NOT NULL,
+       b INT NOT NULL,
+       PRIMARY KEY (a,b),
+       UNIQUE KEY(b)) ENGINE=InnoDB;
+
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+       TABLE_SCHEMA=DATABASE() AND
+       TABLE_NAME='t1' AND
+       COLUMN_KEY='PRI';
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+       TABLE_SCHEMA=DATABASE() AND
+       TABLE_NAME='t2' AND
+       COLUMN_KEY='PRI';
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+       TABLE_SCHEMA=DATABASE() AND
+       TABLE_NAME='t3' AND
+       COLUMN_KEY='PRI';
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
+       TABLE_SCHEMA=DATABASE() AND
+       TABLE_NAME='t4' AND
+       COLUMN_KEY='PRI';
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+INSERT INTO t3 SELECT * FROM t2;
+INSERT INTO t4 SELECT * FROM t2;
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
+
+--echo ######################################
+--cat_file $file
+--echo ######################################
+
+# Check that the resulting dump can be imported back
+
+--exec $MYSQL test < $file
+
+--remove_file $file
+
+DROP TABLE t1, t2, t3, t4;
+
+########################################################################
+# Bug #859078: --innodb-optimize-keys should ignore foreign keys
+########################################################################
+
+CREATE TABLE t1 (
+       id INT NOT NULL PRIMARY KEY
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+       id INT NOT NULL AUTO_INCREMENT,
+       a INT NOT NULL,
+       PRIMARY KEY (id),
+       KEY (a),
+       FOREIGN KEY (a) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
+
+--echo ######################################
+--cat_file $file
+--echo ######################################
+
+# Check that the resulting dump can be imported back
+
+--exec $MYSQL test < $file
+
+--remove_file $file
+
+DROP TABLE t1, t2;
+
+# Wait till we reached the initial number of concurrent sessions
+--source include/wait_until_count_sessions.inc
 
=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc	2012-03-08 15:16:53 +0000
+++ sql/mysqld.cc	2012-07-03 16:14:26 +0000
@@ -5667,7 +5667,8 @@
   OPT_IGNORE_BUILTIN_INNODB,
   OPT_BINLOG_DIRECT_NON_TRANS_UPDATE,
   OPT_DEFAULT_CHARACTER_SET_OLD,
-  OPT_MAX_LONG_DATA_SIZE
+  OPT_MAX_LONG_DATA_SIZE,
+  OPT_EXPAND_FAST_INDEX_CREATION
 };
 
 
@@ -5896,6 +5897,13 @@
 each time the SQL thread starts.",
    &opt_init_slave, &opt_init_slave, 0, GET_STR_ALLOC,
    REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  {"expand-fast-index-creation", OPT_EXPAND_FAST_INDEX_CREATION,
+   "Enable/disable improvements to the InnoDB fast index creation functionality. "
+   "Has no effect when fast index creation is disabled with the "
+   "fast-index-creation option",
+   &global_system_variables.expand_fast_index_creation,
+   &max_system_variables.expand_fast_index_creation,
+   0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, NULL},
   {"language", 'L',
    "Client error messages in given language. May be given as a full path.",
    &language_ptr, &language_ptr, 0, GET_STR, REQUIRED_ARG,
 
=== modified file 'sql/set_var.cc'
--- sql/set_var.cc	2011-07-03 15:47:37 +0000
+++ sql/set_var.cc	2012-07-03 16:14:26 +0000
@@ -921,6 +921,10 @@
                                                      SHOW_LONGLONG,
                                                      get_myisam_mmap_size);
 
+static sys_var_thd_bool
+sys_expand_fast_index_creation(&vars,
+  "expand_fast_index_creation", &SV::expand_fast_index_creation);
+
 
 bool sys_var::check(THD *thd, set_var *var)
 {
 
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h	2012-04-20 16:41:20 +0000
+++ sql/sql_class.h	2012-07-03 16:14:26 +0000
@@ -404,6 +404,8 @@
   DATE_TIME_FORMAT *datetime_format;
   DATE_TIME_FORMAT *time_format;
   my_bool sysdate_is_now;
+
+  my_bool expand_fast_index_creation;
 };
 
 
 
=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc	2012-04-18 11:14:05 +0000
+++ sql/sql_lex.cc	2012-07-03 16:14:26 +0000
@@ -1498,6 +1498,9 @@
   alter_list(rhs.alter_list, mem_root),
   key_list(rhs.key_list, mem_root),
   create_list(rhs.create_list, mem_root),
+  delayed_key_list(rhs.delayed_key_list, mem_root),
+  delayed_key_info(rhs.delayed_key_info),
+  delayed_key_count(rhs.delayed_key_count),
   flags(rhs.flags),
   keys_onoff(rhs.keys_onoff),
   tablespace_op(rhs.tablespace_op),
@@ -1520,6 +1523,7 @@
   list_copy_and_replace_each_value(alter_list, mem_root);
   list_copy_and_replace_each_value(key_list, mem_root);
   list_copy_and_replace_each_value(create_list, mem_root);
+  list_copy_and_replace_each_value(delayed_key_list, mem_root);
   /* partition_names are not deeply copied currently */
 }
 
 
=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h	2012-04-18 11:14:05 +0000
+++ sql/sql_lex.h	2012-07-03 16:14:26 +0000
@@ -911,6 +911,9 @@
   List<Alter_column>            alter_list;
   List<Key>                     key_list;
   List<Create_field>            create_list;
+  List<Key>                     delayed_key_list;
+  KEY                           *delayed_key_info;
+  uint                          delayed_key_count;
   uint                          flags;
   enum enum_enable_or_disable   keys_onoff;
   enum tablespace_op_type       tablespace_op;
@@ -922,6 +925,8 @@
 
 
   Alter_info() :
+    delayed_key_info(NULL),
+    delayed_key_count(0),
     flags(0),
     keys_onoff(LEAVE_AS_IS),
     tablespace_op(NO_TABLESPACE_OP),
@@ -937,6 +942,9 @@
     alter_list.empty();
     key_list.empty();
     create_list.empty();
+    delayed_key_list.empty();
+    delayed_key_info= NULL;
+    delayed_key_count= 0;
     flags= 0;
     keys_onoff= LEAVE_AS_IS;
     tablespace_op= NO_TABLESPACE_OP;
 
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc	2012-02-15 16:21:38 +0000
+++ sql/sql_table.cc	2012-07-03 16:14:26 +0000
@@ -3016,6 +3016,14 @@
   if (!*key_info_buffer || ! key_part_info)
     DBUG_RETURN(TRUE);				// Out of memory
 
+  List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list);
+  alter_info->delayed_key_count= 0;
+  if (alter_info->delayed_key_list.elements > 0)
+  {
+    alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) *
+                                                     (*key_count));
+  }
+
   key_iterator.rewind();
   key_number=0;
   for (; (key=key_iterator++) ; key_number++)
@@ -3394,6 +3402,23 @@
       my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length);
       DBUG_RETURN(TRUE);
     }
+
+    if (alter_info->delayed_key_list.elements > 0)
+    {
+      Key *delayed_key;
+
+      delayed_key_iterator.rewind();
+      while ((delayed_key= delayed_key_iterator++))
+      {
+        if (delayed_key == key)
+        {
+         alter_info->delayed_key_info[alter_info->delayed_key_count++]=
+           *key_info;
+         break;
+        }
+      }
+    }
+
     key_info++;
   }
   if (!unique_key && !primary_key &&
@@ -6099,6 +6124,10 @@
   List<Create_field> new_create_list;
   /* New key definitions are added here */
   List<Key> new_key_list;
+  /* List with secondary keys which should be created after copying the data */
+  List<Key> delayed_key_list;
+  /* Foreign key list returned by handler::get_foreign_key_list() */
+  List<FOREIGN_KEY_INFO> f_key_list;
   List_iterator<Alter_drop> drop_it(alter_info->drop_list);
   List_iterator<Create_field> def_it(alter_info->create_list);
   List_iterator<Alter_column> alter_it(alter_info->alter_list);
@@ -6111,6 +6140,7 @@
   uint used_fields= create_info->used_fields;
   KEY *key_info=table->key_info;
   bool rc= TRUE;
+  bool skip_secondary;
 
   DBUG_ENTER("mysql_prepare_alter_table");
 
@@ -6305,8 +6335,26 @@
   /*
     Collect all keys which isn't in drop list. Add only those
     for which some fields exists.
+
+    We also store secondary keys in delayed_key_list to make use of
+    the InnoDB fast index creation. The following conditions must be
+    met:
+
+    - fast_index_creation is enabled for the current session
+    - expand_fast_index_creation is enabled for the current session;
+    - we are going to create an InnoDB table (this is checked later when the
+      target engine is known);
+    - the key most be a non-UNIQUE one;
+    - there are no foreign keys. This can be optimized later to exclude only
+      those keys which are a part of foreign key constraints. Currently we
+      simply disable this optimization for all keys if there are any foreign
+      key constraints in the table.
   */
 
+  skip_secondary= thd->variables.expand_fast_index_creation &&
+    !table->file->get_foreign_key_list(thd, &f_key_list) &&
+    f_key_list.elements == 0;
+
   for (uint i=0 ; i < table->s->keys ; i++,key_info++)
   {
     char *key_name= key_info->name;
@@ -6408,6 +6456,8 @@
                    test(key_info->flags & HA_GENERATED_KEY),
                    key_parts);
       new_key_list.push_back(key);
+      if (skip_secondary && key_type == Key::MULTIPLE)
+        delayed_key_list.push_back(key);
     }
   }
   {
@@ -6415,7 +6465,21 @@
     while ((key=key_it++))			// Add new keys
     {
       if (key->type != Key::FOREIGN_KEY)
+      {
         new_key_list.push_back(key);
+        if (skip_secondary && key->type == Key::MULTIPLE)
+          delayed_key_list.push_back(key);
+      }
+      else if (skip_secondary)
+      {
+        /*
+          We are adding a foreign key so disable the secondary keys
+          optimization.
+        */
+        skip_secondary= FALSE;
+        delayed_key_list.empty();
+      }
+
       if (key->name &&
 	  !my_strcasecmp(system_charset_info,key->name,primary_key_name))
       {
@@ -6464,12 +6528,100 @@
   rc= FALSE;
   alter_info->create_list.swap(new_create_list);
   alter_info->key_list.swap(new_key_list);
+  alter_info->delayed_key_list.swap(delayed_key_list);
 err:
   DBUG_RETURN(rc);
 }
 
 
 /*
+  Temporarily remove secondary keys previously stored in
+  alter_info->delayed_key_info.
+*/
+static int
+remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
+{
+  uint *key_numbers;
+  uint key_counter= 0;
+  uint i;
+  int error;
+  DBUG_ENTER("remove_secondary_keys");
+  DBUG_ASSERT(alter_info->delayed_key_count > 0);
+
+  key_numbers= (uint *) thd->alloc(sizeof(uint) *
+                                   alter_info->delayed_key_count);
+  for (i= 0; i < alter_info->delayed_key_count; i++)
+  {
+    KEY *key= alter_info->delayed_key_info + i;
+    uint j;
+
+    for (j= 0; j < table->s->keys; j++)
+    {
+      if (!strcmp(table->key_info[j].name, key->name))
+      {
+        key_numbers[key_counter++]= j;
+        break;
+      }
+    }
+  }
+
+  DBUG_ASSERT(key_counter == alter_info->delayed_key_count);
+
+  if ((error= table->file->prepare_drop_index(table, key_numbers,
+                                              key_counter)) ||
+      (error= table->file->final_drop_index(table)))
+  {
+    table->file->print_error(error, MYF(0));
+  }
+
+  DBUG_RETURN(error);
+}
+
+/*
+  Restore secondary keys previously removed in remove_secondary_keys.
+*/
+
+static int
+restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info)
+{
+  uint i;
+  int error;
+  DBUG_ENTER("restore_secondary_keys");
+  DBUG_ASSERT(alter_info->delayed_key_count > 0);
+
+  thd_proc_info(thd, "restoring secondary keys");
+
+  /* Fix the key parts */
+  for (i= 0; i < alter_info->delayed_key_count; i++)
+  {
+    KEY *key = alter_info->delayed_key_info + i;
+    KEY_PART_INFO *key_part;
+    KEY_PART_INFO *part_end;
+
+    part_end= key->key_part + key->key_parts;
+    for (key_part= key->key_part; key_part < part_end; key_part++)
+      key_part->field= table->field[key_part->fieldnr];
+  }
+
+  if ((error= table->file->add_index(table, alter_info->delayed_key_info,
+                                     alter_info->delayed_key_count)))
+  {
+    /*
+      Exchange the key_info for the error message. If we exchange
+      key number by key name in the message later, we need correct info.
+    */
+    KEY *save_key_info= table->key_info;
+    table->key_info= alter_info->delayed_key_info;
+    table->file->print_error(error, MYF(0));
+    table->key_info= save_key_info;
+
+    DBUG_RETURN(error);
+  }
+
+  DBUG_RETURN(0);
+}
+
+/*
   Alter table
 
   SYNOPSIS
@@ -7305,15 +7457,34 @@
   */
   if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
   {
+    /*
+      Check if we can temporarily remove secondary indexes from the table
+      before copying the data and recreate them later to utilize InnoDB fast
+      index creation.
+      TODO: is there a better way to check for InnoDB?
+    */
+    bool optimize_keys= (alter_info->delayed_key_count > 0) &&
+      !my_strcasecmp(system_charset_info,
+                     new_table->file->table_type(), "InnoDB");
     /* We don't want update TIMESTAMP fields during ALTER TABLE. */
     new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
     new_table->next_number_field=new_table->found_next_number_field;
+
     thd_proc_info(thd, "copy to tmp table");
+
+    if (optimize_keys)
+    {
+      /* ignore the error */
+      error= remove_secondary_keys(thd, new_table, alter_info);
+    }
+
     error= copy_data_between_tables(table, new_table,
                                     alter_info->create_list, ignore,
                                     order_num, order, &copied, &deleted,
                                     alter_info->keys_onoff,
                                     alter_info->error_if_not_empty);
+    if (!error && optimize_keys)
+      error= restore_secondary_keys(thd, new_table, alter_info);
   }
   else
   {
 
=== modified file 'storage/innodb_plugin/handler/ha_innodb.cc'
--- storage/innodb_plugin/handler/ha_innodb.cc	2012-05-08 05:19:14 +0000
+++ storage/innodb_plugin/handler/ha_innodb.cc	2012-07-03 16:14:26 +0000
@@ -43,6 +43,8 @@
 #pragma implementation				// gcc: Class implementation
 #endif
 
+#define MYSQL_SERVER
+
 #include <mysql_priv.h>
 
 #include <m_ctype.h>
@@ -682,6 +684,19 @@
 }
 
 /******************************************************************//**
+Returns true if expand_fast_index_creation is enabled for the current
+session.
+@return	the value of the server's expand_fast_index_creation variable */
+extern "C"
+ibool
+thd_expand_fast_index_creation(
+/*================================*/
+	void*	thd)
+{
+	return((ibool) (((THD*) thd)->variables.expand_fast_index_creation));
+}
+
+/******************************************************************//**
 Returns true if the thread supports XA,
 global value of innodb_supports_xa if thd is NULL.
 @return	true if thd has XA support */
 
=== modified file 'storage/innodb_plugin/handler/handler0alter.cc'
--- storage/innodb_plugin/handler/handler0alter.cc	2012-02-28 12:00:00 +0000
+++ storage/innodb_plugin/handler/handler0alter.cc	2012-07-03 16:14:26 +0000
@@ -813,6 +813,8 @@
 					innodb_table, indexed_table,
 					index, num_of_idx, table);
 
+	DBUG_EXECUTE_IF("crash_innodb_add_index_after", DBUG_SUICIDE(););
+
 error_handling:
 	/* After an error, remove all those index definitions from the
 	dictionary which were defined. */
 
=== modified file 'storage/innodb_plugin/include/ha_prototypes.h'
--- storage/innodb_plugin/include/ha_prototypes.h	2010-05-14 13:08:15 +0000
+++ storage/innodb_plugin/include/ha_prototypes.h	2012-07-03 16:14:26 +0000
@@ -268,4 +268,15 @@
 	void*	thd);	/*!< in: thread handle (THD*), or NULL to query
 			the global innodb_lock_wait_timeout */
 
+/******************************************************************//**
+Returns true if innodb_expand_fast_index_creation is enabled for the current
+session.
+@return	the value of the server's innodb_expand_fast_index_creation variable */
+
+ibool
+thd_expand_fast_index_creation(
+/*==================*/
+	void*	thd);	/*!< in: thread handle (THD*) */
+
+
 #endif
 
=== modified file 'storage/innodb_plugin/row/row0merge.c'
--- storage/innodb_plugin/row/row0merge.c	2012-02-10 03:09:12 +0000
+++ storage/innodb_plugin/row/row0merge.c	2012-07-03 16:14:26 +0000
@@ -56,6 +56,7 @@
 #include "log0log.h"
 #include "ut0sort.h"
 #include "handler0alter.h"
+#include "ha_prototypes.h"
 
 #ifdef UNIV_DEBUG
 /** Set these in order ot enable debug printout. */
@@ -2640,6 +2641,9 @@
 		}
 	}
 
+	if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd))
+		dict_update_statistics(new_table, FALSE);
+
 func_exit:
 	close(tmpfd);
 
 
=== modified file 'storage/innodb_plugin/row/row0mysql.c'
--- storage/innodb_plugin/row/row0mysql.c	2012-03-08 15:16:53 +0000
+++ storage/innodb_plugin/row/row0mysql.c	2012-07-03 16:14:26 +0000
@@ -3512,7 +3512,7 @@
 		btr_pcur_store_position(&pcur, &mtr);
 		btr_pcur_commit_specify_mtr(&pcur, &mtr);
 
-		table = dict_load_table(table_name);
+		table = dict_table_get_low(table_name);
 
 		if (table) {
 			row_drop_table_for_mysql(table_name, trx, FALSE);