Debian Codegraph @

mysql-test/suite/funcs_1/r/is_triggers_embedded.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 # No TRIGGER Privilege --> no result for query
148 SELECT * FROM information_schema.triggers
149 WHERE trigger_name = 'trg1';
150 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
151 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
152 SHOW TRIGGERS FROM db_datadict;
153 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
154 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
155 connect testuser3, localhost, testuser3, , test;
156 SHOW GRANTS FOR 'testuser3'@'localhost';
157 # TRIGGER Privilege + SELECT Privilege on t1 --> result for query
158 SELECT * FROM information_schema.triggers
159 WHERE trigger_name = 'trg1';
160 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
161 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
162 SHOW TRIGGERS FROM db_datadict;
163 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
164 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
165 connect testuser4, localhost, testuser4, , test;
166 SHOW GRANTS FOR 'testuser4'@'localhost';
167 # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
168 SELECT * FROM db_datadict.t1;
169 f1 f2 f3
170 DESC db_datadict.t1;
171 Field Type Null Key Default Extra
172 f1 int(11) YES NULL
173 f2 int(11) YES NULL
174 f3 int(11) YES NULL
175 SELECT * FROM information_schema.triggers
176 WHERE trigger_name = 'trg1';
177 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
178 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
179 SHOW TRIGGERS FROM db_datadict;
180 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
181 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
182 connection default;
183 disconnect testuser1;
184 disconnect testuser2;
185 disconnect testuser3;
186 disconnect testuser4;
187 SELECT * FROM information_schema.triggers
188 WHERE trigger_name = 'trg1';
189 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
190 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
191 SHOW TRIGGERS FROM db_datadict;
192 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
193 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # testuser1@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
194 DROP USER 'testuser1'@'localhost';
195 DROP USER 'testuser2'@'localhost';
196 DROP USER 'testuser3'@'localhost';
197 DROP USER 'testuser4'@'localhost';
198 DROP DATABASE db_datadict;
199 #########################################################################
200 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
201 #########################################################################
202 ########################################################################
203 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
204 # DDL on INFORMATION_SCHEMA tables are not supported
205 ########################################################################
206 DROP DATABASE IF EXISTS db_datadict;
207 CREATE DATABASE db_datadict;
208 CREATE TABLE db_datadict.t1 (f1 BIGINT)
209 ENGINE = <engine_type>;
210 CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
211 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
212 INSERT INTO information_schema.triggers
213 SELECT * FROM information_schema.triggers;
214 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
215 UPDATE information_schema.triggers SET trigger_schema = 'test'
216 WHERE table_name = 't1';
217 Got one of the listed errors
218 DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
219 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
220 TRUNCATE information_schema.triggers;
221 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
222 CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
223 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
224 ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
225 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
226 ALTER TABLE information_schema.triggers ADD f1 INT;
227 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
228 DROP TABLE information_schema.triggers;
229 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
230 ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
231 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
232 ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
233 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
234 DROP DATABASE db_datadict;
235 set global sql_mode=@save_sql_mode;