Debian Codegraph @

mysql-test/suite/funcs_1/t/is_routines.test

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