Debian Codegraph @

mysql-test/suite/funcs_1/datadict/is_routines.inc

raw
1 # suite/funcs_1/datadict/is_routines.inc
2 #
3 # Check the layout of information_schema.routines and the impact of
4 # CREATE/ALTER/DROP PROCEDURE/FUNCTION ... on it.
5 #
6 # Note:
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.
12 #
13 # Author:
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 # Create this script based on older scripts and new code.
17 # Last Change:
18 # 2008-06-11 mleich Move t/is_routines.test to this file and
19 # create variants for embedded/non embedded server.
20 #
21
22 --source include/default_charset.inc
23 set sql_mode="";
24
25 let $engine_type = MEMORY;
26 let $other_engine_type = MyISAM;
27
28 let $is_table = ROUTINES;
29
30 # The table INFORMATION_SCHEMA.TABLES must exist
31 eval SHOW TABLES FROM information_schema LIKE '$is_table';
32
33 --echo #######################################################################
34 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
35 --echo #######################################################################
36 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
37 # statement, just as if it were an ordinary user-defined table.
38 #
39 --source suite/funcs_1/datadict/is_table_query.inc
40
41
42 --echo #########################################################################
43 --echo # Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
44 --echo #########################################################################
45 # Ensure that the INFORMATION_SCHEMA.ROUTINES table has the following columns,
46 # in the following order:
47 #
48 # SPECIFIC_NAME (shows the name of an accessible stored procedure, or routine),
49 # ROUTINE_CATALOG (always shows NULL),
50 # ROUTINE_SCHEMA (shows the database, or schema, in which the routine resides),
51 # ROUTINE_NAME (shows the same stored procedure name),
52 # ROUTINE_TYPE (shows whether the stored procedure is a procedure or a function),
53 # DTD_IDENTIFIER (shows, for a function, the complete data type definition of
54 # the value the function will return; otherwise NULL),
55 # ROUTINE_BODY (shows the language in which the stored procedure is written;
56 # currently always SQL),
57 # ROUTINE_DEFINITION (shows as much of the routine body as is possible in the
58 # allotted space),
59 # EXTERNAL_NAME (always shows NULL),
60 # EXTERNAL_LANGUAGE (always shows NULL),
61 # PARAMETER_STYLE (shows the routine's parameter style; always SQL),
62 # IS_DETERMINISTIC (shows whether the routine is deterministic),
63 # SQL_DATA_ACCESS (shows the routine's defined sql-data-access clause value),
64 # SQL_PATH (always shows NULL),
65 # SECURITY_TYPE (shows whether the routine's defined security_type is 'definer'
66 # or 'invoker'),
67 # CREATED (shows the timestamp of the time the routine was created),
68 # LAST_ALTERED (shows the timestamp of the time the routine was last altered),
69 # SQL_MODE (shows the sql_mode setting at the time the routine was created),
70 # ROUTINE_COMMENT (shows the comment, if any, defined for the routine;
71 # otherwise NULL),
72 # DEFINER (shows the user who created the routine).
73 # Starting with MySQL 5.1
74 # CHARACTER_SET_CLIENT
75 # COLLATION_CONNECTION
76 # DATABASE_COLLATION
77 #
78 --source suite/funcs_1/datadict/datadict_bug_12777.inc
79 eval DESCRIBE information_schema.$is_table;
80 --source suite/funcs_1/datadict/datadict_bug_12777.inc
81 --replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
82 eval SHOW CREATE TABLE information_schema.$is_table;
83 --source suite/funcs_1/datadict/datadict_bug_12777.inc
84 eval SHOW COLUMNS FROM information_schema.$is_table;
85
86 USE test;
87 --disable_warnings
88 DROP PROCEDURE IF EXISTS sp_for_routines;
89 DROP FUNCTION IF EXISTS function_for_routines;
90 --enable_warnings
91 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
92 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
93
94 # Show that the column values of
95 # ROUTINE_CATALOG, EXTERNAL_NAME, EXTERNAL_LANGUAGE, SQL_PATH are always NULL
96 # and
97 # ROUTINE_BODY, PARAMETER_STYLE are 'SQL'
98 # and
99 # SPECIFIC_NAME = ROUTINE_NAME.
100 SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
101 routine_body,external_name,external_language,parameter_style,sql_path
102 FROM information_schema.routines
103 WHERE routine_schema = 'test' AND
104 (routine_catalog IS NOT NULL OR external_name IS NOT NULL
105 OR external_language IS NOT NULL OR sql_path IS NOT NULL
106 OR routine_body <> 'SQL' OR parameter_style <> 'SQL'
107 OR specific_name <> routine_name);
108
109 DROP PROCEDURE sp_for_routines;
110 DROP FUNCTION function_for_routines;
111
112
113 --echo ################################################################################
114 --echo # Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
115 --echo ################################################################################
116 # 3.2.8.2: Ensure that the table shows the relevant information on every SQL-invoked
117 # routine (i.e. stored procedure) which is accessible to the current user
118 # or to PUBLIC.
119 # 3.2.8.3: Ensure that the table does not show any information on any stored procedure
120 # that is not accessible to the current user or PUBLIC.
121 #
122 --disable_warnings
123 DROP DATABASE IF EXISTS db_datadict;
124 DROP DATABASE IF EXISTS db_datadict_2;
125 --enable_warnings
126
127 CREATE DATABASE db_datadict;
128 USE db_datadict;
129 --replace_result $other_engine_type <other_engine_type>
130 eval
131 CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
132 ENGINE = $other_engine_type;
133 INSERT INTO res_6_408002_1(f1, f2, f3, f4)
134 VALUES('abc', 'xyz', '1989-11-09', 0815);
135 --disable_warnings
136 DROP PROCEDURE IF EXISTS sp_6_408002_1;
137 --enable_warnings
138 delimiter //;
139 CREATE PROCEDURE sp_6_408002_1()
140 BEGIN
141 SELECT * FROM db_datadict.res_6_408002_1;
142 END//
143 delimiter ;//
144
145 CREATE DATABASE db_datadict_2;
146 USE db_datadict_2;
147 --replace_result $other_engine_type <other_engine_type>
148 eval
149 CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
150 ENGINE = $other_engine_type;
151 INSERT INTO res_6_408002_2(f1, f2, f3, f4)
152 VALUES('abc', 'xyz', '1990-10-03', 4711);
153 --disable_warnings
154 DROP PROCEDURE IF EXISTS sp_6_408002_2;
155 --enable_warnings
156 delimiter //;
157 CREATE PROCEDURE sp_6_408002_2()
158 BEGIN
159 SELECT * FROM db_datadict_2.res_6_408002_2;
160 END//
161 delimiter ;//
162
163 --error 0,ER_CANNOT_USER
164 DROP USER 'testuser1'@'localhost';
165 CREATE USER 'testuser1'@'localhost';
166 --error 0,ER_CANNOT_USER
167 DROP USER 'testuser2'@'localhost';
168 CREATE USER 'testuser2'@'localhost';
169 --error 0,ER_CANNOT_USER
170 DROP USER 'testuser3'@'localhost';
171 CREATE USER 'testuser3'@'localhost';
172
173
174 GRANT SELECT ON db_datadict_2.* TO 'testuser1'@'localhost';
175 GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
176
177 GRANT EXECUTE ON db_datadict.* TO 'testuser1'@'localhost';
178 GRANT SELECT ON db_datadict.* TO 'testuser2'@'localhost';
179
180 GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
181 TO 'testuser2'@'localhost';
182 GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
183 FLUSH PRIVILEGES;
184
185 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
186 connect (testuser1, localhost, testuser1, , db_datadict);
187 --replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss"
188 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
189
190 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
191 connect (testuser2, localhost, testuser2, , db_datadict);
192 --replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss"
193 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
194
195 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
196 connect (testuser3, localhost, testuser3, , "*NO-ONE*");
197 --replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss"
198 SELECT * FROM information_schema.routines where routine_schema <> 'sys';
199
200 # Cleanup
201 connection default;
202 disconnect testuser1;
203 disconnect testuser2;
204 disconnect testuser3;
205
206 DROP USER 'testuser1'@'localhost';
207 DROP USER 'testuser2'@'localhost';
208 DROP USER 'testuser3'@'localhost';
209
210 USE test;
211 DROP DATABASE db_datadict;
212 DROP DATABASE db_datadict_2;
213
214
215 --echo #########################################################################
216 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
217 --echo #########################################################################
218 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
219 # column) automatically inserts all relevant information on that
220 # object into every appropriate INFORMATION_SCHEMA table.
221 # 3.2.1.14: Ensure that the alteration of any existing database object
222 # automatically updates all relevant information on that object in
223 # every appropriate INFORMATION_SCHEMA table.
224 # 3.2.1.15: Ensure that the dropping of any existing database object
225 # automatically deletes all relevant information on that object from
226 # every appropriate INFORMATION_SCHEMA table.
227 #
228 # Some more tests are in t/information_schema_routines.test which exists
229 # in MySQL 5.1 and up only.
230 #
231 --disable_warnings
232 DROP DATABASE IF EXISTS db_datadict;
233 --enable_warnings
234 CREATE DATABASE db_datadict;
235
236 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
237 USE db_datadict;
238 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
239 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
240 --vertical_results
241 --replace_column 24 <created> 25 <modified>
242 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
243 ORDER BY routine_name;
244 --horizontal_results
245
246 ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
247 ALTER FUNCTION function_for_routines COMMENT 'updated comments';
248 --vertical_results
249 --replace_column 24 <created> 25 <modified>
250 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
251 ORDER BY routine_name;
252 --horizontal_results
253
254 DROP PROCEDURE sp_for_routines;
255 DROP FUNCTION function_for_routines;
256 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
257
258 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
259 CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
260 --vertical_results
261 --replace_column 24 <created> 25 <modified>
262 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
263 ORDER BY routine_name;
264 --horizontal_results
265 use test;
266 DROP DATABASE db_datadict;
267 SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
268
269
270 --echo #########################################################################
271 --echo # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
272 --echo # ROUTINE_DEFINITION column
273 --echo #########################################################################
274 # Ensure that a stored procedure with a routine body that is too large to fit
275 # into the INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION column correctly shows
276 # as much of the information as is possible within the allotted size.
277 #
278 --disable_warnings
279 DROP DATABASE IF EXISTS db_datadict;
280 --enable_warnings
281 CREATE DATABASE db_datadict;
282 USE db_datadict;
283 #
284 --replace_result $other_engine_type <other_engine_type>
285 eval
286 CREATE TABLE db_datadict.res_6_408004_1
287 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
288 ENGINE = $other_engine_type;
289 INSERT INTO db_datadict.res_6_408004_1
290 VALUES ('abc', 98765 , 99999999 , 98765, 10);
291 #
292 --replace_result $other_engine_type <other_engine_type>
293 eval
294 CREATE TABLE db_datadict.res_6_408004_2
295 (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
296 ENGINE = $other_engine_type;
297 INSERT INTO db_datadict.res_6_408004_2
298 VALUES ('abc', 98765 , 99999999 , 98765, 10);
299
300 --echo # Checking the max. possible length of (currently) 4 GByte is not
301 --echo # in this environment here.
302
303 delimiter //;
304 CREATE PROCEDURE sp_6_408004 ()
305 BEGIN
306 DECLARE done INTEGER DEFAULt 0;
307 DECLARE variable_number_1 LONGTEXT;
308 DECLARE variable_number_2 MEDIUMINT;
309 DECLARE variable_number_3 LONGBLOB;
310 DECLARE variable_number_4 REAL;
311 DECLARE variable_number_5 YEAR;
312 DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
313 DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
314 DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
315 DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
316 DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
317 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
318 BEGIN
319 OPEN cursor_number_1;
320 WHILE done <> 1 DO
321 FETCH cursor_number_1
322 INTO variable_number_1, variable_number_2, variable_number_3,
323 variable_number_4, variable_number_5;
324 IF done <> 0 THEN
325 INSERT INTO res_6_408004_2
326 VALUES (variable_number_1, variable_number_2, variable_number_3,
327 variable_number_4, variable_number_5);
328 END IF;
329 END WHILE;
330 BEGIN
331 BEGIN
332 SET done = 0;
333 OPEN cursor_number_2;
334 WHILE done <> 1 DO
335 FETCH cursor_number_2
336 INTO variable_number_1, variable_number_2, variable_number_3,
337 variable_number_4, variable_number_5;
338 IF done <> 0 THEN
339 INSERT INTO res_6_408004_2
340 VALUES(variable_number_1, variable_number_2, variable_number_3,
341 variable_number_4, variable_number_5);
342 END IF;
343 END WHILE;
344 END;
345 SET done = 0;
346 OPEN cursor_number_3;
347 WHILE done <> 1 DO
348 FETCH cursor_number_3
349 INTO variable_number_1, variable_number_2, variable_number_3,
350 variable_number_4, variable_number_5;
351 IF done <> 0 THEN
352 INSERT INTO res_6_408004_2
353 VALUES(variable_number_1, variable_number_2, variable_number_3,
354 variable_number_4, variable_number_5);
355 END IF;
356 END WHILE;
357 END;
358 END;
359 BEGIN
360 SET done = 0;
361 OPEN cursor_number_4;
362 WHILE done <> 1 DO
363 FETCH cursor_number_4
364 INTO variable_number_1, variable_number_2, variable_number_3,
365 variable_number_4, variable_number_5;
366 IF done <> 0 THEN
367 INSERT INTO res_6_408004_2
368 VALUES (variable_number_1, variable_number_2, variable_number_3,
369 variable_number_4, variable_number_5);
370 END IF;
371 END WHILE;
372 END;
373 BEGIN
374 SET @a='test row';
375 SELECT @a;
376 SELECT @a;
377 SELECT @a;
378 END;
379 BEGIN
380 SET done = 0;
381 OPEN cursor_number_5;
382 WHILE done <> 1 DO
383 FETCH cursor_number_5
384 INTO variable_number_1, variable_number_2, variable_number_3,
385 variable_number_4, variable_number_5;
386 IF done <> 0 THEN
387 INSERT INTO res_6_408004_2
388 VALUES (variable_number_1, variable_number_2, variable_number_3,
389 variable_number_4, variable_number_5);
390 END IF;
391 END WHILE;
392 END;
393 BEGIN
394 SET @a='test row';
395 SELECT @a;
396 SELECT @a;
397 SELECT @a;
398 END;
399 END//
400 delimiter ;//
401
402 CALL db_datadict.sp_6_408004 ();
403 SELECT * FROM db_datadict.res_6_408004_2;
404
405 --vertical_results
406 --replace_column 24 "YYYY-MM-DD hh:mm:ss" 25 "YYYY-MM-DD hh:mm:ss"
407 SELECT *, LENGTH(routine_definition) FROM information_schema.routines
408 WHERE routine_schema = 'db_datadict';
409 --horizontal_results
410
411 # Cleanup
412 DROP DATABASE db_datadict;
413 # ----------------------------------------------------------------------------------------------
414
415
416 --echo ########################################################################
417 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
418 --echo # DDL on INFORMATION_SCHEMA table are not supported
419 --echo ########################################################################
420 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
421 # INFORMATION_SCHEMA table.
422 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
423 # INFORMATION_SCHEMA table.
424 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
425 # INFORMATION_SCHEMA table.
426 # 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table.
427 # 3.2.1.9: Ensure that no user may alter the definition of an
428 # INFORMATION_SCHEMA table.
429 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
430 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
431 # other database.
432 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
433 # in an INFORMATION_SCHEMA table.
434 #
435 --disable_warnings
436 DROP DATABASE IF EXISTS db_datadict;
437 --enable_warnings
438 CREATE DATABASE db_datadict;
439 USE db_datadict;
440 CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
441 USE test;
442
443 # Note(mleich):
444 # 1. We can get here different error messages.
445 # 2. We do not want to unify the individual messages to the far to unspecific
446 # 'Got one of the listed errors'.
447 let $my_error_message =
448 ##### The previous statement must fail ######
449 # Server type | expected error name | expected error message
450 # --------------------------------------------------------------------------------------------------------------------
451 # not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
452 # embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into
453 # | or similar | or similar
454 ;
455
456 --disable_abort_on_error
457 INSERT INTO information_schema.routines (routine_name, routine_type )
458 VALUES ('p2', 'procedure');
459 if (!$mysql_errno)
460 {
461 --echo $my_error_message
462 exit;
463 }
464 UPDATE information_schema.routines SET routine_name = 'p2'
465 WHERE routine_body = 'sql';
466 if (!$mysql_errno)
467 {
468 --echo $my_error_message
469 exit;
470 }
471 DELETE FROM information_schema.routines ;
472 if (!$mysql_errno)
473 {
474 --echo $my_error_message
475 exit;
476 }
477 TRUNCATE information_schema.routines ;
478 if (!$mysql_errno)
479 {
480 --echo $my_error_message
481 exit;
482 }
483 CREATE INDEX i7 ON information_schema.routines (routine_name);
484 if (!$mysql_errno)
485 {
486 --echo $my_error_message
487 exit;
488 }
489 ALTER TABLE information_schema.routines ADD f1 INT;
490 if (!$mysql_errno)
491 {
492 --echo $my_error_message
493 exit;
494 }
495 ALTER TABLE information_schema.routines DISCARD TABLESPACE;
496 if (!$mysql_errno)
497 {
498 --echo $my_error_message
499 exit;
500 }
501 DROP TABLE information_schema.routines ;
502 if (!$mysql_errno)
503 {
504 --echo $my_error_message
505 exit;
506 }
507 ALTER TABLE information_schema.routines RENAME db_datadict.routines;
508 if (!$mysql_errno)
509 {
510 --echo $my_error_message
511 exit;
512 }
513 ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
514 if (!$mysql_errno)
515 {
516 --echo $my_error_message
517 exit;
518 }
519 --enable_abort_on_error
520
521 # Cleanup
522 DROP DATABASE db_datadict;
523