maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08349
Please review MDEV-7284 INDEX: CREATE OR REPLACE
Hi Sergei,
Please review a patch for MDEV-7284
Thanks.
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index f7aaedf..6db54a6 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -304,3 +304,32 @@ Log_name Pos Event_type Server_id End_log_pos Info
# # Gtid 1 # GTID #-#-#
# # Query 1 # use `test`; DROP TABLE `t1` /* generated by server */
RESET MASTER;
+CREATE TABLE t1(a INT, b INT);
+CREATE TABLE IF NOT EXISTS t1(a INT, b INT);
+Warnings:
+Note 1050 Table 't1' already exists
+CREATE OR REPLACE INDEX i1 ON t1(a);
+CREATE OR REPLACE INDEX i1 ON t1(a);
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+DROP TABLE t1;
+SHOW BINLOG EVENTS;
+Log_name Pos Event_type Server_id End_log_pos Info
+# # Format_desc 1 # VER
+# # Gtid_list 1 # []
+# # Binlog_checkpoint 1 # master-bin.000001
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE TABLE t1(a INT, b INT)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE TABLE IF NOT EXISTS t1(a INT, b INT)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE INDEX i1 ON t1(a)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE INDEX i1 ON t1(a)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE INDEX i1 ON t1(a,b)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE INDEX i1 ON t1(a,b)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TABLE `t1` /* generated by server */
+RESET MASTER;
diff --git a/mysql-test/r/create_drop_index.result b/mysql-test/r/create_drop_index.result
new file mode 100644
index 0000000..113c32a
--- /dev/null
+++ b/mysql-test/r/create_drop_index.result
@@ -0,0 +1,44 @@
+CREATE TABLE t1(a INT, b INT);
+CREATE INDEX IF NOT EXISTS i1 ON t1(a);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `i1` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP INDEX IF EXISTS i1 ON t1;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP INDEX IF EXISTS i1 ON t1;
+Warnings:
+Note 1091 Can't DROP 'i1'; check that column/key exists
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE OR REPLACE INDEX i1 ON t1(a);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `i1` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `i1` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE OR REPLACE INDEX IF NOT EXISTS i1 ON t1(b,a);
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_index.result b/mysql-test/suite/rpl/r/rpl_create_drop_index.result
new file mode 100644
index 0000000..1126c7e
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_index.result
@@ -0,0 +1,23 @@
+include/master-slave.inc
+[connection master]
+CREATE TABLE t1 (a INT, b INT);
+CREATE INDEX i1 ON t1 (a);
+CREATE OR REPLACE INDEX i1 ON t1 (a, b);
+# On slave:
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `i1` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# On master:
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL,
+ `b` int(11) DEFAULT NULL,
+ KEY `i1` (`a`,`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_index.test b/mysql-test/suite/rpl/t/rpl_create_drop_index.test
new file mode 100644
index 0000000..ee183ac
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_index.test
@@ -0,0 +1,19 @@
+--source include/master-slave.inc
+
+connection master;
+
+CREATE TABLE t1 (a INT, b INT);
+CREATE INDEX i1 ON t1 (a);
+CREATE OR REPLACE INDEX i1 ON t1 (a, b);
+sync_slave_with_master;
+
+--echo # On slave:
+SHOW CREATE TABLE t1;
+
+connection master;
+--echo # On master:
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+sync_slave_with_master;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 2a0f183..775c7be 100644
--- a/mysql-test/t/create_drop_binlog.test
+++ b/mysql-test/t/create_drop_binlog.test
@@ -147,3 +147,16 @@ DROP TABLE t1;
--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
SHOW BINLOG EVENTS;
RESET MASTER;
+
+
+CREATE TABLE t1(a INT, b INT);
+CREATE TABLE IF NOT EXISTS t1(a INT, b INT);
+CREATE OR REPLACE INDEX i1 ON t1(a);
+CREATE OR REPLACE INDEX i1 ON t1(a);
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+DROP TABLE t1;
+--replace_column 1 # 2 # 5 #
+--replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/ /Server.ver.*/VER/
+SHOW BINLOG EVENTS;
+RESET MASTER;
diff --git a/mysql-test/t/create_drop_index.test b/mysql-test/t/create_drop_index.test
new file mode 100644
index 0000000..4c86126
--- /dev/null
+++ b/mysql-test/t/create_drop_index.test
@@ -0,0 +1,16 @@
+CREATE TABLE t1(a INT, b INT);
+CREATE INDEX IF NOT EXISTS i1 ON t1(a);
+SHOW CREATE TABLE t1;
+DROP INDEX IF EXISTS i1 ON t1;
+SHOW CREATE TABLE t1;
+DROP INDEX IF EXISTS i1 ON t1;
+SHOW CREATE TABLE t1;
+
+CREATE OR REPLACE INDEX i1 ON t1(a);
+SHOW CREATE TABLE t1;
+CREATE OR REPLACE INDEX i1 ON t1(a,b);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE INDEX IF NOT EXISTS i1 ON t1(b,a);
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a0da5a9..98f8a8a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2866,7 +2866,8 @@ struct LEX: public Query_tables_list
bool add_create_index(Key::Keytype type, const LEX_STRING &name,
ha_key_alg algorithm, DDL_options_st ddl)
{
- if (!(last_key= new Key(type, name, algorithm, false, ddl)))
+ if (check_create_options(ddl) ||
+ !(last_key= new Key(type, name, algorithm, false, ddl)))
return true;
alter_info.key_list.push_back(last_key);
return false;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index c4b07ad..4550b10 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -5830,7 +5830,7 @@ static bool is_candidate_key(KEY *key)
const char *keyname;
while ((key=key_it++))
{
- if (!key->if_not_exists())
+ if (!key->if_not_exists() && !key->or_replace())
continue;
/* If the name of the key is not specified, */
/* let us check the name of the first key part. */
@@ -5891,17 +5891,32 @@ static bool is_candidate_key(KEY *key)
continue;
remove_key:
- push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
- ER_DUP_KEYNAME, ER(ER_DUP_KEYNAME), keyname);
- key_it.remove();
- if (key->type == Key::FOREIGN_KEY)
+ if (key->if_not_exists())
{
- /* ADD FOREIGN KEY appends two items. */
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_DUP_KEYNAME, ER(ER_DUP_KEYNAME), keyname);
key_it.remove();
+ if (key->type == Key::FOREIGN_KEY)
+ {
+ /* ADD FOREIGN KEY appends two items. */
+ key_it.remove();
+ }
+ if (alter_info->key_list.is_empty())
+ alter_info->flags&= ~(Alter_info::ALTER_ADD_INDEX |
+ Alter_info::ADD_FOREIGN_KEY);
+ }
+ else if (key->or_replace())
+ {
+ Alter_drop::drop_type type= (key->type == Key::FOREIGN_KEY) ?
+ Alter_drop::FOREIGN_KEY : Alter_drop::KEY;
+ Alter_drop *ad= new Alter_drop(type, key->name.str, FALSE);
+ if (ad != NULL)
+ {
+ // Adding the index into the drop list for replacing
+ alter_info->flags |= Alter_info::ALTER_DROP_INDEX;
+ alter_info->drop_list.push_back(ad);
+ }
}
- if (alter_info->key_list.is_empty())
- alter_info->flags&= ~(Alter_info::ALTER_ADD_INDEX |
- Alter_info::ADD_FOREIGN_KEY);
}
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1153ff8..8a39e62 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2487,31 +2487,33 @@ create:
}
create_table_set_open_action_and_adjust_tables(lex);
}
- | CREATE opt_unique INDEX_SYM opt_if_not_exists ident
+ | create_or_replace opt_unique INDEX_SYM opt_if_not_exists ident
opt_key_algorithm_clause
ON table_ident
{
if (add_create_index_prepare(Lex, $8))
MYSQL_YYABORT;
- if (Lex->add_create_index($2, $5, $6, $4))
+ if (Lex->add_create_index($2, $5, $6, $1 | $4))
MYSQL_YYABORT;
}
'(' key_list ')' normal_key_options
opt_index_lock_algorithm { }
- | CREATE fulltext INDEX_SYM opt_if_not_exists ident ON table_ident
+ | create_or_replace fulltext INDEX_SYM opt_if_not_exists ident
+ ON table_ident
{
if (add_create_index_prepare(Lex, $7))
MYSQL_YYABORT;
- if (Lex->add_create_index($2, $5, HA_KEY_ALG_UNDEF, $4))
+ if (Lex->add_create_index($2, $5, HA_KEY_ALG_UNDEF, $1 | $4))
MYSQL_YYABORT;
}
'(' key_list ')' fulltext_key_options
opt_index_lock_algorithm { }
- | CREATE spatial INDEX_SYM opt_if_not_exists ident ON table_ident
+ | create_or_replace spatial INDEX_SYM opt_if_not_exists ident
+ ON table_ident
{
if (add_create_index_prepare(Lex, $7))
MYSQL_YYABORT;
- if (Lex->add_create_index($2, $5, HA_KEY_ALG_UNDEF, $4))
+ if (Lex->add_create_index($2, $5, HA_KEY_ALG_UNDEF, $1 | $4))
MYSQL_YYABORT;
}
'(' key_list ')' spatial_key_options
Follow ups
