Debian Codegraph @

mysql-test/suite/funcs_1/r/is_triggers.result

raw
1 set @save_sql_mode=@@global.sql_mode;
2 set global sql_mode="";
3 SHOW TABLES FROM information_schema LIKE 'TRIGGERS';
4 Tables_in_information_schema (TRIGGERS)
5 TRIGGERS
6 #######################################################################
7 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
8 #######################################################################
9 DROP VIEW IF EXISTS test.v1;
10 DROP PROCEDURE IF EXISTS test.p1;
11 DROP FUNCTION IF EXISTS test.f1;
12 CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS;
13 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS;
14 CREATE FUNCTION test.f1() returns BIGINT
15 BEGIN
16 DECLARE counter BIGINT DEFAULT NULL;
17 SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS;
18 RETURN counter;
19 END//
20 # Attention: The printing of the next result sets is disabled.
21 SELECT * FROM information_schema.TRIGGERS;
22 SELECT * FROM test.v1;
23 CALL test.p1;
24 SELECT test.f1();
25 DROP VIEW test.v1;
26 DROP PROCEDURE test.p1;
27 DROP FUNCTION test.f1;
28 #########################################################################
29 # Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
30 #########################################################################
31 DESCRIBE information_schema.TRIGGERS;
32 Field Type Null Key Default Extra
33 TRIGGER_CATALOG varchar(512) NO NULL
34 TRIGGER_SCHEMA varchar(64) NO NULL
35 TRIGGER_NAME varchar(64) NO NULL
36 EVENT_MANIPULATION varchar(6) NO NULL
37 EVENT_OBJECT_CATALOG varchar(512) NO NULL
38 EVENT_OBJECT_SCHEMA varchar(64) NO NULL
39 EVENT_OBJECT_TABLE varchar(64) NO NULL
40 ACTION_ORDER bigint(4) NO NULL
41 ACTION_CONDITION longtext YES NULL
42 ACTION_STATEMENT longtext YES NULL
43 ACTION_ORIENTATION varchar(9) NO NULL
44 ACTION_TIMING varchar(6) NO NULL
45 ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
46 ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
47 ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL
48 ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL
49 CREATED datetime(2) YES NULL
50 SQL_MODE varchar(8192) NO NULL
51 DEFINER varchar(384) YES NULL
52 CHARACTER_SET_CLIENT varchar(32) NO NULL
53 COLLATION_CONNECTION varchar(64) NO NULL
54 DATABASE_COLLATION varchar(64) NO NULL
55 SHOW CREATE TABLE information_schema.TRIGGERS;
56 Table Create Table
57 TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` (
58 `TRIGGER_CATALOG` varchar(512) NOT NULL,
59 `TRIGGER_SCHEMA` varchar(64) NOT NULL,
60 `TRIGGER_NAME` varchar(64) NOT NULL,
61 `EVENT_MANIPULATION` varchar(6) NOT NULL,
62 `EVENT_OBJECT_CATALOG` varchar(512) NOT NULL,
63 `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL,
64 `EVENT_OBJECT_TABLE` varchar(64) NOT NULL,
65 `ACTION_ORDER` bigint(4) NOT NULL,
66 `ACTION_CONDITION` longtext,
67 `ACTION_STATEMENT` longtext,
68 `ACTION_ORIENTATION` varchar(9) NOT NULL,
69 `ACTION_TIMING` varchar(6) NOT NULL,
70 `ACTION_REFERENCE_OLD_TABLE` varchar(64),
71 `ACTION_REFERENCE_NEW_TABLE` varchar(64),
72 `ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL,
73 `ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL,
74 `CREATED` datetime(2),
75 `SQL_MODE` varchar(8192) NOT NULL,
76 `DEFINER` varchar(384),
77 `CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
78 `COLLATION_CONNECTION` varchar(64) NOT NULL,
79 `DATABASE_COLLATION` varchar(64) NOT NULL
80 ) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
81 SHOW COLUMNS FROM information_schema.TRIGGERS;
82 Field Type Null Key Default Extra
83 TRIGGER_CATALOG varchar(512) NO NULL
84 TRIGGER_SCHEMA varchar(64) NO NULL
85 TRIGGER_NAME varchar(64) NO NULL
86 EVENT_MANIPULATION varchar(6) NO NULL
87 EVENT_OBJECT_CATALOG varchar(512) NO NULL
88 EVENT_OBJECT_SCHEMA varchar(64) NO NULL
89 EVENT_OBJECT_TABLE varchar(64) NO NULL
90 ACTION_ORDER bigint(4) NO NULL
91 ACTION_CONDITION longtext YES NULL
92 ACTION_STATEMENT longtext YES NULL
93 ACTION_ORIENTATION varchar(9) NO NULL
94 ACTION_TIMING varchar(6) NO NULL
95 ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
96 ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
97 ACTION_REFERENCE_OLD_ROW varchar(3) NO NULL
98 ACTION_REFERENCE_NEW_ROW varchar(3) NO NULL
99 CREATED datetime(2) YES NULL
100 SQL_MODE varchar(8192) NO NULL
101 DEFINER varchar(384) YES NULL
102 CHARACTER_SET_CLIENT varchar(32) NO NULL
103 COLLATION_CONNECTION varchar(64) NO NULL
104 DATABASE_COLLATION varchar(64) NO NULL
105 SELECT * FROM information_schema.triggers
106 WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
107 OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
108 OR action_reference_new_table IS NOT NULL
109 ORDER BY trigger_schema, trigger_name;
110 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
111 def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
112 def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN DECLARE dummy INT; SELECT "" REGEXP NEW.pattern INTO dummy; END ROW BEFORE NULL NULL OLD NEW # root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
113 ##################################################################################
114 # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
115 ##################################################################################
116 DROP DATABASE IF EXISTS db_datadict;
117 CREATE DATABASE db_datadict;
118 DROP USER 'testuser1'@'localhost';
119 CREATE USER 'testuser1'@'localhost';
120 DROP USER 'testuser2'@'localhost';
121 CREATE USER 'testuser2'@'localhost';
122 DROP USER 'testuser3'@'localhost';
123 CREATE USER 'testuser3'@'localhost';
124 DROP USER 'testuser4'@'localhost';
125 CREATE USER 'testuser4'@'localhost';
126 GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
127 GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
128 GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
129 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
130 connect testuser1, localhost, testuser1, , db_datadict;
131 CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
132 ENGINE = <engine_type>;
133 CREATE TRIGGER trg1 BEFORE INSERT
134 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
135 GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
136 REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
137 GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
138 SELECT * FROM information_schema.triggers
139 WHERE trigger_name = 'trg1';
140 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
141 def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
142 SHOW TRIGGERS FROM db_datadict;
143 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
144 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
145 connect testuser2, localhost, testuser2, , db_datadict;
146 SHOW GRANTS FOR 'testuser2'@'localhost';
147 Grants for testuser2@localhost
148 GRANT USAGE ON *.* TO `testuser2`@`localhost`
149 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, DELETE HISTORY ON `db_datadict`.`t1` TO `testuser2`@`localhost`
150 # No TRIGGER Privilege --> no result for query
151 SELECT * FROM information_schema.triggers
152 WHERE trigger_name = 'trg1';
153 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
154 def db_datadict trg1 INSERT def db_datadict t1 1 NULL NULL ROW BEFORE NULL NULL OLD NEW # NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
155 SHOW TRIGGERS FROM db_datadict;
156 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
157 trg1 INSERT t1 NULL BEFORE # NULL latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
158 connect testuser3, localhost, testuser3, , test;
159 SHOW GRANTS FOR 'testuser3'@'localhost';
160 Grants for testuser3@localhost
161 GRANT TRIGGER ON *.* TO `testuser3`@`localhost`
162 GRANT SELECT ON `db_datadict`.`t1` TO `testuser3`@`localhost`
163 # TRIGGER Privilege + SELECT Privilege on t1 --> result for query
164 SELECT * FROM information_schema.triggers
165 WHERE trigger_name = 'trg1';
166 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
167 def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
168 SHOW TRIGGERS FROM db_datadict;
169 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
170 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
171 connect testuser4, localhost, testuser4, , test;
172 SHOW GRANTS FOR 'testuser4'@'localhost';
173 Grants for testuser4@localhost
174 GRANT TRIGGER ON *.* TO `testuser4`@`localhost`
175 # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
176 SELECT * FROM db_datadict.t1;
177 ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table `db_datadict`.`t1`
178 DESC db_datadict.t1;
179 ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table `db_datadict`.`t1`
180 SELECT * FROM information_schema.triggers
181 WHERE trigger_name = 'trg1';
182 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
183 def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
184 SHOW TRIGGERS FROM db_datadict;
185 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
186 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
187 connection default;
188 disconnect testuser1;
189 disconnect testuser2;
190 disconnect testuser3;
191 disconnect testuser4;
192 SELECT * FROM information_schema.triggers
193 WHERE trigger_name = 'trg1';
194 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
195 def db_datadict trg1 INSERT def db_datadict t1 1 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
196 SHOW TRIGGERS FROM db_datadict;
197 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
198 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
199 DROP USER 'testuser1'@'localhost';
200 DROP USER 'testuser2'@'localhost';
201 DROP USER 'testuser3'@'localhost';
202 DROP USER 'testuser4'@'localhost';
203 DROP DATABASE db_datadict;
204 #########################################################################
205 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
206 #########################################################################
207 ########################################################################
208 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
209 # DDL on INFORMATION_SCHEMA tables are not supported
210 ########################################################################
211 DROP DATABASE IF EXISTS db_datadict;
212 CREATE DATABASE db_datadict;
213 CREATE TABLE db_datadict.t1 (f1 BIGINT)
214 ENGINE = <engine_type>;
215 CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
216 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
217 INSERT INTO information_schema.triggers
218 SELECT * FROM information_schema.triggers;
219 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
220 UPDATE information_schema.triggers SET trigger_schema = 'test'
221 WHERE table_name = 't1';
222 Got one of the listed errors
223 DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
224 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
225 TRUNCATE information_schema.triggers;
226 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
227 CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
228 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
229 ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
230 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
231 ALTER TABLE information_schema.triggers ADD f1 INT;
232 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
233 DROP TABLE information_schema.triggers;
234 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
235 ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
236 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
237 ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
238 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
239 DROP DATABASE db_datadict;
240 set global sql_mode=@save_sql_mode;