Merge lp:~sergei.glushchenko/percona-server/56-expand_fast_index_creation into lp:percona-server/5.6

[Sergei Glushchenko]

The port of expand fast index creation patch.

The only significant change is that online/inplace ALTER TABLE API was introduced in MySQL 5.6.

Drop and create keys on temporary table is performed as following.
Create Alter_info which describe table changes. As fields of the
table are not changed, populate clreate_list with field definitions
using field==orig_field. Create Alter_inplace_info describing
alter operations to be performed. Fill index_drop_buffer or
index_add_buffer respectively. Set ADD_INDEX/DROP_INDEX handler flag.
Check that operation could be performed online by calling
check_if_supported_inplace_alter. Perform alter operation by calling
ha_prepare_inplace_alter_table,ha_inplace_alter_table,
ha_commit_inplace_alter_table.

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/14/

[Sergei Glushchenko (sergei.glushchenko)]

I forgot to add testcases

[Sergei Glushchenko (sergei.glushchenko)]

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/15/

[Alexey Kopytov (akopytov)]

review: Approve

[Stewart Smith (stewart)]

http://jenkins.percona.com/job/merge-PS-5.6-staging/3/BUILD_TYPE=release,Host=ubuntu-lucid-64bit/testReport/junit/%28root%29/main/percona_expand_fast_index_creation/ (and others)

CURRENT_TEST: main.percona_expand_fast_index_creation
--- /mnt/workspace/merge-PS-5.6-staging/BUILD_TYPE/release/Host/ubuntu-lucid-64bit/Percona-Server/mysql-test/r/percona_expand_fast_index_creation.result 2013-01-25 03:39:48.000000000 +0300
+++ /mnt/workspace/merge-PS-5.6-staging/BUILD_TYPE/release/Host/ubuntu-lucid-64bit/Percona-Server/mysql-test/r/percona_expand_fast_index_creation.reject 2013-01-25 04:07:36.000000000 +0300
@@ -36,8 +36,12 @@
 k4 2 3 d 2
 PRIMARY 3 1 a 0
 SET profiling=1;
+Warnings:
+Warning 1287 '@@profiling' is deprecated and will be removed in a future release.
 ALTER TABLE t MODIFY COLUMN z VARCHAR(2);
 SET profiling=0;
+Warnings:
+Warning 1287 '@@profiling' is deprecated and will be removed in a future release.
 SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROFILING
 WHERE STATE = 'restoring secondary keys' AND
 QUERY_ID = (SELECT MAX(QUERY_ID) FROM INFORMATION_SCHEMA.PROFILING);

review: Needs Fixing

[Sergei Glushchenko (sergei.glushchenko)]

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/27/

* @@profiling, SHOW PROFILE and I_S.PROFILING were
deprecated in later MySQL 5.6 version. it led to
test failure
* solution is to use performance_schema.events_stages_long
* also instead of thd_proc_info, THD_STAGE_INFO has been
used
* and new event 'stage/sql/restoring secondary keys' has
been introduced

[Laurynas Biveinis (laurynas-biveinis)]

This has caused bug 1133926.

review: Needs Fixing

=== modified file 'Percona-Server/client/client_priv.h'
--- Percona-Server/client/client_priv.h	2012-12-04 08:24:59 +0000
+++ Percona-Server/client/client_priv.h	2013-01-25 11:48:25 +0000
@@ -100,6 +100,7 @@
   OPT_CONFIG_ALL,
   OPT_SERVER_PUBLIC_KEY,
   OPT_ENABLE_CLEARTEXT_PLUGIN,
+  OPT_INNODB_OPTIMIZE_KEYS,
   OPT_MAX_CLIENT_OPTION
 };
 

=== modified file 'Percona-Server/client/mysqldump.c'
--- Percona-Server/client/mysqldump.c	2012-10-16 06:21:51 +0000
+++ Percona-Server/client/mysqldump.c	2013-01-25 11:48:25 +0000
@@ -47,6 +47,7 @@
 #include <m_ctype.h>
 #include <hash.h>
 #include <stdarg.h>
+#include <my_list.h>
 
 #include "client_priv.h"
 #include "my_default.h"
@@ -84,6 +85,13 @@
 #define IGNORE_DATA 0x01 /* don't dump data for this table */
 #define IGNORE_INSERT_DELAYED 0x02 /* table doesn't support INSERT DELAYED */
 
+typedef enum {
+  KEY_TYPE_NONE,
+  KEY_TYPE_PRIMARY,
+  KEY_TYPE_UNIQUE,
+  KEY_TYPE_NON_UNIQUE
+} key_type_t;
+
 /* general_log or slow_log tables under mysql database */
 static inline my_bool general_log_or_slow_log_tables(const char *db, 
                                                      const char *table)
@@ -155,6 +163,8 @@
 static uint opt_protocol= 0;
 static char *opt_plugin_dir= 0, *opt_default_auth= 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
