[tomato.git] / release / src / router / mysql / mysql-test / suite / funcs_1 / r / is_triggers_embedded.result
| blob | 5e0c7601da702c901f25948375f29b7c35451172 |
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(512) YES NULL
32 TRIGGER_SCHEMA varchar(64) NO
33 TRIGGER_NAME varchar(64) NO
34 EVENT_MANIPULATION varchar(6) NO
35 EVENT_OBJECT_CATALOG varchar(512) YES NULL
36 EVENT_OBJECT_SCHEMA varchar(64) NO
37 EVENT_OBJECT_TABLE varchar(64) NO
38 ACTION_ORDER bigint(4) NO 0
39 ACTION_CONDITION longtext YES NULL
40 ACTION_STATEMENT longtext NO NULL
41 ACTION_ORIENTATION varchar(9) NO
42 ACTION_TIMING varchar(6) NO
43 ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
44 ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
45 ACTION_REFERENCE_OLD_ROW varchar(3) NO
46 ACTION_REFERENCE_NEW_ROW varchar(3) NO
47 CREATED datetime YES NULL
48 SQL_MODE longtext NO NULL
49 DEFINER longtext NO NULL
50 SHOW CREATE TABLE information_schema.TRIGGERS;
51 Table Create Table
52 TRIGGERS CREATE TEMPORARY TABLE `TRIGGERS` (
53 `TRIGGER_CATALOG` varchar(512) default NULL,
54 `TRIGGER_SCHEMA` varchar(64) NOT NULL default '',
55 `TRIGGER_NAME` varchar(64) NOT NULL default '',
56 `EVENT_MANIPULATION` varchar(6) NOT NULL default '',
57 `EVENT_OBJECT_CATALOG` varchar(512) default NULL,
58 `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULL default '',
59 `EVENT_OBJECT_TABLE` varchar(64) NOT NULL default '',
60 `ACTION_ORDER` bigint(4) NOT NULL default '0',
61 `ACTION_CONDITION` longtext,
62 `ACTION_STATEMENT` longtext NOT NULL,
63 `ACTION_ORIENTATION` varchar(9) NOT NULL default '',
64 `ACTION_TIMING` varchar(6) NOT NULL default '',
65 `ACTION_REFERENCE_OLD_TABLE` varchar(64) default NULL,
66 `ACTION_REFERENCE_NEW_TABLE` varchar(64) default NULL,
67 `ACTION_REFERENCE_OLD_ROW` varchar(3) NOT NULL default '',
68 `ACTION_REFERENCE_NEW_ROW` varchar(3) NOT NULL default '',
69 `CREATED` datetime default NULL,
70 `SQL_MODE` longtext NOT NULL,
71 `DEFINER` longtext NOT NULL
72 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
73 SHOW COLUMNS FROM information_schema.TRIGGERS;
74 Field Type Null Key Default Extra
75 TRIGGER_CATALOG varchar(512) YES NULL
76 TRIGGER_SCHEMA varchar(64) NO
77 TRIGGER_NAME varchar(64) NO
78 EVENT_MANIPULATION varchar(6) NO
79 EVENT_OBJECT_CATALOG varchar(512) YES NULL
80 EVENT_OBJECT_SCHEMA varchar(64) NO
81 EVENT_OBJECT_TABLE varchar(64) NO
82 ACTION_ORDER bigint(4) NO 0
83 ACTION_CONDITION longtext YES NULL
84 ACTION_STATEMENT longtext NO NULL
85 ACTION_ORIENTATION varchar(9) NO
86 ACTION_TIMING varchar(6) NO
87 ACTION_REFERENCE_OLD_TABLE varchar(64) YES NULL
88 ACTION_REFERENCE_NEW_TABLE varchar(64) YES NULL
89 ACTION_REFERENCE_OLD_ROW varchar(3) NO
90 ACTION_REFERENCE_NEW_ROW varchar(3) NO
91 CREATED datetime YES NULL
92 SQL_MODE longtext NO NULL
93 DEFINER longtext NO NULL
94 SELECT * FROM information_schema.triggers
95 WHERE trigger_catalog IS NOT NULL OR event_object_catalog IS NOT NULL
96 OR action_condition IS NOT NULL OR action_reference_old_table IS NOT NULL
97 OR action_reference_new_table IS NOT NULL;
98 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
99 ##################################################################################
100 # Testcase 3.2.18.2 + 3.2.18.3: INFORMATION_SCHEMA.TRIGGERS accessible information
101 ##################################################################################
102 DROP DATABASE IF EXISTS db_datadict;
103 CREATE DATABASE db_datadict;
104 DROP USER 'testuser1'@'localhost';
105 CREATE USER 'testuser1'@'localhost';
106 DROP USER 'testuser2'@'localhost';
107 CREATE USER 'testuser2'@'localhost';
108 DROP USER 'testuser3'@'localhost';
109 CREATE USER 'testuser3'@'localhost';
110 DROP USER 'testuser4'@'localhost';
111 CREATE USER 'testuser4'@'localhost';
112 GRANT SUPER ON *.* TO 'testuser1'@'localhost';
113 GRANT SUPER ON *.* TO 'testuser3'@'localhost';
114 GRANT SUPER ON *.* TO 'testuser4'@'localhost';
115 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
116 # Establish connection testuser1 (user=testuser1)
117 CREATE TABLE db_datadict.t1 (f1 INT, f2 INT, f3 INT)
118 ENGINE = <engine_type>;
119 CREATE TRIGGER trg1 BEFORE INSERT
120 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
121 GRANT ALL ON db_datadict.t1 TO 'testuser2'@'localhost';
122 GRANT SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
123 SELECT * FROM information_schema.triggers
124 WHERE trigger_name = 'trg1';
125 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
126 NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost
127 SHOW TRIGGERS FROM db_datadict;
128 Trigger Event Table Statement Timing Created sql_mode Definer
129 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost
130 # Establish connection testuser2 (user=testuser2)
131 SHOW GRANTS FOR 'testuser2'@'localhost';
132 # No SUPER Privilege --> no result for query
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
136 NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost
137 SHOW TRIGGERS FROM db_datadict;
138 Trigger Event Table Statement Timing Created sql_mode Definer
139 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost
140 # Establish connection testuser3 (user=testuser3)
141 SHOW GRANTS FOR 'testuser3'@'localhost';
142 # SUPER Privilege + SELECT Privilege on t1 --> result for query
143 SELECT * FROM information_schema.triggers
144 WHERE trigger_name = 'trg1';
145 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
146 NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost
147 SHOW TRIGGERS FROM db_datadict;
148 Trigger Event Table Statement Timing Created sql_mode Definer
149 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost
150 # Establish connection testuser4 (user=testuser4)
151 SHOW GRANTS FOR 'testuser4'@'localhost';
152 # SUPER Privilege + no SELECT Privilege on t1 --> no result for query
153 SELECT * FROM information_schema.triggers
154 WHERE trigger_name = 'trg1';
155 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
156 NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost
157 SHOW TRIGGERS FROM db_datadict;
158 Trigger Event Table Statement Timing Created sql_mode Definer
159 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost
160 # Switch to connection default and close connections testuser1 - testuser4
161 SELECT * FROM information_schema.triggers
162 WHERE trigger_name = 'trg1';
163 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
164 NULL db_datadict trg1 INSERT NULL db_datadict t1 0 NULL SET @test_before = 2, new.f1 = @test_before ROW BEFORE NULL NULL OLD NEW NULL testuser1@localhost
165 SHOW TRIGGERS FROM db_datadict;
166 Trigger Event Table Statement Timing Created sql_mode Definer
167 trg1 INSERT t1 SET @test_before = 2, new.f1 = @test_before BEFORE NULL testuser1@localhost
168 DROP USER 'testuser1'@'localhost';
169 DROP USER 'testuser2'@'localhost';
170 DROP USER 'testuser3'@'localhost';
171 DROP USER 'testuser4'@'localhost';
172 DROP DATABASE db_datadict;
173 #########################################################################
174 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TRIGGERS modifications
175 #########################################################################
176 ########################################################################
177 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
178 # DDL on INFORMATION_SCHEMA tables are not supported
179 ########################################################################
180 DROP DATABASE IF EXISTS db_datadict;
181 CREATE DATABASE db_datadict;
182 CREATE TABLE db_datadict.t1 (f1 BIGINT)
183 ENGINE = <engine_type>;
184 CREATE TRIGGER db_datadict.trg1 BEFORE INSERT
185 ON db_datadict.t1 FOR EACH ROW SET @test_before = 2, new.f1 = @test_before;
186 INSERT INTO information_schema.triggers
187 SELECT * FROM information_schema.triggers;
188 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
189 UPDATE information_schema.triggers SET trigger_schema = 'test'
190 WHERE table_name = 't1';
191 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
192 DELETE FROM information_schema.triggers WHERE trigger_name = 't1';
193 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
194 TRUNCATE information_schema.triggers;
195 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
196 CREATE INDEX my_idx_on_triggers ON information_schema.triggers(trigger_schema);
197 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
198 ALTER TABLE information_schema.triggers DROP PRIMARY KEY;
199 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
200 ALTER TABLE information_schema.triggers ADD f1 INT;
201 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
202 DROP TABLE information_schema.triggers;
203 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
204 ALTER TABLE information_schema.triggers RENAME db_datadict.triggers;
205 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
206 ALTER TABLE information_schema.triggers RENAME information_schema.xtriggers;
207 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
208 DROP DATABASE db_datadict;