WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX

NDB cluster is not fully supported. This will be added with
WL 1892 (NDB Handler: Add support for CREATE/DROP INDEX).
Some preparatory code for this is already present though.
A change for the "duplicate key" error message is planned
for another changeset.


include/my_base.h:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  Defined a mask of flags which must be the same for two indexes
  if they should compare as compatible.
  Added an error number for a new drop index error message.
mysql-test/r/key.result:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  The test result.
mysql-test/t/key.test:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  The test case.
sql/handler.cc:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  Prepared for a later change in an error message:
  Replace index number by index name for "duplicate key" error.
  Added handling for the new drop index error message.
sql/handler.h:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  Added new flags and methods.
  Removed old flags and methods (from the last attempt).
sql/share/errmsg.txt:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  Added a new error message for drop index.
sql/sql_table.cc:
  WL#1563 - Modify MySQL to support fast CREATE/DROP INDEX
  Moved definitions to the top of the file.
  In mysql_prepare_table() allow an index to have the name
  "PRIMARY" if it has the key type "Key::PRIMARY".
  Added a parenthesis for readability.
  Removed old code from the last attempt.
  Some changes to compare_tables():
  - Input parameter "List<Key> *key_list" is replaced by
    "KEY *key_info_buffer, uint key_count".
  - Output parameters added: "index_drop_buffer/index_drop_count"
    and "index_add_buffer/index_add_count".
  - Key comparison must now find matching keys in changed
    old and new key lists.
  - Key comparison of a key is easier now because both old
    and new keys are of type 'KEY'.
  Call mysql_prepare_table() before compare_tables(). The
  translated KEY structs are needed at some places now.
  Inserted a code segment for checking alter_table_flags().
  Removed mysql_prepare_table() from the 'partition' branches
  (it is done above now).
  Removed a pair of unnecessary braces.
  Inserted a code segment for executing fast add/drop index.
  Made close of table dependent on whether it was opened.
  Prepared for NDB cluster support.
  Fixed commit to be called outside of LOCK_open.

1 files changed, 58 insertions, 0 deletions

diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 31763b84379..f95cee8e8eb 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -372,3 +372,61 @@ desc t1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX
+# To test if this really works, you need to run with --debug
+# and check the trace file.
+#
+# Create a table with named and unnamed indexes.
+create table t1 (
+    c1 int,
+    c2 char(12),
+    c3 varchar(123),
+    c4 timestamp,
+    index (c1),
+    index i1 (c1),
+    index i2 (c2),
+    index i3 (c3),
+    unique i4 (c4),
+    index i5 (c1, c2, c3, c4),
+    primary key (c2, c3),
+    index (c2, c4));
+show create table t1;
+# Some simple tests.
+alter table t1 drop index c1;
+alter table t1 add index (c1);
+# This creates index 'c1_2'.
+alter table t1 add index (c1);
+alter table t1 drop index i3;
+alter table t1 add index i3 (c3);
+# Two indexes at the same time.
+alter table t1 drop index i2, drop index i4;
+alter table t1 add index i2 (c2), add index i4 (c4);
+# Three indexes, one of them reversely.
+alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
+alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
+# include an unique index.
+alter table t1 drop index i2, drop index i4, add unique i4 (c4);
+alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
+# Modify an index by changing its definition.
+alter table t1 drop index c2, add index (c2(4),c3(7));
+# Change nothing. The new key definition is the same as the old one.
+alter table t1 drop index c2, add index (c2(4),c3(7));
+# Test primary key handling.
+alter table t1 add primary key (c1, c2), drop primary key;
+alter table t1 drop primary key;
+# Drop is checked first. Primary key must exist.
+--error 1091
+alter table t1 add primary key (c1, c2), drop primary key;
+show create table t1;
+# Insert non-unique values.
+insert into t1 values(1, 'a', 'a', NULL);
+insert into t1 values(1, 'b', 'b', NULL);
+# Drop some indexes for new adds.
+alter table t1 drop index i3, drop index i2, drop index i1;
+# Add indexes, one is unique on non-unique values.
+--error 1062
+alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
+drop table t1;
+