1 files changed, 509 insertions, 0 deletions
diff --git a/mysql-test/main/trigger-compat.test b/mysql-test/main/trigger-compat.test
new file mode 100644
index 00000000..9c102993
--- /dev/null
+++ b/mysql-test/main/trigger-compat.test
@@ -0,0 +1,509 @@
+# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not
+# supported in embedded server. So, this test should not be run on embedded
+# server.
+
+--source include/not_embedded.inc
+--source include/default_charset.inc
+
+###########################################################################
+#
+# Tests for WL#2818:
+#   - Check that triggers created w/o DEFINER information work well:
+#     - create the first trigger;
+#     - manually remove definer information from corresponding TRG file;
+#     - create the second trigger (the first trigger will be reloaded; check
+#       that we receive a warning);
+#     - check that the triggers loaded correctly;
+#
+###########################################################################
+
+#
+# Prepare environment.
+#
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+
+GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+#
+# Create a table and the first trigger.
+#
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+
+CREATE TABLE t1(num_value INT);
+CREATE TABLE t2(user_str TEXT);
+
+CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER());
+
+#
+# Remove definers from TRG file.
+#
+
+--echo
+--echo ---> patching t1.TRG...
+
+# Here we remove definers.  This is somewhat complex than the original test
+# Previously, the test only used grep -v 'definers=' t1.TRG, but grep is not
+# portable and we have to load the file into a table, exclude the definers line,
+# then load the data to an outfile to accomplish the same effect
+
+--disable_query_log
+--connection default
+CREATE TABLE patch (a blob);
+let $MYSQLD_DATADIR = `select @@datadir`;
+eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/mysqltest_db1/t1.TRG' INTO TABLE patch;
+# remove original t1.TRG file so SELECT INTO OUTFILE won't fail
+--remove_file $MYSQLD_DATADIR/mysqltest_db1/t1.TRG
+--disable_ps2_protocol
+eval SELECT SUBSTRING_INDEX(a,'definers=',1) INTO OUTFILE
+ '$MYSQLD_DATADIR/mysqltest_db1/t1.TRG' 
+FROM patch;
+--enable_ps2_protocol
+DROP TABLE patch;
+--connection wl2818_definer_con
+--enable_query_log
+
+#
+# Create a new trigger.
+#
+
+--echo
+
+CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--echo
+
+SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+
+--echo
+
+--replace_column 17 #
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+
+# Clean up
+DROP TRIGGER wl2818_trg1;
+DROP TRIGGER wl2818_trg2;
+disconnect wl2818_definer_con;
+connection default;
+use mysqltest_db1;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP USER mysqltest_dfn@localhost;
+DROP USER mysqltest_inv@localhost;
+DROP DATABASE mysqltest_db1;
+USE test;
+
+
+--echo #
+--echo # Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
+--echo #
+let $MYSQLD_DATADIR=`SELECT @@datadir`;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2, t3;
+--enable_warnings
+
+CREATE TABLE t1 ( a INT );
+CREATE TABLE t2 ( a INT );
+CREATE TABLE t3 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t3 VALUES (1), (2), (3);
+
+--echo # We simulate importing a trigger from 5.0 by writing a .TRN file for
+--echo # each trigger plus a .TRG file the way MySQL 5.0 would have done it, 
+--echo # with syntax allowed in 5.0 only.
+--echo #
+--echo # Note that in 5.0 the following lines are missing from t1.TRG:
+--echo #
+--echo # client_cs_names='latin1'
+--echo # connection_cl_names='latin1_swedish_ci'
+--echo # db_cl_names='latin1_swedish_ci'
+
+--write_file $MYSQLD_DATADIR/test/tr11.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr12.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr13.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr14.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr15.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a'
+sql_modes=0 0 0 0 0
+definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost'
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t2.TRG
+TYPE=TRIGGERS
+triggers='Not allowed syntax here, and trigger name cant be extracted either.'
+sql_modes=0
+definers='root@localhost'
+EOF
+
+FLUSH TABLE t1;
+FLUSH TABLE t2;
+
+--echo # We will get parse errors for most DDL and DML statements when the table
+--echo # has broken triggers. The parse error refers to the first broken 
+--echo # trigger.
+--error ER_PARSE_ERROR
+CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
+--error ER_PARSE_ERROR
+CREATE TRIGGER tr22 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM non_existing_table;
+--replace_column 6 #
+SHOW TRIGGERS;
+--error ER_PARSE_ERROR
+INSERT INTO t1 VALUES (1);
+--error ER_PARSE_ERROR
+INSERT INTO t2 VALUES (1);
+--error ER_PARSE_ERROR
+DELETE FROM t1;
+--error ER_PARSE_ERROR
+UPDATE t1 SET a = 1 WHERE a = 1;
+SELECT * FROM t1;
+--error ER_PARSE_ERROR
+RENAME TABLE t1 TO t1_2;
+--replace_column 6 #
+SHOW TRIGGERS;
+
+DROP TRIGGER tr11;
+DROP TRIGGER tr12;
+DROP TRIGGER tr13;
+DROP TRIGGER tr14;
+DROP TRIGGER tr15;
+
+--replace_column 6 #
+SHOW TRIGGERS;
+
+--echo # Make sure there is no trigger file left.
+--list_files $MYSQLD_DATADIR/test/ tr*
+
+--echo # We write the same trigger files one more time to test DROP TABLE.
+--write_file $MYSQLD_DATADIR/test/tr11.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr12.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr13.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr14.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr15.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW DELETE FROM t3' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM non_existing_table' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW DELETE FROM non_existing_table a USING non_existing_table a'
+sql_modes=0 0 0 0 0
+definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost'
+EOF
+
+FLUSH TABLE t1;
+FLUSH TABLE t2;
+
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+--echo # Make sure there is no trigger file left.
+
+--list_files $MYSQLD_DATADIR/test/ tr*
+
+CREATE TABLE t1 ( a INT );
+CREATE TABLE t2 ( a INT );
+INSERT INTO t1 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3);
+
+--echo # We write three trigger files. First trigger is syntaxically incorrect, next trigger is correct
+--echo # and last trigger is broken.
+--echo # Next we try to execute SHOW CREATE TRIGGER command for broken trigger and then try to drop one.
+--write_file $MYSQLD_DATADIR/test/tr11.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr12.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE the wrongest trigger_in_the_world' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE DELETE ON t1 FOR EACH ROW DELETE FROM t1 a USING t1 a' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr12 BEFORE INSERT ON t1 FOR EACH ROW DELETE FROM t2'
+sql_modes=0 0 0
+definers='root@localhost' 'root@localhost' 'root@localhost'
+EOF
+
+FLUSH TABLE t1;
+
+SHOW CREATE TRIGGER tr12;
+SHOW CREATE TRIGGER tr11;
+DROP TRIGGER tr12;
+DROP TRIGGER tr11;
+
+DROP TABLE t1;
+DROP TABLE t2;
+
+
+--echo #
+--echo # MDEV-25659 trigger name is empty after upgrade to 10.4
+--echo #
+
+--echo # START: Total triggers 1, broken triggers 1, DROP TABLE
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+
+--write_file $MYSQLD_DATADIR/test/tr1.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW\nBEGIN\n  IF unknown_variable\n  THEN\n    INSERT INTO t2 VALUES (OLD.a);\n  END IF;\nEND'
+sql_modes=1411383296
+definers='root@localhost'
+client_cs_names='utf8'
+connection_cl_names='utf8_general_ci'
+db_cl_names='latin1_swedish_ci'
+created=164206218647
+EOF
+
+FLUSH TABLES;
+--error ER_PARSE_ERROR
+DELETE FROM t1 WHERE a=1;
+--error ER_PARSE_ERROR
+INSERT INTO t1 VALUES (2);
+
+SET time_zone='+00:00';
+--vertical_results
+SHOW TRIGGERS LIKE 't1';
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+--horizontal_results
+SET time_zone=DEFAULT;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+DROP TABLE t1;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+--echo # END: Total triggers 1, broken triggers 1, DROP TABLE
+
+
+--echo # START: Total triggers 1, broken triggers 1, DROP TRIGGER
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+
+--write_file $MYSQLD_DATADIR/test/tr1.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW\nBEGIN\n  IF unknown_variable\n  THEN\n    INSERT INTO t2 VALUES (OLD.a);\n  END IF;\nEND'
+sql_modes=1411383296
+definers='root@localhost'
+client_cs_names='utf8'
+connection_cl_names='utf8_general_ci'
+db_cl_names='latin1_swedish_ci'
+created=164206218647
+EOF
+
+FLUSH TABLES;
+--error ER_PARSE_ERROR
+DELETE FROM t1 WHERE a=1;
+--error ER_PARSE_ERROR
+INSERT INTO t1 VALUES (2);
+
+SET time_zone='+00:00';
+--vertical_results
+SHOW TRIGGERS LIKE 't1';
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+--horizontal_results
+SET time_zone=DEFAULT;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+DROP TRIGGER tr1;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+DROP TABLE t1;
+
+--echo # END: Total triggers 1, broken triggers 1, DROP TRIGGER
+
+
+--echo # START: Total triggers 2, broken triggers 1, DROP TABLE
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+
+--write_file $MYSQLD_DATADIR/test/tr1.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr2.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a+100)' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW\nBEGIN\n  IF unknown_variable\n  THEN\n    INSERT INTO t2 VALUES (OLD.a);\n  END IF;\nEND'
+sql_modes=1411383296 1411383296
+definers='root@localhost' 'root@localhost'
+client_cs_names='utf8' 'utf8'
+connection_cl_names='utf8_general_ci' 'utf8_general_ci'
+db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci'
+created=164206810874 164206810873
+EOF
+
+FLUSH TABLES;
+--error ER_PARSE_ERROR
+DELETE FROM t1 WHERE a=1;
+--error ER_PARSE_ERROR
+INSERT INTO t1 VALUES (2);
+
+SET time_zone='+00:00';
+--vertical_results
+SHOW TRIGGERS LIKE 't1';
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+--horizontal_results
+SET time_zone=DEFAULT;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+DROP TABLE t1;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+--echo # END: Total triggers 2, broken triggers 1, using DROP TABLE
+
+
+--echo # START: Total triggers 2, broken triggers 1, DROP TRIGGER
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+
+--write_file $MYSQLD_DATADIR/test/tr1.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/tr2.TRN
+TYPE=TRIGGERNAME
+trigger_table=t1
+EOF
+
+--write_file $MYSQLD_DATADIR/test/t1.TRG
+TYPE=TRIGGERS
+triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.a+100)' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW\nBEGIN\n  IF unknown_variable\n  THEN\n    INSERT INTO t2 VALUES (OLD.a);\n  END IF;\nEND'
+sql_modes=1411383296 1411383296
+definers='root@localhost' 'root@localhost'
+client_cs_names='utf8' 'utf8'
+connection_cl_names='utf8_general_ci' 'utf8_general_ci'
+db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci'
+created=164206810874 164206810873
+EOF
+
+FLUSH TABLES;
+--error ER_PARSE_ERROR
+DELETE FROM t1 WHERE a=1;
+--error ER_PARSE_ERROR
+INSERT INTO t1 VALUES (2);
+
+SET time_zone='+00:00';
+--vertical_results
+SHOW TRIGGERS LIKE 't1';
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1';
+--horizontal_results
+SET time_zone=DEFAULT;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+DROP TRIGGER tr1;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+# Now we dropped the broken trigger. Make sure the good one is fired.
+# If everything goes as expected, it will try to insert into t2,
+# which does not exists, hence the (expected) error.
+--error ER_NO_SUCH_TABLE
+INSERT INTO t1 VALUES (100);
+
+DROP TABLE t1;
+
+--echo # Listing trigger files
+--list_files $MYSQLD_DATADIR/test *.TR?
+--echo # Listing trigger files done
+
+--echo # END: Total triggers 2, broken triggers 1, using DROP TRIGGER