@@ -200,6 +210,8 @@
 
 HASH ignore_table;
 
+LIST *skipped_keys_list;
+
 static struct my_option my_long_options[] =
 {
   {"all-databases", 'A',
@@ -369,6 +381,11 @@
    "in dump produced with --dump-slave.", &opt_include_master_host_port,
    &opt_include_master_host_port, 0, GET_BOOL, NO_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},
@@ -2447,6 +2464,256 @@
 }
 
 /*
+  Parse the specified key definition string and check if the key contains an
+  AUTO_INCREMENT column as the first key part. We only check for the first key
+  part, because unlike MyISAM, InnoDB does not allow the AUTO_INCREMENT column
+  as a secondary key column, i.e. the AUTO_INCREMENT column would not be
+  considered indexed for such key specification.
+*/
+static my_bool contains_autoinc_column(const char *autoinc_column,
+                                       const char *keydef,
+                                       key_type_t type)
+{
+  char *from, *to;
+  uint idnum;
+
+  DBUG_ASSERT(type != KEY_TYPE_NONE);
+
+  if (autoinc_column == NULL || !(from= strchr(keydef, '`')))
+    return FALSE;
+
+  to= from;
+  idnum= 0;
+
+  while ((to= strchr(to + 1, '`')))
+  {
+    /*
+      Double backticks represent a backtick in identifier, rather than a quote
+      character.
+    */
+    if (to[1] == '`')
+    {
+      to++;
+      continue;
+    }
+
+    if (to <= from + 1)
+      break;                                    /* Broken key definition */
+
+    idnum++;
+
+    /*
+      Skip the check if it's the first identifier and we are processing a
+      secondary key.
+    */
+    if ((type == KEY_TYPE_PRIMARY || idnum != 1) &&
+        !strncmp(autoinc_column, from + 1, to - from - 1))
+      return TRUE;
+
+    /*
+      Check only the first (for PRIMARY KEY) or the second (for secondary keys)
+      quoted identifier.
+    */
+    if ((idnum == 1 + test(type != KEY_TYPE_PRIMARY)) ||
+        !(from= strchr(to + 1, '`')))
+      break;
+
+    to= from;
+  }
+
+  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;
+  my_bool pk_processed= FALSE;
+  char *autoinc_column= NULL;
+  my_bool has_autoinc= FALSE;
+  key_type_t type;
+
+  strend= create_str + strlen(create_str);
+
+  ptr= create_str;
+  while (*ptr)
+  {
+    char *tmp, *orig_ptr, c;
+
+    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 */
+
+    if (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1))
+      type= KEY_TYPE_UNIQUE;
+    else if (!strncmp(ptr, "KEY ", sizeof("KEY ") - 1))
+      type= KEY_TYPE_NON_UNIQUE;
+    else if (!strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
+      type= KEY_TYPE_PRIMARY;
+    else
+      type= KEY_TYPE_NONE;
+
+    has_autoinc= (type != KEY_TYPE_NONE) ?
+      contains_autoinc_column(autoinc_column, ptr, type) : FALSE;
+
+    /* Is it a secondary index definition? */
+    if (c == '\n' &&
+        ((type == KEY_TYPE_UNIQUE && (pk_processed || !has_pk)) ||
+         type == KEY_TYPE_NON_UNIQUE) && !has_autoinc)
+    {
+      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 we are skipping a key which indexes an AUTO_INCREMENT column, it is
+        safe to optimize all subsequent keys, i.e. we should not be checking for
+        that column anymore.
+      */
+      if (type != KEY_TYPE_NONE && has_autoinc)
+      {
+          DBUG_ASSERT(autoinc_column != NULL);
+
+          my_free(autoinc_column);
+          autoinc_column= NULL;
+      }
+
+      if ((has_pk && type == KEY_TYPE_UNIQUE && !pk_processed) ||
+          type == KEY_TYPE_PRIMARY)
+        pk_processed= TRUE;
+
+      if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
+      {
+        /*
+          The first secondary key defined on this column later cannot be
+          skipped, as CREATE TABLE would fail on import. Unless there is a
+          PRIMARY KEY and it indexes that column.
+        */
+        for (end= ptr + 1;
+             /* Skip double backticks as they are a part of identifier */
+             *end != '\0' && (*end != '`' || end[1] == '`');
+             end++)
+          /* empty */;
+
+        if (*end == '`' && end > ptr + 1)
+        {
+          DBUG_ASSERT(autoinc_column == NULL);
+
+          autoinc_column= my_strndup(ptr + 1, end - ptr - 1, MYF(MY_FAE));
+        }
+      }
+
+      *tmp= c;
+
+      if (tmp[-1] == ',')
+        last_comma= tmp - 1;
+      ptr= (*tmp == '\0') ? tmp : tmp + 1;
+    }
+  }
+
+  my_free(autoinc_column);
+}
+
+/*
+  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.
@@ -2484,6 +2751,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));
 
@@ -2525,6 +2793,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);
 
@@ -2712,6 +2983,9 @@
 
       row= mysql_fetch_row(result);
 
+      if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
+        skip_secondary_keys(row[1], has_pk);
+
       is_log_table= general_log_or_slow_log_tables(db, table);
       if (is_log_table)
         row[1]+= 13; /* strlen("CREATE TABLE ")= 13 */
