maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #08208
Re: MDEV-7286 TRIGGER: CREATE OR REPLACE, CREATE IF NOT EXISTS
Hi Sergei,
On 02/26/2015 08:02 PM, Sergei Golubchik wrote:
Hi, Alexander!
Please review a patch for MDEV-7286.
Pretty good. I have just a couple of comments about
build_trig_stmt_query() usage, see below.
Thanks. A fixed version is attached.
See my comments inline:
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 4b20813..d3713cc 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -608,6 +608,67 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
<cut>
+
+ /*
+ Create well-formed trigger definition query. Original query is not
+ appropriated, because definer-clause can be not truncated.
you don't have to preserve old comments verbatim, feel free to
fix them as you see fit. E.g. here I'd say "is not appropriate"
or, better, rewrite the whole comment to make sense
(what does it mean "definer-clause can be not truncated"?)
Changed the comment to:
/*
Create a query with the full trigger definition.
The original query is not appropriate, as it can miss the DEFINER=XXX
part.
*/
+ */
+ stmt_query->append(STRING_WITH_LEN("CREATE "));
+
+ if (lex->create_info.or_replace())
+ stmt_query->append(STRING_WITH_LEN("OR REPLACE "));
+
+ if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
+ {
+ /* SUID trigger */
+ lex->definer->set_lex_string(trg_definer, trg_definer_holder);
+ /*
+ Append definer-clause if the trigger is SUID (a usual trigger in
+ new MySQL versions).
And this comment I'd remove completely. "new MySQL versions" isn't
true anymore. And what's left is "Append definer" one line before
a function append_definer() is called. Useless.
Removed.
<cut>
@@ -722,8 +794,29 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
/* Use the filesystem to enforce trigger namespace constraints. */
if (!access(trigname_buff, F_OK))
{
- my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
- return 1;
+ if (lex->create_info.or_replace())
+ {
+ String drop_trg_query;
+ drop_trg_query.append("DROP TRIGGER ");
+ drop_trg_query.append(lex->spname->m_name.str);
+ if (drop_trigger(thd, tables, &drop_trg_query))
+ return 1;
+ }
+ else if (lex->create_info.if_not_exists())
+ {
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS),
+ trigname_buff);
+ LEX_STRING trg_definer_tmp;
+ build_trig_stmt_query(thd, tables, stmt_query,
+ &trg_definer_tmp, trg_definer_holder);
Why?
The caller expects stmt_query to be properly filled on "false"
returned.
Note, although the trigger exists on the master,
we still put the "CREATE IF NOT EXISTS" query into the binary log,
to create it on the slave in case if it's missing.
+ return false;
+ }
+ else
+ {
+ my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
+ return true;
+ }
}
trigname.trigger_table.str= tables->table_name;
@@ -802,30 +872,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
lex_string_set(trg_db_cl_name,
get_default_db_collation(thd, tables->db)->name);
- /*
- Create well-formed trigger definition query. Original query is not
- appropriated, because definer-clause can be not truncated.
- */
-
- stmt_query->append(STRING_WITH_LEN("CREATE "));
-
- if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
- {
- /*
- Append definer-clause if the trigger is SUID (a usual trigger in
- new MySQL versions).
- */
-
- append_definer(thd, stmt_query, &definer_user, &definer_host);
- }
-
- LEX_STRING stmt_definition;
- stmt_definition.str= (char*) thd->lex->stmt_definition_begin;
- stmt_definition.length= thd->lex->stmt_definition_end
- - thd->lex->stmt_definition_begin;
- trim_whitespace(thd->charset(), & stmt_definition);
-
- stmt_query->append(stmt_definition.str, stmt_definition.length);
+ build_trig_stmt_query(thd, tables, stmt_query,
+ trg_definer, trg_definer_holder);
trg_definer and trg_definer_holder don't seem to be used anywhere
Sorry, I think they are still used.
trg_definer is used here:
!(trg_definer= alloc_lex_string(&table->mem_root)) ||
definers_list.push_back(trg_definer, &table->mem_root) ||
and it points to thr_definer_holder (in case of a SUID trigger)
or is equal to emptry_lex_string (in case of a non-SUID trigger).
By the way, this hack with trg_definer and trg_definer_holder also
looked hard to read for me. I'd better wrap them into a single
structure:
struct Definer_with_buffer
{
LEX_STRING *definer;
char definer_holder[USER_HOST_BUFF_SIZE];
};
and change build_trig_stmt_query() interface to:
static void
build_trig_stmt_query(THD *thd, TABLE_LIST *tables,
String *stmt_query,
Definer_with_buffer *trg_definer);
Note, this Definer_with_buffer could be used in more places,
e.g. in a similar place in sp.cc.
trg_def->str= stmt_query->c_ptr_safe();
trg_def->length= stmt_query->length();
Regards,
Sergei
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index ec70dba..e90dba0 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -2120,6 +2120,11 @@ CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE A SET `pk`=1 WHERE `pk`=0 ;
END ;|
+ERROR HY000: Trigger already exists
+CREATE TRIGGER f1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+UPDATE A SET `pk`=1 WHERE `pk`=0 ;
+END ;|
ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
DROP TABLE t1;
DROP TABLE B;
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index 4b1db84..ad6c260 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -243,3 +243,40 @@ Log_name Pos Event_type Server_id End_log_pos Info
# # Gtid 1 # GTID #-#-#
# # Query 1 # use `test`; DROP USER IF EXISTS u1@localhost
RESET MASTER;
+CREATE TABLE t1 (a INT);
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10;
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11;
+DROP TRIGGER tr1;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21;
+Warnings:
+Note 1359 Trigger already exists
+DROP TRIGGER IF EXISTS tr1;
+DROP TRIGGER IF EXISTS tr1;
+Warnings:
+Note 1360 Trigger does not exist
+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)
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER IF EXISTS tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TRIGGER IF EXISTS tr1
+# # Gtid 1 # GTID #-#-#
+# # Query 1 # use `test`; DROP TABLE `t1` /* generated by server */
+RESET MASTER;
diff --git a/mysql-test/r/create_drop_trigger.result b/mysql-test/r/create_drop_trigger.result
new file mode 100644
index 0000000..a215838
--- /dev/null
+++ b/mysql-test/r/create_drop_trigger.result
@@ -0,0 +1,37 @@
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT @sum;
+@sum
+60
+CREATE TRIGGER IF NOT EXISTS val_sum_new BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + 1 + NEW.val;
+Warnings:
+Note 1359 Trigger already exists
+CREATE OR REPLACE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 2 + NEW.val;
+ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + NEW.val
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 3 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + 3 + NEW.val
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 4 + NEW.val;
+ERROR HY000: Trigger already exists
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+SET @sum = @sum + 3 + NEW.val
+# Clearing up
+DROP TRIGGER IF EXISTS val_sum;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT
+DROP TRIGGER IF EXISTS val_sum;
+Warnings:
+Note 1360 Trigger does not exist
+DROP TABLE t1;
+DROP DATABASE db1;
diff --git a/mysql-test/r/create_or_replace.result b/mysql-test/r/create_or_replace.result
index ff8170b..87c6136 100644
--- a/mysql-test/r/create_or_replace.result
+++ b/mysql-test/r/create_or_replace.result
@@ -6,8 +6,6 @@ INSERT INTO t2 VALUES(1),(2),(3);
#
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS
-create or replace trigger trg before insert on t1 for each row set @a:=1;
-ERROR HY000: Incorrect usage of OR REPLACE and TRIGGERS / SP / EVENT
create or replace table mysql.general_log (a int);
ERROR HY000: You cannot 'CREATE OR REPLACE' a log table if logging is enabled
create or replace table mysql.slow_log (a int);
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 8bf3176..e71090b 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1957,6 +1957,8 @@ drop table if exists t1;
create table t1 (i int, j int);
create trigger t1_bi before insert on t1 for each row begin end;
create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Trigger already exists
+create trigger t1_bi2 before insert on t1 for each row begin end;
ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'
drop trigger t1_bi;
drop trigger t1_bi;
diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result b/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result
new file mode 100644
index 0000000..8cb8061
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_create_drop_trigger.result
@@ -0,0 +1,48 @@
+include/master-slave.inc
+[connection master]
+# Part 1 - initial creation
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 1;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 1
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 1
+# Part 2 - CREATE IF NOT EXISTS (on a existing trigger)
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 2;
+Warnings:
+Note 1359 Trigger already exists
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 1
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 1
+# Part 3 - CREATE OR REPLACE (on a existing trigger)
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 3;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Master
+SET @sum = @sum + NEW.val + 3
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+ACTION_STATEMENT_Slave
+SET @sum = @sum + NEW.val + 3
+# Clearing up
+DROP TRIGGER val_sum;
+DROP TABLE t1;
+DROP TRIGGER IF EXISTS val_sum;
+Warnings:
+Note 1360 Trigger does not exist
+DROP TRIGGER random_trigger;
+ERROR HY000: Trigger does not exist
+DROP DATABASE db1;
+DROP TRIGGER IF EXISTS val_sum;
+ERROR 3D000: No database selected
+# Syncing slave with master
+DROP TRIGGER val_sum;
+ERROR HY000: Trigger does not exist
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test b/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test
new file mode 100644
index 0000000..568d4e2
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_create_drop_trigger.test
@@ -0,0 +1,48 @@
+--source include/master-slave.inc
+
+--echo # Part 1 - initial creation
+connection master;
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 1;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Part 2 - CREATE IF NOT EXISTS (on a existing trigger)
+connection master;
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 2;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Part 3 - CREATE OR REPLACE (on a existing trigger)
+connection master;
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val + 3;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Master FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+sync_slave_with_master;
+SELECT ACTION_STATEMENT AS ACTION_STATEMENT_Slave FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Clearing up
+connection master;
+DROP TRIGGER val_sum;
+DROP TABLE t1;
+DROP TRIGGER IF EXISTS val_sum;
+
+--error ER_TRG_DOES_NOT_EXIST
+DROP TRIGGER random_trigger;
+DROP DATABASE db1;
+
+--error ER_NO_DB_ERROR
+DROP TRIGGER IF EXISTS val_sum;
+
+--echo # Syncing slave with master
+sync_slave_with_master;
+
+--error ER_TRG_DOES_NOT_EXIST
+DROP TRIGGER val_sum;
+
+--source include/rpl_end.inc
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 4d57a5a..30c93f5 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -1622,12 +1622,18 @@ END ; |
INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ;
--delimiter |
---error ER_NOT_SUPPORTED_YET
+--error ER_TRG_ALREADY_EXISTS
CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE A SET `pk`=1 WHERE `pk`=0 ;
END ;|
+--error ER_NOT_SUPPORTED_YET
+CREATE TRIGGER f1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ UPDATE A SET `pk`=1 WHERE `pk`=0 ;
+END ;|
+
--delimiter ;
DROP TABLE t1;
diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test
index 5bcd783..e6f4853 100644
--- a/mysql-test/t/create_drop_binlog.test
+++ b/mysql-test/t/create_drop_binlog.test
@@ -119,3 +119,18 @@ DROP USER IF EXISTS u1@localhost;
--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);
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=10;
+CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=11;
+DROP TRIGGER tr1;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=20;
+CREATE TRIGGER IF NOT EXISTS tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=21;
+DROP TRIGGER IF EXISTS tr1;
+DROP TRIGGER IF EXISTS tr1;
+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_trigger.test b/mysql-test/t/create_drop_trigger.test
new file mode 100644
index 0000000..a8afc87
--- /dev/null
+++ b/mysql-test/t/create_drop_trigger.test
@@ -0,0 +1,31 @@
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (val INT);
+CREATE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+SET @sum=0;
+INSERT INTO t1 VALUES (10), (20), (30);
+SELECT @sum;
+
+--error ER_NOT_SUPPORTED_YET
+CREATE TRIGGER IF NOT EXISTS val_sum_new BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + NEW.val;
+
+CREATE TRIGGER IF NOT EXISTS val_sum AFTER INSERT ON t1 FOR EACH ROW SET @sum = @sum + 1 + NEW.val;
+
+--error ER_WRONG_USAGE
+CREATE OR REPLACE TRIGGER IF NOT EXISTS val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 2 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+CREATE OR REPLACE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 3 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--error ER_TRG_ALREADY_EXISTS
+CREATE TRIGGER val_sum BEFORE INSERT ON t1 FOR EACH ROW SET @sum = @sum + 4 + NEW.val;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+
+--echo # Clearing up
+DROP TRIGGER IF EXISTS val_sum;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='val_sum';
+DROP TRIGGER IF EXISTS val_sum;
+
+DROP TABLE t1;
+DROP DATABASE db1;
diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test
index 9e37950..3e37910 100644
--- a/mysql-test/t/create_or_replace.test
+++ b/mysql-test/t/create_or_replace.test
@@ -21,8 +21,6 @@ INSERT INTO t2 VALUES(1),(2),(3);
--error ER_WRONG_USAGE
CREATE OR REPLACE TABLE IF NOT EXISTS t1 (a int);
---error ER_WRONG_USAGE
-create or replace trigger trg before insert on t1 for each row set @a:=1;
# check that we don't try to create a log table in use
--error ER_BAD_LOG_STATEMENT
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 4a1a3a6..384dd6c 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -2237,8 +2237,10 @@ drop table if exists t1;
create table t1 (i int, j int);
create trigger t1_bi before insert on t1 for each row begin end;
---error ER_NOT_SUPPORTED_YET
+--error ER_TRG_ALREADY_EXISTS
create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_NOT_SUPPORTED_YET
+create trigger t1_bi2 before insert on t1 for each row begin end;
drop trigger t1_bi;
--error ER_TRG_DOES_NOT_EXIST
drop trigger t1_bi;
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 4b20813..41e0ffe 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -608,6 +608,63 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
DBUG_RETURN(result);
}
+/**
+ Build stmt_query to write it in the bin-log
+ and get the trigger definer.
+
+ @param thd current thread context (including trigger definition in
+ LEX)
+ @param tables table list containing one open table for which the
+ trigger is created.
+ @param[out] stmt_query after successful return, this string contains
+ well-formed statement for creation this trigger.
+
+ @param[out] trg_definer The triggger definer.
+ @param[out] trg_definer_holder Used as a buffer for definer.
+
+ @note
+ - Assumes that trigger name is fully qualified.
+ - NULL-string means the following LEX_STRING instance:
+ { str = 0; length = 0 }.
+ - In other words, definer_user and definer_host should contain
+ simultaneously NULL-strings (non-SUID/old trigger) or valid strings
+ (SUID/new trigger).
+*/
+static void build_trig_stmt_query(THD *thd, TABLE_LIST *tables,
+ String *stmt_query,
+ LEX_STRING *trg_definer,
+ char trg_definer_holder[])
+{
+ LEX *lex= thd->lex;
+
+ /*
+ Create a query with the full trigger definition.
+ The original query is not appropriate, as it can miss the DEFINER=XXX part.
+ */
+ stmt_query->append(STRING_WITH_LEN("CREATE "));
+
+ if (lex->create_info.or_replace())
+ stmt_query->append(STRING_WITH_LEN("OR REPLACE "));
+
+ if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
+ {
+ /* SUID trigger */
+ lex->definer->set_lex_string(trg_definer, trg_definer_holder);
+ append_definer(thd, stmt_query, &lex->definer->user, &lex->definer->host);
+ }
+ else
+ {
+ *trg_definer= empty_lex_str;
+ }
+
+ LEX_STRING stmt_definition;
+ stmt_definition.str= (char*) thd->lex->stmt_definition_begin;
+ stmt_definition.length= thd->lex->stmt_definition_end -
+ thd->lex->stmt_definition_begin;
+ trim_whitespace(thd->charset(), &stmt_definition);
+ stmt_query->append(stmt_definition.str, stmt_definition.length);
+}
+
/**
Create trigger for table.
@@ -640,8 +697,6 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
char file_buff[FN_REFLEN], trigname_buff[FN_REFLEN];
LEX_STRING file, trigname_file;
LEX_STRING *trg_def;
- LEX_STRING definer_user;
- LEX_STRING definer_host;
ulonglong *trg_sql_mode;
char trg_definer_holder[USER_HOST_BUFF_SIZE];
LEX_STRING *trg_definer;
@@ -650,6 +705,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
LEX_STRING *trg_client_cs_name;
LEX_STRING *trg_connection_cl_name;
LEX_STRING *trg_db_cl_name;
+ sp_head *trg_body= bodies[lex->trg_chistics.event]
+ [lex->trg_chistics.action_time];
if (check_for_broken_triggers())
return true;
@@ -659,20 +716,31 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
lex->spname->m_db.str))
{
my_error(ER_TRG_IN_WRONG_SCHEMA, MYF(0));
- return 1;
+ return true;
}
- /* We don't allow creation of several triggers of the same type yet */
- if (bodies[lex->trg_chistics.event][lex->trg_chistics.action_time] != 0)
+ /*
+ We don't allow creation of several triggers of the same type yet.
+ If a trigger with the same type already exists:
+ a. Throw a ER_NOT_SUPPORTED_YET error,
+ if the old and the new trigger names are different;
+ b. Or continue, if the old and the new trigger names are the same:
+ - either to recreate the trigger on "CREATE OR REPLACE"
+ - or send a "already exists" warning on "CREATE IF NOT EXISTS"
+ - or send an "alredy exists" error on normal CREATE.
+ */
+ if (trg_body != 0 &&
+ my_strcasecmp(table_alias_charset,
+ trg_body->m_name.str, lex->spname->m_name.str))
{
my_error(ER_NOT_SUPPORTED_YET, MYF(0),
"multiple triggers with the same action time"
" and event for one table");
- return 1;
+ return true;
}
if (sp_process_definer(thd))
- return 1;
+ return true;
/*
Let us check if all references to fields in old/new versions of row in
@@ -701,7 +769,7 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (!trg_field->fixed &&
trg_field->fix_fields(thd, (Item **)0))
- return 1;
+ return true;
}
/*
@@ -722,8 +790,29 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
/* Use the filesystem to enforce trigger namespace constraints. */
if (!access(trigname_buff, F_OK))
{
- my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
- return 1;
+ if (lex->create_info.or_replace())
+ {
+ String drop_trg_query;
+ drop_trg_query.append("DROP TRIGGER ");
+ drop_trg_query.append(lex->spname->m_name.str);
+ if (drop_trigger(thd, tables, &drop_trg_query))
+ return 1;
+ }
+ else if (lex->create_info.if_not_exists())
+ {
+ push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
+ ER_TRG_ALREADY_EXISTS, ER(ER_TRG_ALREADY_EXISTS),
+ trigname_buff);
+ LEX_STRING trg_definer_tmp;
+ build_trig_stmt_query(thd, tables, stmt_query,
+ &trg_definer_tmp, trg_definer_holder);
+ return false;
+ }
+ else
+ {
+ my_error(ER_TRG_ALREADY_EXISTS, MYF(0));
+ return true;
+ }
}
trigname.trigger_table.str= tables->table_name;
@@ -731,7 +820,7 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (sql_create_definition_file(NULL, &trigname_file, &trigname_file_type,
(uchar*)&trigname, trigname_file_parameters))
- return 1;
+ return true;
/*
Soon we will invalidate table object and thus Table_triggers_list object
@@ -764,29 +853,6 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
*trg_sql_mode= thd->variables.sql_mode;
- if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
- {
- /* SUID trigger. */
-
- definer_user= lex->definer->user;
- definer_host= lex->definer->host;
-
- lex->definer->set_lex_string(trg_definer, trg_definer_holder);
- }
- else
- {
- /* non-SUID trigger. */
-
- definer_user.str= 0;
- definer_user.length= 0;
-
- definer_host.str= 0;
- definer_host.length= 0;
-
- trg_definer->str= (char*) "";
- trg_definer->length= 0;
- }
-
/*
Fill character set information:
- client character set contains charset info only;
@@ -802,30 +868,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
lex_string_set(trg_db_cl_name,
get_default_db_collation(thd, tables->db)->name);
- /*
- Create well-formed trigger definition query. Original query is not
- appropriated, because definer-clause can be not truncated.
- */
-
- stmt_query->append(STRING_WITH_LEN("CREATE "));
-
- if (lex->sphead->m_chistics->suid != SP_IS_NOT_SUID)
- {
- /*
- Append definer-clause if the trigger is SUID (a usual trigger in
- new MySQL versions).
- */
-
- append_definer(thd, stmt_query, &definer_user, &definer_host);
- }
-
- LEX_STRING stmt_definition;
- stmt_definition.str= (char*) thd->lex->stmt_definition_begin;
- stmt_definition.length= thd->lex->stmt_definition_end
- - thd->lex->stmt_definition_begin;
- trim_whitespace(thd->charset(), & stmt_definition);
-
- stmt_query->append(stmt_definition.str, stmt_definition.length);
+ build_trig_stmt_query(thd, tables, stmt_query,
+ trg_definer, trg_definer_holder);
trg_def->str= stmt_query->c_ptr_safe();
trg_def->length= stmt_query->length();
@@ -834,11 +878,11 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
if (!sql_create_definition_file(NULL, &file, &triggers_file_type,
(uchar*)this, triggers_file_parameters))
- return 0;
+ return false;
err_with_cleanup:
mysql_file_delete(key_file_trn, trigname_buff, MYF(MY_WME));
- return 1;
+ return true;
}
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 8025340..6ccc294 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -2563,6 +2563,7 @@ create:
{
// TODO: remove this when "MDEV-5359 CREATE OR REPLACE..." is done
if ($1.or_replace() &&
+ Lex->sql_command != SQLCOM_CREATE_TRIGGER &&
Lex->sql_command != SQLCOM_CREATE_VIEW &&
Lex->sql_command != SQLCOM_CREATE_FUNCTION &&
Lex->sql_command != SQLCOM_CREATE_SPFUNCTION &&
@@ -16157,23 +16158,28 @@ view_check_option:
trigger_tail:
TRIGGER_SYM
remember_name
+ opt_if_not_exists
+ {
+ if (Lex->add_create_options_with_check($3))
+ MYSQL_YYABORT;
+ }
sp_name
trg_action_time
trg_event
ON
- remember_name /* $7 */
- { /* $8 */
+ remember_name /* $9 */
+ { /* $10 */
Lex->raw_trg_on_table_name_begin= YYLIP->get_tok_start();
}
- table_ident /* $9 */
+ table_ident /* $11 */
FOR_SYM
- remember_name /* $11 */
- { /* $12 */
+ remember_name /* $13 */
+ { /* $14 */
Lex->raw_trg_on_table_name_end= YYLIP->get_tok_start();
}
EACH_SYM
ROW_SYM
- { /* $15 */
+ { /* $17 */
LEX *lex= thd->lex;
Lex_input_stream *lip= YYLIP;
@@ -16184,17 +16190,17 @@ trigger_tail:
}
lex->stmt_definition_begin= $2;
- lex->ident.str= $7;
- lex->ident.length= $11 - $7;
- lex->spname= $3;
+ lex->ident.str= $9;
+ lex->ident.length= $13 - $9;
+ lex->spname= $5;
- if (!make_sp_head(thd, $3, TYPE_ENUM_TRIGGER))
+ if (!make_sp_head(thd, $5, TYPE_ENUM_TRIGGER))
MYSQL_YYABORT;
lex->sphead->set_body_start(thd, lip->get_cpp_ptr());
}
- sp_proc_stmt /* $16 */
- { /* $17 */
+ sp_proc_stmt /* $18 */
+ { /* $19 */
LEX *lex= Lex;
sp_head *sp= lex->sphead;
@@ -16210,7 +16216,7 @@ trigger_tail:
sp_proc_stmt alternatives are not saving/restoring LEX, so
lex->query_tables can be wiped out.
*/
- if (!lex->select_lex.add_table_to_list(thd, $9,
+ if (!lex->select_lex.add_table_to_list(thd, $11,
(LEX_STRING*) 0,
TL_OPTION_UPDATING,
TL_READ_NO_INSERT,
Follow ups
References
