Debian Codegraph @

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

raw
1 set sql_mode="";
2 SHOW TABLES FROM information_schema LIKE 'ROUTINES';
3 Tables_in_information_schema (ROUTINES)
4 ROUTINES
5 #######################################################################
6 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
7 #######################################################################
8 DROP VIEW IF EXISTS test.v1;
9 DROP PROCEDURE IF EXISTS test.p1;
10 DROP FUNCTION IF EXISTS test.f1;
11 CREATE VIEW test.v1 AS SELECT * FROM information_schema.ROUTINES;
12 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ROUTINES;
13 CREATE FUNCTION test.f1() returns BIGINT
14 BEGIN
15 DECLARE counter BIGINT DEFAULT NULL;
16 SELECT COUNT(*) INTO counter FROM information_schema.ROUTINES;
17 RETURN counter;
18 END//
19 # Attention: The printing of the next result sets is disabled.
20 SELECT * FROM information_schema.ROUTINES;
21 SELECT * FROM test.v1;
22 CALL test.p1;
23 SELECT test.f1();
24 DROP VIEW test.v1;
25 DROP PROCEDURE test.p1;
26 DROP FUNCTION test.f1;
27 #########################################################################
28 # Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
29 #########################################################################
30 DESCRIBE information_schema.ROUTINES;
31 Field Type Null Key Default Extra
32 SPECIFIC_NAME varchar(64) NO NULL
33 ROUTINE_CATALOG varchar(512) NO NULL
34 ROUTINE_SCHEMA varchar(64) NO NULL
35 ROUTINE_NAME varchar(64) NO NULL
36 ROUTINE_TYPE varchar(13) NO NULL
37 DATA_TYPE varchar(64) NO NULL
38 CHARACTER_MAXIMUM_LENGTH int(21) YES NULL
39 CHARACTER_OCTET_LENGTH int(21) YES NULL
40 NUMERIC_PRECISION int(21) YES NULL
41 NUMERIC_SCALE int(21) YES NULL
42 DATETIME_PRECISION bigint(21) unsigned YES NULL
43 CHARACTER_SET_NAME varchar(64) YES NULL
44 COLLATION_NAME varchar(64) YES NULL
45 DTD_IDENTIFIER longtext YES NULL
46 ROUTINE_BODY varchar(8) NO NULL
47 ROUTINE_DEFINITION longtext YES NULL
48 EXTERNAL_NAME varchar(64) YES NULL
49 EXTERNAL_LANGUAGE varchar(64) YES NULL
50 PARAMETER_STYLE varchar(8) NO NULL
51 IS_DETERMINISTIC varchar(3) NO NULL
52 SQL_DATA_ACCESS varchar(64) NO NULL
53 SQL_PATH varchar(64) YES NULL
54 SECURITY_TYPE varchar(7) NO NULL
55 CREATED datetime NO NULL
56 LAST_ALTERED datetime NO NULL
57 SQL_MODE varchar(8192) NO NULL
58 ROUTINE_COMMENT longtext NO NULL
59 DEFINER varchar(384) NO NULL
60 CHARACTER_SET_CLIENT varchar(32) NO NULL
61 COLLATION_CONNECTION varchar(64) NO NULL
62 DATABASE_COLLATION varchar(64) NO NULL
63 SHOW CREATE TABLE information_schema.ROUTINES;
64 Table Create Table
65 ROUTINES CREATE TEMPORARY TABLE `ROUTINES` (
66 `SPECIFIC_NAME` varchar(64) NOT NULL,
67 `ROUTINE_CATALOG` varchar(512) NOT NULL,
68 `ROUTINE_SCHEMA` varchar(64) NOT NULL,
69 `ROUTINE_NAME` varchar(64) NOT NULL,
70 `ROUTINE_TYPE` varchar(13) NOT NULL,
71 `DATA_TYPE` varchar(64) NOT NULL,
72 `CHARACTER_MAXIMUM_LENGTH` int(21),
73 `CHARACTER_OCTET_LENGTH` int(21),
74 `NUMERIC_PRECISION` int(21),
75 `NUMERIC_SCALE` int(21),
76 `DATETIME_PRECISION` bigint(21) unsigned,
77 `CHARACTER_SET_NAME` varchar(64),
78 `COLLATION_NAME` varchar(64),
79 `DTD_IDENTIFIER` longtext,
80 `ROUTINE_BODY` varchar(8) NOT NULL,
81 `ROUTINE_DEFINITION` longtext,
82 `EXTERNAL_NAME` varchar(64),
83 `EXTERNAL_LANGUAGE` varchar(64),
84 `PARAMETER_STYLE` varchar(8) NOT NULL,
85 `IS_DETERMINISTIC` varchar(3) NOT NULL,
86 `SQL_DATA_ACCESS` varchar(64) NOT NULL,
87 `SQL_PATH` varchar(64),
88 `SECURITY_TYPE` varchar(7) NOT NULL,
89 `CREATED` datetime NOT NULL,
90 `LAST_ALTERED` datetime NOT NULL,
91 `SQL_MODE` varchar(8192) NOT NULL,
92 `ROUTINE_COMMENT` longtext NOT NULL,
93 `DEFINER` varchar(384) NOT NULL,
94 `CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
95 `COLLATION_CONNECTION` varchar(64) NOT NULL,
96 `DATABASE_COLLATION` varchar(64) NOT NULL
97 ) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
98 SHOW COLUMNS FROM information_schema.ROUTINES;
99 Field Type Null Key Default Extra
100 SPECIFIC_NAME varchar(64) NO NULL
101 ROUTINE_CATALOG varchar(512) NO NULL
102 ROUTINE_SCHEMA varchar(64) NO NULL
103 ROUTINE_NAME varchar(64) NO NULL
104 ROUTINE_TYPE varchar(13) NO NULL
105 DATA_TYPE varchar(64) NO NULL
106 CHARACTER_MAXIMUM_LENGTH int(21) YES NULL
107 CHARACTER_OCTET_LENGTH int(21) YES NULL
108 NUMERIC_PRECISION int(21) YES NULL
109 NUMERIC_SCALE int(21) YES NULL
110 DATETIME_PRECISION bigint(21) unsigned YES NULL
111 CHARACTER_SET_NAME varchar(64) YES NULL
112 COLLATION_NAME varchar(64) YES NULL
113 DTD_IDENTIFIER longtext YES NULL
114 ROUTINE_BODY varchar(8) NO NULL
115 ROUTINE_DEFINITION longtext YES NULL
116 EXTERNAL_NAME varchar(64) YES NULL
117 EXTERNAL_LANGUAGE varchar(64) YES NULL
118 PARAMETER_STYLE varchar(8) NO NULL
119 IS_DETERMINISTIC varchar(3) NO NULL
120 SQL_DATA_ACCESS varchar(64) NO NULL
121 SQL_PATH varchar(64) YES NULL
122 SECURITY_TYPE varchar(7) NO NULL
123 CREATED datetime NO NULL
124 LAST_ALTERED datetime NO NULL
125 SQL_MODE varchar(8192) NO NULL
126 ROUTINE_COMMENT longtext NO NULL
127 DEFINER varchar(384) NO NULL
128 CHARACTER_SET_CLIENT varchar(32) NO NULL
129 COLLATION_CONNECTION varchar(64) NO NULL
130 DATABASE_COLLATION varchar(64) NO NULL
131 USE test;
132 DROP PROCEDURE IF EXISTS sp_for_routines;
133 DROP FUNCTION IF EXISTS function_for_routines;
134 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
135 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
136 SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
137 routine_body,external_name,external_language,parameter_style,sql_path
138 FROM information_schema.routines
139 WHERE routine_schema = 'test' AND
140 (routine_catalog IS NOT NULL OR external_name IS NOT NULL
141 OR external_language IS NOT NULL OR sql_path IS NOT NULL
142 OR routine_body <> 'SQL' OR parameter_style <> 'SQL'
143 OR specific_name <> routine_name);
144 specific_name routine_catalog routine_schema routine_name routine_type routine_body external_name external_language parameter_style sql_path
145 function_for_routines def test function_for_routines FUNCTION SQL NULL NULL SQL NULL
146 sp_for_routines def test sp_for_routines PROCEDURE SQL NULL NULL SQL NULL
147 DROP PROCEDURE sp_for_routines;
148 DROP FUNCTION function_for_routines;
149 ################################################################################
150 # Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
151 ################################################################################
152 DROP DATABASE IF EXISTS db_datadict;
153 DROP DATABASE IF EXISTS db_datadict_2;
154 CREATE DATABASE db_datadict;
155 USE db_datadict;
156 CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
157 ENGINE = <other_engine_type>;
158 INSERT INTO res_6_408002_1(f1, f2, f3, f4)
159 VALUES('abc', 'xyz', '1989-11-09', 0815);
160 DROP PROCEDURE IF EXISTS sp_6_408002_1;
161 CREATE PROCEDURE sp_6_408002_1()
162 BEGIN
163 SELECT * FROM db_datadict.res_6_408002_1;
164 END//
165 CREATE DATABASE db_datadict_2;
166 USE db_datadict_2;
167 CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
168 ENGINE = <other_engine_type>;
169 INSERT INTO res_6_408002_2(f1, f2, f3, f4)
170 VALUES('abc', 'xyz', '1990-10-03', 4711);
171 DROP PROCEDURE IF EXISTS sp_6_408002_2;
172 CREATE PROCEDURE sp_6_408002_2()
173 BEGIN
174 SELECT * FROM db_datadict_2.res_6_408002_2;
175 END//
176 DROP USER 'testuser1'@'localhost';
177 CREATE USER 'testuser1'@'localhost';
178 DROP USER 'testuser2'@'localhost';
179 CREATE USER 'testuser2'@'localhost';
180 DROP USER 'testuser3'@'localhost';
181 CREATE USER 'testuser3'@'localhost';
182 GRANT SELECT ON db_datadict_2.* TO 'testuser1'@'localhost';
183 GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
184 GRANT EXECUTE ON db_datadict.* TO 'testuser1'@'localhost';
185 GRANT SELECT ON db_datadict.* TO 'testuser2'@'localhost';
186 GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
187 TO 'testuser2'@'localhost';
188 GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
189 FLUSH PRIVILEGES;
190 connect testuser1, localhost, testuser1, , db_datadict;
191 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
192 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
193 sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
194 SELECT * FROM db_datadict.res_6_408002_1;
195 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
196 sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
197 SELECT * FROM db_datadict_2.res_6_408002_2;
198 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
199 add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
200 check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_LOG_FILE_BUFFERING' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
201 check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
202 AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
203 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
204 DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
205 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
206 connect testuser2, localhost, testuser2, , db_datadict;
207 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
208 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
209 sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
210 SELECT * FROM db_datadict.res_6_408002_1;
211 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
212 sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
213 SELECT * FROM db_datadict_2.res_6_408002_2;
214 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
215 add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
216 check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_LOG_FILE_BUFFERING' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
217 check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
218 AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
219 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
220 DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
221 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
222 connect testuser3, localhost, testuser3, , "*NO-ONE*";
223 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
224 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
225 sp_6_408002_1 def db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
226 SELECT * FROM db_datadict.res_6_408002_1;
227 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
228 sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
229 SELECT * FROM db_datadict_2.res_6_408002_2;
230 END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
231 add_suppression def mtr add_suppression PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN INSERT INTO test_suppressions (pattern) VALUES (pattern); FLUSH NO_WRITE_TO_BINLOG TABLE test_suppressions; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
232 check_testcase def mtr check_testcase PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE variable_name NOT IN ('timestamp') AND variable_name not like "Last_IO_Err*" AND variable_name != 'INNODB_IBUF_MAX_SIZE' AND variable_name != 'INNODB_LOG_FILE_BUFFERING' AND variable_name != 'INNODB_USE_NATIVE_AIO' AND variable_name != 'INNODB_BUFFER_POOL_LOAD_AT_STARTUP' AND variable_name not like 'GTID%POS' AND variable_name != 'GTID_BINLOG_STATE' AND variable_name != 'THREAD_POOL_SIZE' ORDER BY variable_name; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA ORDER BY BINARY SCHEMA_NAME; SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mtr_wsrep_notify', 'wsrep_schema') ORDER BY BINARY SCHEMA_NAME; SELECT table_name AS tables_in_test FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='test'; SELECT CONCAT(table_schema, '.', table_name) AS tables_in_mysql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='mysql' ORDER BY tables_in_mysql; SELECT CONCAT(table_schema, '.', table_name) AS columns_in_mysql, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, character_set_name, collation_name, column_type, column_key, extra, column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='mysql' ORDER BY columns_in_mysql; SELECT * FROM INFORMATION_SCHEMA.EVENTS; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME NOT IN ('gs_insert', 'ts_insert') AND TRIGGER_SCHEMA != 'sys'; SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA != 'sys'; SHOW STATUS LIKE 'slave_open_temp_tables'; checksum table mysql.columns_priv, mysql.db, mysql.func, mysql.help_category, mysql.help_keyword, mysql.help_relation, mysql.plugin, mysql.procs_priv, mysql.roles_mapping, mysql.tables_priv, mysql.time_zone, mysql.time_zone_leap_second, mysql.time_zone_name, mysql.time_zone_transition, mysql.time_zone_transition_type, mysql.global_priv; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_STATUS != 'INACTIVE'; select * from information_schema.session_variables where variable_name = 'debug_sync'; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
233 check_warnings def mtr check_warnings PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN DECLARE `pos` bigint unsigned; SET SQL_LOG_BIN=0, SQL_SAFE_UPDATES=0; UPDATE error_log el, global_suppressions gs SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP gs.pattern; UPDATE error_log el, test_suppressions ts SET suspicious=0 WHERE el.suspicious=1 AND el.line REGEXP ts.pattern; SELECT COUNT(*) INTO @num_warnings FROM error_log WHERE suspicious=1; IF @num_warnings > 0 THEN SELECT line FROM error_log WHERE suspicious=1; SELECT 2 INTO result; ELSE SELECT 0 INTO RESULT; END IF; TRUNCATE test_suppressions; DROP TABLE error_log; END NULL NULL SQL NO CONTAINS SQL NULL DEFINER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss root@localhost utf8mb3 utf8mb3_general_ci utf8mb4_uca1400_ai_ci
234 AddGeometryColumn def mysql AddGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
235 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
236 DropGeometryColumn def mysql DropGeometryColumn PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL begin
237 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end NULL NULL SQL NO CONTAINS SQL NULL INVOKER YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss mariadb.sys@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
238 connection default;
239 disconnect testuser1;
240 disconnect testuser2;
241 disconnect testuser3;
242 DROP USER 'testuser1'@'localhost';
243 DROP USER 'testuser2'@'localhost';
244 DROP USER 'testuser3'@'localhost';
245 USE test;
246 DROP DATABASE db_datadict;
247 DROP DATABASE db_datadict_2;
248 #########################################################################
249 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
250 #########################################################################
251 DROP DATABASE IF EXISTS db_datadict;
252 CREATE DATABASE db_datadict;
253 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
254 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
255 USE db_datadict;
256 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
257 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
258 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
259 ORDER BY routine_name;
260 SPECIFIC_NAME function_for_routines
261 ROUTINE_CATALOG def
262 ROUTINE_SCHEMA db_datadict
263 ROUTINE_NAME function_for_routines
264 ROUTINE_TYPE FUNCTION
265 DATA_TYPE int
266 CHARACTER_MAXIMUM_LENGTH NULL
267 CHARACTER_OCTET_LENGTH NULL
268 NUMERIC_PRECISION 10
269 NUMERIC_SCALE 0
270 DATETIME_PRECISION NULL
271 CHARACTER_SET_NAME NULL
272 COLLATION_NAME NULL
273 DTD_IDENTIFIER int(11)
274 ROUTINE_BODY SQL
275 ROUTINE_DEFINITION RETURN 0
276 EXTERNAL_NAME NULL
277 EXTERNAL_LANGUAGE NULL
278 PARAMETER_STYLE SQL
279 IS_DETERMINISTIC NO
280 SQL_DATA_ACCESS CONTAINS SQL
281 SQL_PATH NULL
282 SECURITY_TYPE DEFINER
283 CREATED <created>
284 LAST_ALTERED <modified>
285 SQL_MODE
286 ROUTINE_COMMENT
287 DEFINER root@localhost
288 CHARACTER_SET_CLIENT latin1
289 COLLATION_CONNECTION latin1_swedish_ci
290 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
291 SPECIFIC_NAME sp_for_routines
292 ROUTINE_CATALOG def
293 ROUTINE_SCHEMA db_datadict
294 ROUTINE_NAME sp_for_routines
295 ROUTINE_TYPE PROCEDURE
296 DATA_TYPE
297 CHARACTER_MAXIMUM_LENGTH NULL
298 CHARACTER_OCTET_LENGTH NULL
299 NUMERIC_PRECISION NULL
300 NUMERIC_SCALE NULL
301 DATETIME_PRECISION NULL
302 CHARACTER_SET_NAME NULL
303 COLLATION_NAME NULL
304 DTD_IDENTIFIER NULL
305 ROUTINE_BODY SQL
306 ROUTINE_DEFINITION SELECT 'db_datadict'
307 EXTERNAL_NAME NULL
308 EXTERNAL_LANGUAGE NULL
309 PARAMETER_STYLE SQL
310 IS_DETERMINISTIC NO
311 SQL_DATA_ACCESS CONTAINS SQL
312 SQL_PATH NULL
313 SECURITY_TYPE DEFINER
314 CREATED <created>
315 LAST_ALTERED <modified>
316 SQL_MODE
317 ROUTINE_COMMENT
318 DEFINER root@localhost
319 CHARACTER_SET_CLIENT latin1
320 COLLATION_CONNECTION latin1_swedish_ci
321 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
322 ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
323 ALTER FUNCTION function_for_routines COMMENT 'updated comments';
324 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
325 ORDER BY routine_name;
326 SPECIFIC_NAME function_for_routines
327 ROUTINE_CATALOG def
328 ROUTINE_SCHEMA db_datadict
329 ROUTINE_NAME function_for_routines
330 ROUTINE_TYPE FUNCTION
331 DATA_TYPE int
332 CHARACTER_MAXIMUM_LENGTH NULL
333 CHARACTER_OCTET_LENGTH NULL
334 NUMERIC_PRECISION 10
335 NUMERIC_SCALE 0
336 DATETIME_PRECISION NULL
337 CHARACTER_SET_NAME NULL
338 COLLATION_NAME NULL
339 DTD_IDENTIFIER int(11)
340 ROUTINE_BODY SQL
341 ROUTINE_DEFINITION RETURN 0
342 EXTERNAL_NAME NULL
343 EXTERNAL_LANGUAGE NULL
344 PARAMETER_STYLE SQL
345 IS_DETERMINISTIC NO
346 SQL_DATA_ACCESS CONTAINS SQL
347 SQL_PATH NULL
348 SECURITY_TYPE DEFINER
349 CREATED <created>
350 LAST_ALTERED <modified>
351 SQL_MODE
352 ROUTINE_COMMENT updated comments
353 DEFINER root@localhost
354 CHARACTER_SET_CLIENT latin1
355 COLLATION_CONNECTION latin1_swedish_ci
356 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
357 SPECIFIC_NAME sp_for_routines
358 ROUTINE_CATALOG def
359 ROUTINE_SCHEMA db_datadict
360 ROUTINE_NAME sp_for_routines
361 ROUTINE_TYPE PROCEDURE
362 DATA_TYPE
363 CHARACTER_MAXIMUM_LENGTH NULL
364 CHARACTER_OCTET_LENGTH NULL
365 NUMERIC_PRECISION NULL
366 NUMERIC_SCALE NULL
367 DATETIME_PRECISION NULL
368 CHARACTER_SET_NAME NULL
369 COLLATION_NAME NULL
370 DTD_IDENTIFIER NULL
371 ROUTINE_BODY SQL
372 ROUTINE_DEFINITION SELECT 'db_datadict'
373 EXTERNAL_NAME NULL
374 EXTERNAL_LANGUAGE NULL
375 PARAMETER_STYLE SQL
376 IS_DETERMINISTIC NO
377 SQL_DATA_ACCESS CONTAINS SQL
378 SQL_PATH NULL
379 SECURITY_TYPE INVOKER
380 CREATED <created>
381 LAST_ALTERED <modified>
382 SQL_MODE
383 ROUTINE_COMMENT
384 DEFINER root@localhost
385 CHARACTER_SET_CLIENT latin1
386 COLLATION_CONNECTION latin1_swedish_ci
387 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
388 DROP PROCEDURE sp_for_routines;
389 DROP FUNCTION function_for_routines;
390 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
391 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
392 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
393 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
394 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
395 ORDER BY routine_name;
396 SPECIFIC_NAME function_for_routines
397 ROUTINE_CATALOG def
398 ROUTINE_SCHEMA db_datadict
399 ROUTINE_NAME function_for_routines
400 ROUTINE_TYPE FUNCTION
401 DATA_TYPE int
402 CHARACTER_MAXIMUM_LENGTH NULL
403 CHARACTER_OCTET_LENGTH NULL
404 NUMERIC_PRECISION 10
405 NUMERIC_SCALE 0
406 DATETIME_PRECISION NULL
407 CHARACTER_SET_NAME NULL
408 COLLATION_NAME NULL
409 DTD_IDENTIFIER int(11)
410 ROUTINE_BODY SQL
411 ROUTINE_DEFINITION RETURN 0
412 EXTERNAL_NAME NULL
413 EXTERNAL_LANGUAGE NULL
414 PARAMETER_STYLE SQL
415 IS_DETERMINISTIC NO
416 SQL_DATA_ACCESS CONTAINS SQL
417 SQL_PATH NULL
418 SECURITY_TYPE DEFINER
419 CREATED <created>
420 LAST_ALTERED <modified>
421 SQL_MODE
422 ROUTINE_COMMENT
423 DEFINER root@localhost
424 CHARACTER_SET_CLIENT latin1
425 COLLATION_CONNECTION latin1_swedish_ci
426 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
427 SPECIFIC_NAME sp_for_routines
428 ROUTINE_CATALOG def
429 ROUTINE_SCHEMA db_datadict
430 ROUTINE_NAME sp_for_routines
431 ROUTINE_TYPE PROCEDURE
432 DATA_TYPE
433 CHARACTER_MAXIMUM_LENGTH NULL
434 CHARACTER_OCTET_LENGTH NULL
435 NUMERIC_PRECISION NULL
436 NUMERIC_SCALE NULL
437 DATETIME_PRECISION NULL
438 CHARACTER_SET_NAME NULL
439 COLLATION_NAME NULL
440 DTD_IDENTIFIER NULL
441 ROUTINE_BODY SQL
442 ROUTINE_DEFINITION SELECT 'db_datadict'
443 EXTERNAL_NAME NULL
444 EXTERNAL_LANGUAGE NULL
445 PARAMETER_STYLE SQL
446 IS_DETERMINISTIC NO
447 SQL_DATA_ACCESS CONTAINS SQL
448 SQL_PATH NULL
449 SECURITY_TYPE DEFINER
450 CREATED <created>
451 LAST_ALTERED <modified>
452 SQL_MODE
453 ROUTINE_COMMENT
454 DEFINER root@localhost
455 CHARACTER_SET_CLIENT latin1
456 COLLATION_CONNECTION latin1_swedish_ci
457 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
458 use test;
459 DROP DATABASE db_datadict;
460 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
461 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
462 #########################################################################
463 # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
464 # ROUTINE_DEFINITION column
465 #########################################################################
466 DROP DATABASE IF EXISTS db_datadict;
467 CREATE DATABASE db_datadict;
468 USE db_datadict;
469 CREATE TABLE db_datadict.res_6_408004_1
470 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
471 ENGINE = <other_engine_type>;
472 INSERT INTO db_datadict.res_6_408004_1
473 VALUES ('abc', 98765 , 99999999 , 98765, 10);
474 CREATE TABLE db_datadict.res_6_408004_2
475 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
476 ENGINE = <other_engine_type>;
477 INSERT INTO db_datadict.res_6_408004_2
478 VALUES ('abc', 98765 , 99999999 , 98765, 10);
479 # Checking the max. possible length of (currently) 4 GByte is not
480 # in this environment here.
481 CREATE PROCEDURE sp_6_408004 ()
482 BEGIN
483 DECLARE done INTEGER DEFAULt 0;
484 DECLARE variable_number_1 LONGTEXT;
485 DECLARE variable_number_2 MEDIUMINT;
486 DECLARE variable_number_3 LONGBLOB;
487 DECLARE variable_number_4 REAL;
488 DECLARE variable_number_5 YEAR;
489 DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
490 DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
491 DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
492 DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
493 DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
494 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
495 BEGIN
496 OPEN cursor_number_1;
497 WHILE done <> 1 DO
498 FETCH cursor_number_1
499 INTO variable_number_1, variable_number_2, variable_number_3,
500 variable_number_4, variable_number_5;
501 IF done <> 0 THEN
502 INSERT INTO res_6_408004_2
503 VALUES (variable_number_1, variable_number_2, variable_number_3,
504 variable_number_4, variable_number_5);
505 END IF;
506 END WHILE;
507 BEGIN
508 BEGIN
509 SET done = 0;
510 OPEN cursor_number_2;
511 WHILE done <> 1 DO
512 FETCH cursor_number_2
513 INTO variable_number_1, variable_number_2, variable_number_3,
514 variable_number_4, variable_number_5;
515 IF done <> 0 THEN
516 INSERT INTO res_6_408004_2
517 VALUES(variable_number_1, variable_number_2, variable_number_3,
518 variable_number_4, variable_number_5);
519 END IF;
520 END WHILE;
521 END;
522 SET done = 0;
523 OPEN cursor_number_3;
524 WHILE done <> 1 DO
525 FETCH cursor_number_3
526 INTO variable_number_1, variable_number_2, variable_number_3,
527 variable_number_4, variable_number_5;
528 IF done <> 0 THEN
529 INSERT INTO res_6_408004_2
530 VALUES(variable_number_1, variable_number_2, variable_number_3,
531 variable_number_4, variable_number_5);
532 END IF;
533 END WHILE;
534 END;
535 END;
536 BEGIN
537 SET done = 0;
538 OPEN cursor_number_4;
539 WHILE done <> 1 DO
540 FETCH cursor_number_4
541 INTO variable_number_1, variable_number_2, variable_number_3,
542 variable_number_4, variable_number_5;
543 IF done <> 0 THEN
544 INSERT INTO res_6_408004_2
545 VALUES (variable_number_1, variable_number_2, variable_number_3,
546 variable_number_4, variable_number_5);
547 END IF;
548 END WHILE;
549 END;
550 BEGIN
551 SET @a='test row';
552 SELECT @a;
553 SELECT @a;
554 SELECT @a;
555 END;
556 BEGIN
557 SET done = 0;
558 OPEN cursor_number_5;
559 WHILE done <> 1 DO
560 FETCH cursor_number_5
561 INTO variable_number_1, variable_number_2, variable_number_3,
562 variable_number_4, variable_number_5;
563 IF done <> 0 THEN
564 INSERT INTO res_6_408004_2
565 VALUES (variable_number_1, variable_number_2, variable_number_3,
566 variable_number_4, variable_number_5);
567 END IF;
568 END WHILE;
569 END;
570 BEGIN
571 SET @a='test row';
572 SELECT @a;
573 SELECT @a;
574 SELECT @a;
575 END;
576 END//
577 CALL db_datadict.sp_6_408004 ();
578 @a
579 test row
580 @a
581 test row
582 @a
583 test row
584 @a
585 test row
586 @a
587 test row
588 @a
589 test row
590 SELECT * FROM db_datadict.res_6_408004_2;
591 f1 f2 f3 f4 f5
592 abc 98765 99999999 98765 2010
593 abc 98765 99999999 98765 2010
594 abc 98765 99999999 98765 2010
595 abc 98765 99999999 98765 2010
596 abc 98765 99999999 98765 2010
597 abc 98765 99999999 98765 2010
598 SELECT *, LENGTH(routine_definition) FROM information_schema.routines
599 WHERE routine_schema = 'db_datadict';
600 SPECIFIC_NAME sp_6_408004
601 ROUTINE_CATALOG def
602 ROUTINE_SCHEMA db_datadict
603 ROUTINE_NAME sp_6_408004
604 ROUTINE_TYPE PROCEDURE
605 DATA_TYPE
606 CHARACTER_MAXIMUM_LENGTH NULL
607 CHARACTER_OCTET_LENGTH NULL
608 NUMERIC_PRECISION NULL
609 NUMERIC_SCALE NULL
610 DATETIME_PRECISION NULL
611 CHARACTER_SET_NAME NULL
612 COLLATION_NAME NULL
613 DTD_IDENTIFIER NULL
614 ROUTINE_BODY SQL
615 ROUTINE_DEFINITION BEGIN
616 DECLARE done INTEGER DEFAULt 0;
617 DECLARE variable_number_1 LONGTEXT;
618 DECLARE variable_number_2 MEDIUMINT;
619 DECLARE variable_number_3 LONGBLOB;
620 DECLARE variable_number_4 REAL;
621 DECLARE variable_number_5 YEAR;
622 DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
623 DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
624 DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
625 DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
626 DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
627 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
628 BEGIN
629 OPEN cursor_number_1;
630 WHILE done <> 1 DO
631 FETCH cursor_number_1
632 INTO variable_number_1, variable_number_2, variable_number_3,
633 variable_number_4, variable_number_5;
634 IF done <> 0 THEN
635 INSERT INTO res_6_408004_2
636 VALUES (variable_number_1, variable_number_2, variable_number_3,
637 variable_number_4, variable_number_5);
638 END IF;
639 END WHILE;
640 BEGIN
641 BEGIN
642 SET done = 0;
643 OPEN cursor_number_2;
644 WHILE done <> 1 DO
645 FETCH cursor_number_2
646 INTO variable_number_1, variable_number_2, variable_number_3,
647 variable_number_4, variable_number_5;
648 IF done <> 0 THEN
649 INSERT INTO res_6_408004_2
650 VALUES(variable_number_1, variable_number_2, variable_number_3,
651 variable_number_4, variable_number_5);
652 END IF;
653 END WHILE;
654 END;
655 SET done = 0;
656 OPEN cursor_number_3;
657 WHILE done <> 1 DO
658 FETCH cursor_number_3
659 INTO variable_number_1, variable_number_2, variable_number_3,
660 variable_number_4, variable_number_5;
661 IF done <> 0 THEN
662 INSERT INTO res_6_408004_2
663 VALUES(variable_number_1, variable_number_2, variable_number_3,
664 variable_number_4, variable_number_5);
665 END IF;
666 END WHILE;
667 END;
668 END;
669 BEGIN
670 SET done = 0;
671 OPEN cursor_number_4;
672 WHILE done <> 1 DO
673 FETCH cursor_number_4
674 INTO variable_number_1, variable_number_2, variable_number_3,
675 variable_number_4, variable_number_5;
676 IF done <> 0 THEN
677 INSERT INTO res_6_408004_2
678 VALUES (variable_number_1, variable_number_2, variable_number_3,
679 variable_number_4, variable_number_5);
680 END IF;
681 END WHILE;
682 END;
683 BEGIN
684 SET @a='test row';
685 SELECT @a;
686 SELECT @a;
687 SELECT @a;
688 END;
689 BEGIN
690 SET done = 0;
691 OPEN cursor_number_5;
692 WHILE done <> 1 DO
693 FETCH cursor_number_5
694 INTO variable_number_1, variable_number_2, variable_number_3,
695 variable_number_4, variable_number_5;
696 IF done <> 0 THEN
697 INSERT INTO res_6_408004_2
698 VALUES (variable_number_1, variable_number_2, variable_number_3,
699 variable_number_4, variable_number_5);
700 END IF;
701 END WHILE;
702 END;
703 BEGIN
704 SET @a='test row';
705 SELECT @a;
706 SELECT @a;
707 SELECT @a;
708 END;
709 END
710 EXTERNAL_NAME NULL
711 EXTERNAL_LANGUAGE NULL
712 PARAMETER_STYLE SQL
713 IS_DETERMINISTIC NO
714 SQL_DATA_ACCESS CONTAINS SQL
715 SQL_PATH NULL
716 SECURITY_TYPE DEFINER
717 CREATED YYYY-MM-DD hh:mm:ss
718 LAST_ALTERED YYYY-MM-DD hh:mm:ss
719 SQL_MODE
720 ROUTINE_COMMENT
721 DEFINER root@localhost
722 CHARACTER_SET_CLIENT latin1
723 COLLATION_CONNECTION latin1_swedish_ci
724 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
725 LENGTH(routine_definition) 2549
726 DROP DATABASE db_datadict;
727 ########################################################################
728 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
729 # DDL on INFORMATION_SCHEMA table are not supported
730 ########################################################################
731 DROP DATABASE IF EXISTS db_datadict;
732 CREATE DATABASE db_datadict;
733 USE db_datadict;
734 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
735 USE test;
736 INSERT INTO information_schema.routines (routine_name, routine_type )
737 VALUES ('p2', 'procedure');
738 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
739 UPDATE information_schema.routines SET routine_name = 'p2'
740 WHERE routine_body = 'sql';
741 ERROR HY000: The target table routines of the UPDATE is not updatable
742 DELETE FROM information_schema.routines ;
743 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
744 TRUNCATE information_schema.routines ;
745 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
746 CREATE INDEX i7 ON information_schema.routines (routine_name);
747 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
748 ALTER TABLE information_schema.routines ADD f1 INT;
749 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
750 ALTER TABLE information_schema.routines DISCARD TABLESPACE;
751 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
752 DROP TABLE information_schema.routines ;
753 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
754 ALTER TABLE information_schema.routines RENAME db_datadict.routines;
755 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
756 ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
757 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
758 DROP DATABASE db_datadict;