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 293 294--echo # 295--echo # MDEV-25659 trigger name is empty after upgrade to 10.4 296--echo # 297 298--echo # START: Total triggers 1, broken triggers 1, DROP TABLE 299 300CREATE TABLE t1 (a INT); 301INSERT INTO t1 VALUES (1); 302 303--write_file $MYSQLD_DATADIR/test/tr1.TRN 304TYPE=TRIGGERNAME 305trigger_table=t1 306EOF 307 308--write_file $MYSQLD_DATADIR/test/t1.TRG 309TYPE=TRIGGERS 310triggers='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' 311sql_modes=1411383296 312definers='root@localhost' 313client_cs_names='utf8' 314connection_cl_names='utf8_general_ci' 315db_cl_names='latin1_swedish_ci' 316created=164206218647 317EOF 318 319FLUSH TABLES; 320--error ER_PARSE_ERROR 321DELETE FROM t1 WHERE a=1; 322--error ER_PARSE_ERROR 323INSERT INTO t1 VALUES (2); 324 325SET time_zone='+00:00'; 326--vertical_results 327SHOW TRIGGERS LIKE 't1'; 328SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1'; 329--horizontal_results 330SET time_zone=DEFAULT; 331 332--echo # Listing trigger files 333--list_files $MYSQLD_DATADIR/test *.TR? 334--echo # Listing trigger files done 335 336DROP TABLE t1; 337 338--echo # Listing trigger files 339--list_files $MYSQLD_DATADIR/test *.TR? 340--echo # Listing trigger files done 341 342--echo # END: Total triggers 1, broken triggers 1, DROP TABLE 343 344 345--echo # START: Total triggers 1, broken triggers 1, DROP TRIGGER 346 347CREATE TABLE t1 (a INT); 348INSERT INTO t1 VALUES (1); 349 350--write_file $MYSQLD_DATADIR/test/tr1.TRN 351TYPE=TRIGGERNAME 352trigger_table=t1 353EOF 354 355--write_file $MYSQLD_DATADIR/test/t1.TRG 356TYPE=TRIGGERS 357triggers='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' 358sql_modes=1411383296 359definers='root@localhost' 360client_cs_names='utf8' 361connection_cl_names='utf8_general_ci' 362db_cl_names='latin1_swedish_ci' 363created=164206218647 364EOF 365 366FLUSH TABLES; 367--error ER_PARSE_ERROR 368DELETE FROM t1 WHERE a=1; 369--error ER_PARSE_ERROR 370INSERT INTO t1 VALUES (2); 371 372SET time_zone='+00:00'; 373--vertical_results 374SHOW TRIGGERS LIKE 't1'; 375SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1'; 376--horizontal_results 377SET time_zone=DEFAULT; 378 379--echo # Listing trigger files 380--list_files $MYSQLD_DATADIR/test *.TR? 381--echo # Listing trigger files done 382 383DROP TRIGGER tr1; 384 385--echo # Listing trigger files 386--list_files $MYSQLD_DATADIR/test *.TR? 387--echo # Listing trigger files done 388 389DROP TABLE t1; 390 391--echo # END: Total triggers 1, broken triggers 1, DROP TRIGGER 392 393 394--echo # START: Total triggers 2, broken triggers 1, DROP TABLE 395 396CREATE TABLE t1 (a INT); 397INSERT INTO t1 VALUES (1); 398 399--write_file $MYSQLD_DATADIR/test/tr1.TRN 400TYPE=TRIGGERNAME 401trigger_table=t1 402EOF 403 404--write_file $MYSQLD_DATADIR/test/tr2.TRN 405TYPE=TRIGGERNAME 406trigger_table=t1 407EOF 408 409--write_file $MYSQLD_DATADIR/test/t1.TRG 410TYPE=TRIGGERS 411triggers='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' 412sql_modes=1411383296 1411383296 413definers='root@localhost' 'root@localhost' 414client_cs_names='utf8' 'utf8' 415connection_cl_names='utf8_general_ci' 'utf8_general_ci' 416db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' 417created=164206810874 164206810873 418EOF 419 420FLUSH TABLES; 421--error ER_PARSE_ERROR 422DELETE FROM t1 WHERE a=1; 423--error ER_PARSE_ERROR 424INSERT INTO t1 VALUES (2); 425 426SET time_zone='+00:00'; 427--vertical_results 428SHOW TRIGGERS LIKE 't1'; 429SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1'; 430--horizontal_results 431SET time_zone=DEFAULT; 432 433--echo # Listing trigger files 434--list_files $MYSQLD_DATADIR/test *.TR? 435--echo # Listing trigger files done 436 437DROP TABLE t1; 438 439--echo # Listing trigger files 440--list_files $MYSQLD_DATADIR/test *.TR? 441--echo # Listing trigger files done 442 443--echo # END: Total triggers 2, broken triggers 1, using DROP TABLE 444 445 446--echo # START: Total triggers 2, broken triggers 1, DROP TRIGGER 447 448CREATE TABLE t1 (a INT); 449INSERT INTO t1 VALUES (1); 450 451--write_file $MYSQLD_DATADIR/test/tr1.TRN 452TYPE=TRIGGERNAME 453trigger_table=t1 454EOF 455 456--write_file $MYSQLD_DATADIR/test/tr2.TRN 457TYPE=TRIGGERNAME 458trigger_table=t1 459EOF 460 461--write_file $MYSQLD_DATADIR/test/t1.TRG 462TYPE=TRIGGERS 463triggers='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' 464sql_modes=1411383296 1411383296 465definers='root@localhost' 'root@localhost' 466client_cs_names='utf8' 'utf8' 467connection_cl_names='utf8_general_ci' 'utf8_general_ci' 468db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' 469created=164206810874 164206810873 470EOF 471 472FLUSH TABLES; 473--error ER_PARSE_ERROR 474DELETE FROM t1 WHERE a=1; 475--error ER_PARSE_ERROR 476INSERT INTO t1 VALUES (2); 477 478SET time_zone='+00:00'; 479--vertical_results 480SHOW TRIGGERS LIKE 't1'; 481SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='tr1'; 482--horizontal_results 483SET time_zone=DEFAULT; 484 485--echo # Listing trigger files 486--list_files $MYSQLD_DATADIR/test *.TR? 487--echo # Listing trigger files done 488 489DROP TRIGGER tr1; 490 491--echo # Listing trigger files 492--list_files $MYSQLD_DATADIR/test *.TR? 493--echo # Listing trigger files done 494 495# Now we dropped the broken trigger. Make sure the good one is fired. 496# If everything goes as expected, it will try to insert into t2, 497# which does not exists, hence the (expected) error. 498--error ER_NO_SUCH_TABLE 499INSERT INTO t1 VALUES (100); 500 501DROP TABLE t1; 502 503--echo # Listing trigger files 504--list_files $MYSQLD_DATADIR/test *.TR? 505--echo # Listing trigger files done 506 507--echo # END: Total triggers 2, broken triggers 1, using DROP TRIGGER 508