Debian Codegraph @

mysql-test/suite/funcs_1/r/is_routines.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 NULL 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
194 sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL 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
195 connect testuser2, localhost, testuser2, , db_datadict;
196 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
197 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
198 sp_6_408002_2 def db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL NULL 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 connect testuser3, localhost, testuser3, , "*NO-ONE*";
200 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
201 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
202 connection default;
203 disconnect testuser1;
204 disconnect testuser2;
205 disconnect testuser3;
206 DROP USER 'testuser1'@'localhost';
207 DROP USER 'testuser2'@'localhost';
208 DROP USER 'testuser3'@'localhost';
209 USE test;
210 DROP DATABASE db_datadict;
211 DROP DATABASE db_datadict_2;
212 #########################################################################
213 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
214 #########################################################################
215 DROP DATABASE IF EXISTS db_datadict;
216 CREATE DATABASE db_datadict;
217 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
218 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
219 USE db_datadict;
220 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
221 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
222 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
223 ORDER BY routine_name;
224 SPECIFIC_NAME function_for_routines
225 ROUTINE_CATALOG def
226 ROUTINE_SCHEMA db_datadict
227 ROUTINE_NAME function_for_routines
228 ROUTINE_TYPE FUNCTION
229 DATA_TYPE int
230 CHARACTER_MAXIMUM_LENGTH NULL
231 CHARACTER_OCTET_LENGTH NULL
232 NUMERIC_PRECISION 10
233 NUMERIC_SCALE 0
234 DATETIME_PRECISION NULL
235 CHARACTER_SET_NAME NULL
236 COLLATION_NAME NULL
237 DTD_IDENTIFIER int(11)
238 ROUTINE_BODY SQL
239 ROUTINE_DEFINITION RETURN 0
240 EXTERNAL_NAME NULL
241 EXTERNAL_LANGUAGE NULL
242 PARAMETER_STYLE SQL
243 IS_DETERMINISTIC NO
244 SQL_DATA_ACCESS CONTAINS SQL
245 SQL_PATH NULL
246 SECURITY_TYPE DEFINER
247 CREATED <created>
248 LAST_ALTERED <modified>
249 SQL_MODE
250 ROUTINE_COMMENT
251 DEFINER root@localhost
252 CHARACTER_SET_CLIENT latin1
253 COLLATION_CONNECTION latin1_swedish_ci
254 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
255 SPECIFIC_NAME sp_for_routines
256 ROUTINE_CATALOG def
257 ROUTINE_SCHEMA db_datadict
258 ROUTINE_NAME sp_for_routines
259 ROUTINE_TYPE PROCEDURE
260 DATA_TYPE
261 CHARACTER_MAXIMUM_LENGTH NULL
262 CHARACTER_OCTET_LENGTH NULL
263 NUMERIC_PRECISION NULL
264 NUMERIC_SCALE NULL
265 DATETIME_PRECISION NULL
266 CHARACTER_SET_NAME NULL
267 COLLATION_NAME NULL
268 DTD_IDENTIFIER NULL
269 ROUTINE_BODY SQL
270 ROUTINE_DEFINITION SELECT 'db_datadict'
271 EXTERNAL_NAME NULL
272 EXTERNAL_LANGUAGE NULL
273 PARAMETER_STYLE SQL
274 IS_DETERMINISTIC NO
275 SQL_DATA_ACCESS CONTAINS SQL
276 SQL_PATH NULL
277 SECURITY_TYPE DEFINER
278 CREATED <created>
279 LAST_ALTERED <modified>
280 SQL_MODE
281 ROUTINE_COMMENT
282 DEFINER root@localhost
283 CHARACTER_SET_CLIENT latin1
284 COLLATION_CONNECTION latin1_swedish_ci
285 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
286 ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
287 ALTER FUNCTION function_for_routines COMMENT 'updated comments';
288 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
289 ORDER BY routine_name;
290 SPECIFIC_NAME function_for_routines
291 ROUTINE_CATALOG def
292 ROUTINE_SCHEMA db_datadict
293 ROUTINE_NAME function_for_routines
294 ROUTINE_TYPE FUNCTION
295 DATA_TYPE int
296 CHARACTER_MAXIMUM_LENGTH NULL
297 CHARACTER_OCTET_LENGTH NULL
298 NUMERIC_PRECISION 10
299 NUMERIC_SCALE 0
300 DATETIME_PRECISION NULL
301 CHARACTER_SET_NAME NULL
302 COLLATION_NAME NULL
303 DTD_IDENTIFIER int(11)
304 ROUTINE_BODY SQL
305 ROUTINE_DEFINITION RETURN 0
306 EXTERNAL_NAME NULL
307 EXTERNAL_LANGUAGE NULL
308 PARAMETER_STYLE SQL
309 IS_DETERMINISTIC NO
310 SQL_DATA_ACCESS CONTAINS SQL
311 SQL_PATH NULL
312 SECURITY_TYPE DEFINER
313 CREATED <created>
314 LAST_ALTERED <modified>
315 SQL_MODE
316 ROUTINE_COMMENT updated comments
317 DEFINER root@localhost
318 CHARACTER_SET_CLIENT latin1
319 COLLATION_CONNECTION latin1_swedish_ci
320 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
321 SPECIFIC_NAME sp_for_routines
322 ROUTINE_CATALOG def
323 ROUTINE_SCHEMA db_datadict
324 ROUTINE_NAME sp_for_routines
325 ROUTINE_TYPE PROCEDURE
326 DATA_TYPE
327 CHARACTER_MAXIMUM_LENGTH NULL
328 CHARACTER_OCTET_LENGTH NULL
329 NUMERIC_PRECISION NULL
330 NUMERIC_SCALE NULL
331 DATETIME_PRECISION NULL
332 CHARACTER_SET_NAME NULL
333 COLLATION_NAME NULL
334 DTD_IDENTIFIER NULL
335 ROUTINE_BODY SQL
336 ROUTINE_DEFINITION SELECT 'db_datadict'
337 EXTERNAL_NAME NULL
338 EXTERNAL_LANGUAGE NULL
339 PARAMETER_STYLE SQL
340 IS_DETERMINISTIC NO
341 SQL_DATA_ACCESS CONTAINS SQL
342 SQL_PATH NULL
343 SECURITY_TYPE INVOKER
344 CREATED <created>
345 LAST_ALTERED <modified>
346 SQL_MODE
347 ROUTINE_COMMENT
348 DEFINER root@localhost
349 CHARACTER_SET_CLIENT latin1
350 COLLATION_CONNECTION latin1_swedish_ci
351 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
352 DROP PROCEDURE sp_for_routines;
353 DROP FUNCTION function_for_routines;
354 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
355 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
356 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
357 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
358 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
359 ORDER BY routine_name;
360 SPECIFIC_NAME function_for_routines
361 ROUTINE_CATALOG def
362 ROUTINE_SCHEMA db_datadict
363 ROUTINE_NAME function_for_routines
364 ROUTINE_TYPE FUNCTION
365 DATA_TYPE int
366 CHARACTER_MAXIMUM_LENGTH NULL
367 CHARACTER_OCTET_LENGTH NULL
368 NUMERIC_PRECISION 10
369 NUMERIC_SCALE 0
370 DATETIME_PRECISION NULL
371 CHARACTER_SET_NAME NULL
372 COLLATION_NAME NULL
373 DTD_IDENTIFIER int(11)
374 ROUTINE_BODY SQL
375 ROUTINE_DEFINITION RETURN 0
376 EXTERNAL_NAME NULL
377 EXTERNAL_LANGUAGE NULL
378 PARAMETER_STYLE SQL
379 IS_DETERMINISTIC NO
380 SQL_DATA_ACCESS CONTAINS SQL
381 SQL_PATH NULL
382 SECURITY_TYPE DEFINER
383 CREATED <created>
384 LAST_ALTERED <modified>
385 SQL_MODE
386 ROUTINE_COMMENT
387 DEFINER root@localhost
388 CHARACTER_SET_CLIENT latin1
389 COLLATION_CONNECTION latin1_swedish_ci
390 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
391 SPECIFIC_NAME sp_for_routines
392 ROUTINE_CATALOG def
393 ROUTINE_SCHEMA db_datadict
394 ROUTINE_NAME sp_for_routines
395 ROUTINE_TYPE PROCEDURE
396 DATA_TYPE
397 CHARACTER_MAXIMUM_LENGTH NULL
398 CHARACTER_OCTET_LENGTH NULL
399 NUMERIC_PRECISION NULL
400 NUMERIC_SCALE NULL
401 DATETIME_PRECISION NULL
402 CHARACTER_SET_NAME NULL
403 COLLATION_NAME NULL
404 DTD_IDENTIFIER NULL
405 ROUTINE_BODY SQL
406 ROUTINE_DEFINITION SELECT 'db_datadict'
407 EXTERNAL_NAME NULL
408 EXTERNAL_LANGUAGE NULL
409 PARAMETER_STYLE SQL
410 IS_DETERMINISTIC NO
411 SQL_DATA_ACCESS CONTAINS SQL
412 SQL_PATH NULL
413 SECURITY_TYPE DEFINER
414 CREATED <created>
415 LAST_ALTERED <modified>
416 SQL_MODE
417 ROUTINE_COMMENT
418 DEFINER root@localhost
419 CHARACTER_SET_CLIENT latin1
420 COLLATION_CONNECTION latin1_swedish_ci
421 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
422 use test;
423 DROP DATABASE db_datadict;
424 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
425 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
426 #########################################################################
427 # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
428 # ROUTINE_DEFINITION column
429 #########################################################################
430 DROP DATABASE IF EXISTS db_datadict;
431 CREATE DATABASE db_datadict;
432 USE db_datadict;
433 CREATE TABLE db_datadict.res_6_408004_1
434 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
435 ENGINE = <other_engine_type>;
436 INSERT INTO db_datadict.res_6_408004_1
437 VALUES ('abc', 98765 , 99999999 , 98765, 10);
438 CREATE TABLE db_datadict.res_6_408004_2
439 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
440 ENGINE = <other_engine_type>;
441 INSERT INTO db_datadict.res_6_408004_2
442 VALUES ('abc', 98765 , 99999999 , 98765, 10);
443 # Checking the max. possible length of (currently) 4 GByte is not
444 # in this environment here.
445 CREATE PROCEDURE sp_6_408004 ()
446 BEGIN
447 DECLARE done INTEGER DEFAULt 0;
448 DECLARE variable_number_1 LONGTEXT;
449 DECLARE variable_number_2 MEDIUMINT;
450 DECLARE variable_number_3 LONGBLOB;
451 DECLARE variable_number_4 REAL;
452 DECLARE variable_number_5 YEAR;
453 DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
454 DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
455 DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
456 DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
457 DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
458 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
459 BEGIN
460 OPEN cursor_number_1;
461 WHILE done <> 1 DO
462 FETCH cursor_number_1
463 INTO variable_number_1, variable_number_2, variable_number_3,
464 variable_number_4, variable_number_5;
465 IF done <> 0 THEN
466 INSERT INTO res_6_408004_2
467 VALUES (variable_number_1, variable_number_2, variable_number_3,
468 variable_number_4, variable_number_5);
469 END IF;
470 END WHILE;
471 BEGIN
472 BEGIN
473 SET done = 0;
474 OPEN cursor_number_2;
475 WHILE done <> 1 DO
476 FETCH cursor_number_2
477 INTO variable_number_1, variable_number_2, variable_number_3,
478 variable_number_4, variable_number_5;
479 IF done <> 0 THEN
480 INSERT INTO res_6_408004_2
481 VALUES(variable_number_1, variable_number_2, variable_number_3,
482 variable_number_4, variable_number_5);
483 END IF;
484 END WHILE;
485 END;
486 SET done = 0;
487 OPEN cursor_number_3;
488 WHILE done <> 1 DO
489 FETCH cursor_number_3
490 INTO variable_number_1, variable_number_2, variable_number_3,
491 variable_number_4, variable_number_5;
492 IF done <> 0 THEN
493 INSERT INTO res_6_408004_2
494 VALUES(variable_number_1, variable_number_2, variable_number_3,
495 variable_number_4, variable_number_5);
496 END IF;
497 END WHILE;
498 END;
499 END;
500 BEGIN
501 SET done = 0;
502 OPEN cursor_number_4;
503 WHILE done <> 1 DO
504 FETCH cursor_number_4
505 INTO variable_number_1, variable_number_2, variable_number_3,
506 variable_number_4, variable_number_5;
507 IF done <> 0 THEN
508 INSERT INTO res_6_408004_2
509 VALUES (variable_number_1, variable_number_2, variable_number_3,
510 variable_number_4, variable_number_5);
511 END IF;
512 END WHILE;
513 END;
514 BEGIN
515 SET @a='test row';
516 SELECT @a;
517 SELECT @a;
518 SELECT @a;
519 END;
520 BEGIN
521 SET done = 0;
522 OPEN cursor_number_5;
523 WHILE done <> 1 DO
524 FETCH cursor_number_5
525 INTO variable_number_1, variable_number_2, variable_number_3,
526 variable_number_4, variable_number_5;
527 IF done <> 0 THEN
528 INSERT INTO res_6_408004_2
529 VALUES (variable_number_1, variable_number_2, variable_number_3,
530 variable_number_4, variable_number_5);
531 END IF;
532 END WHILE;
533 END;
534 BEGIN
535 SET @a='test row';
536 SELECT @a;
537 SELECT @a;
538 SELECT @a;
539 END;
540 END//
541 CALL db_datadict.sp_6_408004 ();
542 @a
543 test row
544 @a
545 test row
546 @a
547 test row
548 @a
549 test row
550 @a
551 test row
552 @a
553 test row
554 SELECT * FROM db_datadict.res_6_408004_2;
555 f1 f2 f3 f4 f5
556 abc 98765 99999999 98765 2010
557 abc 98765 99999999 98765 2010
558 abc 98765 99999999 98765 2010
559 abc 98765 99999999 98765 2010
560 abc 98765 99999999 98765 2010
561 abc 98765 99999999 98765 2010
562 SELECT *, LENGTH(routine_definition) FROM information_schema.routines
563 WHERE routine_schema = 'db_datadict';
564 SPECIFIC_NAME sp_6_408004
565 ROUTINE_CATALOG def
566 ROUTINE_SCHEMA db_datadict
567 ROUTINE_NAME sp_6_408004
568 ROUTINE_TYPE PROCEDURE
569 DATA_TYPE
570 CHARACTER_MAXIMUM_LENGTH NULL
571 CHARACTER_OCTET_LENGTH NULL
572 NUMERIC_PRECISION NULL
573 NUMERIC_SCALE NULL
574 DATETIME_PRECISION NULL
575 CHARACTER_SET_NAME NULL
576 COLLATION_NAME NULL
577 DTD_IDENTIFIER NULL
578 ROUTINE_BODY SQL
579 ROUTINE_DEFINITION BEGIN
580 DECLARE done INTEGER DEFAULt 0;
581 DECLARE variable_number_1 LONGTEXT;
582 DECLARE variable_number_2 MEDIUMINT;
583 DECLARE variable_number_3 LONGBLOB;
584 DECLARE variable_number_4 REAL;
585 DECLARE variable_number_5 YEAR;
586 DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
587 DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
588 DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
589 DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
590 DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
591 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
592 BEGIN
593 OPEN cursor_number_1;
594 WHILE done <> 1 DO
595 FETCH cursor_number_1
596 INTO variable_number_1, variable_number_2, variable_number_3,
597 variable_number_4, variable_number_5;
598 IF done <> 0 THEN
599 INSERT INTO res_6_408004_2
600 VALUES (variable_number_1, variable_number_2, variable_number_3,
601 variable_number_4, variable_number_5);
602 END IF;
603 END WHILE;
604 BEGIN
605 BEGIN
606 SET done = 0;
607 OPEN cursor_number_2;
608 WHILE done <> 1 DO
609 FETCH cursor_number_2
610 INTO variable_number_1, variable_number_2, variable_number_3,
611 variable_number_4, variable_number_5;
612 IF done <> 0 THEN
613 INSERT INTO res_6_408004_2
614 VALUES(variable_number_1, variable_number_2, variable_number_3,
615 variable_number_4, variable_number_5);
616 END IF;
617 END WHILE;
618 END;
619 SET done = 0;
620 OPEN cursor_number_3;
621 WHILE done <> 1 DO
622 FETCH cursor_number_3
623 INTO variable_number_1, variable_number_2, variable_number_3,
624 variable_number_4, variable_number_5;
625 IF done <> 0 THEN
626 INSERT INTO res_6_408004_2
627 VALUES(variable_number_1, variable_number_2, variable_number_3,
628 variable_number_4, variable_number_5);
629 END IF;
630 END WHILE;
631 END;
632 END;
633 BEGIN
634 SET done = 0;
635 OPEN cursor_number_4;
636 WHILE done <> 1 DO
637 FETCH cursor_number_4
638 INTO variable_number_1, variable_number_2, variable_number_3,
639 variable_number_4, variable_number_5;
640 IF done <> 0 THEN
641 INSERT INTO res_6_408004_2
642 VALUES (variable_number_1, variable_number_2, variable_number_3,
643 variable_number_4, variable_number_5);
644 END IF;
645 END WHILE;
646 END;
647 BEGIN
648 SET @a='test row';
649 SELECT @a;
650 SELECT @a;
651 SELECT @a;
652 END;
653 BEGIN
654 SET done = 0;
655 OPEN cursor_number_5;
656 WHILE done <> 1 DO
657 FETCH cursor_number_5
658 INTO variable_number_1, variable_number_2, variable_number_3,
659 variable_number_4, variable_number_5;
660 IF done <> 0 THEN
661 INSERT INTO res_6_408004_2
662 VALUES (variable_number_1, variable_number_2, variable_number_3,
663 variable_number_4, variable_number_5);
664 END IF;
665 END WHILE;
666 END;
667 BEGIN
668 SET @a='test row';
669 SELECT @a;
670 SELECT @a;
671 SELECT @a;
672 END;
673 END
674 EXTERNAL_NAME NULL
675 EXTERNAL_LANGUAGE NULL
676 PARAMETER_STYLE SQL
677 IS_DETERMINISTIC NO
678 SQL_DATA_ACCESS CONTAINS SQL
679 SQL_PATH NULL
680 SECURITY_TYPE DEFINER
681 CREATED YYYY-MM-DD hh:mm:ss
682 LAST_ALTERED YYYY-MM-DD hh:mm:ss
683 SQL_MODE
684 ROUTINE_COMMENT
685 DEFINER root@localhost
686 CHARACTER_SET_CLIENT latin1
687 COLLATION_CONNECTION latin1_swedish_ci
688 DATABASE_COLLATION utf8mb4_uca1400_ai_ci
689 LENGTH(routine_definition) 2549
690 DROP DATABASE db_datadict;
691 ########################################################################
692 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
693 # DDL on INFORMATION_SCHEMA table are not supported
694 ########################################################################
695 DROP DATABASE IF EXISTS db_datadict;
696 CREATE DATABASE db_datadict;
697 USE db_datadict;
698 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
699 USE test;
700 INSERT INTO information_schema.routines (routine_name, routine_type )
701 VALUES ('p2', 'procedure');
702 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
703 UPDATE information_schema.routines SET routine_name = 'p2'
704 WHERE routine_body = 'sql';
705 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
706 DELETE FROM information_schema.routines ;
707 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
708 TRUNCATE information_schema.routines ;
709 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
710 CREATE INDEX i7 ON information_schema.routines (routine_name);
711 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
712 ALTER TABLE information_schema.routines ADD f1 INT;
713 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
714 ALTER TABLE information_schema.routines DISCARD TABLESPACE;
715 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
716 DROP TABLE information_schema.routines ;
717 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
718 ALTER TABLE information_schema.routines RENAME db_datadict.routines;
719 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
720 ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
721 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
722 DROP DATABASE db_datadict;