Debian Codegraph @

mysql-test/suite/funcs_1/r/is_schemata_embedded.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 def db_datadict_2 utf8mb4 utf8mb4_uca1400_ai_ci NULL
97 SHOW DATABASES LIKE 'db_datadict_%';
98 Database (db_datadict_%)
99 db_datadict_1
100 db_datadict_2
101 connect testuser2, localhost, testuser2, , db_datadict_2;
102 SELECT * FROM information_schema.schemata
103 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
104 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
105 def db_datadict_1 utf8mb4 utf8mb4_uca1400_ai_ci NULL
106 def db_datadict_2 utf8mb4 utf8mb4_uca1400_ai_ci NULL
107 SHOW DATABASES LIKE 'db_datadict_%';
108 Database (db_datadict_%)
109 db_datadict_1
110 db_datadict_2
111 connect testuser3, localhost, testuser3, ,"*NO-ONE*";
112 SELECT * FROM information_schema.schemata
113 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
114 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
115 def db_datadict_1 utf8mb4 utf8mb4_uca1400_ai_ci NULL
116 def db_datadict_2 utf8mb4 utf8mb4_uca1400_ai_ci NULL
117 SHOW DATABASES LIKE 'db_datadict_%';
118 Database (db_datadict_%)
119 db_datadict_1
120 db_datadict_2
121 connection default;
122 disconnect testuser1;
123 disconnect testuser2;
124 disconnect testuser3;
125 DROP USER 'testuser1'@'localhost';
126 DROP USER 'testuser2'@'localhost';
127 DROP USER 'testuser3'@'localhost';
128 DROP DATABASE db_datadict_1;
129 DROP DATABASE db_datadict_2;
130 #################################################################################
131 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
132 #################################################################################
133 DROP DATABASE IF EXISTS db_datadict;
134 SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
135 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
136 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
137 SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
138 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH SCHEMA_COMMENT
139 def db_datadict latin1 latin1_swedish_ci NULL
140 SELECT schema_name, default_character_set_name
141 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
142 schema_name default_character_set_name
143 db_datadict latin1
144 ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
145 SELECT schema_name, default_character_set_name
146 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
147 schema_name default_character_set_name
148 db_datadict utf8mb3
149 ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
150 SELECT schema_name, default_collation_name FROM information_schema.schemata
151 WHERE schema_name = 'db_datadict';
152 schema_name default_collation_name
153 db_datadict latin1_swedish_ci
154 ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
155 SELECT schema_name, default_collation_name FROM information_schema.schemata
156 WHERE schema_name = 'db_datadict';
157 schema_name default_collation_name
158 db_datadict latin1_general_cs
159 SELECT schema_name
160 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
161 schema_name
162 db_datadict
163 DROP DATABASE db_datadict;
164 SELECT schema_name
165 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
166 schema_name
167 ########################################################################
168 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
169 # DDL on INFORMATION_SCHEMA tables are not supported
170 ########################################################################
171 DROP DATABASE IF EXISTS db_datadict;
172 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
173 INSERT INTO information_schema.schemata
174 (catalog_name, schema_name, default_character_set_name, sql_path)
175 VALUES (NULL, 'db1', 'latin1', NULL);
176 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
177 INSERT INTO information_schema.schemata
178 SELECT * FROM information_schema.schemata;
179 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
180 UPDATE information_schema.schemata
181 SET default_character_set_name = 'utf8'
182 WHERE schema_name = 'db_datadict';
183 ERROR HY000: The target table schemata of the UPDATE is not updatable
184 UPDATE information_schema.schemata SET catalog_name = 't_4711';
185 ERROR HY000: The target table schemata of the UPDATE is not updatable
186 DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
187 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
188 TRUNCATE information_schema.schemata;
189 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
190 CREATE INDEX i1 ON information_schema.schemata(schema_name);
191 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
192 ALTER TABLE information_schema.schemata ADD f1 INT;
193 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
194 DROP TABLE information_schema.schemata;
195 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
196 ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
197 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
198 ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
199 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
200 DROP DATABASE db_datadict;