#include "client_priv.h"
#include "mysql.h"
-@@ -143,6 +144,8 @@
+@@ -145,6 +146,8 @@
static my_bool server_supports_sql_no_fcache= FALSE;
/*
Dynamic_string wrapper functions. In this file use these
wrappers, they will terminate the process if there is
-@@ -188,6 +191,8 @@
+@@ -190,6 +193,8 @@
HASH ignore_table;
static struct my_option my_long_options[] =
{
{"all-databases", 'A',
-@@ -351,6 +356,11 @@
+@@ -353,6 +358,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},
{"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},
-@@ -2238,6 +2248,77 @@
+@@ -2240,6 +2250,189 @@
}
/*
++ Parse the specified key definition string and check if the key indexes
++ any of the columns from ignored_columns.
++*/
++static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef)
++{
++ char *leftp, *rightp;
++
++ if ((leftp = strchr(keydef, '(')) &&
++ (rightp = strchr(leftp, ')')) &&
++ rightp > leftp + 3 && /* (`...`) */
++ leftp[1] == '`' &&
++ rightp[-1] == '`' &&
++ my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3))
++ return TRUE;
++
++ return FALSE;
++}
++
++
++/*
+ Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string
+ and store them into a temporary list to be used later.
+
+ SYNOPSIS
+ skip_secondary_keys()
+ create_str SHOW CREATE TABLE output
++ has_pk TRUE, if the table has PRIMARY KEY
++ (or UNIQUE key on non-nullable columns)
+
+
+ DESCRIPTION
+
-+ Stores all lines starting with "KEY" or "UNIQUE KEY" or "CONSTRAINT"
++ 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)
++static void skip_secondary_keys(char *create_str, my_bool has_pk)
+{
+ char *ptr, *strend;
+ char *last_comma = NULL;
++ HASH ignored_columns;
++ my_bool pk_processed= FALSE;
++
++ if (my_hash_init(&ignored_columns, charset_info, 16, 0, 0,
++ (my_hash_get_key) get_table_key, my_free, 0))
++ exit(EX_EOM);
+
+ strend= create_str + strlen(create_str);
+
+ ptr= create_str;
+ while (*ptr)
+ {
-+ char *tmp, *orig_ptr;
++ char *tmp, *orig_ptr, c;
++ my_bool is_unique= FALSE;
+
+ orig_ptr= ptr;
+ /* Skip leading whitespace */
+ /* Read the next line */
+ for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++);
+
++ c= *tmp;
++ *tmp= '\0'; /* so strstr() only processes the current line */
++
+ /* Is it a secondary index definition? */
-+ if (*tmp == '\n' &&
-+ (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1) ||
-+ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) ||
-+ !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1)))
++ if (c == '\n' &&
++ (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) &&
++ (pk_processed || !has_pk)) ||
++ !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) &&
++ !contains_ignored_column(&ignored_columns, ptr))
+ {
+ char *data, *end= tmp - 1;
+
+ if (last_comma != NULL)
+ {
+ *last_comma= ' ';
-+ last_comma = NULL;
+ }
+ }
+ else
+ {
++ char *end;
++
++ if (last_comma != NULL && *ptr != ')')
++ {
++ /*
++ It's not the last line of CREATE TABLE, so we have skipped a key
++ definition. We have to restore the last removed comma.
++ */
++ *last_comma= ',';
++ }
++
++ if ((has_pk && is_unique && !pk_processed) ||
++ !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1))
++ pk_processed= TRUE;
++
++ if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`')
++ {
++ /*
++ If a secondary key is defined on this column later,
++ it cannot be skipped, as CREATE TABLE would fail on import.
++ */
++ for (end= ptr + 1; *end != '`' && *end != '\0'; end++);
++ if (*end == '`' && end > ptr + 1 &&
++ my_hash_insert(&ignored_columns,
++ (uchar *) my_strndup(ptr + 1,
++ end - ptr - 1, MYF(0))))
++ {
++ exit(EX_EOM);
++ }
++ }
++
++ *tmp= c;
++
+ if (tmp[-1] == ',')
+ last_comma= tmp - 1;
+ ptr= (*tmp == '\0') ? tmp : tmp + 1;
+ }
+ }
++
++ my_hash_free(&ignored_columns);
+}
+
++/*
++ Check if the table has a primary key defined either explicitly or
++ implicitly (i.e. a unique key on non-nullable columns).
++
++ SYNOPSIS
++ my_bool has_primary_key(const char *table_name)
++
++ table_name quoted table name
++
++ RETURNS TRUE if the table has a primary key
++
++ DESCRIPTION
++*/
++
++static my_bool has_primary_key(const char *table_name)
++{
++ MYSQL_RES *res= NULL;
++ MYSQL_ROW row;
++ char query_buff[QUERY_LENGTH];
++ my_bool has_pk= TRUE;
++
++ my_snprintf(query_buff, sizeof(query_buff),
++ "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE "
++ "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND "
++ "COLUMN_KEY='PRI'", table_name);
++ if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) ||
++ !(row= mysql_fetch_row(res)))
++ {
++ fprintf(stderr, "Warning: Couldn't determine if table %s has a "
++ "primary key (%s). "
++ "--innodb-optimize-keys may work inefficiently.\n",
++ table_name, mysql_error(mysql));
++ goto cleanup;
++ }
++
++ has_pk= atoi(row[0]) > 0;
++
++cleanup:
++ if (res)
++ mysql_free_result(res);
++
++ return has_pk;
++}
++
++
+/*
get_table_structure -- retrievs database structure, prints out corresponding
CREATE statement and fills out insert_pat if the table is the type we will
be dumping.
-@@ -2478,6 +2559,9 @@
+@@ -2276,6 +2469,7 @@
+ int len;
+ 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));
+
+@@ -2317,6 +2511,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);
+
+@@ -2480,6 +2677,9 @@
row= mysql_fetch_row(result);
+ if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB"))
-+ skip_secondary_keys(row[1]);
++ skip_secondary_keys(row[1], has_pk);
+
fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" :
"/*!40101 SET @saved_cs_client = @@character_set_client */;\n"
"/*!40101 SET character_set_client = utf8 */;\n"
-@@ -3572,6 +3656,27 @@
+@@ -3574,6 +3774,27 @@
goto err;
}
{
--- /dev/null
+++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result
-@@ -0,0 +1,109 @@
+@@ -0,0 +1,367 @@
+#
+# Test the --innodb-optimize-keys option.
+#
+ `a` int(11) DEFAULT NULL,
+ `b` varchar(255) DEFAULT NULL,
+ `c` decimal(10,3) DEFAULT NULL,
-+ PRIMARY KEY (`id`)
++ 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`), ADD CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE;
++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`;
+
+######################################
+DROP TABLE t1, t2;
---- a/mysql-test/suite/innodb/r/innodb.result
-+++ b/mysql-test/suite/innodb/r/innodb.result
-@@ -1673,7 +1673,7 @@
- 71
- SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted';
- variable_value - @innodb_rows_inserted_orig
--1066
-+1108
- SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated';
- variable_value - @innodb_rows_updated_orig
- 866
---- a/mysql-test/suite/innodb/t/innodb-index.test
-+++ b/mysql-test/suite/innodb/t/innodb-index.test
-@@ -87,6 +87,11 @@
- show create table t1;
- --error ER_MULTIPLE_PRI_KEY
- alter table t1 add primary key (c);
-+# Suppress the error log messages occuring on duplicate key error
-+# during ALTER TABLE when using fast index creation
-+--disable_query_log
-+call mtr.add_suppression("Cannot find index PRIMARY in InnoDB index translation table.");
-+--enable_query_log
- --error ER_DUP_ENTRY
- alter table t1 drop primary key, add primary key (b);
- create unique index c on t1 (c);
---- a/mysql-test/suite/innodb/t/innodb.test
-+++ b/mysql-test/suite/innodb/t/innodb.test
-@@ -21,6 +21,12 @@
-
- -- source include/have_innodb.inc
-
-+# Suppress the error log message occuring on duplicate key error
-+# during ALTER TABLE when using fast index creation
-+--disable_query_log
-+call mtr.add_suppression("Cannot find index v_2 in InnoDB index translation table.");
-+--enable_query_log
-+
- let $MYSQLD_DATADIR= `select @@datadir`;
-
- # Save the original values of some variables in order to be able to
++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;
--- /dev/null
+++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test
-@@ -0,0 +1,62 @@
+@@ -0,0 +1,188 @@
+# Embedded server doesn't support external clients
+--source include/not_embedded.inc
+
+DROP TABLE t1;
+
+
-+# Check that for InnoDB tables secondary and foreign keys are created
-+# after the data is dumped
++# 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);
+
+DROP TABLE t1, t2;
+
++########################################################################
++# Bug #812179: AUTO_INCREMENT columns must be skipped by the
++# --innodb-optimize-keys optimization in mysqldump
++########################################################################
++
++CREATE TABLE t1 (
++ id INT NOT NULL AUTO_INCREMENT,
++ KEY (id)
++) ENGINE=InnoDB;
++
++CREATE TABLE t2 (
++ id INT NOT NULL AUTO_INCREMENT,
++ UNIQUE KEY (id)
++) ENGINE=InnoDB;
++
++INSERT INTO t1 VALUES (), (), ();
++INSERT INTO t2 VALUES (), (), ();
++
++--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
++
++--echo ######################################
++--cat_file $file
++--echo ######################################
++
++# Check that the resulting dump can be imported back
++
++--exec $MYSQL test < $file
++
++--remove_file $file
++
++DROP TABLE t1, t2;
++
++########################################################################
++# Bug #851674: --innodb-optimize-keys does not work correctly with table
++# without PRIMARY KEY
++########################################################################
++
++CREATE TABLE t1 (
++ a INT NOT NULL,
++ UNIQUE KEY (a)) ENGINE=InnoDB;
++
++CREATE TABLE t2 (
++ a INT NOT NULL,
++ b INT NOT NULL,
++ UNIQUE KEY (a,b)) ENGINE=InnoDB;
++
++CREATE TABLE t3 (
++ a INT,
++ b INT,
++ UNIQUE KEY (a,b)) ENGINE=InnoDB;
++
++CREATE TABLE t4 (
++ a INT NOT NULL,
++ b INT NOT NULL,
++ PRIMARY KEY (a,b),
++ UNIQUE KEY(b)) ENGINE=InnoDB;
++
++SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
++ TABLE_SCHEMA=DATABASE() AND
++ TABLE_NAME='t1' AND
++ COLUMN_KEY='PRI';
++SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
++ TABLE_SCHEMA=DATABASE() AND
++ TABLE_NAME='t2' AND
++ COLUMN_KEY='PRI';
++SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
++ TABLE_SCHEMA=DATABASE() AND
++ TABLE_NAME='t3' AND
++ COLUMN_KEY='PRI';
++SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE
++ TABLE_SCHEMA=DATABASE() AND
++ TABLE_NAME='t4' AND
++ COLUMN_KEY='PRI';
++
++INSERT INTO t1 VALUES (1), (2), (3);
++INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
++INSERT INTO t3 SELECT * FROM t2;
++INSERT INTO t4 SELECT * FROM t2;
++
++--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file
++
++--echo ######################################
++--cat_file $file
++--echo ######################################
++
++# Check that the resulting dump can be imported back
++
++--exec $MYSQL test < $file
++
++--remove_file $file
++
++DROP TABLE t1, t2, t3, t4;
++
++########################################################################
++# Bug #859078: --innodb-optimize-keys should ignore foreign keys
++########################################################################
++
++CREATE TABLE t1 (
++ id INT NOT NULL PRIMARY KEY
++) ENGINE=InnoDB;
++
++CREATE TABLE t2 (
++ id INT NOT NULL AUTO_INCREMENT,
++ a INT NOT NULL,
++ PRIMARY KEY (id),
++ KEY (a),
++ FOREIGN KEY (a) REFERENCES t2 (id)
++) ENGINE=InnoDB;
++
++INSERT INTO t1 VALUES (1), (2), (3);
++INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
++
++--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file
++
++--echo ######################################
++--cat_file $file
++--echo ######################################
++
++# Check that the resulting dump can be imported back
++
++--exec $MYSQL test < $file
++
++--remove_file $file
++
++DROP TABLE t1, t2;
++
+# Wait till we reached the initial number of concurrent sessions
+--source include/wait_until_count_sessions.inc
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
-@@ -1630,6 +1630,9 @@
+@@ -1632,6 +1632,9 @@
alter_list(rhs.alter_list, mem_root),
key_list(rhs.key_list, mem_root),
create_list(rhs.create_list, mem_root),
flags(rhs.flags),
keys_onoff(rhs.keys_onoff),
tablespace_op(rhs.tablespace_op),
-@@ -1652,6 +1655,7 @@
+@@ -1654,6 +1657,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);
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
-@@ -1009,6 +1009,9 @@
+@@ -1013,6 +1013,9 @@
List<Alter_column> alter_list;
List<Key> key_list;
List<Create_field> create_list;
uint flags;
enum enum_enable_or_disable keys_onoff;
enum tablespace_op_type tablespace_op;
-@@ -1020,6 +1023,8 @@
+@@ -1024,6 +1027,8 @@
Alter_info() :
flags(0),
keys_onoff(LEAVE_AS_IS),
tablespace_op(NO_TABLESPACE_OP),
-@@ -1035,6 +1040,9 @@
+@@ -1039,6 +1044,9 @@
alter_list.empty();
key_list.empty();
create_list.empty();
tablespace_op= NO_TABLESPACE_OP;
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
-@@ -2776,7 +2776,7 @@
- file The handler for the new table.
- key_info_buffer OUT An array of KEY structs for the indexes.
- key_count OUT The number of elements in the array.
-- select_field_count The number of fields coming from a select table.
-+ select_field_count The number of fields coming from a select table.
-
- DESCRIPTION
- Prepares the table and key structures for table creation.
-@@ -3122,7 +3122,6 @@
- }
-
- /* Create keys */
--
- List_iterator<Key> key_iterator(alter_info->key_list);
- List_iterator<Key> key_iterator2(alter_info->key_list);
- uint key_parts=0, fk_key_count=0;
-@@ -3220,6 +3219,14 @@
+@@ -3220,6 +3220,14 @@
if (!*key_info_buffer || ! key_part_info)
DBUG_RETURN(TRUE); // Out of memory
key_iterator.rewind();
key_number=0;
for (; (key=key_iterator++) ; key_number++)
-@@ -3638,6 +3645,22 @@
+@@ -3638,6 +3646,22 @@
key_info->comment.str= key->key_create_info.comment.str;
}
key_info++;
}
if (!unique_key && !primary_key &&
-@@ -5256,6 +5279,10 @@
+@@ -5256,6 +5280,10 @@
List<Create_field> new_create_list;
/* New key definitions are added here */
List<Key> new_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);
-@@ -5268,6 +5295,7 @@
+@@ -5268,6 +5296,7 @@
uint used_fields= create_info->used_fields;
KEY *key_info=table->key_info;
bool rc= TRUE;
DBUG_ENTER("mysql_prepare_alter_table");
-@@ -5457,7 +5485,23 @@
+@@ -5457,7 +5486,26 @@
/*
Collect all keys which isn't in drop list. Add only those
for which some fields exists.
+ 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;
+ key constraints in the table.
+ */
+
-+ skip_secondary=
++ skip_secondary= thd->variables.online_alter_index &&
++ 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++)
{
-@@ -5574,6 +5618,8 @@
+@@ -5574,6 +5622,8 @@
test(key_info->flags & HA_GENERATED_KEY),
key_parts);
new_key_list.push_back(key);
}
}
{
-@@ -5581,7 +5627,21 @@
+@@ -5581,7 +5631,21 @@
while ((key=key_it++)) // Add new keys
{
if (key->type != Key::FOREIGN_KEY)
if (key->name.str &&
!my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
{
-@@ -5630,12 +5690,104 @@
+@@ -5630,12 +5694,104 @@
rc= FALSE;
alter_info->create_list.swap(new_create_list);
alter_info->key_list.swap(new_key_list);
Alter table
SYNOPSIS
-@@ -6428,19 +6580,38 @@
+@@ -6428,19 +6584,38 @@
*/
if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER))
{
}
else
{
+--- /dev/null
++++ b/mysql-test/r/percona_innodb_expand_fast_index_creation.result
+@@ -0,0 +1,64 @@
++CREATE TABLE t1(
++id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
++a CHAR(1) NOT NULL,
++b CHAR(36) NOT NULL) ENGINE=InnoDB;
++INSERT INTO t1(a,b) VALUES ('a','b');
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++ALTER TABLE t1 ADD KEY (a);
++affected rows: 0
++info: Records: 0 Duplicates: 0 Warnings: 0
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++id 1
++select_type SIMPLE
++table t1
++type ALL
++possible_keys a
++key NULL
++key_len NULL
++ref NULL
++rows 16
++Extra
++id 1
++select_type SIMPLE
++table t2
++type ref
++possible_keys a
++key a
++key_len 1
++ref test.t1.a
++rows 1
++Extra Using where
++ALTER TABLE t1 DROP KEY a;
++SET expand_fast_index_creation = 1;
++SELECT @@expand_fast_index_creation;
++@@expand_fast_index_creation
++1
++ALTER TABLE t1 ADD KEY (a);
++affected rows: 0
++info: Records: 0 Duplicates: 0 Warnings: 0
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++id 1
++select_type SIMPLE
++table t1
++type ALL
++possible_keys a
++key NULL
++key_len NULL
++ref NULL
++rows 16
++Extra
++id 1
++select_type SIMPLE
++table t2
++type ALL
++possible_keys a
++key NULL
++key_len NULL
++ref NULL
++rows 16
++Extra Using where; Using join buffer
++SET expand_fast_index_creation = 0;
++DROP TABLE t1;
+--- /dev/null
++++ b/mysql-test/t/percona_innodb_expand_fast_index_creation.test
+@@ -0,0 +1,45 @@
++--source include/have_innodb.inc
++
++########################################################################
++# Bug #857590: Fast index creation does not update index statistics
++########################################################################
++
++CREATE TABLE t1(
++ id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
++ a CHAR(1) NOT NULL,
++ b CHAR(36) NOT NULL) ENGINE=InnoDB;
++
++INSERT INTO t1(a,b) VALUES ('a','b');
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++INSERT INTO t1(a,b) SELECT a,b FROM t1;
++
++# Check that fast index creation is used
++--enable_info
++ALTER TABLE t1 ADD KEY (a);
++--disable_info
++
++# The default (wrong) plan due to bogus statistics
++--vertical_results
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++--horizontal_results
++
++ALTER TABLE t1 DROP KEY a;
++
++SET expand_fast_index_creation = 1;
++SELECT @@expand_fast_index_creation;
++
++# Check that stats are updated with the option enabled
++
++--enable_info
++ALTER TABLE t1 ADD KEY (a);
++--disable_info
++
++--vertical_results
++EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b;
++--horizontal_results
++
++SET expand_fast_index_creation = 0;
++
++DROP TABLE t1;
+--- a/storage/innobase/row/row0merge.c
++++ b/storage/innobase/row/row0merge.c
+@@ -56,6 +56,7 @@
+ #include "log0log.h"
+ #include "ut0sort.h"
+ #include "handler0alter.h"
++#include "ha_prototypes.h"
+
+ /* Ignore posix_fadvise() on those platforms where it does not exist */
+ #if defined __WIN__
+@@ -2673,6 +2674,9 @@
+ }
+ }
+
++ if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd))
++ dict_update_statistics(new_table, FALSE, TRUE);
++
+ func_exit:
+ row_merge_file_destroy_low(tmpfd);
+
+--- a/sql/sql_class.h
++++ b/sql/sql_class.h
+@@ -556,6 +556,7 @@
+
+ double long_query_time_double;
+
++ my_bool expand_fast_index_creation;
+ } SV;
+
+
+--- a/sql/sys_vars.cc
++++ b/sql/sys_vars.cc
+@@ -710,6 +710,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 "
+--- a/storage/innobase/handler/ha_innodb.cc
++++ b/storage/innobase/handler/ha_innodb.cc
+@@ -999,6 +999,19 @@
+ return(THDVAR((THD*) thd, flush_log_at_trx_commit));
+ }
+
++/******************************************************************//**
++Returns true if expand_fast_index_creation is enabled for the current
++session.
++@return the value of the server's expand_fast_index_creation variable */
++extern "C" UNIV_INTERN
++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 */
+--- a/storage/innobase/include/ha_prototypes.h
++++ b/storage/innobase/include/ha_prototypes.h
+@@ -303,4 +303,15 @@
+ innobase_get_lower_case_table_names(void);
+ /*=====================================*/
+
++/******************************************************************//**
++Returns true if innodb_expand_fast_index_creation is enabled for the current
++session.
++@return the value of the server's innodb_expand_fast_index_creation variable */
++
++ibool
++thd_expand_fast_index_creation(
++/*==================*/
++ void* thd); /*!< in: thread handle (THD*) */
++
++
+ #endif
+--- /dev/null
++++ b/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result
+@@ -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
+--- /dev/null
++++ b/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test
+@@ -0,0 +1,2 @@
++SELECT @@global.expand_fast_index_creation;
++SELECT @@local.expand_fast_index_creation;
+--- a/mysql-test/r/mysqld--help-notwin.result
++++ b/mysql-test/r/mysqld--help-notwin.result
+@@ -140,6 +140,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
+@@ -821,6 +825,7 @@
+ div-precision-increment 4
+ engine-condition-pushdown TRUE
+ event-scheduler OFF
++expand-fast-index-creation FALSE
+ expire-logs-days 0
+ external-locking FALSE
+ flush FALSE
+--- a/mysql-test/r/mysqld--help-win.result
++++ b/mysql-test/r/mysqld--help-win.result
+@@ -140,6 +140,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
+@@ -775,6 +779,7 @@
+ div-precision-increment 4
+ engine-condition-pushdown TRUE
+ event-scheduler OFF
++expand-fast-index-creation FALSE
+ expire-logs-days 0
+ external-locking FALSE
+ flush FALSE