@@ -3363,6 +3637,36 @@
 }
 
 
+
+/*
+  Perform delayed secondary index creation for --innodb-optimize-keys.
+*/
+
+static void restore_secondary_keys(char *table)
+{
+    if (skipped_keys_list)
+    {
+      uint keys;
+      skipped_keys_list= list_reverse(skipped_keys_list);
+      fprintf(md_result_file, "ALTER TABLE %s ", 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);
+        my_free(node);
+      }
+
+      DBUG_ASSERT(skipped_keys_list == NULL);
+    }
+}
+
+
+
 /*
 
  SYNOPSIS
@@ -3406,11 +3710,15 @@
   if (strcmp(table_type, "VIEW") == 0)
     DBUG_VOID_RETURN;
 
+  result_table= quote_name(table,table_buff, 1);
+  opt_quoted_table= quote_name(table, table_buff2, 0);
+
   /* Check --no-data flag */
   if (opt_no_data)
   {
     verbose_msg("-- Skipping dump data for table '%s', --no-data was used\n",
                 table);
+    restore_secondary_keys(opt_quoted_table);
     DBUG_VOID_RETURN;
   }
 
@@ -3447,9 +3755,6 @@
     DBUG_VOID_RETURN;
   }
 
-  result_table= quote_name(table,table_buff, 1);
-  opt_quoted_table= quote_name(table, table_buff2, 0);
-
   verbose_msg("-- Sending SELECT query...\n");
 
   init_dynamic_string_checked(&query_string, "", 1024, 1024);
@@ -3838,6 +4143,8 @@
       goto err;
     }
 
