1
SHOW TABLES FROM information_schema LIKE 'ROUTINES';
2
Tables_in_information_schema (ROUTINES)
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.ROUTINES;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ROUTINES;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.ROUTINES;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.ROUTINES;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
28
#########################################################################
29
DESCRIBE information_schema.ROUTINES;
30
Field Type Null Key Default Extra
31
SPECIFIC_NAME varchar(64) NO
32
ROUTINE_CATALOG varchar(512) YES NULL
33
ROUTINE_SCHEMA varchar(64) NO
34
ROUTINE_NAME varchar(64) NO
35
ROUTINE_TYPE varchar(9) NO
36
DATA_TYPE varchar(256) NO
37
CHARACTER_MAXIMUM_LENGTH int(21) YES NULL
38
CHARACTER_OCTET_LENGTH int(21) YES NULL
39
NUMERIC_PRECISION int(21) YES NULL
40
NUMERIC_SCALE int(21) YES NULL
41
CHARACTER_SET_NAME varchar(64) YES NULL
42
COLLATION_NAME varchar(64) YES NULL
43
DTD_IDENTIFIER longtext YES NULL
44
ROUTINE_BODY varchar(8) NO
45
ROUTINE_DEFINITION longtext YES NULL
46
EXTERNAL_NAME varchar(64) YES NULL
47
EXTERNAL_LANGUAGE varchar(64) YES NULL
48
PARAMETER_STYLE varchar(8) NO
49
IS_DETERMINISTIC varchar(3) NO
50
SQL_DATA_ACCESS varchar(64) NO
51
SQL_PATH varchar(64) YES NULL
52
SECURITY_TYPE varchar(7) NO
53
CREATED datetime NO 0000-00-00 00:00:00
54
LAST_ALTERED datetime NO 0000-00-00 00:00:00
55
SQL_MODE longtext NO NULL
56
ROUTINE_COMMENT varchar(64) NO
57
DEFINER varchar(77) NO
58
CHARACTER_SET_CLIENT varchar(32) NO
59
COLLATION_CONNECTION varchar(32) NO
60
DATABASE_COLLATION varchar(32) NO
61
SHOW CREATE TABLE information_schema.ROUTINES;
63
ROUTINES CREATE TEMPORARY TABLE `ROUTINES` (
64
`SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '',
65
`ROUTINE_CATALOG` varchar(512) DEFAULT NULL,
66
`ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
67
`ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '',
68
`ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '',
69
`DATA_TYPE` varchar(256) NOT NULL DEFAULT '',
70
`CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL,
71
`CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL,
72
`NUMERIC_PRECISION` int(21) DEFAULT NULL,
73
`NUMERIC_SCALE` int(21) DEFAULT NULL,
74
`CHARACTER_SET_NAME` varchar(64) DEFAULT NULL,
75
`COLLATION_NAME` varchar(64) DEFAULT NULL,
76
`DTD_IDENTIFIER` longtext,
77
`ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '',
78
`ROUTINE_DEFINITION` longtext,
79
`EXTERNAL_NAME` varchar(64) DEFAULT NULL,
80
`EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL,
81
`PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '',
82
`IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '',
83
`SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '',
84
`SQL_PATH` varchar(64) DEFAULT NULL,
85
`SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '',
86
`CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
87
`LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
88
`SQL_MODE` longtext NOT NULL,
89
`ROUTINE_COMMENT` varchar(64) NOT NULL DEFAULT '',
90
`DEFINER` varchar(77) NOT NULL DEFAULT '',
91
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '',
92
`COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '',
93
`DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT ''
94
) ENGINE=MyISAM DEFAULT CHARSET=utf8
95
SHOW COLUMNS FROM information_schema.ROUTINES;
96
Field Type Null Key Default Extra
97
SPECIFIC_NAME varchar(64) NO
98
ROUTINE_CATALOG varchar(512) YES NULL
99
ROUTINE_SCHEMA varchar(64) NO
100
ROUTINE_NAME varchar(64) NO
101
ROUTINE_TYPE varchar(9) NO
102
DATA_TYPE varchar(256) NO
103
CHARACTER_MAXIMUM_LENGTH int(21) YES NULL
104
CHARACTER_OCTET_LENGTH int(21) YES NULL
105
NUMERIC_PRECISION int(21) YES NULL
106
NUMERIC_SCALE int(21) YES NULL
107
CHARACTER_SET_NAME varchar(64) YES NULL
108
COLLATION_NAME varchar(64) YES NULL
109
DTD_IDENTIFIER longtext YES NULL
110
ROUTINE_BODY varchar(8) NO
111
ROUTINE_DEFINITION longtext YES NULL
112
EXTERNAL_NAME varchar(64) YES NULL
113
EXTERNAL_LANGUAGE varchar(64) YES NULL
114
PARAMETER_STYLE varchar(8) NO
115
IS_DETERMINISTIC varchar(3) NO
116
SQL_DATA_ACCESS varchar(64) NO
117
SQL_PATH varchar(64) YES NULL
118
SECURITY_TYPE varchar(7) NO
119
CREATED datetime NO 0000-00-00 00:00:00
120
LAST_ALTERED datetime NO 0000-00-00 00:00:00
121
SQL_MODE longtext NO NULL
122
ROUTINE_COMMENT varchar(64) NO
123
DEFINER varchar(77) NO
124
CHARACTER_SET_CLIENT varchar(32) NO
125
COLLATION_CONNECTION varchar(32) NO
126
DATABASE_COLLATION varchar(32) NO
128
DROP PROCEDURE IF EXISTS sp_for_routines;
129
DROP FUNCTION IF EXISTS function_for_routines;
130
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
131
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
132
SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
133
routine_body,external_name,external_language,parameter_style,sql_path
134
FROM information_schema.routines
135
WHERE routine_catalog IS NOT NULL OR external_name IS NOT NULL
136
OR external_language IS NOT NULL OR sql_path IS NOT NULL
137
OR routine_body <> 'SQL' OR parameter_style <> 'SQL'
138
OR specific_name <> routine_name;
139
specific_name routine_catalog routine_schema routine_name routine_type routine_body external_name external_language parameter_style sql_path
140
DROP PROCEDURE sp_for_routines;
141
DROP FUNCTION function_for_routines;
142
################################################################################
143
# Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
144
################################################################################
145
DROP DATABASE IF EXISTS db_datadict;
146
DROP DATABASE IF EXISTS db_datadict_2;
147
CREATE DATABASE db_datadict;
149
CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
150
ENGINE = <other_engine_type>;
151
INSERT INTO res_6_408002_1(f1, f2, f3, f4)
152
VALUES('abc', 'xyz', '1989-11-09', 0815);
153
DROP PROCEDURE IF EXISTS sp_6_408002_1;
154
CREATE PROCEDURE sp_6_408002_1()
156
SELECT * FROM db_datadict.res_6_408002_1;
158
CREATE DATABASE db_datadict_2;
160
CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
161
ENGINE = <other_engine_type>;
162
INSERT INTO res_6_408002_2(f1, f2, f3, f4)
163
VALUES('abc', 'xyz', '1990-10-03', 4711);
164
DROP PROCEDURE IF EXISTS sp_6_408002_2;
165
CREATE PROCEDURE sp_6_408002_2()
167
SELECT * FROM db_datadict_2.res_6_408002_2;
169
DROP USER 'testuser1'@'localhost';
170
CREATE USER 'testuser1'@'localhost';
171
DROP USER 'testuser2'@'localhost';
172
CREATE USER 'testuser2'@'localhost';
173
DROP USER 'testuser3'@'localhost';
174
CREATE USER 'testuser3'@'localhost';
175
GRANT SELECT ON db_datadict_2.* TO 'testuser1'@'localhost';
176
GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
177
GRANT EXECUTE ON db_datadict.* TO 'testuser1'@'localhost';
178
GRANT SELECT ON db_datadict.* TO 'testuser2'@'localhost';
179
GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
180
TO 'testuser2'@'localhost';
181
GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
183
# Establish connection testuser1 (user=testuser1)
184
SELECT * FROM information_schema.routines;
185
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
186
sp_6_408002_1 NULL db_datadict sp_6_408002_1 PROCEDURE NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <last_altered> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
187
sp_6_408002_2 NULL db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <last_altered> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
188
# Establish connection testuser2 (user=testuser2)
189
SELECT * FROM information_schema.routines;
190
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
191
sp_6_408002_2 NULL db_datadict_2 sp_6_408002_2 PROCEDURE NULL NULL NULL NULL NULL NULL NULL SQL NULL NULL NULL SQL NO CONTAINS SQL NULL DEFINER <created> <last_altered> root@localhost latin1 latin1_swedish_ci latin1_swedish_ci
192
# Establish connection testuser3 (user=testuser3)
193
SELECT * FROM information_schema.routines;
194
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
195
# Switch to connection default and close connections testuser1,testuser2,testuser3
196
DROP USER 'testuser1'@'localhost';
197
DROP USER 'testuser2'@'localhost';
198
DROP USER 'testuser3'@'localhost';
200
DROP DATABASE db_datadict;
201
DROP DATABASE db_datadict_2;
202
#########################################################################
203
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
204
#########################################################################
205
DROP DATABASE IF EXISTS db_datadict;
206
CREATE DATABASE db_datadict;
207
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
208
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
210
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
211
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
212
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
213
ORDER BY routine_name;
214
SPECIFIC_NAME function_for_routines
216
ROUTINE_SCHEMA db_datadict
217
ROUTINE_NAME function_for_routines
218
ROUTINE_TYPE FUNCTION
220
CHARACTER_MAXIMUM_LENGTH NULL
221
CHARACTER_OCTET_LENGTH NULL
224
CHARACTER_SET_NAME NULL
226
DTD_IDENTIFIER int(11)
228
ROUTINE_DEFINITION RETURN 0
230
EXTERNAL_LANGUAGE NULL
233
SQL_DATA_ACCESS CONTAINS SQL
235
SECURITY_TYPE DEFINER
237
LAST_ALTERED <last_altered>
240
DEFINER root@localhost
241
CHARACTER_SET_CLIENT latin1
242
COLLATION_CONNECTION latin1_swedish_ci
243
DATABASE_COLLATION latin1_swedish_ci
244
SPECIFIC_NAME sp_for_routines
246
ROUTINE_SCHEMA db_datadict
247
ROUTINE_NAME sp_for_routines
248
ROUTINE_TYPE PROCEDURE
250
CHARACTER_MAXIMUM_LENGTH NULL
251
CHARACTER_OCTET_LENGTH NULL
252
NUMERIC_PRECISION NULL
254
CHARACTER_SET_NAME NULL
258
ROUTINE_DEFINITION SELECT 'db_datadict'
260
EXTERNAL_LANGUAGE NULL
263
SQL_DATA_ACCESS CONTAINS SQL
265
SECURITY_TYPE DEFINER
267
LAST_ALTERED <last_altered>
270
DEFINER root@localhost
271
CHARACTER_SET_CLIENT latin1
272
COLLATION_CONNECTION latin1_swedish_ci
273
DATABASE_COLLATION latin1_swedish_ci
274
ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
275
ALTER FUNCTION function_for_routines COMMENT 'updated comments';
276
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
277
ORDER BY routine_name;
278
SPECIFIC_NAME function_for_routines
280
ROUTINE_SCHEMA db_datadict
281
ROUTINE_NAME function_for_routines
282
ROUTINE_TYPE FUNCTION
284
CHARACTER_MAXIMUM_LENGTH NULL
285
CHARACTER_OCTET_LENGTH NULL
288
CHARACTER_SET_NAME NULL
290
DTD_IDENTIFIER int(11)
292
ROUTINE_DEFINITION RETURN 0
294
EXTERNAL_LANGUAGE NULL
297
SQL_DATA_ACCESS CONTAINS SQL
299
SECURITY_TYPE DEFINER
301
LAST_ALTERED <last_altered>
303
ROUTINE_COMMENT updated comments
304
DEFINER root@localhost
305
CHARACTER_SET_CLIENT latin1
306
COLLATION_CONNECTION latin1_swedish_ci
307
DATABASE_COLLATION latin1_swedish_ci
308
SPECIFIC_NAME sp_for_routines
310
ROUTINE_SCHEMA db_datadict
311
ROUTINE_NAME sp_for_routines
312
ROUTINE_TYPE PROCEDURE
314
CHARACTER_MAXIMUM_LENGTH NULL
315
CHARACTER_OCTET_LENGTH NULL
316
NUMERIC_PRECISION NULL
318
CHARACTER_SET_NAME NULL
322
ROUTINE_DEFINITION SELECT 'db_datadict'
324
EXTERNAL_LANGUAGE NULL
327
SQL_DATA_ACCESS CONTAINS SQL
329
SECURITY_TYPE INVOKER
331
LAST_ALTERED <last_altered>
334
DEFINER root@localhost
335
CHARACTER_SET_CLIENT latin1
336
COLLATION_CONNECTION latin1_swedish_ci
337
DATABASE_COLLATION latin1_swedish_ci
338
DROP PROCEDURE sp_for_routines;
339
DROP FUNCTION function_for_routines;
340
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
341
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
342
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
343
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
344
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
345
ORDER BY routine_name;
346
SPECIFIC_NAME function_for_routines
348
ROUTINE_SCHEMA db_datadict
349
ROUTINE_NAME function_for_routines
350
ROUTINE_TYPE FUNCTION
352
CHARACTER_MAXIMUM_LENGTH NULL
353
CHARACTER_OCTET_LENGTH NULL
356
CHARACTER_SET_NAME NULL
358
DTD_IDENTIFIER int(11)
360
ROUTINE_DEFINITION RETURN 0
362
EXTERNAL_LANGUAGE NULL
365
SQL_DATA_ACCESS CONTAINS SQL
367
SECURITY_TYPE DEFINER
369
LAST_ALTERED <last_altered>
372
DEFINER root@localhost
373
CHARACTER_SET_CLIENT latin1
374
COLLATION_CONNECTION latin1_swedish_ci
375
DATABASE_COLLATION latin1_swedish_ci
376
SPECIFIC_NAME sp_for_routines
378
ROUTINE_SCHEMA db_datadict
379
ROUTINE_NAME sp_for_routines
380
ROUTINE_TYPE PROCEDURE
382
CHARACTER_MAXIMUM_LENGTH NULL
383
CHARACTER_OCTET_LENGTH NULL
384
NUMERIC_PRECISION NULL
386
CHARACTER_SET_NAME NULL
390
ROUTINE_DEFINITION SELECT 'db_datadict'
392
EXTERNAL_LANGUAGE NULL
395
SQL_DATA_ACCESS CONTAINS SQL
397
SECURITY_TYPE DEFINER
399
LAST_ALTERED <last_altered>
402
DEFINER root@localhost
403
CHARACTER_SET_CLIENT latin1
404
COLLATION_CONNECTION latin1_swedish_ci
405
DATABASE_COLLATION latin1_swedish_ci
407
DROP DATABASE db_datadict;
408
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
409
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE 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
410
#########################################################################
411
# 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
412
# ROUTINE_DEFINITION column
413
#########################################################################
414
DROP DATABASE IF EXISTS db_datadict;
415
CREATE DATABASE db_datadict;
417
CREATE TABLE db_datadict.res_6_408004_1
418
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
419
ENGINE = <other_engine_type>;
420
INSERT INTO db_datadict.res_6_408004_1
421
VALUES ('abc', 98765 , 99999999 , 98765, 10);
422
CREATE TABLE db_datadict.res_6_408004_2
423
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
424
ENGINE = <other_engine_type>;
425
INSERT INTO db_datadict.res_6_408004_2
426
VALUES ('abc', 98765 , 99999999 , 98765, 10);
427
# Checking the max. possible length of (currently) 4 GByte is not
428
# in this environment here.
429
CREATE PROCEDURE sp_6_408004 ()
431
DECLARE done INTEGER DEFAULt 0;
432
DECLARE variable_number_1 LONGTEXT;
433
DECLARE variable_number_2 MEDIUMINT;
434
DECLARE variable_number_3 LONGBLOB;
435
DECLARE variable_number_4 REAL;
436
DECLARE variable_number_5 YEAR;
437
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
438
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
439
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
440
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
441
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
442
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
444
OPEN cursor_number_1;
446
FETCH cursor_number_1
447
INTO variable_number_1, variable_number_2, variable_number_3,
448
variable_number_4, variable_number_5;
450
INSERT INTO res_6_408004_2
451
VALUES (variable_number_1, variable_number_2, variable_number_3,
452
variable_number_4, variable_number_5);
458
OPEN cursor_number_2;
460
FETCH cursor_number_2
461
INTO variable_number_1, variable_number_2, variable_number_3,
462
variable_number_4, variable_number_5;
464
INSERT INTO res_6_408004_2
465
VALUES(variable_number_1, variable_number_2, variable_number_3,
466
variable_number_4, variable_number_5);
471
OPEN cursor_number_3;
473
FETCH cursor_number_3
474
INTO variable_number_1, variable_number_2, variable_number_3,
475
variable_number_4, variable_number_5;
477
INSERT INTO res_6_408004_2
478
VALUES(variable_number_1, variable_number_2, variable_number_3,
479
variable_number_4, variable_number_5);
486
OPEN cursor_number_4;
488
FETCH cursor_number_4
489
INTO variable_number_1, variable_number_2, variable_number_3,
490
variable_number_4, variable_number_5;
492
INSERT INTO res_6_408004_2
493
VALUES (variable_number_1, variable_number_2, variable_number_3,
494
variable_number_4, variable_number_5);
506
OPEN cursor_number_5;
508
FETCH cursor_number_5
509
INTO variable_number_1, variable_number_2, variable_number_3,
510
variable_number_4, variable_number_5;
512
INSERT INTO res_6_408004_2
513
VALUES (variable_number_1, variable_number_2, variable_number_3,
514
variable_number_4, variable_number_5);
525
CALL db_datadict.sp_6_408004 ();
538
SELECT * FROM db_datadict.res_6_408004_2;
540
abc 98765 99999999 98765 2010
541
abc 98765 99999999 98765 2010
542
abc 98765 99999999 98765 2010
543
abc 98765 99999999 98765 2010
544
abc 98765 99999999 98765 2010
545
abc 98765 99999999 98765 2010
546
SELECT *, LENGTH(routine_definition) FROM information_schema.routines
547
WHERE routine_schema = 'db_datadict';
548
SPECIFIC_NAME sp_6_408004
550
ROUTINE_SCHEMA db_datadict
551
ROUTINE_NAME sp_6_408004
552
ROUTINE_TYPE PROCEDURE
554
CHARACTER_MAXIMUM_LENGTH NULL
555
CHARACTER_OCTET_LENGTH NULL
556
NUMERIC_PRECISION NULL
558
CHARACTER_SET_NAME NULL
562
ROUTINE_DEFINITION BEGIN
563
DECLARE done INTEGER DEFAULt 0;
564
DECLARE variable_number_1 LONGTEXT;
565
DECLARE variable_number_2 MEDIUMINT;
566
DECLARE variable_number_3 LONGBLOB;
567
DECLARE variable_number_4 REAL;
568
DECLARE variable_number_5 YEAR;
569
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
570
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
571
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
572
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
573
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
574
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
576
OPEN cursor_number_1;
578
FETCH cursor_number_1
579
INTO variable_number_1, variable_number_2, variable_number_3,
580
variable_number_4, variable_number_5;
582
INSERT INTO res_6_408004_2
583
VALUES (variable_number_1, variable_number_2, variable_number_3,
584
variable_number_4, variable_number_5);
590
OPEN cursor_number_2;
592
FETCH cursor_number_2
593
INTO variable_number_1, variable_number_2, variable_number_3,
594
variable_number_4, variable_number_5;
596
INSERT INTO res_6_408004_2
597
VALUES(variable_number_1, variable_number_2, variable_number_3,
598
variable_number_4, variable_number_5);
603
OPEN cursor_number_3;
605
FETCH cursor_number_3
606
INTO variable_number_1, variable_number_2, variable_number_3,
607
variable_number_4, variable_number_5;
609
INSERT INTO res_6_408004_2
610
VALUES(variable_number_1, variable_number_2, variable_number_3,
611
variable_number_4, variable_number_5);
618
OPEN cursor_number_4;
620
FETCH cursor_number_4
621
INTO variable_number_1, variable_number_2, variable_number_3,
622
variable_number_4, variable_number_5;
624
INSERT INTO res_6_408004_2
625
VALUES (variable_number_1, variable_number_2, variable_number_3,
626
variable_number_4, variable_number_5);
638
OPEN cursor_number_5;
640
FETCH cursor_number_5
641
INTO variable_number_1, variable_number_2, variable_number_3,
642
variable_number_4, variable_number_5;
644
INSERT INTO res_6_408004_2
645
VALUES (variable_number_1, variable_number_2, variable_number_3,
646
variable_number_4, variable_number_5);
658
EXTERNAL_LANGUAGE NULL
661
SQL_DATA_ACCESS CONTAINS SQL
663
SECURITY_TYPE DEFINER
665
LAST_ALTERED <last_altered>
668
DEFINER root@localhost
669
CHARACTER_SET_CLIENT latin1
670
COLLATION_CONNECTION latin1_swedish_ci
671
DATABASE_COLLATION latin1_swedish_ci
672
LENGTH(routine_definition) 2549
673
DROP DATABASE db_datadict;
674
########################################################################
675
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
676
# DDL on INFORMATION_SCHEMA table are not supported
677
########################################################################
678
DROP DATABASE IF EXISTS db_datadict;
679
CREATE DATABASE db_datadict;
681
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
683
INSERT INTO information_schema.routines (routine_name, routine_type )
684
VALUES ('p2', 'procedure');
685
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
686
UPDATE information_schema.routines SET routine_name = 'p2'
687
WHERE routine_body = 'sql';
688
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
689
DELETE FROM information_schema.routines ;
690
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
691
TRUNCATE information_schema.routines ;
692
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
693
CREATE INDEX i7 ON information_schema.routines (routine_name);
694
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
695
ALTER TABLE information_schema.routines ADD f1 INT;
696
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
697
ALTER TABLE information_schema.routines DISCARD TABLESPACE;
698
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
699
DROP TABLE information_schema.routines ;
700
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
701
ALTER TABLE information_schema.routines RENAME db_datadict.routines;
702
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
703
ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
704
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
705
DROP DATABASE db_datadict;
added
removed
mysql-test/suite/funcs_1/r/is_routines.result
