=== modified file 'client/client_priv.h'
--- client/client_priv.h        2013-07-18 14:46:57 +0000
+++ client/client_priv.h        2013-10-28 21:56:47 +0000
@@ -92,6 +92,7 @@
   OPT_REPORT_PROGRESS,
   OPT_SKIP_ANNOTATE_ROWS_EVENTS,
   OPT_SSL_CRL, OPT_SSL_CRLPATH,
+  OPT_INNODB_OPTIMIZE_KEYS,
   OPT_MAX_CLIENT_OPTION /* should be always the last */
 };


=== modified file 'client/mysqldump.c'
--- client/mysqldump.c  2013-10-03 15:00:44 +0000
+++ client/mysqldump.c  2013-10-28 22:07:53 +0000
@@ -48,6 +48,7 @@
 #include <m_ctype.h>
 #include <hash.h>
 #include <stdarg.h>
+#include <my_list.h>

 #include "client_priv.h"
 #include "mysql.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;
+
 /* Chars needed to store LONGLONG, excluding trailing '\0'. */
 #define LONGLONG_LEN 20

@@ -147,6 +155,7 @@
 #endif
 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
@@ -193,6 +202,8 @@

 HASH ignore_table;

+LIST *skipped_keys_list;
+
 static struct my_option my_long_options[] =
 {
   {"all-databases", 'A',
@@ -356,6 +367,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},
@@ -2474,6 +2490,354 @@
 }

 /*
+  Find the first occurrence of a quoted identifier in a given string. Returns
+  the pointer to the opening quote, and stores the pointer to the closing quote
+  to the memory location pointed to by the 'end' argument,
+
+  If no quoted identifiers are found, returns NULL (and the value pointed to by
+  'end' is undefined in this case).
+*/
+
+static const char *parse_quoted_identifier(const char *str,
+                                          const char **end)
+{
+  const char *from;
+  const char *to;
+
+  if (!(from= strchr(str, '`')))
+    return NULL;
+
+  to= from;
+
+  while ((to= strchr(to + 1, '`'))) {
+    /*
+      Double backticks represent a backtick in identifier, rather than a quote
+      character.
+    */
+    if (to[1] == '`')
+      {
+       to++;
+       continue;
+      }
+
+    break;
+  }
+
+  if (to <= from + 1)
+    return NULL;                                /* Empty identifier */
+
+  *end= to;
+
+  return from;
+}
+
+/*
+  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)
+{
+  const char *from, *to;
+  uint idnum;
+
+  DBUG_ASSERT(type != KEY_TYPE_NONE);
+
+  if (autoinc_column == NULL)
+    return FALSE;
+
+  idnum= 0;
+
+  /*
+    There is only 1 iteration of the following loop for type == KEY_TYPE_PRIMARY
+    and 2 iterations for type == KEY_TYPE_UNIQUE / KEY_TYPE_NON_UNIQUE.
+  */
+  while ((from= parse_quoted_identifier(keydef, &to)))
+    {
+      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)))
+       break;
+
+      keydef= to + 1;
+    }
+
+  return FALSE;
+}
+
+/*
+  Find a node in the skipped keys list whose name matches a quoted
+  identifier specified as 'id_from' and 'id_to' arguments.
+*/
+
+static LIST *find_matching_skipped_key(const char *id_from,
+                                       const char *id_to)
+{
+  LIST *list;
+  size_t id_len;
+
+  id_len= id_to - id_from + 1;
+  DBUG_ASSERT(id_len > 2);
+
+  for (list= skipped_keys_list; list; list= list_rest(list))
+    {
+      const char *keydef;
+      const char *keyname_from;
+      const char *keyname_to;
+      size_t keyname_len;
+
+      keydef= list->data;
+
+      if ((keyname_from= parse_quoted_identifier(keydef, &keyname_to)))
+       {
+         keyname_len= keyname_to - keyname_from + 1;
+
+         if (id_len == keyname_len &&
+             !strncmp(keyname_from, id_from, id_len))
+           return list;
+       }
+    }
+
+  return NULL;
+}
+
+/*
+  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;
+  const char *constr_from;
+  const char *constr_to;
+  LIST *keydef_node;
+
+  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, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1) &&
+         (constr_from= parse_quoted_identifier(ptr, &constr_to)) &&
+         (keydef_node= find_matching_skipped_key(constr_from, constr_to)))
+       {
+         char *keydef;
+         size_t keydef_len;
+
+         /*
+        There's a skipped key with the same name as the constraint name.  Let's
+        put it back before the current constraint definition and remove from the
+        skipped keys list.
+         */
+         keydef= keydef_node->data;
+         keydef_len= strlen(keydef) + 5;           /* ", \n  " */
+
+         memmove(orig_ptr + keydef_len, orig_ptr, strend - orig_ptr + 1);
+         memcpy(ptr, keydef, keydef_len - 5);
+         memcpy(ptr + keydef_len - 5, ", \n  ", 5);
+
+         skipped_keys_list= list_delete(skipped_keys_list, keydef_node);
+         my_free(keydef);
+         my_free(keydef_node);
+
+         strend+= keydef_len;
+         orig_ptr+= keydef_len;
+         ptr+= keydef_len;
+         tmp+= keydef_len;
+
+         type= KEY_TYPE_NONE;
+       }
+      else 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.
@@ -2511,6 +2875,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));

