summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/alter_table.result40
-rw-r--r--mysql-test/r/fulltext.result11
-rw-r--r--mysql-test/r/partition.result22
-rw-r--r--mysql-test/t/alter_table.test27
-rw-r--r--mysql-test/t/fulltext.test11
-rw-r--r--mysql-test/t/partition.test28
-rw-r--r--sql/field.cc5
-rw-r--r--sql/field.h8
-rw-r--r--sql/partition_info.h1
-rw-r--r--sql/sp_head.cc2
-rw-r--r--sql/sql_class.cc3
-rw-r--r--sql/sql_class.h22
-rw-r--r--sql/sql_lex.cc1
-rw-r--r--sql/sql_lex.h3
-rw-r--r--sql/sql_parse.cc20
-rw-r--r--sql/sql_table.cc253
-rw-r--r--sql/sql_trigger.cc2
-rw-r--r--sql/sql_view.cc2
-rw-r--r--sql/sql_yacc.yy150
19 files changed, 523 insertions, 88 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 92b9d86365d..b6e99952c23 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -1340,3 +1340,43 @@ rename table t2 to t1;
execute stmt1;
deallocate prepare stmt1;
drop table t2;
+CREATE TABLE t1 (
+id INT(11) NOT NULL,
+x_param INT(11) DEFAULT NULL,
+PRIMARY KEY (id)
+);
+ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT,
+ADD COLUMN IF NOT EXISTS lol INT AFTER id;
+Warnings:
+Note 1060 Duplicate column name 'id'
+ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id;
+Warnings:
+Note 1060 Duplicate column name 'lol'
+ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
+ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
+Warnings:
+Note 1091 Can't DROP 'lol'; check that column/key exists
+ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
+ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
+Warnings:
+Note 1061 Duplicate key name 'x_param'
+ALTER TABLE t1 MODIFY IF EXISTS lol INT;
+Warnings:
+Note 1054 Unknown column 'lol' in 't1'
+DROP INDEX IF EXISTS x_param ON t1;
+DROP INDEX IF EXISTS x_param ON t1;
+Warnings:
+Note 1091 Can't DROP 'x_param'; check that column/key exists
+CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
+CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
+Warnings:
+Note 1061 Duplicate key name 'x_param1'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL,
+ `x_param` int(11) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ KEY `x_param1` (`x_param`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result
index c067ff02574..e6abd44c267 100644
--- a/mysql-test/r/fulltext.result
+++ b/mysql-test/r/fulltext.result
@@ -699,3 +699,14 @@ EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests
+CREATE TABLE t1 (
+id int(11) auto_increment,
+title varchar(100) default '',
+PRIMARY KEY (id),
+KEY ind5 (title)
+) ENGINE=MyISAM;
+CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title);
+CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title);
+Warnings:
+Note 1061 Duplicate key name 'ft1'
+DROP TABLE t1;
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 86425825601..c6a806bec80 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2493,3 +2493,25 @@ i
3
4
DROP TABLE t1;
+CREATE TABLE t1 ( d DATE NOT NULL)
+PARTITION BY RANGE( YEAR(d) ) (
+PARTITION p0 VALUES LESS THAN (1960),
+PARTITION p1 VALUES LESS THAN (1970),
+PARTITION p2 VALUES LESS THAN (1980),
+PARTITION p3 VALUES LESS THAN (1990)
+);
+ALTER TABLE t1 ADD PARTITION IF NOT EXISTS(
+PARTITION `p5` VALUES LESS THAN (2010)
+COMMENT 'APSTART \' APEND'
+);
+ALTER TABLE t1 ADD PARTITION IF NOT EXISTS(
+PARTITION `p5` VALUES LESS THAN (2010)
+COMMENT 'APSTART \' APEND'
+);
+Warnings:
+Note 1517 Duplicate partition name p5
+alter table t1 drop partition if exists p5;
+alter table t1 drop partition if exists p5;
+Warnings:
+Note 1507 Error in list of partitions to DROP
+DROP TABLE t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index eade7ba721e..d48b1687fa0 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1231,3 +1231,30 @@ execute stmt1;
deallocate prepare stmt1;
drop table t2;
+#
+# Test of ALTER TABLE IF [NOT] EXISTS
+#
+
+CREATE TABLE t1 (
+ id INT(11) NOT NULL,
+ x_param INT(11) DEFAULT NULL,
+ PRIMARY KEY (id)
+);
+
+ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT,
+ ADD COLUMN IF NOT EXISTS lol INT AFTER id;
+ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id;
+ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
+ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
+
+ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
+ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
+ALTER TABLE t1 MODIFY IF EXISTS lol INT;
+
+DROP INDEX IF EXISTS x_param ON t1;
+DROP INDEX IF EXISTS x_param ON t1;
+CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
+CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index b4b09413896..6e44b4c1578 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -646,3 +646,14 @@ DEALLOCATE PREPARE stmt;
DROP TABLE t1;
--echo End of 5.1 tests
+
+CREATE TABLE t1 (
+ id int(11) auto_increment,
+ title varchar(100) default '',
+ PRIMARY KEY (id),
+ KEY ind5 (title)
+) ENGINE=MyISAM;
+
+CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title);
+CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title);
+DROP TABLE t1;
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 038907702d5..bad59ff09c3 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -2494,3 +2494,31 @@ INSERT INTO t1 VALUES (1),(2),(2),(3),(4);
ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
SELECT * from t1 order by i;
DROP TABLE t1;
+
+#
+# Test ALTER TABLE ADD/DROP PARTITION IF EXISTS
+#
+
+CREATE TABLE t1 ( d DATE NOT NULL)
+PARTITION BY RANGE( YEAR(d) ) (
+ PARTITION p0 VALUES LESS THAN (1960),
+ PARTITION p1 VALUES LESS THAN (1970),
+ PARTITION p2 VALUES LESS THAN (1980),
+ PARTITION p3 VALUES LESS THAN (1990)
+);
+
+ALTER TABLE t1 ADD PARTITION IF NOT EXISTS(
+PARTITION `p5` VALUES LESS THAN (2010)
+COMMENT 'APSTART \' APEND'
+);
+
+ALTER TABLE t1 ADD PARTITION IF NOT EXISTS(
+PARTITION `p5` VALUES LESS THAN (2010)
+COMMENT 'APSTART \' APEND'
+);
+
+alter table t1 drop partition if exists p5;
+alter table t1 drop partition if exists p5;
+
+DROP TABLE t1;
+
diff --git a/sql/field.cc b/sql/field.cc
index 0a45c32d811..e2ec9d35707 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -8886,6 +8886,7 @@ void Create_field::init_for_tmp_table(enum_field_types sql_type_arg,
FLAGSTR(pack_flag, FIELDFLAG_DECIMAL),
f_packtype(pack_flag)));
vcol_info= 0;
+ create_if_not_exists= FALSE;
stored_in_db= TRUE;
DBUG_VOID_RETURN;
@@ -8923,7 +8924,7 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type,
char *fld_change, List<String> *fld_interval_list,
CHARSET_INFO *fld_charset, uint fld_geom_type,
Virtual_column_info *fld_vcol_info,
- engine_option_value *create_opt)
+ engine_option_value *create_opt, bool check_exists)
{
uint sign_len, allowed_type_modifier= 0;
ulong max_field_charlength= MAX_FIELD_CHARLENGTH;
@@ -8977,6 +8978,7 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type,
comment= *fld_comment;
vcol_info= fld_vcol_info;
+ create_if_not_exists= check_exists;
stored_in_db= TRUE;
/* Initialize data for a computed field */
@@ -9585,6 +9587,7 @@ Create_field::Create_field(Field *old_field,Field *orig_field)
comment= old_field->comment;
decimals= old_field->decimals();
vcol_info= old_field->vcol_info;
+ create_if_not_exists= FALSE;
stored_in_db= old_field->stored_in_db;
option_list= old_field->option_list;
option_struct= old_field->option_struct;
diff --git a/sql/field.h b/sql/field.h
index 6e880e78c6e..d03479bbd06 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -2385,8 +2385,9 @@ public:
uint8 interval_id; // For rea_create_table
uint offset,pack_flag;
+ bool create_if_not_exists; // Used in ALTER TABLE IF NOT EXISTS
- /*
+ /*
This is additinal data provided for any computed(virtual) field.
In particular it includes a pointer to the item by which this field
can be computed from other fields.
@@ -2399,7 +2400,8 @@ public:
*/
bool stored_in_db;
- Create_field() :after(0), option_list(NULL), option_struct(NULL)
+ Create_field() :after(0), option_list(NULL), option_struct(NULL),
+ create_if_not_exists(FALSE)
{}
Create_field(Field *field, Field *orig_field);
/* Used to make a clone of this object for ALTER/CREATE TABLE */
@@ -2417,7 +2419,7 @@ public:
Item *on_update_value, LEX_STRING *comment, char *change,
List<String> *interval_list, CHARSET_INFO *cs,
uint uint_geom_type, Virtual_column_info *vcol_info,
- engine_option_value *option_list);
+ engine_option_value *option_list, bool check_exists);
bool field_flags_are_binary()
{
diff --git a/sql/partition_info.h b/sql/partition_info.h
index e59d4ec8ba4..2fbdfff6636 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -306,6 +306,7 @@ private:
char *create_default_partition_names(uint part_no, uint num_parts,
uint start_no);
char *create_subpartition_name(uint subpart_no, const char *part_name);
+public:
bool has_unique_name(partition_element *element);
};
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index cb9c0325845..f0a87673857 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -2434,7 +2434,7 @@ sp_head::fill_field_definition(THD *thd, LEX *lex,
lex->charset ? lex->charset :
thd->variables.collation_database,
lex->uint_geom_type,
- lex->vcol_info, NULL))
+ lex->vcol_info, NULL, FALSE))
return TRUE;
if (field_def->interval_list.elements)
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index caf56f9f84c..d0f3f83d1d8 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -112,7 +112,8 @@ Key::Key(const Key &rhs, MEM_ROOT *mem_root)
columns(rhs.columns, mem_root),
name(rhs.name),
option_list(rhs.option_list),
- generated(rhs.generated)
+ generated(rhs.generated),
+ create_if_not_exists(rhs.create_if_not_exists)
{
list_copy_and_replace_each_value(columns, mem_root);
}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 6b779484bbc..9e550da4449 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -222,8 +222,9 @@ public:
enum drop_type {KEY, COLUMN };
const char *name;
enum drop_type type;
- Alter_drop(enum drop_type par_type,const char *par_name)
- :name(par_name), type(par_type) {}
+ bool drop_if_exists;
+ Alter_drop(enum drop_type par_type,const char *par_name, bool par_exists)
+ :name(par_name), type(par_type), drop_if_exists(par_exists) {}
/**
Used to make a clone of this object for ALTER/CREATE TABLE
@sa comment for Key_part_spec::clone
@@ -257,20 +258,23 @@ public:
LEX_STRING name;
engine_option_value *option_list;
bool generated;
+ bool create_if_not_exists;
Key(enum Keytype type_par, const LEX_STRING &name_arg,
KEY_CREATE_INFO *key_info_arg,
bool generated_arg, List<Key_part_spec> &cols,
- engine_option_value *create_opt)
+ engine_option_value *create_opt, bool if_not_exists_opt)
:type(type_par), key_create_info(*key_info_arg), columns(cols),
- name(name_arg), option_list(create_opt), generated(generated_arg)
+ name(name_arg), option_list(create_opt), generated(generated_arg),
+ create_if_not_exists(if_not_exists_opt)
{}
Key(enum Keytype type_par, const char *name_arg, size_t name_len_arg,
KEY_CREATE_INFO *key_info_arg, bool generated_arg,
List<Key_part_spec> &cols,
- engine_option_value *create_opt)
+ engine_option_value *create_opt, bool if_not_exists_opt)
:type(type_par), key_create_info(*key_info_arg), columns(cols),
- option_list(create_opt), generated(generated_arg)
+ option_list(create_opt), generated(generated_arg),
+ create_if_not_exists(if_not_exists_opt)
{
name.str= (char *)name_arg;
name.length= name_len_arg;
@@ -301,8 +305,10 @@ public:
uint delete_opt, update_opt, match_opt;
Foreign_key(const LEX_STRING &name_arg, List<Key_part_spec> &cols,
Table_ident *table, List<Key_part_spec> &ref_cols,
- uint delete_opt_arg, uint update_opt_arg, uint match_opt_arg)
- :Key(FOREIGN_KEY, name_arg, &default_key_create_info, 0, cols, NULL),
+ uint delete_opt_arg, uint update_opt_arg, uint match_opt_arg,
+ bool if_not_exists_opt)
+ :Key(FOREIGN_KEY, name_arg, &default_key_create_info, 0, cols, NULL,
+ if_not_exists_opt),
ref_table(table), ref_columns(ref_cols),
delete_opt(delete_opt_arg), update_opt(update_opt_arg),
match_opt(match_opt_arg)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index a56d9f4adc8..c1a138ec3ee 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -507,6 +507,7 @@ void lex_start(THD *thd)
lex->expr_allows_subselect= TRUE;
lex->use_only_table_context= FALSE;
lex->parse_vcol_expr= FALSE;
+ lex->check_exists= FALSE;
lex->verbose= 0;
lex->name.str= 0;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ec229144b36..77ee3dfc461 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2509,7 +2509,8 @@ struct LEX: public Query_tables_list
uint16 create_view_algorithm;
uint8 create_view_check;
uint8 context_analysis_only;
- bool drop_if_exists, drop_temporary, local_file, one_shot_set;
+ bool drop_temporary, local_file, one_shot_set;
+ bool check_exists;
bool autocommit;
bool verbose, no_write_to_binlog;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c68e20f1b3a..c6e46cbab80 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2084,7 +2084,7 @@ mysql_execute_command(THD *thd)
if (!(lex->sql_command == SQLCOM_UPDATE_MULTI) &&
!(lex->sql_command == SQLCOM_SET_OPTION) &&
!(lex->sql_command == SQLCOM_DROP_TABLE &&
- lex->drop_temporary && lex->drop_if_exists) &&
+ lex->drop_temporary && lex->check_exists) &&
all_tables_not_ok(thd, all_tables))
{
/* we warn the slave SQL thread */
@@ -3285,7 +3285,7 @@ end_with_restore_list:
thd->variables.option_bits|= OPTION_KEEP_LOG;
}
/* DDL and binlog write order are protected by metadata locks. */
- res= mysql_rm_table(thd, first_table, lex->drop_if_exists,
+ res= mysql_rm_table(thd, first_table, lex->check_exists,
lex->drop_temporary);
}
break;
@@ -3499,7 +3499,7 @@ end_with_restore_list:
#endif
if (check_access(thd, DROP_ACL, lex->name.str, NULL, NULL, 1, 0))
break;
- res= mysql_rm_db(thd, lex->name.str, lex->drop_if_exists, 0);
+ res= mysql_rm_db(thd, lex->name.str, lex->check_exists, 0);
break;
}
case SQLCOM_ALTER_DB_UPGRADE:
@@ -3627,7 +3627,7 @@ end_with_restore_list:
case SQLCOM_DROP_EVENT:
if (!(res= Events::drop_event(thd,
lex->spname->m_db, lex->spname->m_name,
- lex->drop_if_exists)))
+ lex->check_exists)))
my_ok(thd);
break;
#else
@@ -4314,7 +4314,7 @@ create_sp_error:
if (lex->spname->m_db.str == NULL)
{
- if (lex->drop_if_exists)
+ if (lex->check_exists)
{
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_SP_DOES_NOT_EXIST, ER(ER_SP_DOES_NOT_EXIST),
@@ -4383,7 +4383,7 @@ create_sp_error:
my_ok(thd);
break;
case SP_KEY_NOT_FOUND:
- if (lex->drop_if_exists)
+ if (lex->check_exists)
{
res= write_bin_log(thd, TRUE, thd->query(), thd->query_length());
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
@@ -4596,7 +4596,7 @@ create_sp_error:
if ((err_code= drop_server(thd, &lex->server_options)))
{
- if (! lex->drop_if_exists && err_code == ER_FOREIGN_SERVER_DOESNT_EXIST)
+ if (! lex->check_exists && err_code == ER_FOREIGN_SERVER_DOESNT_EXIST)
{
DBUG_PRINT("info", ("problem dropping server %s",
lex->server_options.server_name));
@@ -6016,7 +6016,7 @@ bool add_field_to_list(THD *thd, LEX_STRING *field_name, enum_field_types type,
lex->col_list.push_back(new Key_part_spec(*field_name, 0));
key= new Key(Key::PRIMARY, null_lex_str,
&default_key_create_info,
- 0, lex->col_list, NULL);
+ 0, lex->col_list, NULL, lex->check_exists);
lex->alter_info.key_list.push_back(key);
lex->col_list.empty();
}
@@ -6026,7 +6026,7 @@ bool add_field_to_list(THD *thd, LEX_STRING *field_name, enum_field_types type,
lex->col_list.push_back(new Key_part_spec(*field_name, 0));
key= new Key(Key::UNIQUE, null_lex_str,
&default_key_create_info, 0,
- lex->col_list, NULL);
+ lex->col_list, NULL, lex->check_exists);
lex->alter_info.key_list.push_back(key);
lex->col_list.empty();
}
@@ -6078,7 +6078,7 @@ bool add_field_to_list(THD *thd, LEX_STRING *field_name, enum_field_types type,
new_field->init(thd, field_name->str, type, length, decimals, type_modifier,
default_value, on_update_value, comment, change,
interval_list, cs, uint_geom_type, vcol_info,
- create_options))
+ create_options, lex->check_exists))
DBUG_RETURN(1);
lex->alter_info.create_list.push_back(new_field);
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 39c297fb5a2..9079bca7c1d 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4954,6 +4954,246 @@ is_index_maintenance_unique (TABLE *table, Alter_info *alter_info)
/*
+ Preparation for table creation
+
+ SYNOPSIS
+ handle_if_exists_option()
+ thd Thread object.
+ table The altered table.
+ alter_info List of columns and indexes to create
+
+ DESCRIPTION
+ Looks for the IF [NOT] EXISTS options, checks the states and remove items
+ from the list if existing found.
+
+ RETURN VALUES
+ NONE
+*/
+
+static void
+handle_if_exists_options(THD *thd, TABLE *table, Alter_info *alter_info)
+{
+ Field **f_ptr;
+ DBUG_ENTER("handle_if_exists_option");
+
+ /* Handle ADD COLUMN IF NOT EXISTS. */
+ {
+ List_iterator<Create_field> it(alter_info->create_list);
+ Create_field *sql_field;
+
+ while ((sql_field=it++))
+ {
+ if (!sql_field->create_if_not_exists || sql_field->change)
+ continue;
+ /*
+ If there is a field with the same name in the table already,
+ remove the sql_field from the list.
+ */
+ for (f_ptr=table->field; *f_ptr; f_ptr++)
+ {
+ if (my_strcasecmp(system_charset_info,
+ sql_field->field_name, (*f_ptr)->field_name) == 0)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_DUP_FIELDNAME, ER(ER_DUP_FIELDNAME),
+ sql_field->field_name);
+ it.remove();
+ if (alter_info->create_list.is_empty())
+ {
+ alter_info->flags&= ~ALTER_ADD_COLUMN;
+ if (alter_info->key_list.is_empty())
+ alter_info->flags&= ~ALTER_ADD_INDEX;
+ }
+ break;
+ }
+ }
+ }
+ }
+
+ /* Handle MODIFY COLUMN IF EXISTS. */
+ {
+ List_iterator<Create_field> it(alter_info->create_list);
+ Create_field *sql_field;
+
+ while ((sql_field=it++))
+ {
+ if (!sql_field->create_if_not_exists || !sql_field->change)
+ continue;
+ /*
+ If there is NO field with the same name in the table already,
+ remove the sql_field from the list.
+ */
+ for (f_ptr=table->field; *f_ptr; f_ptr++)
+ {
+ if (my_strcasecmp(system_charset_info,
+ sql_field->field_name, (*f_ptr)->field_name) == 0)
+ {
+ break;
+ }
+ }
+ if (*f_ptr == NULL)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_BAD_FIELD_ERROR, ER(ER_BAD_FIELD_ERROR),
+ sql_field->change, table->s->table_name.str);
+ it.remove();
+ if (alter_info->create_list.is_empty())
+ {
+ alter_info->flags&= ~(ALTER_ADD_COLUMN | ALTER_CHANGE_COLUMN);
+ if (alter_info->key_list.is_empty())
+ alter_info->flags&= ~ALTER_ADD_INDEX;
+ }
+ }
+ }
+ }
+
+ /* Handle DROP COLUMN/KEY IF EXISTS. */
+ {
+ List_iterator<Alter_drop> drop_it(alter_info->drop_list);
+ Alter_drop *drop;
+ bool remove_drop;
+ while ((drop= drop_it++))
+ {
+ if (!drop->drop_if_exists)
+ continue;
+ remove_drop= TRUE;
+ if (drop->type == Alter_drop::COLUMN)
+ {
+ /*
+ If there is NO field with that name in the table,
+ remove the 'drop' from the list.
+ */
+ for (f_ptr=table->field; *f_ptr; f_ptr++)
+ {
+ if (my_strcasecmp(system_charset_info,
+ drop->name, (*f_ptr)->field_name) == 0)
+ {
+ remove_drop= FALSE;
+ break;
+ }
+ }
+ }
+ else /* Alter_drop::KEY */
+ {
+ uint n_key;
+ for (n_key=0; n_key < table->s->keys; n_key++)
+ {
+ if (my_strcasecmp(system_charset_info,
+ drop->name, table->key_info[n_key].name) == 0)
+ {
+ remove_drop= FALSE;
+ break;
+ }
+ }
+ }
+ if (remove_drop)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_CANT_DROP_FIELD_OR_KEY, ER(ER_CANT_DROP_FIELD_OR_KEY),
+ drop->name);
+ drop_it.remove();
+ if (alter_info->drop_list.is_empty())
+ alter_info->flags&= ~(ALTER_DROP_COLUMN | ALTER_DROP_INDEX);
+ }
+ }
+ }
+
+ /* ALTER TABLE ADD KEY IF NOT EXISTS */
+ /* ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS */
+ {
+ Key *key;
+ List_iterator<Key> key_it(alter_info->key_list);
+ uint n_key;
+ while ((key=key_it++))
+ {
+ if (!key->create_if_not_exists)
+ continue;
+ for (n_key=0; n_key < table->s->keys; n_key++)
+ {
+ if (my_strcasecmp(system_charset_info,
+ key->name.str, table->key_info[n_key].name) == 0)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_DUP_KEYNAME, ER(ER_DUP_KEYNAME), key->name.str);
+ 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_ADD_INDEX;
+ break;
+ }
+ }
+ }
+ }
+
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+ partition_info *tab_part_info= table->part_info;
+ if (tab_part_info && thd->lex->check_exists)
+ {
+ /* ALTER TABLE ADD PARTITION IF NOT EXISTS */
+ if (alter_info->flags & ALTER_ADD_PARTITION)
+ {
+ partition_info *alt_part_info= thd->lex->part_info;
+ if (alt_part_info)
+ {
+ List_iterator<partition_element> new_part_it(alt_part_info->partitions);
+ partition_element *pe;
+ while ((pe= new_part_it++))
+ {
+ if (!tab_part_info->has_unique_name(pe))
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_SAME_NAME_PARTITION, ER(ER_SAME_NAME_PARTITION),
+ pe->partition_name);
+ alter_info->flags&= ~ALTER_ADD_PARTITION;
+ thd->lex->part_info= NULL;
+ break;
+ }
+ }
+ }
+ }
+ /* ALTER TABLE DROP PARTITION IF EXISTS */
+ if (alter_info->flags & ALTER_DROP_PARTITION)
+ {
+ List_iterator<char> names_it(alter_info->partition_names);
+ char *name;
+
+ while ((name= names_it++))
+ {
+ List_iterator<partition_element> part_it(tab_part_info->partitions);
+ partition_element *part_elem;
+ while ((part_elem= part_it++))
+ {
+ if (my_strcasecmp(system_charset_info,
+ part_elem->partition_name, name) == 0)
+ break;
+ }
+ if (!part_elem)
+ {
+ push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_DROP_PARTITION_NON_EXISTENT,
+ ER(ER_DROP_PARTITION_NON_EXISTENT), "DROP");
+ names_it.remove();
+ }
+ }
+ if (alter_info->partition_names.elements == 0)
+ alter_info->flags&= ~ALTER_DROP_PARTITION;
+ }
+ }
+#endif /*WITH_PARTITION_STORAGE_ENGINE*/
+
+ /* Clear the ALTER_FOREIGN_KEY flag if nothing other than that set. */
+ if (alter_info->flags == ALTER_FOREIGN_KEY)
+ alter_info->flags= 0;
+
+ DBUG_VOID_RETURN;
+}
+
+
+/*
SYNOPSIS
mysql_compare_tables()
table The original table.
@@ -5873,7 +6113,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
key= new Key(key_type, key_name, strlen(key_name),
&key_create_info,
test(key_info->flags & HA_GENERATED_KEY),
- key_parts, key_info->option_list);
+ key_parts, key_info->option_list, FALSE);
new_key_list.push_back(key);
}
}
@@ -6386,6 +6626,17 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
DBUG_RETURN(error);
}
+ handle_if_exists_options(thd, table, alter_info);
+
+ /* Look if we have to do anything at all. */
+ /* Normally ALTER can become NOOP only after handling */
+ /* the IF (NOT) EXISTS options. */
+ if (alter_info->flags == 0)
+ {
+ copied= deleted= 0;
+ goto end_temporary;
+ }
+
/* We have to do full alter table. */
#ifdef WITH_PARTITION_STORAGE_ENGINE
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 882f7fcca8c..35a4464b9e2 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -443,7 +443,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
if (!create)
{
- bool if_exists= thd->lex->drop_if_exists;
+ bool if_exists= thd->lex->check_exists;
/*
Protect the query table list from the temporary and potentially
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 0a84dd996ce..37f6af13bb7 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1672,7 +1672,7 @@ bool mysql_drop_view(THD *thd, TABLE_LIST *views, enum_drop_mode drop_mode)
{
char name[FN_REFLEN];
my_snprintf(name, sizeof(name), "%s.%s", view->db, view->table_name);
- if (thd->lex->drop_if_exists)
+ if (thd->lex->check_exists)
{
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
ER_BAD_TABLE_ERROR, ER(ER_BAD_TABLE_ERROR),
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 0c3a016fa28..20a2d72ef26 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -722,7 +722,7 @@ static bool add_create_index (LEX *lex, Key::Keytype type,
{
Key *key;
key= new Key(type, name, info ? info : &lex->key_create_info, generated,
- lex->col_list, lex->option_list);
+ lex->col_list, lex->option_list, lex->check_exists);
if (key == NULL)
return TRUE;
@@ -1454,7 +1454,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
NCHAR_STRING opt_component key_cache_name
sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty
- opt_constraint constraint opt_ident
+ opt_constraint constraint opt_ident opt_if_not_exists_ident
%type <lex_str_ptr>
opt_table_alias
@@ -1471,7 +1471,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%type <num>
type type_with_opt_collate int_type real_type order_dir lock_option
- udf_type if_exists opt_local opt_table_options table_options
+ udf_type opt_if_exists opt_local opt_table_options table_options
table_option opt_if_not_exists opt_no_write_to_binlog
opt_temporary all_or_any opt_distinct
opt_ignore_leaves fulltext_options spatial_type union_option
@@ -2131,36 +2131,36 @@ create:
}
create_table_set_open_action_and_adjust_tables(lex);
}
- | CREATE opt_unique INDEX_SYM ident key_alg ON table_ident
+ | CREATE opt_unique INDEX_SYM opt_if_not_exists ident key_alg ON table_ident
{
- if (add_create_index_prepare(Lex, $7))
+ if (add_create_index_prepare(Lex, $8))
MYSQL_YYABORT;
}
'(' key_list ')' normal_key_options
{
- if (add_create_index(Lex, $2, $4))
+ if (add_create_index(Lex, $2, $5))
MYSQL_YYABORT;
}
- | CREATE fulltext INDEX_SYM ident init_key_options ON
+ | CREATE fulltext INDEX_SYM opt_if_not_exists ident init_key_options ON
table_ident
{
- if (add_create_index_prepare(Lex, $7))
+ if (add_create_index_prepare(Lex, $8))
MYSQL_YYABORT;
}
'(' key_list ')' fulltext_key_options
{
- if (add_create_index(Lex, $2, $4))
+ if (add_create_index(Lex, $2, $5))
MYSQL_YYABORT;
}
- | CREATE spatial INDEX_SYM ident init_key_options ON
+ | CREATE spatial INDEX_SYM opt_if_not_exists ident init_key_options ON
table_ident
{
- if (add_create_index_prepare(Lex, $7))
+ if (add_create_index_prepare(Lex, $8))
MYSQL_YYABORT;
}
'(' key_list ')' spatial_key_options
{
- if (add_create_index(Lex, $2, $4))
+ if (add_create_index(Lex, $2, $5))
MYSQL_YYABORT;
}
| CREATE DATABASE opt_if_not_exists ident
@@ -5079,9 +5079,17 @@ table_option:
;
opt_if_not_exists:
- /* empty */ { $$= 0; }
- | IF not EXISTS { $$=HA_LEX_CREATE_IF_NOT_EXISTS; }
- ;
+ /* empty */
+ {
+ Lex->check_exists= FALSE;
+ $$= 0;
+ }
+ | IF not EXISTS
+ {
+ Lex->check_exists= TRUE;
+ $$=HA_LEX_CREATE_IF_NOT_EXISTS;
+ }
+ ;
opt_create_table_options:
/* empty */
@@ -5399,14 +5407,14 @@ column_def:
;
key_def:
- normal_key_type opt_ident key_alg '(' key_list ')'
+ normal_key_type opt_if_not_exists_ident key_alg '(' key_list ')'
{ Lex->option_list= NULL; }
normal_key_options
{
if (add_create_index (Lex, $1, $2))
MYSQL_YYABORT;
}
- | fulltext opt_key_or_index opt_ident init_key_options
+ | fulltext opt_key_or_index opt_if_not_exists_ident init_key_options
'(' key_list ')'
{ Lex->option_list= NULL; }
fulltext_key_options
@@ -5414,7 +5422,7 @@ key_def:
if (add_create_index (Lex, $1, $3))
MYSQL_YYABORT;
}
- | spatial opt_key_or_index opt_ident init_key_options
+ | spatial opt_key_or_index opt_if_not_exists_ident init_key_options
'(' key_list ')'
{ Lex->option_list= NULL; }
spatial_key_options
@@ -5430,7 +5438,7 @@ key_def:
if (add_create_index (Lex, $2, $3.str ? $3 : $1))
MYSQL_YYABORT;
}
- | opt_constraint FOREIGN KEY_SYM opt_ident '(' key_list ')' references
+ | opt_constraint FOREIGN KEY_SYM opt_if_not_exists_ident '(' key_list ')' references
{
LEX *lex=Lex;
Key *key= new Foreign_key($4.str ? $4 : $1, lex->col_list,
@@ -5438,7 +5446,8 @@ key_def:
lex->ref_list,
lex->fk_delete_opt,
lex->fk_update_opt,
- lex->fk_match_option);
+ lex->fk_match_option,
+ lex->check_exists);
if (key == NULL)
MYSQL_YYABORT;
lex->alter_info.key_list.push_back(key);
@@ -6407,6 +6416,18 @@ opt_ident:
| field_ident { $$= $1; }
;
+opt_if_not_exists_ident:
+ opt_if_not_exists opt_ident
+ {
+ LEX *lex= Lex;
+ if (lex->check_exists && lex->sql_command != SQLCOM_ALTER_TABLE)
+ {
+ my_parse_error(ER(ER_SYNTAX_ERROR));
+ MYSQL_YYABORT;
+ }
+ $$= $2;
+ };
+
opt_component:
/* empty */ { $$= null_lex_str; }
| '.' ident { $$= $2; }
@@ -6663,7 +6684,7 @@ alter_commands:
new table and so forth.
*/
| add_partition_rule
- | DROP PARTITION_SYM alt_part_name_list
+ | DROP PARTITION_SYM opt_if_exists alt_part_name_list
{
Lex->alter_info.flags|= ALTER_DROP_PARTITION;
}
@@ -6764,7 +6785,7 @@ all_or_alt_part_name_list:
;
add_partition_rule:
- ADD PARTITION_SYM opt_no_write_to_binlog
+ ADD PARTITION_SYM opt_if_not_exists opt_no_write_to_binlog
{
LEX *lex= Lex;
lex->part_info= new partition_info();
@@ -6774,7 +6795,7 @@ add_partition_rule:
MYSQL_YYABORT;
}
lex->alter_info.flags|= ALTER_ADD_PARTITION;
- lex->no_write_to_binlog= $3;
+ lex->no_write_to_binlog= $4;
}
add_part_extra
{}
@@ -6850,7 +6871,7 @@ alter_list:
;
add_column:
- ADD opt_column
+ ADD opt_column opt_if_not_exists
{
LEX *lex=Lex;
lex->change=0;
@@ -6872,10 +6893,10 @@ alter_list_item:
{
Lex->alter_info.flags|= ALTER_ADD_COLUMN | ALTER_ADD_INDEX;
}
- | CHANGE opt_column field_ident
+ | CHANGE opt_column opt_if_exists field_ident
{
LEX *lex=Lex;
- lex->change= $3.str;
+ lex->change= $4.str;
lex->alter_info.flags|= ALTER_CHANGE_COLUMN;
lex->option_list= NULL;
}
@@ -6883,7 +6904,7 @@ alter_list_item:
{
Lex->create_last_non_select_table= Lex->last_table();
}
- | MODIFY_SYM opt_column field_ident
+ | MODIFY_SYM opt_column opt_if_exists field_ident
{
LEX *lex=Lex;
lex->length=lex->dec=0; lex->type=0;
@@ -6897,12 +6918,12 @@ alter_list_item:
field_def
{
LEX *lex=Lex;
- if (add_field_to_list(lex->thd,&$3,
- (enum enum_field_types) $5,
+ if (add_field_to_list(lex->thd,&$4,
+ (enum enum_field_types) $6,
lex->length,lex->dec,lex->type,
lex->default_value, lex->on_update_value,
&lex->comment,
- $3.str, &lex->interval_list, lex->charset,
+ $4.str, &lex->interval_list, lex->charset,
lex->uint_geom_type,
lex->vcol_info, lex->option_list))
MYSQL_YYABORT;
@@ -6911,32 +6932,33 @@ alter_list_item:
{
Lex->create_last_non_select_table= Lex->last_table();
}
- | DROP opt_column field_ident opt_restrict
+ | DROP opt_column opt_if_exists field_ident opt_restrict
{
LEX *lex=Lex;
- Alter_drop *ad= new Alter_drop(Alter_drop::COLUMN, $3.str);
+ Alter_drop *ad= new Alter_drop(Alter_drop::COLUMN, $4.str, $3);
if (ad == NULL)
MYSQL_YYABORT;
lex->alter_info.drop_list.push_back(ad);
lex->alter_info.flags|= ALTER_DROP_COLUMN;
}
- | DROP FOREIGN KEY_SYM opt_ident
+ | DROP FOREIGN KEY_SYM opt_if_exists opt_ident
{
Lex->alter_info.flags|= ALTER_DROP_INDEX | ALTER_FOREIGN_KEY;
}
| DROP PRIMARY_SYM KEY_SYM
{
LEX *lex=Lex;
- Alter_drop *ad= new Alter_drop(Alter_drop::KEY, primary_key_name);
+ Alter_drop *ad= new Alter_drop(Alter_drop::KEY, primary_key_name,
+ FALSE);
if (ad == NULL)
MYSQL_YYABORT;
lex->alter_info.drop_list.push_back(ad);
lex->alter_info.flags|= ALTER_DROP_INDEX;
}
- | DROP key_or_index field_ident
+ | DROP key_or_index opt_if_exists field_ident
{
LEX *lex=Lex;
- Alter_drop *ad= new Alter_drop(Alter_drop::KEY, $3.str);
+ Alter_drop *ad= new Alter_drop(Alter_drop::KEY, $4.str, $3);
if (ad == NULL)
MYSQL_YYABORT;
lex->alter_info.drop_list.push_back(ad);
@@ -10815,41 +10837,41 @@ do:
*/
drop:
- DROP opt_temporary table_or_tables if_exists
+ DROP opt_temporary table_or_tables opt_if_exists
{
LEX *lex=Lex;
lex->sql_command = SQLCOM_DROP_TABLE;
lex->drop_temporary= $2;
- lex->drop_if_exists= $4;
+ lex->check_exists= $4;
YYPS->m_lock_type= TL_UNLOCK;
YYPS->m_mdl_type= MDL_EXCLUSIVE;
}
table_list opt_restrict
{}
- | DROP INDEX_SYM ident ON table_ident {}
+ | DROP INDEX_SYM opt_if_exists ident ON table_ident {}
{
LEX *lex=Lex;
- Alter_drop *ad= new Alter_drop(Alter_drop::KEY, $3.str);
+ Alter_drop *ad= new Alter_drop(Alter_drop::KEY, $4.str, $3);
if (ad == NULL)
MYSQL_YYABORT;
lex->sql_command= SQLCOM_DROP_INDEX;
lex->alter_info.reset();
lex->alter_info.flags= ALTER_DROP_INDEX;
lex->alter_info.drop_list.push_back(ad);
- if (!lex->current_select->add_table_to_list(lex->thd, $5, NULL,
+ if (!lex->current_select->add_table_to_list(lex->thd, $6, NULL,
TL_OPTION_UPDATING,
TL_READ_NO_INSERT,
MDL_SHARED_NO_WRITE))
MYSQL_YYABORT;
}
- | DROP DATABASE if_exists ident
+ | DROP DATABASE opt_if_exists ident
{
LEX *lex=Lex;
lex->sql_command= SQLCOM_DROP_DB;
- lex->drop_if_exists=$3;
+ lex->check_exists=$3;
lex->name= $4;
}
- | DROP FUNCTION_SYM if_exists ident '.' ident
+ | DROP FUNCTION_SYM opt_if_exists ident '.' ident
{
THD *thd= YYTHD;
LEX *lex= thd->lex;
@@ -10865,14 +10887,14 @@ drop:
MYSQL_YYABORT;
}
lex->sql_command = SQLCOM_DROP_FUNCTION;
- lex->drop_if_exists= $3;
+ lex->check_exists= $3;
spname= new sp_name($4, $6, true);
if (spname == NULL)
MYSQL_YYABORT;
spname->init_qname(thd);
lex->spname= spname;
}
- | DROP FUNCTION_SYM if_exists ident
+ | DROP FUNCTION_SYM opt_if_exists ident
{
THD *thd= YYTHD;
LEX *lex= thd->lex;
@@ -10886,14 +10908,14 @@ drop:
if (thd->db && lex->copy_db_to(&db.str, &db.length))
MYSQL_YYABORT;
lex->sql_command = SQLCOM_DROP_FUNCTION;
- lex->drop_if_exists= $3;
+ lex->check_exists= $3;
spname= new sp_name(db, $4, false);
if (spname == NULL)
MYSQL_YYABORT;
spname->init_qname(thd);
lex->spname= spname;
}
- | DROP PROCEDURE_SYM if_exists sp_name
+ | DROP PROCEDURE_SYM opt_if_exists sp_name
{
LEX *lex=Lex;
if (lex->sphead)
@@ -10902,34 +10924,34 @@ drop:
MYSQL_YYABORT;
}
lex->sql_command = SQLCOM_DROP_PROCEDURE;
- lex->drop_if_exists= $3;
+ lex->check_exists= $3;
lex->spname= $4;
}
| DROP USER clear_privileges user_list
{
Lex->sql_command = SQLCOM_DROP_USER;
}
- | DROP VIEW_SYM if_exists
+ | DROP VIEW_SYM opt_if_exists
{
LEX *lex= Lex;
lex->sql_command= SQLCOM_DROP_VIEW;
- lex->drop_if_exists= $3;
+ lex->check_exists= $3;
YYPS->m_lock_type= TL_UNLOCK;
YYPS->m_mdl_type= MDL_EXCLUSIVE;
}
table_list opt_restrict
{}
- | DROP EVENT_SYM if_exists sp_name
+ | DROP EVENT_SYM opt_if_exists sp_name
{
- Lex->drop_if_exists= $3;
+ Lex->check_exists= $3;
Lex->spname= $4;
Lex->sql_command = SQLCOM_DROP_EVENT;
}
- | DROP TRIGGER_SYM if_exists sp_name
+ | DROP TRIGGER_SYM opt_if_exists sp_name
{
LEX *lex= Lex;
lex->sql_command= SQLCOM_DROP_TRIGGER;
- lex->drop_if_exists= $3;
+ lex->check_exists= $3;
lex->spname= $4;
}
| DROP TABLESPACE tablespace_name opt_ts_engine opt_ts_wait
@@ -10942,10 +10964,10 @@ drop:
LEX *lex= Lex;
lex->alter_tablespace_info->ts_cmd_type= DROP_LOGFILE_GROUP;
}
- | DROP SERVER_SYM if_exists ident_or_text
+ | DROP SERVER_SYM opt_if_exists ident_or_text
{
Lex->sql_command = SQLCOM_DROP_SERVER;
- Lex->drop_if_exists= $3;
+ Lex->check_exists= $3;
Lex->server_options.server_name= $4.str;
Lex->server_options.server_name_length= $4.length;
}
@@ -10983,9 +11005,17 @@ table_alias_ref:
}
;
-if_exists:
- /* empty */ { $$= 0; }
- | IF EXISTS { $$= 1; }
+opt_if_exists:
+ /* empty */
+ {
+ Lex->check_exists= FALSE;
+ $$= 0;
+ }
+ | IF EXISTS
+ {
+ Lex->check_exists= TRUE;
+ $$= 1;
+ }
;
opt_temporary: