Debian Codegraph @

mysql-test/suite/funcs_1/r/is_schemata.result

raw
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) NO NULL
32 SCHEMA_NAME varchar(64) NO NULL
33 DEFAULT_CHARACTER_SET_NAME varchar(32) NO NULL
34 DEFAULT_COLLATION_NAME varchar(64) NO NULL
35 SQL_PATH varchar(512) YES NULL
36 SCHEMA_COMMENT varchar(1024) NO NULL
37 SHOW CREATE TABLE information_schema.SCHEMATA;
38 Table Create Table
39 SCHEMATA CREATE TEMPORARY TABLE `SCHEMATA` (
40 `CATALOG_NAME` varchar(512) NOT NULL,
41 `SCHEMA_NAME` varchar(64) NOT NULL,
42 `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL,
43 `DEFAULT_COLLATION_NAME` varchar(64) NOT NULL,
44 `SQL_PATH` varchar(512),
45 `SCHEMA_COMMENT` varchar(1024) NOT NULL
46 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
47 SHOW COLUMNS FROM information_schema.SCHEMATA;
48 Field Type Null Key Default Extra
49 CATALOG_NAME varchar(512) NO NULL
50 SCHEMA_NAME varchar(64) NO NULL
51 DEFAULT_CHARACTER_SET_NAME varchar(32) NO NULL
52 DEFAULT_COLLATION_NAME varchar(64) NO NULL
53 SQL_PATH varchar(512) YES NULL
54 SCHEMA_COMMENT varchar(1024) NO NULL
55 SELECT catalog_name, schema_name, sql_path
56 FROM information_schema.schemata
57 WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL
58 ORDER BY schema_name;
59 catalog_name schema_name sql_path
60 def information_schema NULL
61 def mtr NULL
62 def mysql NULL
63 def performance_schema NULL
64 def sys NULL
65 def test NULL
66 ###############################################################################
67 # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information
68 ###############################################################################
69 DROP DATABASE IF EXISTS db_datadict_1;
70 DROP DATABASE IF EXISTS db_datadict_2;
71 CREATE DATABASE db_datadict_1;
72 CREATE DATABASE db_datadict_2;
73 DROP USER 'testuser1'@'localhost';
74 CREATE USER 'testuser1'@'localhost';
75 DROP USER 'testuser2'@'localhost';
76 CREATE USER 'testuser2'@'localhost';
77 DROP USER 'testuser3'@'localhost';
78 CREATE USER 'testuser3'@'localhost';
79 GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
80 GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
81 GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
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 SCHEMA_COMMENT
85 def db_datadict_1 utf8mb4 utf8mb4_uca1400_ai_ci NULL
86 def db_datadict_2 utf8mb4 utf8mb4_uca1400_ai_ci NULL
87 SHOW DATABASES LIKE 'db_datadict_%';
88 Database (db_datadict_%)
89 db_datadict_1
90 db_datadict_2
91 connect testuser1, localhost, testuser1, , db_datadict_1;
92 SELECT * FROM information_schema.schemata
93 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
94 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
95 def db_datadict_1 utf8mb4 utf8mb4_uca1400_ai_ci NULL
96 SHOW DATABASES LIKE 'db_datadict_%';
97 Database (db_datadict_%)
98 db_datadict_1
99 connect testuser2, localhost, testuser2, , db_datadict_2;
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 SCHEMA_COMMENT
103 def db_datadict_1 utf8mb4 utf8mb4_uca1400_ai_ci NULL
104 def db_datadict_2 utf8mb4 utf8mb4_uca1400_ai_ci NULL
105 SHOW DATABASES LIKE 'db_datadict_%';
106 Database (db_datadict_%)
107 db_datadict_1
108 db_datadict_2
109 connect testuser3, localhost, testuser3, ,"*NO-ONE*";
110 SELECT * FROM information_schema.schemata
111 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
112 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
113 SHOW DATABASES LIKE 'db_datadict_%';
114 Database (db_datadict_%)
115 connection default;
116 disconnect testuser1;
117 disconnect testuser2;
118 disconnect testuser3;
119 DROP USER 'testuser1'@'localhost';
120 DROP USER 'testuser2'@'localhost';
121 DROP USER 'testuser3'@'localhost';
122 DROP DATABASE db_datadict_1;
123 DROP DATABASE db_datadict_2;
124 #################################################################################
125 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
126 #################################################################################
127 DROP DATABASE IF EXISTS db_datadict;
128 SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
129 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
130 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
131 SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
132 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
133 def db_datadict latin1 latin1_swedish_ci NULL
134 SELECT schema_name, default_character_set_name
135 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
136 schema_name default_character_set_name
137 db_datadict latin1
138 ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
139 SELECT schema_name, default_character_set_name
140 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
141 schema_name default_character_set_name
142 db_datadict utf8mb3
143 ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
144 SELECT schema_name, default_collation_name FROM information_schema.schemata
145 WHERE schema_name = 'db_datadict';
146 schema_name default_collation_name
147 db_datadict latin1_swedish_ci
148 ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
149 SELECT schema_name, default_collation_name FROM information_schema.schemata
150 WHERE schema_name = 'db_datadict';
151 schema_name default_collation_name
152 db_datadict latin1_general_cs
153 SELECT schema_name
154 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
155 schema_name
156 db_datadict
157 DROP DATABASE db_datadict;
158 SELECT schema_name
159 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
160 schema_name
161 ########################################################################
162 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
163 # DDL on INFORMATION_SCHEMA tables are not supported
164 ########################################################################
165 DROP DATABASE IF EXISTS db_datadict;
166 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
167 INSERT INTO information_schema.schemata
168 (catalog_name, schema_name, default_character_set_name, sql_path)
169 VALUES (NULL, 'db1', 'latin1', NULL);
170 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
171 INSERT INTO information_schema.schemata
172 SELECT * FROM information_schema.schemata;
173 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
174 UPDATE information_schema.schemata
175 SET default_character_set_name = 'utf8'
176 WHERE schema_name = 'db_datadict';
177 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
178 UPDATE information_schema.schemata SET catalog_name = 't_4711';
179 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
180 DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
181 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
182 TRUNCATE information_schema.schemata;
183 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
184 CREATE INDEX i1 ON information_schema.schemata(schema_name);
185 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
186 ALTER TABLE information_schema.schemata ADD f1 INT;
187 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
188 DROP TABLE information_schema.schemata;
189 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
190 ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
191 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
192 ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
193 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
194 DROP DATABASE db_datadict;