summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/funcs_1/r/is_schemata.result
blob: 1851c089b04c19a85cc9354bd34ac45dc05957ff (plain)
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
SHOW TABLES FROM information_schema LIKE 'SCHEMATA';
Tables_in_information_schema (SCHEMATA)
SCHEMATA
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.SCHEMATA;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMATA;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.SCHEMATA;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.SCHEMATA;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA layout;
#########################################################################
DESCRIBE          information_schema.SCHEMATA;
Field	Type	Null	Key	Default	Extra
CATALOG_NAME	varchar(512)	NO		NULL	
SCHEMA_NAME	varchar(64)	NO		NULL	
DEFAULT_CHARACTER_SET_NAME	varchar(32)	NO		NULL	
DEFAULT_COLLATION_NAME	varchar(64)	NO		NULL	
SQL_PATH	varchar(512)	YES		NULL	
SCHEMA_COMMENT	varchar(1024)	NO		NULL	
SHOW CREATE TABLE information_schema.SCHEMATA;
Table	Create Table
SCHEMATA	CREATE TEMPORARY TABLE `SCHEMATA` (
  `CATALOG_NAME` varchar(512) NOT NULL,
  `SCHEMA_NAME` varchar(64) NOT NULL,
  `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL,
  `DEFAULT_COLLATION_NAME` varchar(64) NOT NULL,
  `SQL_PATH` varchar(512),
  `SCHEMA_COMMENT` varchar(1024) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SHOW COLUMNS FROM information_schema.SCHEMATA;
Field	Type	Null	Key	Default	Extra
CATALOG_NAME	varchar(512)	NO		NULL	
SCHEMA_NAME	varchar(64)	NO		NULL	
DEFAULT_CHARACTER_SET_NAME	varchar(32)	NO		NULL	
DEFAULT_COLLATION_NAME	varchar(64)	NO		NULL	
SQL_PATH	varchar(512)	YES		NULL	
SCHEMA_COMMENT	varchar(1024)	NO		NULL	
SELECT catalog_name, schema_name, sql_path
FROM information_schema.schemata
WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL
ORDER BY schema_name;
catalog_name	schema_name	sql_path
def	information_schema	NULL
def	mtr	NULL
def	mysql	NULL
def	performance_schema	NULL
def	sys	NULL
def	test	NULL
###############################################################################
# Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information
###############################################################################
DROP DATABASE IF EXISTS db_datadict_1;
DROP DATABASE IF EXISTS db_datadict_2;
CREATE DATABASE db_datadict_1;
CREATE DATABASE db_datadict_2;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP   USER 'testuser3'@'localhost';
CREATE USER 'testuser3'@'localhost';
GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
SELECT * FROM information_schema.schemata
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	db_datadict_1	latin1	latin1_swedish_ci	NULL	
def	db_datadict_2	latin1	latin1_swedish_ci	NULL	
SHOW DATABASES LIKE 'db_datadict_%';
Database (db_datadict_%)
db_datadict_1
db_datadict_2
connect  testuser1, localhost, testuser1, , db_datadict_1;
SELECT * FROM information_schema.schemata
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	db_datadict_1	latin1	latin1_swedish_ci	NULL	
SHOW DATABASES LIKE 'db_datadict_%';
Database (db_datadict_%)
db_datadict_1
connect  testuser2, localhost, testuser2, , db_datadict_2;
SELECT * FROM information_schema.schemata
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	db_datadict_1	latin1	latin1_swedish_ci	NULL	
def	db_datadict_2	latin1	latin1_swedish_ci	NULL	
SHOW DATABASES LIKE 'db_datadict_%';
Database (db_datadict_%)
db_datadict_1
db_datadict_2
connect  testuser3, localhost, testuser3, ,"*NO-ONE*";
SELECT * FROM information_schema.schemata
WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
SHOW DATABASES LIKE 'db_datadict_%';
Database (db_datadict_%)
connection default;
disconnect testuser1;
disconnect testuser2;
disconnect testuser3;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
DROP DATABASE db_datadict_1;
DROP DATABASE db_datadict_2;
#################################################################################
# Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
#################################################################################
DROP DATABASE IF EXISTS db_datadict;
SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH	SCHEMA_COMMENT
def	db_datadict	latin1	latin1_swedish_ci	NULL	
SELECT schema_name, default_character_set_name
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
schema_name	default_character_set_name
db_datadict	latin1
ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
SELECT schema_name, default_character_set_name
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
schema_name	default_character_set_name
db_datadict	utf8mb3
ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
SELECT schema_name, default_collation_name FROM information_schema.schemata
WHERE schema_name = 'db_datadict';
schema_name	default_collation_name
db_datadict	latin1_swedish_ci
ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
SELECT schema_name, default_collation_name FROM information_schema.schemata
WHERE schema_name = 'db_datadict';
schema_name	default_collation_name
db_datadict	latin1_general_cs
SELECT schema_name
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
schema_name
db_datadict
DROP DATABASE db_datadict;
SELECT schema_name
FROM information_schema.schemata WHERE schema_name = 'db_datadict';
schema_name
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA tables are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
INSERT INTO information_schema.schemata
(catalog_name, schema_name, default_character_set_name, sql_path)
VALUES (NULL, 'db1', 'latin1', NULL);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.schemata
SELECT * FROM information_schema.schemata;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.schemata
SET default_character_set_name = 'utf8'
WHERE schema_name = 'db_datadict';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.schemata SET catalog_name = 't_4711';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.schemata;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i1 ON information_schema.schemata(schema_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schemata ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.schemata;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;