@@ -2552,6 +2917,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);

@@ -2731,6 +3099,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 */
@@ -3375,6 +3746,36 @@
   return query;
 }

+/*
+  Dump delayed secondary index definitions when --innodb-optimize-keys is used.
+*/
+
+static void dump_skipped_keys(const char *table)
+{
+  uint keys;
+
+  if (!skipped_keys_list)
+    return;
+
+  verbose_msg("-- Dumping delayed secondary index definitions for table %s\n",
+              table);
+
+  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);
+}

 /*

@@ -3419,9 +3820,14 @@
   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)
   {
+    dump_skipped_keys(opt_quoted_table);
+
     verbose_msg("-- Skipping dump data for table '%s', --no-data was used\n",
                 table);
     DBUG_VOID_RETURN;
@@ -3851,6 +4257,8 @@
       goto err;
     }

+    dump_skipped_keys(opt_quoted_table);
+
     /* Moved enable keys to before unlock per bug 15977 */
     if (opt_disable_keys)
     {

=== modified file 'man/mysqldump.1'
--- man/mysqldump.1     2013-06-12 22:13:23 +0000
+++ man/mysqldump.1     2013-10-28 22:28:07 +0000
@@ -985,6 +985,25 @@
 .sp -1
 .IP \(bu 2.3
 .\}
+.\" mysqldump: innodb-optimize-keys option
+.\" innodb-optimize-keys option: mysqldump
+\fB\-\-innodb\-optimize\-keys\fR
+.sp
+Use InnoDB fast
+index creation by
+creating secondary indexes
+after
+dumping the data\&.
+.RE
+.sp
+.RS 4
+.ie n \{\
+\h'-04'\(bu\h'+03'\c
+.\}
+.el \{\
+.sp -1
+.IP \(bu 2.3
+.\}
 .\" mysqldump: insert-ignore option
 .\" insert-ignore option: mysqldump
 \fB\-\-insert\-ignore\fR

=== 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  2013-10-28 22:41:51 +0000
@@ -0,0 +1,526 @@
+#
+# 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;
+CREATE TABLE t1 (id INT PRIMARY KEY, c INT, KEY(c)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 1234);
+######################################
+
+/*!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,
+  `c` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+ALTER TABLE `t1` ADD KEY `c` (`c`);
+/*!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 `t1` (
+`id` int(11) NOT NULL AUTO_INCREMENT,
+`a` int(11) NOT NULL,
+PRIMARY KEY (`id`),
+KEY `a` (`a`),
+CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t1` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+######################################
+
+/*!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) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `a` (`a`),
+  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t1` (`id`)
+) ENGINE=InnoDB 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;

=== 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    2013-10-28 22:15:11 +0000
@@ -0,0 +1,275 @@
+# 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;
+
+############################################################################
+# Bug #989253: mysqldump --innodb-optimize-keys --no-data results in no keys
+############################################################################
+
+CREATE TABLE t1 (id INT PRIMARY KEY, c INT, KEY(c)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1, 1234);
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys --no-data test t1 >$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;
+
+#############################################################################
+# Bug #1081016: mysqldump --innodb-optimize-keys may produce invalid SQL with
+#               explicitly named FK constraints
+#############################################################################
+
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `a` int(11) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `a` (`a`),
+  CONSTRAINT `a` FOREIGN KEY (`a`) REFERENCES `t1` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$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;
+
+# Wait till we reached the initial number of concurrent sessions
+--source include/wait_until_count_sessions.inc