+    restore_secondary_keys(opt_quoted_table);
+
     /* Moved enable keys to before unlock per bug 15977 */
     if (opt_disable_keys)
     {

=== modified file 'Percona-Server/mysql-test/r/mysqld--help-notwin.result'
--- Percona-Server/mysql-test/r/mysqld--help-notwin.result	2012-12-04 08:24:59 +0000
+++ Percona-Server/mysql-test/r/mysqld--help-notwin.result	2013-01-25 11:48:25 +0000
@@ -191,6 +191,10 @@
  and DISABLED (keep the event scheduler completely
  deactivated, it cannot be activated run-time)
  -T, --exit-info[=#] Used for debugging. Use at your own risk.
+ --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
  --expire-logs-days=# 
  If non-zero, binary logs will be purged after
  expire_logs_days days; possible purges happen at startup
@@ -1023,6 +1027,7 @@
 end-markers-in-json FALSE
 eq-range-index-dive-limit 10
 event-scheduler OFF
+expand-fast-index-creation FALSE
 expire-logs-days 0
 explicit-defaults-for-timestamp FALSE
 external-locking FALSE

=== modified file 'Percona-Server/mysql-test/r/mysqld--help-win.result'
--- Percona-Server/mysql-test/r/mysqld--help-win.result	2012-12-04 08:24:59 +0000
+++ Percona-Server/mysql-test/r/mysqld--help-win.result	2013-01-25 11:48:25 +0000
@@ -191,6 +191,10 @@
  and DISABLED (keep the event scheduler completely
  deactivated, it cannot be activated run-time)
  -T, --exit-info[=#] Used for debugging. Use at your own risk.
+ --expand-fast-index-creation 
+ Enable/disable improvements to InnoDB fast index creation
+ functionality. Has no effect when fast index creation is
+ disabled with the fast-index-creation option
  --expire-logs-days=# 
  If non-zero, binary logs will be purged after
  expire_logs_days days; possible purges happen at startup
@@ -1020,6 +1024,7 @@
 end-markers-in-json FALSE
 eq-range-index-dive-limit 10
 event-scheduler OFF
+expand-fast-index-creation FALSE
 expire-logs-days 0
 explicit-defaults-for-timestamp FALSE
 external-locking FALSE

=== added file 'Percona-Server/mysql-test/r/percona_expand_fast_index_creation.result'
--- Percona-Server/mysql-test/r/percona_expand_fast_index_creation.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/r/percona_expand_fast_index_creation.result	2013-01-25 11:48:25 +0000
@@ -0,0 +1,78 @@
+SET expand_fast_index_creation=ON;
+CREATE TABLE idx (a INT PRIMARY KEY);
+CREATE TABLE t (a INT PRIMARY KEY AUTO_INCREMENT, b FLOAT, c VARCHAR(15), d VARCHAR(17), z VARCHAR(1)) ENGINE=InnoDB;
+INSERT INTO t (b) VALUES (RAND());
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+ALTER TABLE t ADD KEY k1(b), ADD KEY k2(a,b), ADD KEY k3(a,b,d), ADD UNIQUE KEY k4(a,c,d);
+INSERT INTO idx (a) SELECT i.INDEX_ID FROM
+INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID;
+SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM
+INFORMATION_SCHEMA.INNODB_SYS_FIELDS f,
+INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND
+f.INDEX_ID = i.INDEX_ID
+ORDER BY
+i.NAME, f.NAME;
+NAME	TYPE	N_FIELDS	NAME	POS
+k1	0	1	b	0
+k2	0	2	a	0
+k2	0	2	b	1
+k3	0	3	a	0
+k3	0	3	b	1
+k3	0	3	d	2
+k4	2	3	a	0
+k4	2	3	c	1
+k4	2	3	d	2
+PRIMARY	3	1	a	0
+TRUNCATE TABLE performance_schema.events_stages_history_long;
+ALTER TABLE t MODIFY COLUMN z VARCHAR(2);
+SELECT COUNT(*)
+FROM performance_schema.events_stages_history_long e,
+performance_schema.threads t
+WHERE
+e.EVENT_NAME = 'stage/sql/restoring secondary keys' AND
+e.THREAD_ID = t.THREAD_ID AND 
+t.PROCESSLIST_ID = CONNECTION_ID();
+COUNT(*)
+1
+INSERT INTO idx (a) SELECT i.INDEX_ID FROM
+INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID;
+SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM
+INFORMATION_SCHEMA.INNODB_SYS_FIELDS f,
+INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND
+f.INDEX_ID = i.INDEX_ID
+ORDER BY
+i.NAME, f.NAME;
+NAME	TYPE	N_FIELDS	NAME	POS
+k1	0	1	b	0
+k2	0	2	a	0
+k2	0	2	b	1
+k3	0	3	a	0
+k3	0	3	b	1
+k3	0	3	d	2
+k4	2	3	a	0
+k4	2	3	c	1
+k4	2	3	d	2
+PRIMARY	3	1	a	0
+SELECT COUNT(DISTINCT a) FROM idx;
+COUNT(DISTINCT a)
+10
+DROP TABLE idx;
+DROP TABLE t;

=== added file 'Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result'
--- Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result	2013-01-25 11:48:25 +0000
@@ -0,0 +1,443 @@
+#
+# 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;
+CREATE TABLE t1 (
+id INT NOT NULL AUTO_INCREMENT,
+uid INT NOT NULL,
+`id``` INT NOT NULL,
+```id` INT NOT NULL,
+# The following ones may be skipped and used in ALTER TABLE later
+KEY k1 (```id`, id),
+KEY k2 (```id`, `id```),
+# The following one should be kept in CREATE TABLE
+KEY k3 (id, uid),
+# The following one may be skipped again
+KEY k4 (id, `id```)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (id),
+KEY k1 (id),
+KEY k2 (id)
+) ENGINE=InnoDB;
+Warnings:
+Note	1831	Duplicate index 'k2' defined on the table 'test.t2'. This is deprecated and will be disallowed in a future release.
+######################################
+
+/*!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,
+  `uid` int(11) NOT NULL,
+  `id``` int(11) NOT NULL,
+  ```id` int(11) NOT NULL,
+  KEY `k3` (`id`,`uid`) 
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t1` WRITE;
+/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
+ALTER TABLE `t1` ADD KEY `k1` (```id`,`id`), ADD KEY `k2` (```id`,`id```), ADD KEY `k4` (`id`,`id```);
+/*!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,
+  PRIMARY KEY (`id`) 
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+LOCK TABLES `t2` WRITE;
+/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
+ALTER TABLE `t2` ADD KEY `k1` (`id`), ADD KEY `k2` (`id`);
+/*!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 'Percona-Server/mysql-test/suite/innodb/r/percona_bug_999147.result'
--- Percona-Server/mysql-test/suite/innodb/r/percona_bug_999147.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/r/percona_bug_999147.result	2013-01-25 11:48:25 +0000
@@ -0,0 +1,17 @@
+DROP TABLE IF EXISTS t1;
+SET SESSION expand_fast_index_creation=ON;
+CREATE TEMPORARY TABLE t1 (a INT, b INT, INDEX(a));
+SET debug="+d,crash_innodb_add_index_after";
+ALTER TABLE t1 ADD INDEX (b);
+ERROR HY000: Lost connection to MySQL server during query
+SELECT NAME, FLAG FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
+NAME	FLAG
+SYS_DATAFILES	0
+SYS_FOREIGN	0
+SYS_FOREIGN_COLS	0
+SYS_TABLESPACES	0
+mysql/innodb_index_stats	1
+mysql/innodb_table_stats	1
+mysql/slave_master_info	1
+mysql/slave_relay_log_info	1
+mysql/slave_worker_info	1

=== added file 'Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147-master.opt'
--- Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147-master.opt	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147-master.opt	2013-01-25 11:48:25 +0000
@@ -0,0 +1,1 @@
+--skip-stack-trace --skip-core-file --innodb-file-per-table=1

=== added file 'Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147.test'
--- Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/innodb/t/percona_bug_999147.test	2013-01-25 11:48:25 +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.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));
+
+--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
+
+SELECT NAME, FLAG FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

=== added file 'Percona-Server/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result'
--- Percona-Server/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result	2013-01-25 11:48:25 +0000
@@ -0,0 +1,6 @@
+SELECT @@global.expand_fast_index_creation;
+@@global.expand_fast_index_creation
+0
+SELECT @@local.expand_fast_index_creation;
+@@local.expand_fast_index_creation
+0

=== added file 'Percona-Server/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test'
--- Percona-Server/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test	2013-01-25 11:48:25 +0000
@@ -0,0 +1,2 @@
+SELECT @@global.expand_fast_index_creation;
+SELECT @@local.expand_fast_index_creation;

=== added file 'Percona-Server/mysql-test/t/percona_expand_fast_index_creation.test'
--- Percona-Server/mysql-test/t/percona_expand_fast_index_creation.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/t/percona_expand_fast_index_creation.test	2013-01-25 11:48:25 +0000
@@ -0,0 +1,77 @@
+######################################################################
+# Expand Fast Index Creation test
+# Here we perform some DDL and ensure that Expand Fast Index Creation
+# worked correctly
+######################################################################
+
+--source include/have_perfschema.inc
+
+SET expand_fast_index_creation=ON;
+CREATE TABLE idx (a INT PRIMARY KEY);
+CREATE TABLE t (a INT PRIMARY KEY AUTO_INCREMENT, b FLOAT, c VARCHAR(15), d VARCHAR(17), z VARCHAR(1)) ENGINE=InnoDB;
+INSERT INTO t (b) VALUES (RAND());
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+INSERT INTO t (b) SELECT RAND() FROM t;
+ALTER TABLE t ADD KEY k1(b), ADD KEY k2(a,b), ADD KEY k3(a,b,d), ADD UNIQUE KEY k4(a,c,d);
+
+# store InnoDB ID's of INDEXES for t
+INSERT INTO idx (a) SELECT i.INDEX_ID FROM
+	INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+	INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+	t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID;
+
+# confirm that there are two indexes
+SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM
+	INFORMATION_SCHEMA.INNODB_SYS_FIELDS f,
+	INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+	INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+	t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND
+	f.INDEX_ID = i.INDEX_ID
+ORDER BY
+	i.NAME, f.NAME;
+
+# truncate older stages
+TRUNCATE TABLE performance_schema.events_stages_history_long;
+
+# perform alter
+ALTER TABLE t MODIFY COLUMN z VARCHAR(2);
+
+# make sure expand fast index creation worked
+SELECT COUNT(*)
+	FROM performance_schema.events_stages_history_long e,
+	     performance_schema.threads t
+	WHERE
+	     e.EVENT_NAME = 'stage/sql/restoring secondary keys' AND
+	     e.THREAD_ID = t.THREAD_ID AND 
+	     t.PROCESSLIST_ID = CONNECTION_ID();
+
+# store new index ID's
+INSERT INTO idx (a) SELECT i.INDEX_ID FROM
+	INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+	INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+	t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID;
+
+# confirm that there are two indexes
+SELECT i.NAME, i.TYPE, i.N_FIELDS, f.NAME, f.POS FROM
+	INFORMATION_SCHEMA.INNODB_SYS_FIELDS f,
+	INFORMATION_SCHEMA.INNODB_SYS_INDEXES i,
+	INFORMATION_SCHEMA.INNODB_SYS_TABLES t
+WHERE
+	t.NAME = 'test/t' AND i.TABLE_ID = t.TABLE_ID AND
+	f.INDEX_ID = i.INDEX_ID
+ORDER BY
+	i.NAME, f.NAME;
+
+# confirm that there are four different ID's
+SELECT COUNT(DISTINCT a) FROM idx;
+
+DROP TABLE idx;
+DROP TABLE t;

=== added file 'Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test'
--- Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test	1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test	2013-01-25 11:48:25 +0000
@@ -0,0 +1,227 @@
+# 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.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;
+
+########################################################################
+# Bug #1039536: mysqldump --innodb-optimize-keys can generate invalid table
+#               definitions
+########################################################################
+
+CREATE TABLE t1 (
+       id INT NOT NULL AUTO_INCREMENT,
+       uid INT NOT NULL,
+       `id``` INT NOT NULL,
+       ```id` INT NOT NULL,
+       # The following ones may be skipped and used in ALTER TABLE later
+       KEY k1 (```id`, id),
+       KEY k2 (```id`, `id```),
+       # The following one should be kept in CREATE TABLE
+       KEY k3 (id, uid),
+       # The following one may be skipped again
+       KEY k4 (id, `id```)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+       id INT NOT NULL AUTO_INCREMENT,
+       PRIMARY KEY (id),
+       KEY k1 (id),
+       KEY k2 (id)
+) ENGINE=InnoDB;
+
+--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 'Percona-Server/sql/handler.cc'
--- Percona-Server/sql/handler.cc	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/handler.cc	2013-01-25 11:48:25 +0000
@@ -4256,12 +4256,14 @@
      The exception is if we're about to roll back changes (commit= false).
      In this case, we might be rolling back after a failed lock upgrade,
      so we could be holding the same lock level as for inplace_alter_table().
+     TABLE::mdl_ticket is 0 for temporary tables.
    */
-   DBUG_ASSERT(ha_thd()->mdl_context.is_lock_owner(MDL_key::TABLE,
+   DBUG_ASSERT((table->s->tmp_table != NO_TMP_TABLE && !table->mdl_ticket) ||
+               (ha_thd()->mdl_context.is_lock_owner(MDL_key::TABLE,
                                                    table->s->db.str,
                                                    table->s->table_name.str,
                                                    MDL_EXCLUSIVE) ||
-               !commit);
+               !commit));
 
    return commit_inplace_alter_table(altered_table, ha_alter_info, commit);
 }

=== modified file 'Percona-Server/sql/mysqld.cc'
--- Percona-Server/sql/mysqld.cc	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/mysqld.cc	2013-01-25 11:48:25 +0000
@@ -9440,6 +9440,7 @@
 PSI_stage_info stage_slave_waiting_worker_to_free_events= { 0, "Waiting for Slave Workers to free pending events", 0};
 PSI_stage_info stage_slave_waiting_worker_queue= { 0, "Waiting for Slave Worker queue", 0};
 PSI_stage_info stage_slave_waiting_event_from_coordinator= { 0, "Waiting for an event from Coordinator", 0};
+PSI_stage_info stage_restoring_secondary_keys= { 0, "restoring secondary keys", 0};
 
 #ifdef HAVE_PSI_INTERFACE
 
@@ -9537,7 +9538,8 @@
   & stage_waiting_for_the_next_event_in_relay_log,
   & stage_waiting_for_the_slave_thread_to_advance_position,
   & stage_waiting_to_finalize_termination,
-  & stage_waiting_to_get_readlock
+  & stage_waiting_to_get_readlock,
+  & stage_restoring_secondary_keys
 };
 
 PSI_socket_key key_socket_tcpip, key_socket_unix, key_socket_client_connection;

=== modified file 'Percona-Server/sql/mysqld.h'
--- Percona-Server/sql/mysqld.h	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/mysqld.h	2013-01-25 11:48:25 +0000
@@ -492,6 +492,7 @@
 extern PSI_stage_info stage_slave_waiting_worker_queue;
 extern PSI_stage_info stage_slave_waiting_event_from_coordinator;
 extern PSI_stage_info stage_slave_waiting_workers_to_exit;
+extern PSI_stage_info stage_restoring_secondary_keys;
 #ifdef HAVE_PSI_STATEMENT_INTERFACE
 /**
   Statement instrumentation keys (sql).

=== modified file 'Percona-Server/sql/sql_alter.cc'
--- Percona-Server/sql/sql_alter.cc	2012-10-16 06:21:51 +0000
+++ Percona-Server/sql/sql_alter.cc	2013-01-25 11:48:25 +0000
@@ -25,6 +25,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),
   partition_names(rhs.partition_names, mem_root),
@@ -45,6 +48,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 'Percona-Server/sql/sql_alter.h'
--- Percona-Server/sql/sql_alter.h	2012-10-16 06:21:51 +0000
+++ Percona-Server/sql/sql_alter.h	2013-01-25 11:48:25 +0000
@@ -171,6 +171,12 @@
   List<Key>                     key_list;
   // List of columns, used by both CREATE and ALTER TABLE.
   List<Create_field>            create_list;
+  // List of keys, which creation is delayed to benefit from fast index creation
+  List<Key>                     delayed_key_list;
+  // Keys, which creation is delayed to benefit from fast index creation
+  KEY                           *delayed_key_info;
+  // Count of keys, which creation is delayed to benefit from fast index creation
+  uint                          delayed_key_count;
   // Type of ALTER TABLE operation.
   uint                          flags;
   // Enable or disable keys.

=== modified file 'Percona-Server/sql/sql_class.h'
--- Percona-Server/sql/sql_class.h	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/sql_class.h	2013-01-25 11:48:25 +0000
@@ -546,6 +546,7 @@
   Gtid_specification gtid_next;
   Gtid_set_or_null gtid_next_list;
 
+  my_bool expand_fast_index_creation;
 } SV;
 
 

=== modified file 'Percona-Server/sql/sql_table.cc'
--- Percona-Server/sql/sql_table.cc	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/sql_table.cc	2013-01-25 11:48:25 +0000
@@ -3634,6 +3634,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++)
@@ -4043,6 +4051,22 @@
     // Check if a duplicate index is defined.
     check_duplicate_key(thd, key, key_info, &alter_info->key_list);
 
+    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++;
   }
 
@@ -6522,6 +6546,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);
@@ -6534,6 +6562,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");
 
@@ -6744,8 +6773,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;
@@ -6867,6 +6914,10 @@
                    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);
+      }
     }
   }
   {
@@ -6874,6 +6925,23 @@
     while ((key=key_it++))			// Add new keys
     {
       new_key_list.push_back(key);
+
+      if (key->type != Key::FOREIGN_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.str &&
 	  !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
       {
@@ -6941,6 +7009,7 @@
   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);
 }
@@ -7373,6 +7442,131 @@
 }
 
 
+/*
+  Temporarily remove secondary keys previously stored in
+  alter_info->delayed_key_info.
+*/
+static int
+remove_secondary_keys(THD *thd, HA_CREATE_INFO* create_info, TABLE *table,
+                      Alter_info *alter_info)
+{
+  uint i;
+  DBUG_ENTER("remove_secondary_keys");
+  DBUG_ASSERT(alter_info->delayed_key_count > 0);
+
+  /*
+    We need to mark all fields for read and write as being done in
+    mysql_alter_table.
+  */
+  table->use_all_columns();
+
+  /*
+    Create Alter_info for the table and fill create_list with fields
+    definitions. Not that fields not changed, so we set field==ogrig_field.
+  */
+  Alter_info alter_info_new;
+  Field **f_ptr, *field;
+
+  for (f_ptr= table->field; (field= *f_ptr); f_ptr++)
+  {
+    Create_field* new_field= new Create_field(field, field);
+    alter_info_new.create_list.push_back(new_field);
+  }
+
+  Alter_inplace_info ha_alter_info(create_info, &alter_info_new,
+                                     table->key_info, table->s->keys,
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+                                     thd->work_part_info,
+#else
+                                     NULL,
+#endif
+                                     FALSE);
+
+  ha_alter_info.handler_flags= Alter_inplace_info::DROP_INDEX;
+  ha_alter_info.index_drop_count= alter_info->delayed_key_count;
+
+  /* Fill index_drop_buffer with keys to drop */
+  ha_alter_info.index_drop_buffer= (KEY**) thd->alloc(sizeof(KEY*) *
+                                   alter_info->delayed_key_count);
+  for (i= 0; i < alter_info->delayed_key_count; i++)
+    ha_alter_info.index_drop_buffer[i] = &(alter_info->delayed_key_info[i]);
+
+  if (table->file->check_if_supported_inplace_alter(table, &ha_alter_info) ==
+      HA_ALTER_INPLACE_NOT_SUPPORTED)
+    DBUG_RETURN(-1);
+
+  if (table->file->ha_prepare_inplace_alter_table(table, &ha_alter_info) ||
+      table->file->ha_inplace_alter_table(table, &ha_alter_info) ||
+      table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, true))
+  {
+    table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, false);
+    DBUG_RETURN(-1);
+  }
+
+  DBUG_RETURN(0);
+}
+
+/*
+  Restore secondary keys previously removed in remove_secondary_keys.
+*/
+
+static int
+restore_secondary_keys(THD *thd, HA_CREATE_INFO* create_info, TABLE *table,
+                       Alter_info *alter_info)
+{
+  uint i;
+  DBUG_ENTER("restore_secondary_keys");
+  DBUG_ASSERT(alter_info->delayed_key_count > 0);
+
+  THD_STAGE_INFO(thd, stage_restoring_secondary_keys);
+
+  /*
+    Create Alter_info for the table and fill create_list with fields
+    definitions. Not that fields not changed, so we set field==ogrig_field.
+  */
+  Alter_info alter_info_new;
+  Field **f_ptr, *field;
+
+  for (f_ptr= table->field; (field= *f_ptr); f_ptr++)
+  {
+    Create_field* new_field= new Create_field(field, field);
+    alter_info_new.create_list.push_back(new_field);
+  }
+
+  Alter_inplace_info ha_alter_info(create_info, &alter_info_new,
+                        alter_info->delayed_key_info, table->s->keys,
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+                        thd->work_part_info,
+#else
+                        NULL,
+#endif
+                        FALSE);
+
+  ha_alter_info.handler_flags= Alter_inplace_info::ADD_INDEX;
+  ha_alter_info.index_add_count= alter_info->delayed_key_count;
+
+  ha_alter_info.index_add_buffer= (uint*) thd->alloc(sizeof(uint) *
+                                   alter_info->delayed_key_count);
+
+  /* Fill index_add_buffer with key indexes from key_info_buffer */
+  for (i= 0; i < alter_info->delayed_key_count; i++)
+    ha_alter_info.index_add_buffer[i] = i;
+
+  if (table->file->check_if_supported_inplace_alter(table, &ha_alter_info) ==
+      HA_ALTER_INPLACE_NOT_SUPPORTED)
+    DBUG_RETURN(-1);
+
+  if (table->file->ha_prepare_inplace_alter_table(table, &ha_alter_info) ||
+      table->file->ha_inplace_alter_table(table, &ha_alter_info) ||
+      table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, true))
+  {
+    table->file->ha_commit_inplace_alter_table(table, &ha_alter_info, false);
+    DBUG_RETURN(-1);
+  }
+
+  DBUG_RETURN(0);
+}
+
 /**
   Alter table
 
@@ -8067,18 +8261,39 @@
   */
   if (!(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");
     new_table->next_number_field=new_table->found_next_number_field;
     THD_STAGE_INFO(thd, stage_copy_to_tmp_table);
     DBUG_EXECUTE_IF("abort_copy_table", {
         my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
         goto err_new_table_cleanup;
       });
+
+    if (optimize_keys)
+    {
+      /* ignore the error */
+      remove_secondary_keys(thd, create_info, new_table, alter_info);
+    }
+
     if (copy_data_between_tables(table, new_table,
                                  alter_info->create_list, ignore,
                                  order_num, order, &copied, &deleted,
                                  alter_info->keys_onoff,
                                  &alter_ctx))
       goto err_new_table_cleanup;
+
+    if (optimize_keys)
+      if (restore_secondary_keys(thd, create_info, new_table, alter_info))
+        goto err_new_table_cleanup;
   }
   else
   {

=== modified file 'Percona-Server/sql/sys_vars.cc'
--- Percona-Server/sql/sys_vars.cc	2012-12-04 08:24:59 +0000
+++ Percona-Server/sql/sys_vars.cc	2013-01-25 11:48:25 +0000
@@ -1270,6 +1270,14 @@
        ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update));
 #endif
 
