diff options
| author | Monty <monty@mariadb.org> | 2016-10-02 15:35:08 +0300 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2016-10-05 01:11:07 +0300 |
| commit | 8be53a389c8eebed337057fa366b7c4506ba16b1 (patch) | |
| tree | abd36c95a598f1d6e5537e94fd7de034715ce299 /mysql-test/r | |
| parent | 0bae1957dd124f8382ae6af1de0e2168fc200bfb (diff) | |
| download | mariadb-git-8be53a389c8eebed337057fa366b7c4506ba16b1.tar.gz | |
MDEV-6112 multiple triggers per table
This is similar to MysQL Worklog 3253, but with
a different implementation. The disk format and
SQL syntax is identical with MySQL 5.7.
Fetures supported:
- "Any" ammount of any trigger
- Supports FOLLOWS and PRECEDES to be
able to put triggers in a certain execution order.
Implementation details:
- Class Trigger added to hold information about a trigger.
Before this trigger information was stored in a set of lists in
Table_triggers_list and in Table_triggers_list::bodies
- Each Trigger has a next field that poinst to the next Trigger with the
same action and time.
- When accessing a trigger, we now always access all linked triggers
- The list are now only used to load and save trigger files.
- MySQL trigger test case (trigger_wl3253) added and we execute these
identically.
- Even more gracefully handling of wrong trigger files than before. This
is useful if a trigger file uses functions or syntax not provided by
the server.
- Each trigger now has a "Created" field that shows when the trigger was
created, with 2 decimals.
Other comments:
- Many of the changes in test files was done because of the new "Created"
field in the trigger file. This shows up in SHOW ... TRIGGER and when
using information_schema.trigger.
- Don't check if all memory is released if on uses --gdb; This is needed
to be able to get a list from safemalloc of not freed memory while
debugging.
- Added option to trim_whitespace() to know how many prefix characters
was skipped.
- Changed a few ulonglong sql_mode to sql_mode_t, to find some wrong usage
of sql_mode.
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/create.result | 1 | ||||
| -rw-r--r-- | mysql-test/r/create_drop_trigger.result | 1 | ||||
| -rw-r--r-- | mysql-test/r/ddl_i18n_koi8r.result | 96 | ||||
| -rw-r--r-- | mysql-test/r/ddl_i18n_utf8.result | 96 | ||||
| -rw-r--r-- | mysql-test/r/features.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/merge.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/mysql_comments.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/mysqlcheck.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/mysqldump.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/show_check.result | 105 | ||||
| -rw-r--r-- | mysql-test/r/trigger-compat.result | 30 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 57 | ||||
| -rw-r--r-- | mysql-test/r/trigger_notembedded.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/trigger_wl3253.result | 502 |
15 files changed, 767 insertions, 205 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 36a192c2609..dd10628bce5 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -1352,7 +1352,6 @@ 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_trigger.result b/mysql-test/r/create_drop_trigger.result index a215838b0fd..f7ae8d21a7f 100644 --- a/mysql-test/r/create_drop_trigger.result +++ b/mysql-test/r/create_drop_trigger.result @@ -8,7 +8,6 @@ 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 diff --git a/mysql-test/r/ddl_i18n_koi8r.result b/mysql-test/r/ddl_i18n_koi8r.result index 8423f8fe367..ba43c1c867f 100644 --- a/mysql-test/r/ddl_i18n_koi8r.result +++ b/mysql-test/r/ddl_i18n_koi8r.result @@ -1198,7 +1198,7 @@ END| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1211,10 +1211,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1227,10 +1227,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1243,10 +1243,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1259,7 +1259,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1275,7 +1275,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -1287,7 +1287,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest2| @@ -1304,7 +1304,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -1316,13 +1316,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1337,7 +1337,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1352,7 +1352,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci u SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1367,7 +1367,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1470,7 +1470,7 @@ use mysqltest1| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1483,10 +1483,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1499,10 +1499,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1515,10 +1515,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1531,7 +1531,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1547,7 +1547,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -1559,7 +1559,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest2| @@ -1576,7 +1576,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -1588,13 +1588,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1609,7 +1609,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1624,7 +1624,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci u SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1639,7 +1639,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -1904,7 +1904,7 @@ use mysqltest1| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1917,10 +1917,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1933,10 +1933,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10); @@ -1949,10 +1949,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = _koi8r ''; SET @a2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; @@ -1965,7 +1965,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = _koi8r ''; SET @b2 = _utf8 'текст'; -END koi8r koi8r_general_ci utf8_unicode_ci +END koi8r koi8r_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1981,7 +1981,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -1993,7 +1993,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest2| @@ -2010,7 +2010,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = ''; SET @a1 = ''; SET @a2 = ''; -END BEFORE NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END BEFORE # root@localhost koi8r koi8r_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); @@ -2022,13 +2022,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = ''; SET @b1 = ''; SET @b2 = ''; -END AFTER NULL root@localhost koi8r koi8r_general_ci utf8_unicode_ci +END AFTER # root@localhost koi8r koi8r_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -2043,7 +2043,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -2058,7 +2058,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci u SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10); INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); @@ -2073,7 +2073,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE 1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(1)); INSERT INTO log VALUES(COLLATION('')); diff --git a/mysql-test/r/ddl_i18n_utf8.result b/mysql-test/r/ddl_i18n_utf8.result index 2c9ea77b809..47786196037 100644 --- a/mysql-test/r/ddl_i18n_utf8.result +++ b/mysql-test/r/ddl_i18n_utf8.result @@ -1198,7 +1198,7 @@ END| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1211,10 +1211,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1227,10 +1227,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1243,10 +1243,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1259,7 +1259,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1275,7 +1275,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -1287,7 +1287,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest2| @@ -1304,7 +1304,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -1316,13 +1316,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1337,7 +1337,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1352,7 +1352,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci utf SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1367,7 +1367,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1470,7 +1470,7 @@ use mysqltest1| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1483,10 +1483,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1499,10 +1499,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1515,10 +1515,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1531,7 +1531,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1547,7 +1547,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -1559,7 +1559,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest2| @@ -1576,7 +1576,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -1588,13 +1588,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1609,7 +1609,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1624,7 +1624,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci utf SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1639,7 +1639,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -1904,7 +1904,7 @@ use mysqltest1| SHOW CREATE TRIGGER trg1| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg1 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1917,10 +1917,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER trg2| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg2 CREATE DEFINER=`root`@`localhost` TRIGGER trg2 AFTER INSERT ON t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1933,10 +1933,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg3| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg3 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg3 BEFORE INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10); @@ -1949,10 +1949,10 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = _utf8 'текст'; SET @a3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW CREATE TRIGGER mysqltest2.trg4| -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created trg4 CREATE DEFINER=`root`@`localhost` TRIGGER mysqltest2.trg4 AFTER INSERT ON mysqltest2.t1 FOR EACH ROW BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; @@ -1965,7 +1965,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = _utf8 'текст'; SET @b3 = _koi8r ''; -END utf8 utf8_general_ci utf8_unicode_ci +END utf8 utf8_general_ci utf8_unicode_ci # SHOW TRIGGERS| @@ -1981,7 +1981,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg2 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -1993,7 +1993,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest2| @@ -2010,7 +2010,7 @@ INSERT INTO log VALUES(@@character_set_client); SET @a1 = 'текст'; SET @a2 = 'текст'; SET @a3 = 'текст'; -END BEFORE NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END BEFORE # root@localhost utf8 utf8_general_ci utf8_unicode_ci trg4 INSERT t1 BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); @@ -2022,13 +2022,13 @@ INSERT INTO log VALUES(@@character_set_client); SET @b1 = 'текст'; SET @b2 = 'текст'; SET @b3 = 'текст'; -END AFTER NULL root@localhost utf8 utf8_general_ci utf8_unicode_ci +END AFTER # root@localhost utf8 utf8_general_ci utf8_unicode_ci use mysqltest1| SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg1'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg1 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg1 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -2043,7 +2043,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg2'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest1 trg2 INSERT def mysqltest1 t1 0 NULL BEGIN +def mysqltest1 trg2 INSERT def mysqltest1 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -2058,7 +2058,7 @@ END ROW AFTER NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci utf SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg3'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg3 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg3 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10); INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); @@ -2073,7 +2073,7 @@ END ROW BEFORE NULL NULL OLD NEW CREATED root@localhost utf8 utf8_general_ci ut SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'trg4'| TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest2 trg4 INSERT def mysqltest2 t1 0 NULL BEGIN +def mysqltest2 trg4 INSERT def mysqltest2 t1 1 NULL BEGIN DECLARE перем1 CHAR(10) CHARACTER SET utf8; INSERT INTO log VALUES(COLLATION(перем1)); INSERT INTO log VALUES(COLLATION('текст')); diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result index b6a3ffa11ad..c6d1a6b0bac 100644 --- a/mysql-test/r/features.result +++ b/mysql-test/r/features.result @@ -118,7 +118,7 @@ select @a; 1 SHOW TRIGGERS IN test like 't1'; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg INSERT t1 set @a:=1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg INSERT t1 set @a:=1 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop trigger trg; drop table t1; show status like "%trigger%"; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index c7a4edba65a..8be595a55f1 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -964,34 +964,34 @@ trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; -end BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; -end BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +end BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; -end AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +end AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest'); TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def test trg1 INSERT def test t1 0 NULL begin +def test trg1 INSERT def test t1 1 NULL begin if new.j > 10 then set new.j := 10; end if; -end ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -def test trg2 UPDATE def test t1 0 NULL begin +end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test trg2 UPDATE def test t1 1 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; -end ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -def test trg3 UPDATE def test t1 0 NULL begin +end ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test trg3 UPDATE def test t1 1 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; -end ROW AFTER NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +end ROW AFTER NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci drop trigger trg1; drop trigger trg2; drop trigger trg3; @@ -1406,7 +1406,7 @@ trigger_name t1_ai show triggers from mysqltest; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +t1_ai INSERT t1 set @a = new.a + new.b + new.c AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci connect con27629,localhost,mysqltest_1,,mysqltest; show columns from t1; Field Type Null Key Default Extra diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 93b16896380..7f34d0a9217 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2289,8 +2289,8 @@ CREATE TABLE t2(a int); CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2); CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo(); SHOW CREATE TRIGGER tr1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo() latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo() latin1 latin1_swedish_ci latin1_swedish_ci # DROP TRIGGER tr1; DROP TABLE t1, t2, t3; # diff --git a/mysql-test/r/mysql_comments.result b/mysql-test/r/mysql_comments.result index 4865c7a7af0..9ba8dff3d55 100644 --- a/mysql-test/r/mysql_comments.result +++ b/mysql-test/r/mysql_comments.result @@ -27,10 +27,10 @@ Procedure sql_mode Create Procedure character_set_client collation_connection Da foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n\n\n\n\n \n\n \n values ("foo", 42) latin1 latin1_swedish_ci latin1_swedish_ci Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n \n declare b int;\n declare c float;\n\n \n \n\n \nend latin1 latin1_swedish_ci latin1_swedish_ci -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n\n\n\n \n declare b int;\n declare c float;\n\n \n \n\n \n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci --TIME-- +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n\n\n\n \n declare b int;\n declare c float;\n\n \n \n\n \n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci --TIME-- id data trig 12 "Pass 2 : --enable-comments" @@ -54,10 +54,10 @@ Procedure sql_mode Create Procedure character_set_client collation_connection Da foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n## These comments are part of the procedure body, and should be kept.\n# Comment 2a\n-- Comment 2b\n/* Comment 2c */\n -- empty line below\n\n -- empty line above\n values ("foo", 42) # comment 3, still part of the body latin1 latin1_swedish_ci latin1_swedish_ci Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\nend latin1 latin1_swedish_ci latin1_swedish_ci -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n# comment 1a\n-- comment 1b\n/*\n comment 1c\n*/\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci --TIME-- +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n# comment 1a\n-- comment 1b\n/*\n comment 1c\n*/\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci --TIME-- id data trig 12 set global sql_mode=default; diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 8894616fbb3..ff9cc5d5c81 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -241,15 +241,15 @@ CREATE TABLE t1 (a INT) engine=myisam; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def #mysql50#a@b tr1 INSERT def #mysql50#a@b #mysql50#c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -def #mysql50#a@b tr2 INSERT def #mysql50#a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def #mysql50#a@b tr1 INSERT def #mysql50#a@b #mysql50#c@d 1 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def #mysql50#a@b tr2 INSERT def #mysql50#a@b t1 1 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci mysqlcheck --fix-db-names --fix-table-names --all-databases USE `a@b`; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA="a@b" ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def a@b tr1 INSERT def a@b c@d 0 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci -def a@b tr2 INSERT def a@b t1 0 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci +def a@b tr1 INSERT def a@b c@d 1 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci +def a@b tr2 INSERT def a@b t1 1 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci INSERT INTO `c@d` VALUES (2), (1); SELECT * FROM `c@d`; a diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 98594973467..04612e02b7f 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2717,7 +2717,7 @@ a2 SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation testref INSERT test1 BEGIN -INSERT INTO test2 SET a2 = NEW.a1; END BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +INSERT INTO test2 SET a2 = NEW.a1; END BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM `test1`; a1 1 @@ -4558,7 +4558,7 @@ CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT "Meow"; SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr1 UPDATE t1 SET @f1 = 1 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation test ev1 root@localhost SYSTEM ONE TIME 2030-01-01 00:00:00 NULL NULL NULL NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -4577,7 +4577,7 @@ DROP PROCEDURE pr1; reload table; this should restore table and trigger SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr1 UPDATE t1 SET @f1 = 1 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation SELECT name,body FROM mysql.proc WHERE NAME = 'pr1'; @@ -4586,7 +4586,7 @@ name body reload db; this should restore routines and events SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -tr1 UPDATE t1 SET @f1 = 1 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr1 UPDATE t1 SET @f1 = 1 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation test ev1 root@localhost SYSTEM ONE TIME 2030-01-01 00:00:00 NULL NULL NULL NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -4812,7 +4812,7 @@ CREATE VIEW v2 AS SELECT * FROM t2; </row> </table_data> <triggers name="t2"> - <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN @@ -4820,7 +4820,7 @@ INSERT INTO t2 VALUES(1); END ]]> </trigger> - <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW BEGIN @@ -4951,7 +4951,7 @@ END </row> </table_data> <triggers name="t2"> - <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN @@ -4959,7 +4959,7 @@ INSERT INTO t2 VALUES(1); END ]]> </trigger> - <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW BEGIN @@ -5093,7 +5093,7 @@ connection conn_1; </row> </table_data> <triggers name="t2"> - <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig1" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig1 BEFORE INSERT ON t2 FOR EACH ROW BEGIN @@ -5101,7 +5101,7 @@ INSERT INTO t2 VALUES(1); END ]]> </trigger> - <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci"> + <trigger Trigger="trig2" sql_mode="" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="--TIME--"> <![CDATA[ CREATE DEFINER=`root`@`localhost` TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW BEGIN diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index d3c4ec94cb7..ed768343581 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1009,14 +1009,14 @@ def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION Event 253 18 6 N 1 0 def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 1 0 33 def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 17 0 33 def information_schema TRIGGERS TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS CREATED Created 12 19 0 Y 128 0 63 +def information_schema TRIGGERS TRIGGERS CREATED Created 12 22 22 Y 128 2 63 def information_schema TRIGGERS TRIGGERS SQL_MODE sql_mode 253 24576 42 N 1 0 33 def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 567 14 N 1 0 33 def information_schema TRIGGERS TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 1 0 33 def information_schema TRIGGERS TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 1 0 33 def information_schema TRIGGERS TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -t1_bi INSERT t1 SET @a = 1 BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost binary binary latin1_swedish_ci +t1_bi INSERT t1 SET @a = 1 BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost binary binary latin1_swedish_ci ---------------------------------------------------------------- SELECT TRIGGER_CATALOG, @@ -1058,6 +1058,11 @@ def information_schema TRIGGERS TRIGGERS SQL_MODE SQL_MODE 253 24576 42 N 1 0 33 def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 567 14 N 1 0 33 TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER def test t1_bi INSERT def test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost +SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name='t1_bi'; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def information_schema TRIGGERS TRIGGERS CREATED CREATED 12 22 22 Y 128 2 63 +CREATED +# ---------------------------------------------------------------- SHOW CREATE VIEW v1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr @@ -1363,70 +1368,70 @@ DROP PROCEDURE IF EXISTS p1; CREATE TABLE t1(c1 INT); CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; SHOW CREATE TRIGGER t1_bi; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CREATE PROCEDURE p1() SHOW CREATE TRIGGER t1_bi; CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # CALL p1(); -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # PREPARE stmt1 FROM 'SHOW CREATE TRIGGER t1_bi'; EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # EXECUTE stmt1; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1 latin1 latin1_swedish_ci latin1_swedish_ci # DROP TABLE t1; DROP PROCEDURE p1; DEALLOCATE PREPARE stmt1; @@ -1457,10 +1462,10 @@ Function sql_mode Create Function character_set_client collation_connection Data f1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1 RETURN 'тест' koi8r koi8r_general_ci latin1_swedish_ci SHOW CREATE TRIGGER t1_bi; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW -SET NEW.c1 = 'тест' koi8r koi8r_general_ci latin1_swedish_ci +SET NEW.c1 = 'тест' koi8r koi8r_general_ci latin1_swedish_ci # SHOW CREATE EVENT ev1; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation ev1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`root`@`localhost` EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 'тест' AS test koi8r koi8r_general_ci latin1_swedish_ci @@ -1532,16 +1537,16 @@ connect con1, localhost, root; LOCK TABLE t1 WRITE; connection default; SHOW CREATE TRIGGER t1_bi; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci # connection con1; UNLOCK TABLES; # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction connection default; START TRANSACTION; SHOW CREATE TRIGGER t1_bi; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci # connection con1; ALTER TABLE t1 CHARACTER SET = utf8; disconnect con1; diff --git a/mysql-test/r/trigger-compat.result b/mysql-test/r/trigger-compat.result index 3d96507d71f..55793cdf5c2 100644 --- a/mysql-test/r/trigger-compat.result +++ b/mysql-test/r/trigger-compat.result @@ -29,8 +29,8 @@ wl2818_trg2 mysqltest_dfn@localhost SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest_db1 wl2818_trg1 INSERT def mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci -def mysqltest_db1 wl2818_trg2 INSERT def mysqltest_db1 t1 0 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_dfn@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 wl2818_trg1 INSERT def mysqltest_db1 t1 1 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 wl2818_trg2 INSERT def mysqltest_db1 t1 1 NULL INSERT INTO t2 VALUES(CURRENT_USER()) ROW AFTER NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_dfn@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER wl2818_trg1; Warnings: Warning 1454 No definer attribute for trigger 'mysqltest_db1'.'wl2818_trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger. @@ -74,9 +74,11 @@ CREATE TRIGGER tr22 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM non_existing_ta ERROR 42000: Unknown trigger has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Not allowed syntax here, and trigger name cant be extracted either.' at line 1' SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -tr11 INSERT t1 DELETE FROM t3 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -tr12 INSERT t1 DELETE FROM t3 AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -tr14 DELETE t1 DELETE FROM non_existing_table AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr11 INSERT t1 DELETE FROM t3 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr12 INSERT t1 DELETE FROM t3 AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr15 UPDATE t1 CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a BEFORE # latin1 latin1_swedish_ci latin1_swedish_ci +tr13 DELETE t1 CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a BEFORE # latin1 latin1_swedish_ci latin1_swedish_ci +tr14 DELETE t1 DELETE FROM non_existing_table AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci INSERT INTO t1 VALUES (1); ERROR 42000: Trigger 'tr13' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a USING t1 a' at line 1' INSERT INTO t2 VALUES (1); @@ -94,9 +96,11 @@ RENAME TABLE t1 TO t1_2; ERROR 42000: Trigger 'tr13' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a USING t1 a' at line 1' SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -tr11 INSERT t1 DELETE FROM t3 BEFORE NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -tr12 INSERT t1 DELETE FROM t3 AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -tr14 DELETE t1 DELETE FROM non_existing_table AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr11 INSERT t1 DELETE FROM t3 BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr12 INSERT t1 DELETE FROM t3 AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr15 UPDATE t1 CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a BEFORE # latin1 latin1_swedish_ci latin1_swedish_ci +tr13 DELETE t1 CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a BEFORE # latin1 latin1_swedish_ci latin1_swedish_ci +tr14 DELETE t1 DELETE FROM non_existing_table AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER tr11; Warnings: Warning 1603 Triggers for table `test`.`t1` have no creation context @@ -124,16 +128,16 @@ INSERT INTO t1 VALUES (1), (2), (3); INSERT INTO t2 VALUES (1), (2), (3); # We write three trigger files. First trigger is syntaxically incorrect, next trigger is correct # and last trigger is broken. -# Next we try to execute SHOW CREATE TRGGIR command for broken trigger and then try to drop one. +# Next we try to execute SHOW CREATE TRIGGER command for broken trigger and then try to drop one. FLUSH TABLE t1; SHOW CREATE TRIGGER tr12; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr12 CREATE DEFINER=`root`@`localhost` TRIGGER tr12 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr12 CREATE DEFINER=`root`@`localhost` TRIGGER tr12 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2 latin1 latin1_swedish_ci latin1_swedish_ci NULL Warnings: Warning 1603 Triggers for table `test`.`t1` have no creation context SHOW CREATE TRIGGER tr11; -Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr11 CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr11 CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a latin1 latin1_swedish_ci latin1_swedish_ci NULL DROP TRIGGER tr12; DROP TRIGGER tr11; DROP TABLE t1; diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 19a8d53a428..2ae70730c3a 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -303,7 +303,7 @@ create trigger trg before insert on t1 for each row set @a:=1; create trigger trg after insert on t1 for each row set @a:=1; ERROR HY000: Trigger already exists create trigger trg2 before insert on t1 for each row set @a:=1; -ERROR 42000: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table' +drop trigger trg2; create trigger trg before insert on t3 for each row set @a:=1; ERROR HY000: Trigger already exists create trigger trg2 before insert on t3 for each row set @a:=1; @@ -1988,8 +1988,8 @@ 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_bi2; drop trigger t1_bi; ERROR HY000: Trigger does not exist lock tables t1 read; @@ -2123,6 +2123,7 @@ CREATE TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1 # Used to crash SHOW TRIGGERS IN db1; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +trg1 INSERT t2 CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERTINTOt1 VALUES (1) BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci INSERT INTO t2 VALUES (1); ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (1)' at line 1' SELECT * FROM t1; @@ -2298,6 +2299,8 @@ SET optimizer_switch=@save_optimizer_switch; DROP TRIGGER tr; DROP TABLE t1, t2; End of 5.3 tests. +set time_zone="+00:00"; +SET TIMESTAMP=UNIX_TIMESTAMP('2001-01-01 10:20:30'); SET @@session.sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES'; CREATE TABLE t1 (c CHAR(1) NOT NULL); CREATE TRIGGER t1_bi @@ -2311,5 +2314,55 @@ END; SET @@session.sql_mode = default; INSERT INTO t1 VALUES ('a'); ERROR 22001: Data too long for column 'c' at row 1 +show create trigger t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +t1_bi STRICT_TRANS_TABLES,STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi +BEFORE INSERT +ON t1 +FOR EACH ROW +BEGIN +SET NEW.c = 'www'; +END latin1 latin1_swedish_ci latin1_swedish_ci 2001-01-01 10:20:30.00 DROP TRIGGER t1_bi; DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +set time_zone= @@global.time_zone; +create table t1 (i int); +create trigger tr1 after insert on t1 for each row set @a=@a+1; +create trigger tr2 after insert on t1 for each row set @a=@a+1; +create trigger tr3 after insert on t1 for each row set @a=@a+1; +flush status; +show status like 'Executed_triggers'; +Variable_name Value +Executed_triggers 0 +set @a=0; +insert into t1 values (1); +show status like 'Executed_triggers'; +Variable_name Value +Executed_triggers 3 +select @a; +@a +3 +drop table t1; +create table t1 (i int); +set time_zone="+0:00"; +SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.33'); +select now(2); +now(2) +2016-01-01 10:10:10.33 +create or replace trigger tr1 after insert on t1 for each row set @a=@a+1; +SET TIMESTAMP=UNIX_TIMESTAMP('2016-01-01 10:10:10.99'); +select now(2); +now(2) +2016-01-01 10:10:10.99 +create or replace trigger tr2 after insert on t1 for each row set @a=@a+1; +select now(2); +now(2) +2016-01-01 10:10:10.99 +select trigger_name, action_order, created from information_schema.triggers +where event_object_table = 't1' and trigger_schema='test'; +trigger_name action_order created +tr1 1 2016-01-01 10:10:10.33 +tr2 2 2016-01-01 10:10:10.99 +drop table t1; +set time_zone= @@global.time_zone; diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result index b0addf8d50f..36352623998 100644 --- a/mysql-test/r/trigger_notembedded.result +++ b/mysql-test/r/trigger_notembedded.result @@ -133,8 +133,8 @@ INSERT INTO t1 VALUES(6); ERROR HY000: The user specified as a definer ('mysqltest_nonexs'@'localhost') does not exist SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation -trg1 INSERT t1 SET @new_sum = 0 BEFORE NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_inv@localhost latin1 latin1_swedish_ci latin1_swedish_ci -trg2 INSERT t1 SET @new_sum = 0 AFTER NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_nonexs@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg1 INSERT t1 SET @new_sum = 0 BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_inv@localhost latin1 latin1_swedish_ci latin1_swedish_ci +trg2 INSERT t1 SET @new_sum = 0 AFTER # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION mysqltest_nonexs@localhost latin1 latin1_swedish_ci latin1_swedish_ci DROP TRIGGER trg1; DROP TRIGGER trg2; CREATE TRIGGER trg1 BEFORE INSERT ON t1 @@ -163,11 +163,11 @@ trg5 @abcdef@@@hostname SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION -def mysqltest_db1 trg1 INSERT def mysqltest_db1 t1 0 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci -def mysqltest_db1 trg2 INSERT def mysqltest_db1 t1 0 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @ latin1 latin1_swedish_ci latin1_swedish_ci -def mysqltest_db1 trg3 UPDATE def mysqltest_db1 t1 0 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abc@def@@% latin1 latin1_swedish_ci latin1_swedish_ci -def mysqltest_db1 trg4 UPDATE def mysqltest_db1 t1 0 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @hostname latin1 latin1_swedish_ci latin1_swedish_ci -def mysqltest_db1 trg5 DELETE def mysqltest_db1 t1 0 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW NULL NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abcdef@@@hostname latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 trg1 INSERT def mysqltest_db1 t1 1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 trg2 INSERT def mysqltest_db1 t1 1 NULL SET @a = 2 ROW AFTER NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @ latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 trg3 UPDATE def mysqltest_db1 t1 1 NULL SET @a = 3 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abc@def@@% latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 trg4 UPDATE def mysqltest_db1 t1 1 NULL SET @a = 4 ROW AFTER NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @hostname latin1 latin1_swedish_ci latin1_swedish_ci +def mysqltest_db1 trg5 DELETE def mysqltest_db1 t1 1 NULL SET @a = 5 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION @abcdef@@@hostname latin1 latin1_swedish_ci latin1_swedish_ci connection default; DROP USER mysqltest_dfn@localhost; DROP USER mysqltest_inv@localhost; diff --git a/mysql-test/r/trigger_wl3253.result b/mysql-test/r/trigger_wl3253.result new file mode 100644 index 00000000000..c39591fdc17 --- /dev/null +++ b/mysql-test/r/trigger_wl3253.result @@ -0,0 +1,502 @@ +# +# WL#3253: multiple triggers per table +# +SET @binlog_format_saved = @@binlog_format; +SET binlog_format=ROW; +SET time_zone='+00:00'; +# +# Test 1. +# Check that the sequence of triggers for the same combination +# of event type/action type can be created for a table +# and is fired consequently in the order of its creation +# during statement execution. +# In this test we check BEFORE triggers. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +1 1 +101 2 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 2. +# Check that the sequence of triggers for the same combination +# of event type/action type can be created for a table +# and is fired consequently in the order of its creation +# during statement execution. +# In this test we check AFTER triggers. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +1 1 +101 2 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 3. +# Check that the sequences of triggers for the different event types +# can be created for a table and are fired consequently +# in the order of its creation during statement execution. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a); +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +1 1 +101 2 +UPDATE t1 SET a = 5; +SELECT * FROM t2 ORDER BY b; +a b +1 1 +101 2 +5 3 +305 4 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 4. +# Check that every new created trigger has unique action_order value +# started from 1 and NOT NULL value for creation timestamp. +# +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name created action_order +tr1_bi 2013-01-31 09:00:00.00 1 +tr2_bi 2013-01-31 09:00:01.00 2 +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +# +# Test 5. +# Check that action_order attribute isn't shown +# in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1_bi INSERT t1 SET @a:=1 BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE TRIGGER tr1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci # +DROP TABLE t1; +# +# Test 6. +# Check that action_order attribute is reused when trigger +# are recreated. +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +DROP TRIGGER tr1_bi; +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +DROP TABLE t1; +# +# Test 7. +# Check that it is possible to create several triggers with +# the same value for creation timestamp. +# +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name created action_order +tr1_bi 2013-01-31 09:00:01.00 1 +tr2_bi 2013-01-31 09:00:01.00 2 +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +# +# Test 8. +# Check that SHOW CREATE TRIGGER outputs the CREATED attribute +# and it is not NULL +# +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SHOW CREATE TRIGGER tr1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr1_bi NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci 2013-01-31 09:00:01.00 +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +# +# Test 9. +# Check that SHOW TRIGGERS outputs the CREATED attribute +# and it is not NULL. +# +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1_bi INSERT t1 SET @a:=1 BEFORE 2013-01-31 09:00:01.00 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW 2013-01-31 09:00:01.00 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +DROP TABLE t1; +SET TIMESTAMP=DEFAULT; +# +# Test 10. +# Check that FOLLOWS clause is supported and works correctly. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr2_bi 2 +tr3_bi 3 +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +101 1 +201 2 +301 3 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 11. +# Check that PRECEDES clause is supported and works correctly. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300); +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr3_bi INSERT INTO t2 (a) VALUES (NEW.a + 200); +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr2_bi 2 +tr3_bi 3 +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +101 1 +201 2 +301 3 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 12. +# Check that the PRECEDES works properly for the 1st trigger in the chain. +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100); +CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi INSERT INTO t2 (a) VALUES (NEW.a); +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr0_bi 1 +tr1_bi 2 +INSERT INTO t1 VALUES (1); +SELECT * FROM t2 ORDER BY b; +a b +1 1 +101 2 +DROP TABLE t2; +DROP TABLE t1; +# +# Test 13. +# Check that error is reported if the FOLLOWS clause references to +# non-existing trigger +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr0_bi SET @a:=2; +ERROR HY000: Referenced trigger 'tr0_bi' for the given action time and event type does not exist +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr3_bi 2 +DROP TABLE t1; +# +# Test 14. +# Check that error is reported if the PRECEDES clause references to +# non-existing trigger +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr0_bi SET @a:=2; +ERROR HY000: Referenced trigger 'tr0_bi' for the given action time and event type does not exist +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr3_bi 2 +DROP TABLE t1; +# +# Test 15. +# Check that action_order value is independent for each type of event +# (INSERT/UPDATE/DELETE) +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr2_bi 2 +tr1_bu 1 +CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr2_bi SET @a:=3; +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; +SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test'; +trigger_name action_order +tr1_bi 1 +tr2_bi 2 +tr3_bi 3 +tr1_bu 1 +tr2_bu 2 +DROP TABLE t1; +# +# Test 16. +# Check that the trigger in the clause FOLLOWS/PRECEDES can refences +# only to the trigger for the same ACTION/TIMINMG +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; +CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; +ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist +CREATE TRIGGER tr2_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; +ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist +CREATE TRIGGER tr1_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3; +ERROR HY000: Referenced trigger 'tr1_bu' for the given action time and event type does not exist +CREATE TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3; +ERROR HY000: Referenced trigger 'tr1_bi' for the given action time and event type does not exist +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1_bi INSERT t1 SET @a:=1 BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr1_bu UPDATE t1 SET @a:=3 BEFORE # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test tr1_bu UPDATE def test t1 1 NULL SET @a:=3 ROW BEFORE NULL NULL OLD NEW # NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +DROP TABLE t1; +# +# Test 17. Check that table's triggers are dumped correctly. +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +DROP TABLE t1; +# +# Test 18. Check that table's triggers are dumped in right order +# taking into account the PRECEDES/FOLLOWS clauses. +# +CREATE TABLE t1 (a INT); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0; +CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=0; +# Expected order of triggers in the dump is: tr0_bi, tr1_bi, tr1_1_bi, tr2_i. +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_cs_client = @@character_set_client */ ; +/*!50003 SET @saved_cs_results = @@character_set_results */ ; +/*!50003 SET @saved_col_connection = @@collation_connection */ ; +/*!50003 SET character_set_client = latin1 */ ; +/*!50003 SET character_set_results = latin1 */ ; +/*!50003 SET collation_connection = latin1_swedish_ci */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; +DELIMITER ;; +/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2 */;; +DELIMITER ; +/*!50003 SET sql_mode = @saved_sql_mode */ ; +/*!50003 SET character_set_client = @saved_cs_client */ ; +/*!50003 SET character_set_results = @saved_cs_results */ ; +/*!50003 SET collation_connection = @saved_col_connection */ ; +DROP TABLE t1; +# +# Test 19. Check that table's triggers are dumped correctly in xml. +# +CREATE TABLE t1 (a INT); +SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00'); +CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1; +CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2; +CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3; +SET TIMESTAMP=DEFAULT; +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="test"> + <table_data name="t1"> + </table_data> + <triggers name="t1"> + <trigger Trigger="tr1_bi" sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 +]]> + </trigger> + <trigger Trigger="tr2_bi" sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2 +]]> + </trigger> + <trigger Trigger="tr1_bu" sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" character_set_client="latin1" collation_connection="latin1_swedish_ci" Database_Collation="latin1_swedish_ci" Created="2013-01-31 09:00:00.00"> +<![CDATA[ +CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3 +]]> + </trigger> + </triggers> +</database> +</mysqldump> +DROP TABLE t1; +# +# Test 20. Check that the statement CHECK TABLE FOR UPGRADE outputs +# the warnings for triggers created by a server without support for wl3253. +# +CREATE TABLE t1 (a INT); +FLUSH TABLE t1; +CHECK TABLE t1 FOR UPGRADE; +Table Op Msg_type Msg_text +test.t1 check status OK +SHOW TRIGGERS; +Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation +tr1_bi INSERT t1 SET @a:=1 BEFORE NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +tr1_ai INSERT t1 SET @a:=2 AFTER NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test'; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +def test tr1_bi INSERT def test t1 1 NULL SET @a:=1 ROW BEFORE NULL NULL OLD NEW NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +def test tr1_ai INSERT def test t1 1 NULL SET @a:=2 ROW AFTER NULL NULL OLD NEW NULL NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE TRIGGER tr1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr1_bi NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 latin1 latin1_swedish_ci latin1_swedish_ci NULL +SHOW CREATE TRIGGER tr1_ai; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation Created +tr1_ai NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW SET @a:=2 latin1 latin1_swedish_ci latin1_swedish_ci NULL +DROP TABLE t1; +SET binlog_format=@binlog_format_saved; +# End of tests. +# |