|
1
by brian
clean slate |
1 |
SHOW TABLES FROM information_schema LIKE 'SCHEMATA';
|
2 |
Tables_in_information_schema (SCHEMATA)
|
|
3 |
SCHEMATA
|
|
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.SCHEMATA;
|
|
11 |
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMATA;
|
|
12 |
CREATE FUNCTION test.f1() returns BIGINT
|
|
13 |
BEGIN
|
|
14 |
DECLARE counter BIGINT DEFAULT NULL;
|
|
15 |
SELECT COUNT(*) INTO counter FROM information_schema.SCHEMATA;
|
|
16 |
RETURN counter;
|
|
17 |
END//
|
|
18 |
# Attention: The printing of the next result sets is disabled.
|
|
19 |
SELECT * FROM information_schema.SCHEMATA;
|
|
20 |
SELECT * FROM test.v1;
|
|
21 |
CALL test.p1;
|
|
22 |
SELECT test.f1();
|
|
23 |
DROP VIEW test.v1;
|
|
24 |
DROP PROCEDURE test.p1;
|
|
25 |
DROP FUNCTION test.f1;
|
|
26 |
#########################################################################
|
|
27 |
# Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA layout;
|
|
28 |
#########################################################################
|
|
29 |
DESCRIBE information_schema.SCHEMATA;
|
|
30 |
Field Type Null Key Default Extra
|
|
31 |
CATALOG_NAME varchar(512) YES NULL
|
|
32 |
SCHEMA_NAME varchar(64) NO
|
|
33 |
DEFAULT_CHARACTER_SET_NAME varchar(64) NO
|
|
34 |
DEFAULT_COLLATION_NAME varchar(64) NO
|
|
35 |
SQL_PATH varchar(512) YES NULL
|
|
36 |
SHOW CREATE TABLE information_schema.SCHEMATA;
|
|
37 |
Table Create Table
|
|
38 |
SCHEMATA CREATE TEMPORARY TABLE `SCHEMATA` (
|
|
39 |
`CATALOG_NAME` varchar(512) DEFAULT NULL,
|
|
40 |
`SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
|
|
41 |
`DEFAULT_CHARACTER_SET_NAME` varchar(64) NOT NULL DEFAULT '',
|
|
42 |
`DEFAULT_COLLATION_NAME` varchar(64) NOT NULL DEFAULT '',
|
|
43 |
`SQL_PATH` varchar(512) DEFAULT NULL
|
|
44 |
) ENGINE=MEMORY DEFAULT CHARSET=utf8
|
|
45 |
SHOW COLUMNS FROM information_schema.SCHEMATA;
|
|
46 |
Field Type Null Key Default Extra
|
|
47 |
CATALOG_NAME varchar(512) YES NULL
|
|
48 |
SCHEMA_NAME varchar(64) NO
|
|
49 |
DEFAULT_CHARACTER_SET_NAME varchar(64) NO
|
|
50 |
DEFAULT_COLLATION_NAME varchar(64) NO
|
|
51 |
SQL_PATH varchar(512) YES NULL
|
|
52 |
SELECT catalog_name, schema_name, sql_path
|
|
53 |
FROM information_schema.schemata
|
|
54 |
WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL;
|
|
55 |
catalog_name schema_name sql_path
|
|
56 |
###############################################################################
|
|
57 |
# Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information
|
|
58 |
###############################################################################
|
|
59 |
DROP DATABASE IF EXISTS db_datadict_1;
|
|
60 |
DROP DATABASE IF EXISTS db_datadict_2;
|
|
61 |
CREATE DATABASE db_datadict_1;
|
|
62 |
CREATE DATABASE db_datadict_2;
|
|
63 |
DROP USER 'testuser1'@'localhost';
|
|
64 |
CREATE USER 'testuser1'@'localhost';
|
|
65 |
DROP USER 'testuser2'@'localhost';
|
|
66 |
CREATE USER 'testuser2'@'localhost';
|
|
67 |
DROP USER 'testuser3'@'localhost';
|
|
68 |
CREATE USER 'testuser3'@'localhost';
|
|
69 |
GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
|
|
70 |
GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
|
|
71 |
GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
|
|
72 |
SELECT * FROM information_schema.schemata
|
|
73 |
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
|
|
74 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
75 |
NULL db_datadict_1 latin1 latin1_swedish_ci NULL
|
|
76 |
NULL db_datadict_2 latin1 latin1_swedish_ci NULL
|
|
77 |
SHOW DATABASES LIKE 'db_datadict_%';
|
|
78 |
Database (db_datadict_%)
|
|
79 |
db_datadict_1
|
|
80 |
db_datadict_2
|
|
81 |
# Establish connection testuser1 (user=testuser1)
|
|
82 |
SELECT * FROM information_schema.schemata
|
|
83 |
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
|
|
84 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
85 |
NULL db_datadict_1 latin1 latin1_swedish_ci NULL
|
|
86 |
SHOW DATABASES LIKE 'db_datadict_%';
|
|
87 |
Database (db_datadict_%)
|
|
88 |
db_datadict_1
|
|
89 |
# Establish connection testuser2 (user=testuser2)
|
|
90 |
SELECT * FROM information_schema.schemata
|
|
91 |
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
|
|
92 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
93 |
NULL db_datadict_1 latin1 latin1_swedish_ci NULL
|
|
94 |
NULL db_datadict_2 latin1 latin1_swedish_ci NULL
|
|
95 |
SHOW DATABASES LIKE 'db_datadict_%';
|
|
96 |
Database (db_datadict_%)
|
|
97 |
db_datadict_1
|
|
98 |
db_datadict_2
|
|
99 |
# Establish connection testuser3 (user=testuser3)
|
|
100 |
SELECT * FROM information_schema.schemata
|
|
101 |
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
|
|
102 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
103 |
SHOW DATABASES LIKE 'db_datadict_%';
|
|
104 |
Database (db_datadict_%)
|
|
105 |
# Switch to connection default and close connections testuser1,testuser2,testuser3
|
|
106 |
DROP USER 'testuser1'@'localhost';
|
|
107 |
DROP USER 'testuser2'@'localhost';
|
|
108 |
DROP USER 'testuser3'@'localhost';
|
|
109 |
DROP DATABASE db_datadict_1;
|
|
110 |
DROP DATABASE db_datadict_2;
|
|
111 |
#################################################################################
|
|
112 |
# Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
|
|
113 |
#################################################################################
|
|
114 |
DROP DATABASE IF EXISTS db_datadict;
|
|
115 |
SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
116 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
117 |
CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
|
|
118 |
SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
119 |
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
|
|
120 |
NULL db_datadict latin1 latin1_swedish_ci NULL
|
|
121 |
SELECT schema_name, default_character_set_name
|
|
122 |
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
123 |
schema_name default_character_set_name
|
|
124 |
db_datadict latin1
|
|
125 |
ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
|
|
126 |
SELECT schema_name, default_character_set_name
|
|
127 |
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
128 |
schema_name default_character_set_name
|
|
129 |
db_datadict utf8
|
|
130 |
ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
|
|
131 |
SELECT schema_name, default_collation_name FROM information_schema.schemata
|
|
132 |
WHERE schema_name = 'db_datadict';
|
|
133 |
schema_name default_collation_name
|
|
134 |
db_datadict latin1_swedish_ci
|
|
135 |
ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
|
|
136 |
SELECT schema_name, default_collation_name FROM information_schema.schemata
|
|
137 |
WHERE schema_name = 'db_datadict';
|
|
138 |
schema_name default_collation_name
|
|
139 |
db_datadict latin1_general_cs
|
|
140 |
SELECT schema_name
|
|
141 |
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
142 |
schema_name
|
|
143 |
db_datadict
|
|
144 |
DROP DATABASE db_datadict;
|
|
145 |
SELECT schema_name
|
|
146 |
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
147 |
schema_name
|
|
148 |
########################################################################
|
|
149 |
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
|
|
150 |
# DDL on INFORMATION_SCHEMA tables are not supported
|
|
151 |
########################################################################
|
|
152 |
DROP DATABASE IF EXISTS db_datadict;
|
|
153 |
CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
|
|
154 |
INSERT INTO information_schema.schemata
|
|
155 |
(catalog_name, schema_name, default_character_set_name, sql_path)
|
|
156 |
VALUES (NULL, 'db1', 'latin1', NULL);
|
|
157 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
158 |
INSERT INTO information_schema.schemata
|
|
159 |
SELECT * FROM information_schema.schemata;
|
|
160 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
161 |
UPDATE information_schema.schemata
|
|
162 |
SET default_character_set_name = 'utf8'
|
|
163 |
WHERE schema_name = 'db_datadict';
|
|
164 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
165 |
UPDATE information_schema.schemata SET catalog_name = 't_4711';
|
|
166 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
167 |
DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
|
|
168 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
169 |
TRUNCATE information_schema.schemata;
|
|
170 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
171 |
CREATE INDEX i1 ON information_schema.schemata(schema_name);
|
|
172 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
173 |
ALTER TABLE information_schema.schemata ADD f1 INT;
|
|
174 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
175 |
DROP TABLE information_schema.schemata;
|
|
176 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
177 |
ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
|
|
178 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
179 |
ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
|
|
180 |
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
|
|
181 |
DROP DATABASE db_datadict;
|