1
# suite/funcs_1/t/is_routines.test
3
# Check the layout of information_schema.routines and the impact of
4
# CREATE/ALTER/DROP PROCEDURE/FUNCTION ... on it.
7
# This test is not intended
8
# - to show information about the all time existing routines (there are no
9
# in the moment) within the databases information_schema and mysql
10
# - for checking storage engine properties
11
# Therefore please do not alter $engine_type and $other_engine_type.
14
# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
16
# Create this script based on older scripts and new code.
19
# --source suite/funcs_1/datadict/datadict.pre
21
let $engine_type = MEMORY;
22
let $other_engine_type = MyISAM;
24
let $is_table = ROUTINES;
26
# The table INFORMATION_SCHEMA.TABLES must exist
27
eval SHOW TABLES FROM information_schema LIKE '$is_table';
29
--echo #######################################################################
30
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
31
--echo #######################################################################
32
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
33
# statement, just as if it were an ordinary user-defined table.
35
--source suite/funcs_1/datadict/is_table_query.inc
38
--echo #########################################################################
39
--echo # Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
40
--echo #########################################################################
41
# Ensure that the INFORMATION_SCHEMA.ROUTINES table has the following columns,
42
# in the following order:
44
# SPECIFIC_NAME (shows the name of an accessible stored procedure, or routine),
45
# ROUTINE_CATALOG (always shows NULL),
46
# ROUTINE_SCHEMA (shows the database, or schema, in which the routine resides),
47
# ROUTINE_NAME (shows the same stored procedure name),
48
# ROUTINE_TYPE (shows whether the stored procedure is a procedure or a function),
49
# DTD_IDENTIFIER (shows, for a function, the complete data type definition of
50
# the value the function will return; otherwise NULL),
51
# ROUTINE_BODY (shows the language in which the stored procedure is written;
52
# currently always SQL),
53
# ROUTINE_DEFINITION (shows as much of the routine body as is possible in the
55
# EXTERNAL_NAME (always shows NULL),
56
# EXTERNAL_LANGUAGE (always shows NULL),
57
# PARAMETER_STYLE (shows the routine's parameter style; always SQL),
58
# IS_DETERMINISTIC (shows whether the routine is deterministic),
59
# SQL_DATA_ACCESS (shows the routine's defined sql-data-access clause value),
60
# SQL_PATH (always shows NULL),
61
# SECURITY_TYPE (shows whether the routine's defined security_type is 'definer'
63
# CREATED (shows the timestamp of the time the routine was created),
64
# LAST_ALTERED (shows the timestamp of the time the routine was last altered),
65
# SQL_MODE (shows the sql_mode setting at the time the routine was created),
66
# ROUTINE_COMMENT (shows the comment, if any, defined for the routine;
68
# DEFINER (shows the user who created the routine).
69
# Starting with MySQL 5.1
70
# CHARACTER_SET_CLIENT
71
# COLLATION_CONNECTION
74
--source suite/funcs_1/datadict/datadict_bug_12777.inc
75
eval DESCRIBE information_schema.$is_table;
76
--source suite/funcs_1/datadict/datadict_bug_12777.inc
77
eval SHOW CREATE TABLE information_schema.$is_table;
78
--source suite/funcs_1/datadict/datadict_bug_12777.inc
79
eval SHOW COLUMNS FROM information_schema.$is_table;
83
DROP PROCEDURE IF EXISTS sp_for_routines;
84
DROP FUNCTION IF EXISTS function_for_routines;
86
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
87
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
89
# Show that the column values of
90
# ROUTINE_CATALOG, EXTERNAL_NAME, EXTERNAL_LANGUAGE, SQL_PATH are always NULL
92
# ROUTINE_BODY, PARAMETER_STYLE are 'SQL'
94
# SPECIFIC_NAME = ROUTINE_NAME.
95
SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
96
routine_body,external_name,external_language,parameter_style,sql_path
97
FROM information_schema.routines
98
WHERE routine_catalog IS NOT NULL OR external_name IS NOT NULL
99
OR external_language IS NOT NULL OR sql_path IS NOT NULL
100
OR routine_body <> 'SQL' OR parameter_style <> 'SQL'
101
OR specific_name <> routine_name;
103
DROP PROCEDURE sp_for_routines;
104
DROP FUNCTION function_for_routines;
107
--echo ################################################################################
108
--echo # Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
109
--echo ################################################################################
110
# 3.2.8.2: Ensure that the table shows the relevant information on every SQL-invoked
111
# routine (i.e. stored procedure) which is accessible to the current user
113
# 3.2.8.3: Ensure that the table does not show any information on any stored procedure
114
# that is not accessible to the current user or PUBLIC.
117
DROP DATABASE IF EXISTS db_datadict;
118
DROP DATABASE IF EXISTS db_datadict_2;
121
CREATE DATABASE db_datadict;
123
--replace_result $other_engine_type <other_engine_type>
125
CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
126
ENGINE = $other_engine_type;
127
INSERT INTO res_6_408002_1(f1, f2, f3, f4)
128
VALUES('abc', 'xyz', '1989-11-09', 0815);
130
DROP PROCEDURE IF EXISTS sp_6_408002_1;
133
CREATE PROCEDURE sp_6_408002_1()
135
SELECT * FROM db_datadict.res_6_408002_1;
139
CREATE DATABASE db_datadict_2;
141
--replace_result $other_engine_type <other_engine_type>
143
CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
144
ENGINE = $other_engine_type;
145
INSERT INTO res_6_408002_2(f1, f2, f3, f4)
146
VALUES('abc', 'xyz', '1990-10-03', 4711);
148
DROP PROCEDURE IF EXISTS sp_6_408002_2;
151
CREATE PROCEDURE sp_6_408002_2()
153
SELECT * FROM db_datadict_2.res_6_408002_2;
157
--error 0,ER_CANNOT_USER
158
DROP USER 'testuser1'@'localhost';
159
CREATE USER 'testuser1'@'localhost';
160
--error 0,ER_CANNOT_USER
161
DROP USER 'testuser2'@'localhost';
162
CREATE USER 'testuser2'@'localhost';
163
--error 0,ER_CANNOT_USER
164
DROP USER 'testuser3'@'localhost';
165
CREATE USER 'testuser3'@'localhost';
168
GRANT SELECT ON db_datadict_2.* TO 'testuser1'@'localhost';
169
GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
171
GRANT EXECUTE ON db_datadict.* TO 'testuser1'@'localhost';
172
GRANT SELECT ON db_datadict.* TO 'testuser2'@'localhost';
174
GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
175
TO 'testuser2'@'localhost';
176
GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
179
--echo # Establish connection testuser1 (user=testuser1)
180
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
181
connect (testuser1, localhost, testuser1, , db_datadict);
182
--replace_column 23 <created> 24 <last_altered>
183
SELECT * FROM information_schema.routines;
185
--echo # Establish connection testuser2 (user=testuser2)
186
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
187
connect (testuser2, localhost, testuser2, , db_datadict);
188
--replace_column 23 <created> 24 <last_altered>
189
SELECT * FROM information_schema.routines;
191
--echo # Establish connection testuser3 (user=testuser3)
192
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
193
connect (testuser3, localhost, testuser3, , test);
194
--replace_column 23 <created> 24 <last_altered>
195
SELECT * FROM information_schema.routines;
198
--echo # Switch to connection default and close connections testuser1,testuser2,testuser3
200
disconnect testuser1;
201
disconnect testuser2;
202
disconnect testuser3;
204
DROP USER 'testuser1'@'localhost';
205
DROP USER 'testuser2'@'localhost';
206
DROP USER 'testuser3'@'localhost';
209
DROP DATABASE db_datadict;
210
DROP DATABASE db_datadict_2;
213
--echo #########################################################################
214
--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
215
--echo #########################################################################
216
# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
217
# column) automatically inserts all relevant information on that
218
# object into every appropriate INFORMATION_SCHEMA table.
219
# 3.2.1.14: Ensure that the alteration of any existing database object
220
# automatically updates all relevant information on that object in
221
# every appropriate INFORMATION_SCHEMA table.
222
# 3.2.1.15: Ensure that the dropping of any existing database object
223
# automatically deletes all relevant information on that object from
224
# every appropriate INFORMATION_SCHEMA table.
226
# Some more tests are in t/information_schema_routines.test which exists
227
# in MySQL 5.1 and up only.
230
DROP DATABASE IF EXISTS db_datadict;
232
CREATE DATABASE db_datadict;
234
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
236
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
237
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
239
--replace_column 23 <created> 24 <last_altered>
240
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
241
ORDER BY routine_name;
244
ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
245
ALTER FUNCTION function_for_routines COMMENT 'updated comments';
247
--replace_column 23 <created> 24 <last_altered>
248
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
249
ORDER BY routine_name;
252
DROP PROCEDURE sp_for_routines;
253
DROP FUNCTION function_for_routines;
254
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
256
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
257
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
259
--replace_column 23 <created> 24 <last_altered>
260
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
261
ORDER BY routine_name;
264
DROP DATABASE db_datadict;
265
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
268
--echo #########################################################################
269
--echo # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
270
--echo # ROUTINE_DEFINITION column
271
--echo #########################################################################
272
# Ensure that a stored procedure with a routine body that is too large to fit
273
# into the INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION column correctly shows
274
# as much of the information as is possible within the allotted size.
277
DROP DATABASE IF EXISTS db_datadict;
279
CREATE DATABASE db_datadict;
282
--replace_result $other_engine_type <other_engine_type>
284
CREATE TABLE db_datadict.res_6_408004_1
285
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
286
ENGINE = $other_engine_type;
287
INSERT INTO db_datadict.res_6_408004_1
288
VALUES ('abc', 98765 , 99999999 , 98765, 10);
290
--replace_result $other_engine_type <other_engine_type>
292
CREATE TABLE db_datadict.res_6_408004_2
293
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
294
ENGINE = $other_engine_type;
295
INSERT INTO db_datadict.res_6_408004_2
296
VALUES ('abc', 98765 , 99999999 , 98765, 10);
298
--echo # Checking the max. possible length of (currently) 4 GByte is not
299
--echo # in this environment here.
302
CREATE PROCEDURE sp_6_408004 ()
304
DECLARE done INTEGER DEFAULt 0;
305
DECLARE variable_number_1 LONGTEXT;
306
DECLARE variable_number_2 MEDIUMINT;
307
DECLARE variable_number_3 LONGBLOB;
308
DECLARE variable_number_4 REAL;
309
DECLARE variable_number_5 YEAR;
310
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
311
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
312
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
313
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
314
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
315
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
317
OPEN cursor_number_1;
319
FETCH cursor_number_1
320
INTO variable_number_1, variable_number_2, variable_number_3,
321
variable_number_4, variable_number_5;
323
INSERT INTO res_6_408004_2
324
VALUES (variable_number_1, variable_number_2, variable_number_3,
325
variable_number_4, variable_number_5);
331
OPEN cursor_number_2;
333
FETCH cursor_number_2
334
INTO variable_number_1, variable_number_2, variable_number_3,
335
variable_number_4, variable_number_5;
337
INSERT INTO res_6_408004_2
338
VALUES(variable_number_1, variable_number_2, variable_number_3,
339
variable_number_4, variable_number_5);
344
OPEN cursor_number_3;
346
FETCH cursor_number_3
347
INTO variable_number_1, variable_number_2, variable_number_3,
348
variable_number_4, variable_number_5;
350
INSERT INTO res_6_408004_2
351
VALUES(variable_number_1, variable_number_2, variable_number_3,
352
variable_number_4, variable_number_5);
359
OPEN cursor_number_4;
361
FETCH cursor_number_4
362
INTO variable_number_1, variable_number_2, variable_number_3,
363
variable_number_4, variable_number_5;
365
INSERT INTO res_6_408004_2
366
VALUES (variable_number_1, variable_number_2, variable_number_3,
367
variable_number_4, variable_number_5);
379
OPEN cursor_number_5;
381
FETCH cursor_number_5
382
INTO variable_number_1, variable_number_2, variable_number_3,
383
variable_number_4, variable_number_5;
385
INSERT INTO res_6_408004_2
386
VALUES (variable_number_1, variable_number_2, variable_number_3,
387
variable_number_4, variable_number_5);
400
CALL db_datadict.sp_6_408004 ();
401
SELECT * FROM db_datadict.res_6_408004_2;
404
--replace_column 23 <created> 24 <last_altered>
405
SELECT *, LENGTH(routine_definition) FROM information_schema.routines
406
WHERE routine_schema = 'db_datadict';
410
DROP DATABASE db_datadict;
411
# ----------------------------------------------------------------------------------------------
414
--echo ########################################################################
415
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
416
--echo # DDL on INFORMATION_SCHEMA table are not supported
417
--echo ########################################################################
418
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
419
# INFORMATION_SCHEMA table.
420
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
421
# INFORMATION_SCHEMA table.
422
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
423
# INFORMATION_SCHEMA table.
424
# 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table.
425
# 3.2.1.9: Ensure that no user may alter the definition of an
426
# INFORMATION_SCHEMA table.
427
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
428
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
430
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
431
# in an INFORMATION_SCHEMA table.
434
DROP DATABASE IF EXISTS db_datadict;
436
CREATE DATABASE db_datadict;
438
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
441
--error ER_DBACCESS_DENIED_ERROR
442
INSERT INTO information_schema.routines (routine_name, routine_type )
443
VALUES ('p2', 'procedure');
445
--error ER_DBACCESS_DENIED_ERROR
446
UPDATE information_schema.routines SET routine_name = 'p2'
447
WHERE routine_body = 'sql';
449
--error ER_DBACCESS_DENIED_ERROR
450
DELETE FROM information_schema.routines ;
452
--error ER_DBACCESS_DENIED_ERROR
453
TRUNCATE information_schema.routines ;
455
--error ER_DBACCESS_DENIED_ERROR
456
CREATE INDEX i7 ON information_schema.routines (routine_name);
458
--error ER_DBACCESS_DENIED_ERROR
459
ALTER TABLE information_schema.routines ADD f1 INT;
461
--error ER_DBACCESS_DENIED_ERROR
462
ALTER TABLE information_schema.routines DISCARD TABLESPACE;
464
--error ER_DBACCESS_DENIED_ERROR
465
DROP TABLE information_schema.routines ;
467
--error ER_DBACCESS_DENIED_ERROR
468
ALTER TABLE information_schema.routines RENAME db_datadict.routines;
470
--error ER_DBACCESS_DENIED_ERROR
471
ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
474
DROP DATABASE db_datadict;