Debian Codegraph @

mysql-test/suite/funcs_1/r/is_schemata_ci.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(64) YES NULL
32 SCHEMA_NAME varchar(64) YES NULL
33 DEFAULT_CHARACTER_SET_NAME varchar(64) NO NULL
34 DEFAULT_COLLATION_NAME varchar(64) NO NULL
35 SQL_PATH binary(0) YES NULL
36 DEFAULT_ENCRYPTION enum('NO','YES') NO NULL
37 SHOW CREATE TABLE information_schema.SCHEMATA;
38 View Create View character_set_client collation_connection
39 SCHEMATA CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`SCHEMATA` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `CATALOG_NAME`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `SCHEMA_NAME`,`cs`.`name` AS `DEFAULT_CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATION_NAME`,NULL AS `SQL_PATH`,`sch`.`default_encryption` AS `DEFAULT_ENCRYPTION` from (((`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`sch`.`default_collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where (0 <> can_access_database(`sch`.`name`)) utf8mb3 utf8mb3_general_ci
40 SHOW COLUMNS FROM information_schema.SCHEMATA;
41 Field Type Null Key Default Extra
42 CATALOG_NAME varchar(64) YES NULL
43 SCHEMA_NAME varchar(64) YES NULL
44 DEFAULT_CHARACTER_SET_NAME varchar(64) NO NULL
45 DEFAULT_COLLATION_NAME varchar(64) NO NULL
46 SQL_PATH binary(0) YES NULL
47 DEFAULT_ENCRYPTION enum('NO','YES') NO NULL
48 SELECT catalog_name, schema_name, sql_path
49 FROM information_schema.schemata
50 WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL
51 ORDER BY schema_name;
52 CATALOG_NAME SCHEMA_NAME SQL_PATH
53 def information_schema NULL
54 def mtr NULL
55 def mysql NULL
56 def performance_schema NULL
57 def sys NULL
58 def test NULL
59 ###############################################################################
60 # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information
61 ###############################################################################
62 DROP DATABASE IF EXISTS db_datadict_1;
63 DROP DATABASE IF EXISTS db_datadict_2;
64 CREATE DATABASE db_datadict_1 charset latin1;
65 CREATE DATABASE db_datadict_2 charset latin1;
66 DROP USER 'testuser1'@'localhost';
67 CREATE USER 'testuser1'@'localhost';
68 DROP USER 'testuser2'@'localhost';
69 CREATE USER 'testuser2'@'localhost';
70 DROP USER 'testuser3'@'localhost';
71 CREATE USER 'testuser3'@'localhost';
72 GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
73 GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
74 GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
75 SELECT * FROM information_schema.schemata
76 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
77 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION
78 def db_datadict_1 latin1 latin1_swedish_ci NULL NO
79 def db_datadict_2 latin1 latin1_swedish_ci NULL NO
80 SHOW DATABASES LIKE 'db_datadict_%';
81 Database (db_datadict_%)
82 db_datadict_1
83 db_datadict_2
84 # Establish connection testuser1 (user=testuser1)
85 SELECT * FROM information_schema.schemata
86 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
87 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION
88 def db_datadict_1 latin1 latin1_swedish_ci NULL NO
89 SHOW DATABASES LIKE 'db_datadict_%';
90 Database (db_datadict_%)
91 db_datadict_1
92 # Establish connection testuser2 (user=testuser2)
93 SELECT * FROM information_schema.schemata
94 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
95 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION
96 def db_datadict_1 latin1 latin1_swedish_ci NULL NO
97 def db_datadict_2 latin1 latin1_swedish_ci NULL NO
98 SHOW DATABASES LIKE 'db_datadict_%';
99 Database (db_datadict_%)
100 db_datadict_1
101 db_datadict_2
102 # Establish connection testuser3 (user=testuser3)
103 SELECT * FROM information_schema.schemata
104 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
105 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION
106 SHOW DATABASES LIKE 'db_datadict_%';
107 Database (db_datadict_%)
108 # Switch to connection default and close connections testuser1,testuser2,testuser3
109 DROP USER 'testuser1'@'localhost';
110 DROP USER 'testuser2'@'localhost';
111 DROP USER 'testuser3'@'localhost';
112 DROP DATABASE db_datadict_1;
113 DROP DATABASE db_datadict_2;
114 #################################################################################
115 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications
116 #################################################################################
117 DROP DATABASE IF EXISTS db_datadict;
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 DEFAULT_ENCRYPTION
120 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
121 SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict';
122 CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH DEFAULT_ENCRYPTION
123 def db_datadict latin1 latin1_swedish_ci NULL NO
124 SELECT schema_name, default_character_set_name
125 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
126 SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME
127 db_datadict latin1
128 ALTER SCHEMA db_datadict CHARACTER SET 'utf8';
129 Warnings:
130 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
131 SELECT schema_name, default_character_set_name
132 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
133 SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME
134 db_datadict utf8mb3
135 ALTER SCHEMA db_datadict CHARACTER SET 'latin1';
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_swedish_ci
140 ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs';
141 SELECT schema_name, default_collation_name FROM information_schema.schemata
142 WHERE schema_name = 'db_datadict';
143 SCHEMA_NAME DEFAULT_COLLATION_NAME
144 db_datadict latin1_general_cs
145 SELECT schema_name
146 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
147 SCHEMA_NAME
148 db_datadict
149 DROP DATABASE db_datadict;
150 SELECT schema_name
151 FROM information_schema.schemata WHERE schema_name = 'db_datadict';
152 SCHEMA_NAME
153 ########################################################################
154 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
155 # DDL on INFORMATION_SCHEMA tables are not supported
156 ########################################################################
157 DROP DATABASE IF EXISTS db_datadict;
158 CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
159 INSERT INTO information_schema.schemata
160 (catalog_name, schema_name, default_character_set_name, sql_path)
161 VALUES (NULL, 'db1', 'latin1', NULL);
162 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
163 INSERT INTO information_schema.schemata
164 SELECT * FROM information_schema.schemata;
165 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
166 UPDATE information_schema.schemata
167 SET default_character_set_name = 'utf8'
168 WHERE schema_name = 'db_datadict';
169 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
170 UPDATE information_schema.schemata SET catalog_name = 't_4711';
171 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
172 DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict';
173 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
174 TRUNCATE information_schema.schemata;
175 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
176 CREATE INDEX i1 ON information_schema.schemata(schema_name);
177 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
178 ALTER TABLE information_schema.schemata ADD f1 INT;
179 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
180 DROP TABLE information_schema.schemata;
181 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
182 ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
183 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
184 ALTER TABLE information_schema.schemata RENAME information_schema.xschemata;
185 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
186 DROP DATABASE db_datadict;