+static Sys_var_mybool Sys_expand_fast_index_creation(
+       "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",
+       SESSION_VAR(expand_fast_index_creation), CMD_LINE(OPT_ARG),
+       DEFAULT(FALSE));
+
 static Sys_var_ulong Sys_expire_logs_days(
        "expire_logs_days",
        "If non-zero, binary logs will be purged after expire_logs_days "

=== modified file 'Percona-Server/storage/innobase/handler/ha_innodb.cc'
--- Percona-Server/storage/innobase/handler/ha_innodb.cc	2012-12-17 22:22:38 +0000
+++ Percona-Server/storage/innobase/handler/ha_innodb.cc	2013-01-25 11:48:25 +0000
@@ -31,6 +31,8 @@
 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
 
 *****************************************************************************/
+ 
+#define MYSQL_SERVER
 
 #include <sql_table.h>	// explain_filename, nz2, EXPLAIN_PARTITIONS_AS_COMMENT,
 			// EXPLAIN_FILENAME_MAX_EXTRA_LENGTH
@@ -1245,6 +1247,18 @@
 	}
 }
 
+/******************************************************************//**
+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 */
+ibool
+thd_expand_fast_index_creation(
+/*================================*/
+	void*	thd)
+{
+	return((ibool) (((THD*) thd)->variables.expand_fast_index_creation));
+}
+
 /********************************************************************//**
 Obtain the InnoDB transaction of a MySQL thread.
 @return	reference to transaction pointer */

=== modified file 'Percona-Server/storage/innobase/handler/handler0alter.cc'
--- Percona-Server/storage/innobase/handler/handler0alter.cc	2012-12-04 08:24:59 +0000
+++ Percona-Server/storage/innobase/handler/handler0alter.cc	2013-01-25 11:48:25 +0000
@@ -2968,6 +2968,8 @@
 
 	ut_a(trx->lock.n_active_thrs == 0);
 
+	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 'Percona-Server/storage/innobase/include/ha_prototypes.h'
--- Percona-Server/storage/innobase/include/ha_prototypes.h	2012-10-16 06:21:51 +0000
+++ Percona-Server/storage/innobase/include/ha_prototypes.h	2013-01-25 11:48:25 +0000
@@ -533,6 +533,16 @@
 /*=================*/
 	int	error_code);	/*!< in: MySQL error code */
 
+/******************************************************************//**
+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*) */
+
 /*********************************************************************//**
 Compute the next autoinc value.