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