Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'SCHEMATA_EXTENSIONS';
2 Tables_in_information_schema (SCHEMATA_EXTENSIONS)
3 SCHEMATA_EXTENSIONS
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_EXTENSIONS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.SCHEMATA_EXTENSIONS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.SCHEMATA_EXTENSIONS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.SCHEMATA_EXTENSIONS;
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_EXTENSIONS layout;
28 #########################################################################
29 DESCRIBE information_schema.SCHEMATA_EXTENSIONS;
30 Field Type Null Key Default Extra
31 CATALOG_NAME varchar(64) NO NULL
32 SCHEMA_NAME varchar(64) NO NULL
33 OPTIONS varchar(256) YES NULL
34 SHOW CREATE TABLE information_schema.SCHEMATA_EXTENSIONS;
35 View Create View character_set_client collation_connection
36 SCHEMATA_EXTENSIONS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`SCHEMATA_EXTENSIONS` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,get_dd_schema_options(`sch`.`options`) AS `OPTIONS` from (`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) where (0 <> can_access_database(`sch`.`name`)) utf8mb3 utf8mb3_general_ci
37 SHOW COLUMNS FROM information_schema.SCHEMATA_EXTENSIONS;
38 Field Type Null Key Default Extra
39 CATALOG_NAME varchar(64) NO NULL
40 SCHEMA_NAME varchar(64) NO NULL
41 OPTIONS varchar(256) YES NULL
42 SELECT catalog_name, schema_name
43 FROM information_schema.schemata_extensions
44 WHERE catalog_name != 'def';
45 CATALOG_NAME SCHEMA_NAME
46 #########################################################################
47 # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS
48 # accessible information
49 #########################################################################
50 DROP DATABASE IF EXISTS db_datadict_1;
51 DROP DATABASE IF EXISTS db_datadict_2;
52 CREATE DATABASE db_datadict_1;
53 CREATE DATABASE db_datadict_2;
54 DROP USER 'testuser1'@'localhost';
55 CREATE USER 'testuser1'@'localhost';
56 DROP USER 'testuser2'@'localhost';
57 CREATE USER 'testuser2'@'localhost';
58 DROP USER 'testuser3'@'localhost';
59 CREATE USER 'testuser3'@'localhost';
60 GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost';
61 GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost';
62 GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost';
63 SELECT * FROM information_schema.schemata_extensions
64 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
65 CATALOG_NAME SCHEMA_NAME OPTIONS
66 def db_datadict_1
67 def db_datadict_2
68 SHOW DATABASES LIKE 'db_datadict_%';
69 Database (db_datadict_%)
70 db_datadict_1
71 db_datadict_2
72 # Establish connection testuser1 (user=testuser1)
73 SELECT * FROM information_schema.schemata_extensions
74 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
75 CATALOG_NAME SCHEMA_NAME OPTIONS
76 def db_datadict_1
77 SHOW DATABASES LIKE 'db_datadict_%';
78 Database (db_datadict_%)
79 db_datadict_1
80 # Establish connection testuser2 (user=testuser2)
81 SELECT * FROM information_schema.schemata_extensions
82 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
83 CATALOG_NAME SCHEMA_NAME OPTIONS
84 def db_datadict_1
85 def db_datadict_2
86 SHOW DATABASES LIKE 'db_datadict_%';
87 Database (db_datadict_%)
88 db_datadict_1
89 db_datadict_2
90 # Establish connection testuser3 (user=testuser3)
91 SELECT * FROM information_schema.schemata_extensions
92 WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name;
93 CATALOG_NAME SCHEMA_NAME OPTIONS
94 SHOW DATABASES LIKE 'db_datadict_%';
95 Database (db_datadict_%)
96 # Switch to connection default and close connections testuser1,testuser2,
97 # testuser3
98 DROP USER 'testuser1'@'localhost';
99 DROP USER 'testuser2'@'localhost';
100 DROP USER 'testuser3'@'localhost';
101 DROP DATABASE db_datadict_1;
102 DROP DATABASE db_datadict_2;
103 #########################################################################
104 # Testcases 3.2.1.13+3.2.1.14+3.2.1.15:
105 # INFORMATION_SCHEMA.SCHEMATA_EXTENSIONS modifications
106 #########################################################################
107 DROP DATABASE IF EXISTS db_datadict;
108 SELECT * FROM information_schema.schemata_extensions
109 WHERE schema_name = 'db_datadict';
110 CATALOG_NAME SCHEMA_NAME OPTIONS
111 CREATE DATABASE db_datadict;
112 SELECT * FROM information_schema.schemata_extensions
113 WHERE schema_name = 'db_datadict';
114 CATALOG_NAME SCHEMA_NAME OPTIONS
115 def db_datadict
116 SELECT schema_name, options FROM information_schema.schemata_extensions
117 WHERE schema_name = 'db_datadict';
118 SCHEMA_NAME OPTIONS
119 db_datadict
120 ALTER SCHEMA db_datadict READ ONLY=1;
121 SELECT schema_name, options FROM information_schema.schemata_extensions
122 WHERE schema_name = 'db_datadict';
123 SCHEMA_NAME OPTIONS
124 db_datadict READ ONLY=1
125 ALTER SCHEMA db_datadict READ ONLY=0;
126 SELECT schema_name, options FROM information_schema.schemata_extensions
127 WHERE schema_name = 'db_datadict';
128 SCHEMA_NAME OPTIONS
129 db_datadict
130 SELECT schema_name
131 FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict';
132 SCHEMA_NAME
133 db_datadict
134 DROP DATABASE db_datadict;
135 SELECT schema_name
136 FROM information_schema.schemata_extensions WHERE schema_name = 'db_datadict';
137 SCHEMA_NAME
138 ########################################################################
139 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
140 # DDL on INFORMATION_SCHEMA tables are not supported
141 ########################################################################
142 DROP DATABASE IF EXISTS db_datadict;
143 CREATE DATABASE db_datadict;
144 INSERT INTO information_schema.schemata_extensions
145 (catalog_name, schema_name, options)
146 VALUES ('def', 'db1', '');
147 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
148 INSERT INTO information_schema.schemata_extensions
149 SELECT * FROM information_schema.schemata_extensions;
150 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
151 UPDATE information_schema.schemata_extensions
152 SET options = 'abc'
153 WHERE schema_name = 'db_datadict';
154 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
155 UPDATE information_schema.schemata_extensions SET catalog_name = 't_4711';
156 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
157 DELETE FROM information_schema.schemata_extensions
158 WHERE schema_name = 'db_datadict';
159 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
160 TRUNCATE information_schema.schemata_extensions;
161 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
162 CREATE INDEX i1 ON information_schema.schemata_extensions(schema_name);
163 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
164 ALTER TABLE information_schema.schemata_extensions ADD f1 INT;
165 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
166 DROP TABLE information_schema.schemata_extensions;
167 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
168 ALTER TABLE information_schema.schemata_extensions
169 RENAME db_datadict.schemata_extensions;
170 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
171 ALTER TABLE information_schema.schemata_extensions
172 RENAME information_schema.xschemata_extensions;
173 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
174 DROP DATABASE db_datadict;