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