Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'TRIGGERS';
2 Tables_in_information_schema (TRIGGERS)
3 TRIGGERS
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.TRIGGERS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TRIGGERS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.TRIGGERS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.TRIGGERS;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.12.1: INFORMATION_SCHEMA.TRIGGERS layout
28 #########################################################################
29 DESCRIBE information_schema.TRIGGERS;
30 Field Type Null Key Default Extra
31 TRIGGER_CATALOG varchar(64) YES NULL
32 TRIGGER_SCHEMA varchar(64) YES NULL
33 TRIGGER_NAME varchar(64) NO NULL
34 EVENT_MANIPULATION enum('INSERT','UPDATE','DELETE') NO NULL
35 EVENT_OBJECT_CATALOG varchar(64) YES NULL
36 EVENT_OBJECT_SCHEMA varchar(64) YES NULL
37 EVENT_OBJECT_TABLE varchar(64) YES NULL
38 ACTION_ORDER int unsigned NO NULL
39 ACTION_CONDITION binary(0) YES NULL
40 ACTION_STATEMENT longtext NO NULL
41 ACTION_ORIENTATION varchar(3) NO
42 ACTION_TIMING enum('BEFORE','AFTER') NO NULL
43 ACTION_REFERENCE_OLD_TABLE binary(0) YES NULL
44 ACTION_REFERENCE_NEW_TABLE binary(0) YES NULL
45 ACTION_REFERENCE_OLD_ROW varchar(3) NO
46 ACTION_REFERENCE_NEW_ROW varchar(3) NO
47 CREATED timestamp(2) NO NULL
48 SQL_MODE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NO NULL
49 DEFINER varchar(288) NO NULL
50 CHARACTER_SET_CLIENT varchar(64) NO NULL
51 COLLATION_CONNECTION varchar(64) NO NULL
52 DATABASE_COLLATION varchar(64) NO NULL
53 SHOW CREATE TABLE information_schema.TRIGGERS;
54 View Create View character_set_client collation_connection
55 TRIGGERS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TRIGGERS` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `TRIGGER_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TRIGGER_SCHEMA`,`trg`.`name` AS `TRIGGER_NAME`,`trg`.`event_type` AS `EVENT_MANIPULATION`,(`cat`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_SCHEMA`,(`tbl`.`name` collate utf8mb3_tolower_ci) AS `EVENT_OBJECT_TABLE`,`trg`.`action_order` AS `ACTION_ORDER`,NULL AS `ACTION_CONDITION`,`trg`.`action_statement_utf8` AS `ACTION_STATEMENT`,'ROW' AS `ACTION_ORIENTATION`,`trg`.`action_timing` AS `ACTION_TIMING`,NULL AS `ACTION_REFERENCE_OLD_TABLE`,NULL AS `ACTION_REFERENCE_NEW_TABLE`,'OLD' AS `ACTION_REFERENCE_OLD_ROW`,'NEW' AS `ACTION_REFERENCE_NEW_ROW`,`trg`.`created` AS `CREATED`,`trg`.`sql_mode` AS `SQL_MODE`,`trg`.`definer` AS `DEFINER`,`cs_client`.`name` AS `CHARACTER_SET_CLIENT`,`coll_conn`.`name` AS `COLLATION_CONNECTION`,`coll_db`.`name` AS `DATABASE_COLLATION` from (((((((`mysql`.`triggers` `trg` join `mysql`.`tables` `tbl` on((`tbl`.`id` = `trg`.`table_id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll_client` on((`coll_client`.`id` = `trg`.`client_collation_id`))) join `mysql`.`character_sets` `cs_client` on((`cs_client`.`id` = `coll_client`.`character_set_id`))) join `mysql`.`collations` `coll_conn` on((`coll_conn`.`id` = `trg`.`connection_collation_id`))) join `mysql`.`collations` `coll_db` on((`coll_db`.`id` = `trg`.`schema_collation_id`))) where ((`tbl`.`type` <> 'VIEW') and (0 <> can_access_trigger(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) utf8mb3 utf8mb3_general_ci
56 SHOW COLUMNS FROM information_schema.TRIGGERS;
57 Field Type Null Key Default Extra
58 TRIGGER_CATALOG varchar(64) YES NULL
59 TRIGGER_SCHEMA varchar(64) YES NULL
60 TRIGGER_NAME varchar(64) NO NULL
61 EVENT_MANIPULATION enum('INSERT','UPDATE','DELETE') NO NULL
62 EVENT_OBJECT_CATALOG varchar(64) YES NULL
63 EVENT_OBJECT_SCHEMA varchar(64) YES NULL
64 EVENT_OBJECT_TABLE varchar(64) YES NULL
65 ACTION_ORDER int unsigned NO NULL
66 ACTION_CONDITION binary(0) YES NULL
67 ACTION_STATEMENT longtext NO NULL
68 ACTION_ORIENTATION varchar(3) NO
69 ACTION_TIMING enum('BEFORE','AFTER') NO NULL
70 ACTION_REFERENCE_OLD_TABLE binary(0) YES NULL
71 ACTION_REFERENCE_NEW_TABLE binary(0) YES NULL
72 ACTION_REFERENCE_OLD_ROW varchar(3) NO
73 ACTION_REFERENCE_NEW_ROW varchar(3) NO
74 CREATED timestamp(2) NO NULL
75 SQL_MODE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NO NULL
76 DEFINER varchar(288) NO NULL
77 CHARACTER_SET_CLIENT varchar(64) NO NULL
78 COLLATION_CONNECTION varchar(64) NO NULL
79 DATABASE_COLLATION varchar(64) NO NULL
80 SELECT * FROM information_schema.triggers
81 WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
82 OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
83 OR action_reference_new_table IS NOT NULL
84 ORDER BY TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME;
85 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
86 def mtr gs_insert INSERT def mtr global_suppressions 1 NULL BEGIN
87 DECLARE dummy INT;
88 SET GLOBAL regexp_time_limit = 0;
89 SELECT "" REGEXP NEW.pattern INTO dummy;
90 SET GLOBAL regexp_time_limit = DEFAULT;
91 END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_0900_ai_ci
92 def mtr ts_insert INSERT def mtr test_suppressions 1 NULL BEGIN
93 DECLARE dummy INT;
94 SET GLOBAL regexp_time_limit = 0;
95 SELECT "" REGEXP NEW.pattern INTO dummy;
96 SET GLOBAL regexp_time_limit = DEFAULT;
97 END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_0900_ai_ci
98 def sys sys_config_insert_set_user INSERT def sys sys_config 1 NULL BEGIN
99 IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
100 SET NEW.set_by = USER();
101 END IF;
102 END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION mysql.sys@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
103 def sys sys_config_update_set_user UPDATE def sys sys_config 1 NULL BEGIN
104 IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
105 SET NEW.set_by = USER();
106 END IF;
107 END ROW BEFORE NULL NULL OLD NEW # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION mysql.sys@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
108 ##################################################################################
109 # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
110 ##################################################################################
111 DROP DATABASE IF EXISTS db_datadict;
112 CREATE DATABASE db_datadict charset latin1;
113 DROP USER 'testuser1'@'localhost';
114 CREATE USER 'testuser1'@'localhost';
115 DROP USER 'testuser2'@'localhost';
116 CREATE USER 'testuser2'@'localhost';
117 DROP USER 'testuser3'@'localhost';
118 CREATE USER 'testuser3'@'localhost';
119 DROP USER 'testuser4'@'localhost';
120 CREATE USER 'testuser4'@'localhost';
121 GRANT TRIGGER ON *.* TO 'testuser1'@'localhost';
122 GRANT TRIGGER ON *.* TO 'testuser3'@'localhost';
123 GRANT TRIGGER ON *.* TO 'testuser4'@'localhost';
124 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
125 # Establish connection testuser1 (user=testuser1)
126 CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
127 ENGINE = <engine_type>;
128 CREATE TRIGGER trg1 BEFORE INSERT
129 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
130 GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
131 REVOKE TRIGGER ON db_datadict.t1 FROM 'testuser2'@'localhost';
132 GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
133 SELECT * FROM information_schema.triggers
134 WHERE trigger_name = 'trg1';
135 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
136 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 # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
137 SHOW TRIGGERS FROM db_datadict;
138 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
139 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
140 # Establish connection testuser2 (user=testuser2)
141 SHOW GRANTS FOR 'testuser2'@'localhost';
142 Grants for testuser2@localhost
143 GRANT USAGE ON *.* TO `testuser2`@`localhost`
144 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `db_datadict`.`t1` TO `testuser2`@`localhost`
145 # No TRIGGER Privilege --> no result for query
146 SELECT * FROM information_schema.triggers
147 WHERE trigger_name = 'trg1';
148 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
149 SHOW TRIGGERS FROM db_datadict;
150 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
151 # Establish connection testuser3 (user=testuser3)
152 SHOW GRANTS FOR 'testuser3'@'localhost';
153 Grants for testuser3@localhost
154 GRANT TRIGGER ON *.* TO `testuser3`@`localhost`
155 GRANT SELECT ON `db_datadict`.`t1` TO `testuser3`@`localhost`
156 # TRIGGER Privilege + SELECT Privilege on t1 --> result for query
157 SELECT * FROM information_schema.triggers
158 WHERE trigger_name = 'trg1';
159 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
160 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 # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
161 SHOW TRIGGERS FROM db_datadict;
162 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
163 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
164 # Establish connection testuser4 (user=testuser4)
165 SHOW GRANTS FOR 'testuser4'@'localhost';
166 Grants for testuser4@localhost
167 GRANT TRIGGER ON *.* TO `testuser4`@`localhost`
168 # TRIGGER Privilege + no SELECT Privilege on t1 --> result for query
169 SELECT * FROM db_datadict.t1;
170 ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1'
171 DESC db_datadict.t1;
172 ERROR 42000: SELECT command denied to user 'testuser4'@'localhost' for table 't1'
173 SELECT * FROM information_schema.triggers
174 WHERE trigger_name = 'trg1';
175 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
176 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 # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
177 SHOW TRIGGERS FROM db_datadict;
178 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
179 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
180 # Switch to connection default and close connections testuser1 - testuser4
181 SELECT * FROM information_schema.triggers
182 WHERE trigger_name = 'trg1';
183 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
184 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 # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
185 SHOW TRIGGERS FROM db_datadict;
186 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
187 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION testuser1@localhost utf8mb4 utf8mb4_0900_ai_ci latin1_swedish_ci
188 DROP USER 'testuser1'@'localhost';
189 Warnings:
190 Warning 4005 User 'testuser1'@'localhost' is referenced as a definer account in a trigger.
191 DROP USER 'testuser2'@'localhost';
192 DROP USER 'testuser3'@'localhost';
193 DROP USER 'testuser4'@'localhost';
194 DROP DATABASE db_datadict;
195 #########################################################################
196 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
197 #########################################################################
198 ########################################################################
199 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
200 # DDL on INFORMATION_SCHEMA tables are not supported
201 ########################################################################
202 DROP DATABASE IF EXISTS db_datadict;
203 CREATE DATABASE db_datadict charset latin1;
204 CREATE TABLE db_datadict.t1 (f1 BIGINT)
205 ENGINE = <engine_type>;
206 CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
207 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
208 INSERT INTO information_schema.triggers
209 SELECT * FROM information_schema.triggers;
210 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
211 UPDATE information_schema.triggers SET trigger_schema = 'test'
212 WHERE table_name = 't1';
213 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
214 DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
215 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
216 TRUNCATE information_schema.triggers;
217 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
218 CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
219 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
220 ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
221 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
222 ALTER TABLE information_schema.triggers ADD f1 INT;
223 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
224 DROP TABLE information_schema.triggers;
225 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
226 ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
227 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
228 ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
229 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
230 DROP DATABASE db_datadict;