1# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not 2# supported in embedded server. So, this test should not be run on embedded 3# server. 4 5--source include/not_embedded.inc 6--source include/default_charset.inc 7 8########################################################################### 9# 10# Tests for WL#2818: 11# - Check that triggers created w/o DEFINER information work well: 12# - create the first trigger; 13# - manually remove definer information from corresponding TRG file; 14# - create the second trigger (the first trigger will be reloaded; check 15# that we receive a warning); 16# - check that the triggers loaded correctly; 17# 18########################################################################### 19 20# 21# Prepare environment. 22# 23DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; 24DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; 25DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; 26DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%'; 27FLUSH PRIVILEGES; 28 29--disable_warnings 30DROP DATABASE IF EXISTS mysqltest_db1; 31--enable_warnings 32 33CREATE DATABASE mysqltest_db1; 34 35CREATE USER mysqltest_dfn@localhost; 36CREATE USER mysqltest_inv@localhost; 37 38GRANT CREATE, TRIGGER ON mysqltest_db1.* TO mysqltest_dfn@localhost; 39 40# 41# Create a table and the first trigger. 42# 43 44--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1) 45--connection wl2818_definer_con 46 47CREATE TABLE t1(num_value INT); 48CREATE TABLE t2(user_str TEXT); 49 50CREATE TRIGGER wl2818_trg1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CURRENT_USER()); 51 52# 53# Remove definers from TRG file. 54# 55 56--echo 57--echo ---> patching t1.TRG... 58 59# Here we remove definers. This is somewhat complex than the original test 60# Previously, the test only used grep -v 'definers=' t1.TRG, but grep is not 61# portable and we have to load the file into a table, exclude the definers line, 62# then load the data to an outfile to accomplish the same effect 63 64--disable_query_log 65--connection default 66CREATE TABLE patch (a blob); 67let $MYSQLD_DATADIR = `select @@datadir`; 68eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/mysqltest_db1/t1.TRG' INTO TABLE patch; 69# remove original t1.TRG file so SELECT INTO OUTFILE won't fail 70--remove_file $MYSQLD_DATADIR/mysqltest_db1/t1.TRG 71eval SELECT SUBSTRING_INDEX(a,'definers=',1) INTO OUTFILE 72 '$MYSQLD_DATADIR/mysqltest_db1/t1.TRG' 73FROM patch; 74DROP TABLE patch; 75--connection wl2818_definer_con 76--enable_query_log 77 78# 79# Create a new trigger. 80# 81 82--echo 83 84CREATE TRIGGER wl2818_trg2 AFTER INSERT ON t1 85 FOR EACH ROW 86 INSERT INTO t2 VALUES(CURRENT_USER()); 87 88--echo 89 90SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 91 92--echo 93 94--replace_column 17 # 95SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name; 96 97# Clean up 98DROP TRIGGER wl2818_trg1; 99DROP TRIGGER wl2818_trg2; 100disconnect wl2818_definer_con; 101connection default; 102use mysqltest_db1; 103DROP TABLE t1; 104DROP TABLE t2; 105DROP USER mysqltest_dfn@localhost; 106DROP USER mysqltest_inv@localhost; 107DROP DATABASE mysqltest_db1; 108USE test; 109 110 111--echo # 112--echo # Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way 113--echo # 114let $MYSQLD_DATADIR=`SELECT @@datadir`; 115 116--disable_warnings 117DROP TABLE IF EXISTS t1, t2, t3; 118--enable_warnings 119 120CREATE TABLE t1 ( a INT ); 121CREATE TABLE t2 ( a INT ); 122CREATE TABLE t3 ( a INT ); 123INSERT INTO t1 VALUES (1), (2), (3); 124INSERT INTO t2 VALUES (1), (2), (3); 125INSERT INTO t3 VALUES (1), (2), (3); 126 127--echo # We simulate importing a trigger from 5.0 by writing a .TRN file for 128--echo # each trigger plus a .TRG file the way MySQL 5.0 would have done it, 129--echo # with syntax allowed in 5.0 only. 130--echo # 131--echo # Note that in 5.0 the following lines are missing from t1.TRG: 132--echo # 133--echo # client_cs_names='latin1' 134--echo # connection_cl_names='latin1_swedish_ci' 135--echo # db_cl_names='latin1_swedish_ci' 136 137--write_file $MYSQLD_DATADIR/test/tr11.TRN 138TYPE=TRIGGERNAME 139trigger_table=t1 140EOF 141 142--write_file $MYSQLD_DATADIR/test/tr12.TRN 143TYPE=TRIGGERNAME 144trigger_table=t1 145EOF 146 147--write_file $MYSQLD_DATADIR/test/tr13.TRN 148TYPE=TRIGGERNAME 149trigger_table=t1 150EOF 151 152--write_file $MYSQLD_DATADIR/test/tr14.TRN 153TYPE=TRIGGERNAME 154trigger_table=t1 155EOF 156 157--write_file $MYSQLD_DATADIR/test/tr15.TRN 158TYPE=TRIGGERNAME 159trigger_table=t1 160EOF 161 162--write_file $MYSQLD_DATADIR/test/t1.TRG 163TYPE=TRIGGERS 164triggers='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' 165sql_modes=0 0 0 0 0 166definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 167EOF 168 169--write_file $MYSQLD_DATADIR/test/t2.TRG 170TYPE=TRIGGERS 171triggers='Not allowed syntax here, and trigger name cant be extracted either.' 172sql_modes=0 173definers='root@localhost' 174EOF 175 176FLUSH TABLE t1; 177FLUSH TABLE t2; 178 179--echo # We will get parse errors for most DDL and DML statements when the table 180--echo # has broken triggers. The parse error refers to the first broken 181--echo # trigger. 182--error ER_PARSE_ERROR 183CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1); 184--error ER_PARSE_ERROR 185CREATE TRIGGER tr22 BEFORE INSERT ON t2 FOR EACH ROW DELETE FROM non_existing_table; 186--replace_column 6 # 187SHOW TRIGGERS; 188--error ER_PARSE_ERROR 189INSERT INTO t1 VALUES (1); 190--error ER_PARSE_ERROR 191INSERT INTO t2 VALUES (1); 192--error ER_PARSE_ERROR 193DELETE FROM t1; 194--error ER_PARSE_ERROR 195UPDATE t1 SET a = 1 WHERE a = 1; 196SELECT * FROM t1; 197--error ER_PARSE_ERROR 198RENAME TABLE t1 TO t1_2; 199--replace_column 6 # 200SHOW TRIGGERS; 201 202DROP TRIGGER tr11; 203DROP TRIGGER tr12; 204DROP TRIGGER tr13; 205DROP TRIGGER tr14; 206DROP TRIGGER tr15; 207 208--replace_column 6 # 209SHOW TRIGGERS; 210 211--echo # Make sure there is no trigger file left. 212--list_files $MYSQLD_DATADIR/test/ tr* 213 214--echo # We write the same trigger files one more time to test DROP TABLE. 215--write_file $MYSQLD_DATADIR/test/tr11.TRN 216TYPE=TRIGGERNAME 217trigger_table=t1 218EOF 219 220--write_file $MYSQLD_DATADIR/test/tr12.TRN 221TYPE=TRIGGERNAME 222trigger_table=t1 223EOF 224 225--write_file $MYSQLD_DATADIR/test/tr13.TRN 226TYPE=TRIGGERNAME 227trigger_table=t1 228EOF 229 230--write_file $MYSQLD_DATADIR/test/tr14.TRN 231TYPE=TRIGGERNAME 232trigger_table=t1 233EOF 234 235--write_file $MYSQLD_DATADIR/test/tr15.TRN 236TYPE=TRIGGERNAME 237trigger_table=t1 238EOF 239 240--write_file $MYSQLD_DATADIR/test/t1.TRG 241TYPE=TRIGGERS 242triggers='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' 243sql_modes=0 0 0 0 0 244definers='root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 'root@localhost' 245EOF 246 247FLUSH TABLE t1; 248FLUSH TABLE t2; 249 250DROP TABLE t1; 251DROP TABLE t2; 252DROP TABLE t3; 253 254--echo # Make sure there is no trigger file left. 255 256--list_files $MYSQLD_DATADIR/test/ tr* 257 258CREATE TABLE t1 ( a INT ); 259CREATE TABLE t2 ( a INT ); 260INSERT INTO t1 VALUES (1), (2), (3); 261INSERT INTO t2 VALUES (1), (2), (3); 262 263--echo # We write three trigger files. First trigger is syntaxically incorrect, next trigger is correct 264--echo # and last trigger is broken. 265--echo # Next we try to execute SHOW CREATE TRIGGER command for broken trigger and then try to drop one. 266--write_file $MYSQLD_DATADIR/test/tr11.TRN 267TYPE=TRIGGERNAME 268trigger_table=t1 269EOF 270 271--write_file $MYSQLD_DATADIR/test/tr12.TRN 272TYPE=TRIGGERNAME 273trigger_table=t1 274EOF 275 276--write_file $MYSQLD_DATADIR/test/t1.TRG 277TYPE=TRIGGERS 278triggers='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' 279sql_modes=0 0 0 280definers='root@localhost' 'root@localhost' 'root@localhost' 281EOF 282 283FLUSH TABLE t1; 284 285SHOW CREATE TRIGGER tr12; 286SHOW CREATE TRIGGER tr11; 287DROP TRIGGER tr12; 288DROP TRIGGER tr11; 289 290DROP TABLE t1; 291DROP TABLE t2; 292