Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'TABLES';
2 Tables_in_information_schema (TABLES)
3 TABLES
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.TABLES;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLES;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.TABLES;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.TABLES;
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.12.1: INFORMATION_SCHEMA.TABLES layout
28 #########################################################################
29 DESCRIBE information_schema.TABLES;
30 Field Type Null Key Default Extra
31 TABLE_CATALOG varchar(64) NO NULL
32 TABLE_SCHEMA varchar(64) NO NULL
33 TABLE_NAME varchar(64) NO NULL
34 TABLE_TYPE enum('BASE TABLE','VIEW','SYSTEM VIEW') NO NULL
35 ENGINE varchar(64) YES NULL
36 VERSION int YES NULL
37 ROW_FORMAT enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') YES NULL
38 TABLE_ROWS bigint unsigned YES NULL
39 AVG_ROW_LENGTH bigint unsigned YES NULL
40 DATA_LENGTH bigint unsigned YES NULL
41 MAX_DATA_LENGTH bigint unsigned YES NULL
42 INDEX_LENGTH bigint unsigned YES NULL
43 DATA_FREE bigint unsigned YES NULL
44 AUTO_INCREMENT bigint unsigned YES NULL
45 CREATE_TIME timestamp NO NULL
46 UPDATE_TIME datetime YES NULL
47 CHECK_TIME datetime YES NULL
48 TABLE_COLLATION varchar(64) YES NULL
49 CHECKSUM bigint YES NULL
50 CREATE_OPTIONS varchar(256) YES NULL
51 TABLE_COMMENT text YES NULL
52 SHOW CREATE TABLE information_schema.TABLES;
53 View Create View character_set_client collation_connection
54 TABLES CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLES` AS select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,if((`tbl`.`type` = 'VIEW'),NULL,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `TABLE_ROWS`,if((`tbl`.`type` = 'VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `AVG_ROW_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `MAX_DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `INDEX_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_FREE`,if((`tbl`.`type` = 'VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,((0 <> is_visible_dd_object(`tbl`.`hidden`,false,`tbl`.`options`)) is false),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `UPDATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,if((`tbl`.`type` = 'VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1),if((`sch`.`default_encryption` = 'YES'),1,0))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) utf8mb3 utf8mb3_general_ci
55 SHOW COLUMNS FROM information_schema.TABLES;
56 Field Type Null Key Default Extra
57 TABLE_CATALOG varchar(64) NO NULL
58 TABLE_SCHEMA varchar(64) NO NULL
59 TABLE_NAME varchar(64) NO NULL
60 TABLE_TYPE enum('BASE TABLE','VIEW','SYSTEM VIEW') NO NULL
61 ENGINE varchar(64) YES NULL
62 VERSION int YES NULL
63 ROW_FORMAT enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') YES NULL
64 TABLE_ROWS bigint unsigned YES NULL
65 AVG_ROW_LENGTH bigint unsigned YES NULL
66 DATA_LENGTH bigint unsigned YES NULL
67 MAX_DATA_LENGTH bigint unsigned YES NULL
68 INDEX_LENGTH bigint unsigned YES NULL
69 DATA_FREE bigint unsigned YES NULL
70 AUTO_INCREMENT bigint unsigned YES NULL
71 CREATE_TIME timestamp NO NULL
72 UPDATE_TIME datetime YES NULL
73 CHECK_TIME datetime YES NULL
74 TABLE_COLLATION varchar(64) YES NULL
75 CHECKSUM bigint YES NULL
76 CREATE_OPTIONS varchar(256) YES NULL
77 TABLE_COMMENT text YES NULL
78 SELECT table_catalog, table_schema, table_name
79 FROM information_schema.tables WHERE table_catalog IS NULL OR table_catalog <> 'def';
80 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
81 ################################################################################
82 # Testcase 3.2.12.2 + 3.2.12.3: INFORMATION_SCHEMA.TABLES accessible information
83 ################################################################################
84 DROP DATABASE IF EXISTS db_datadict;
85 CREATE DATABASE db_datadict charset latin1;
86 DROP USER 'testuser1'@'localhost';
87 CREATE USER 'testuser1'@'localhost';
88 GRANT CREATE, CREATE VIEW, INSERT, SELECT ON db_datadict.*
89 TO 'testuser1'@'localhost' WITH GRANT OPTION;
90 DROP USER 'testuser2'@'localhost';
91 CREATE USER 'testuser2'@'localhost';
92 DROP USER 'testuser3'@'localhost';
93 CREATE USER 'testuser3'@'localhost';
94 CREATE TABLE db_datadict.tb1 (f1 INT, f2 INT, f3 INT)
95 ENGINE = <engine_type>;
96 ANALYZE TABLE db_datadict.tb1;
97 Table Op Msg_type Msg_text
98 db_datadict.tb1 analyze status Table is already up to date
99 GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost';
100 GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION;
101 # Establish connection testuser1 (user=testuser1)
102 CREATE TABLE tb2 (f1 DECIMAL)
103 ENGINE = <engine_type>;
104 ANALYZE TABLE tb2;
105 Table Op Msg_type Msg_text
106 db_datadict.tb2 analyze status Table is already up to date
107 CREATE TABLE tb3 (f1 VARCHAR(200))
108 ENGINE = <engine_type>;
109 ANALYZE TABLE tb3;
110 Table Op Msg_type Msg_text
111 db_datadict.tb3 analyze status Table is already up to date
112 GRANT SELECT ON db_datadict.tb3 to 'testuser3'@'localhost';
113 GRANT INSERT ON db_datadict.tb3 to 'testuser2'@'localhost';
114 CREATE VIEW v3 AS SELECT * FROM tb3;
115 GRANT SELECT ON db_datadict.v3 to 'testuser3'@'localhost';
116 SELECT * FROM information_schema.tables
117 WHERE table_schema = 'db_datadict' ORDER BY table_name;
118 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
119 def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
120 def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
121 def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
122 def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW
123 SHOW TABLES FROM db_datadict;
124 Tables_in_db_datadict
125 tb1
126 tb2
127 tb3
128 v3
129 # Establish connection testuser2 (user=testuser2)
130 SELECT * FROM information_schema.tables
131 WHERE table_schema = 'db_datadict' ORDER BY table_name;
132 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
133 def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
134 def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
135 SHOW TABLES FROM db_datadict;
136 Tables_in_db_datadict
137 tb1
138 tb3
139 # Establish connection testuser3 (user=testuser3)
140 SELECT * FROM information_schema.tables
141 WHERE table_schema = 'db_datadict' ORDER BY table_name;
142 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
143 def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
144 def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW
145 SHOW TABLES FROM db_datadict;
146 Tables_in_db_datadict
147 tb3
148 v3
149 # Switch to connection default (user=root)
150 SELECT * FROM information_schema.tables
151 WHERE table_schema = 'db_datadict' ORDER BY table_name;
152 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
153 def db_datadict tb1 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
154 def db_datadict tb2 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
155 def db_datadict tb3 BASE TABLE #ENG# 10 #RF# 0 #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# latin1_swedish_ci #CS#
156 def db_datadict v3 VIEW #ENG# NULL #RF# NULL #ARL# #DL# #MDL# #IL# #DF# NULL #CRT #UT# #CT# NULL #CS# NULL VIEW
157 SHOW TABLES FROM db_datadict;
158 Tables_in_db_datadict
159 tb1
160 tb2
161 tb3
162 v3
163 # Close connection testuser1, testuser2, testuser3
164 DROP USER 'testuser1'@'localhost';
165 Warnings:
166 Warning 4005 User 'testuser1'@'localhost' is referenced as a definer account in a view.
167 DROP USER 'testuser2'@'localhost';
168 DROP USER 'testuser3'@'localhost';
169 DROP DATABASE db_datadict;
170 #########################################################################
171 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLES modifications
172 #########################################################################
173 DROP TABLE IF EXISTS test.t1_my_table;
174 DROP DATABASE IF EXISTS db_datadict;
175 CREATE DATABASE db_datadict charset latin1;
176 SELECT table_name FROM information_schema.tables
177 WHERE table_name LIKE 't1_my_table%';
178 TABLE_NAME
179 CREATE TABLE test.t1_my_table (f1 BIGINT)
180 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
181 COMMENT = 'Initial Comment' ENGINE = <engine_type>;
182 ANALYZE TABLE test.t1_my_table;
183 Table Op Msg_type Msg_text
184 test.t1_my_table analyze status Table is already up to date
185 SELECT * FROM information_schema.tables
186 WHERE table_name = 't1_my_table';
187 TABLE_CATALOG def
188 TABLE_SCHEMA test
189 TABLE_NAME t1_my_table
190 TABLE_TYPE BASE TABLE
191 ENGINE #ENG#
192 VERSION 10
193 ROW_FORMAT #RF#
194 TABLE_ROWS 0
195 AVG_ROW_LENGTH #ARL#
196 DATA_LENGTH #DL#
197 MAX_DATA_LENGTH #MDL#
198 INDEX_LENGTH #IL#
199 DATA_FREE #DF#
200 AUTO_INCREMENT NULL
201 CREATE_TIME #CRT
202 UPDATE_TIME #UT#
203 CHECK_TIME #CT#
204 TABLE_COLLATION latin1_swedish_ci
205 CHECKSUM #CS#
206 CREATE_OPTIONS
207 TABLE_COMMENT Initial Comment
208 SELECT table_name FROM information_schema.tables
209 WHERE table_name LIKE 't1_my_table%';
210 TABLE_NAME
211 t1_my_table
212 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
213 ANALYZE TABLE test.t1_my_tablex;
214 Table Op Msg_type Msg_text
215 test.t1_my_tablex analyze status Table is already up to date
216 SELECT table_name FROM information_schema.tables
217 WHERE table_name LIKE 't1_my_table%';
218 TABLE_NAME
219 t1_my_tablex
220 SELECT table_schema,table_name FROM information_schema.tables
221 WHERE table_name = 't1_my_tablex';
222 TABLE_SCHEMA TABLE_NAME
223 test t1_my_tablex
224 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
225 SELECT table_schema,table_name FROM information_schema.tables
226 WHERE table_name = 't1_my_tablex';
227 TABLE_SCHEMA TABLE_NAME
228 db_datadict t1_my_tablex
229 SELECT table_name, engine FROM information_schema.tables
230 WHERE table_name = 't1_my_tablex';
231 TABLE_NAME ENGINE
232 t1_my_tablex <engine_type>
233 ALTER TABLE db_datadict.t1_my_tablex
234 ENGINE = <other_engine_type>;
235 ANALYZE TABLE db_datadict.t1_my_tablex;
236 Table Op Msg_type Msg_text
237 db_datadict.t1_my_tablex analyze status Table is already up to date
238 SELECT table_name, engine FROM information_schema.tables
239 WHERE table_name = 't1_my_tablex';
240 TABLE_NAME ENGINE
241 t1_my_tablex <other_engine_type>
242 SELECT table_name, table_rows FROM information_schema.tables
243 WHERE table_name = 't1_my_tablex';
244 TABLE_NAME TABLE_ROWS
245 t1_my_tablex 0
246 INSERT INTO db_datadict.t1_my_tablex VALUES(1),(2);
247 ANALYZE TABLE db_datadict.t1_my_tablex;
248 Table Op Msg_type Msg_text
249 db_datadict.t1_my_tablex analyze status OK
250 SELECT table_name, table_rows FROM information_schema.tables
251 WHERE table_name = 't1_my_tablex';
252 TABLE_NAME TABLE_ROWS
253 t1_my_tablex 2
254 SELECT table_name, table_collation FROM information_schema.tables
255 WHERE table_name = 't1_my_tablex';
256 TABLE_NAME TABLE_COLLATION
257 t1_my_tablex latin1_swedish_ci
258 ALTER TABLE db_datadict.t1_my_tablex DEFAULT CHARACTER SET utf8;
259 Warnings:
260 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.
261 SELECT table_name, table_collation FROM information_schema.tables
262 WHERE table_name = 't1_my_tablex';
263 TABLE_NAME TABLE_COLLATION
264 t1_my_tablex utf8mb3_general_ci
265 SELECT table_name, table_collation FROM information_schema.tables
266 WHERE table_name = 't1_my_tablex';
267 TABLE_NAME TABLE_COLLATION
268 t1_my_tablex utf8mb3_general_ci
269 ALTER TABLE db_datadict.t1_my_tablex
270 DEFAULT CHARACTER SET latin1 COLLATE latin1_german1_ci;
271 SELECT table_name, table_collation FROM information_schema.tables
272 WHERE table_name = 't1_my_tablex';
273 TABLE_NAME TABLE_COLLATION
274 t1_my_tablex latin1_german1_ci
275 SELECT table_name, TABLE_COMMENT FROM information_schema.tables
276 WHERE table_name = 't1_my_tablex';
277 TABLE_NAME TABLE_COMMENT
278 t1_my_tablex Initial Comment
279 ALTER TABLE db_datadict.t1_my_tablex COMMENT 'Changed Comment';
280 SELECT table_name, TABLE_COMMENT FROM information_schema.tables
281 WHERE table_name = 't1_my_tablex';
282 TABLE_NAME TABLE_COMMENT
283 t1_my_tablex Changed Comment
284 SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
285 WHERE table_name = 't1_my_tablex';
286 TABLE_NAME AUTO_INCREMENT
287 t1_my_tablex NULL
288 ALTER TABLE db_datadict.t1_my_tablex
289 ADD f2 BIGINT AUTO_INCREMENT, ADD PRIMARY KEY (f2);
290 ANALYZE TABLE db_datadict.t1_my_tablex;
291 Table Op Msg_type Msg_text
292 db_datadict.t1_my_tablex analyze status OK
293 SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
294 WHERE table_name = 't1_my_tablex';
295 TABLE_NAME AUTO_INCREMENT
296 t1_my_tablex 3
297 SELECT table_name, ROW_FORMAT FROM information_schema.tables
298 WHERE table_name = 't1_my_tablex';
299 TABLE_NAME ROW_FORMAT
300 t1_my_tablex Fixed
301 ALTER TABLE db_datadict.t1_my_tablex ROW_FORMAT = dynamic;
302 ANALYZE TABLE db_datadict.t1_my_tablex;
303 Table Op Msg_type Msg_text
304 db_datadict.t1_my_tablex analyze status OK
305 SELECT table_name, ROW_FORMAT FROM information_schema.tables
306 WHERE table_name = 't1_my_tablex';
307 TABLE_NAME ROW_FORMAT
308 t1_my_tablex Dynamic
309 SELECT table_name, checksum FROM information_schema.tables
310 WHERE table_name = 't1_my_tablex';
311 TABLE_NAME CHECKSUM
312 t1_my_tablex NULL
313 ALTER TABLE db_datadict.t1_my_tablex CHECKSUM = 1;
314 ANALYZE TABLE db_datadict.t1_my_tablex;
315 Table Op Msg_type Msg_text
316 db_datadict.t1_my_tablex analyze status OK
317 SELECT table_name, checksum IS NOT NULL FROM information_schema.tables
318 WHERE table_name = 't1_my_tablex';
319 TABLE_NAME checksum IS NOT NULL
320 t1_my_tablex 1
321 SELECT UPDATE_TIME, checksum INTO @UPDATE_TIME, @checksum
322 FROM information_schema.tables
323 WHERE table_name = 't1_my_tablex';
324 INSERT INTO db_datadict.t1_my_tablex SET f1 = 3;
325 FLUSH TABLES;
326 ANALYZE TABLE db_datadict.t1_my_tablex;
327 Table Op Msg_type Msg_text
328 db_datadict.t1_my_tablex analyze status OK
329 SELECT UPDATE_TIME > @UPDATE_TIME
330 AS "Is current UPDATE_TIME bigger than before last INSERT?"
331 FROM information_schema.tables
332 WHERE table_name = 't1_my_tablex';
333 Is current UPDATE_TIME bigger than before last INSERT?
334 1
335 SELECT checksum <> @checksum
336 AS "Is current CHECKSUM different than before last INSERT?"
337 FROM information_schema.tables
338 WHERE table_name = 't1_my_tablex';
339 Is current CHECKSUM different than before last INSERT?
340 1
341 SELECT CREATE_TIME INTO @CREATE_TIME FROM information_schema.tables
342 WHERE table_name = 't1_my_tablex';
343 SELECT table_name FROM information_schema.tables
344 WHERE table_name LIKE 't1_my_table%';
345 TABLE_NAME
346 t1_my_tablex
347 DROP TABLE db_datadict.t1_my_tablex;
348 SELECT table_name FROM information_schema.tables
349 WHERE table_name LIKE 't1_my_table%';
350 TABLE_NAME
351 CREATE TABLE test.t1_my_tablex (f1 BIGINT)
352 ENGINE = <other_engine_type>;
353 ANALYZE TABLE test.t1_my_tablex;
354 Table Op Msg_type Msg_text
355 test.t1_my_tablex analyze status Table is already up to date
356 SELECT CREATE_TIME > @CREATE_TIME
357 AS "Is current CREATE_TIME bigger than for the old dropped table?"
358 FROM information_schema.tables
359 WHERE table_name = 't1_my_tablex';
360 Is current CREATE_TIME bigger than for the old dropped table?
361 1
362 DROP TABLE test.t1_my_tablex;
363 CREATE VIEW test.t1_my_tablex AS SELECT 1;
364 ANALYZE TABLE test.t1_my_tablex;
365 Table Op Msg_type Msg_text
366 test.t1_my_tablex analyze Error 'test.t1_my_tablex' is not BASE TABLE
367 test.t1_my_tablex analyze status Operation failed
368 SELECT * FROM information_schema.tables
369 WHERE table_name = 't1_my_tablex';
370 TABLE_CATALOG def
371 TABLE_SCHEMA test
372 TABLE_NAME t1_my_tablex
373 TABLE_TYPE VIEW
374 ENGINE NULL
375 VERSION NULL
376 ROW_FORMAT NULL
377 TABLE_ROWS NULL
378 AVG_ROW_LENGTH NULL
379 DATA_LENGTH NULL
380 MAX_DATA_LENGTH NULL
381 INDEX_LENGTH NULL
382 DATA_FREE NULL
383 AUTO_INCREMENT NULL
384 CREATE_TIME #CRT
385 UPDATE_TIME NULL
386 CHECK_TIME NULL
387 TABLE_COLLATION NULL
388 CHECKSUM NULL
389 CREATE_OPTIONS NULL
390 TABLE_COMMENT VIEW
391 DROP VIEW test.t1_my_tablex;
392 SELECT table_name FROM information_schema.tables
393 WHERE table_name = 't1_my_tablex';
394 TABLE_NAME
395 CREATE TEMPORARY TABLE test.t1_my_tablex
396 ENGINE = <other_engine_type>
397 AS SELECT 1;
398 SELECT table_name, table_type FROM information_schema.tables
399 WHERE table_name = 't1_my_tablex';
400 DROP TEMPORARY TABLE test.t1_my_tablex;
401 CREATE TABLE db_datadict.t1_my_tablex
402 ENGINE = <engine_type> AS
403 SELECT 1;
404 ANALYZE TABLE db_datadict.t1_my_tablex;
405 Table Op Msg_type Msg_text
406 db_datadict.t1_my_tablex analyze status OK
407 SELECT table_name FROM information_schema.tables
408 WHERE table_name = 't1_my_tablex';
409 TABLE_NAME
410 t1_my_tablex
411 DROP DATABASE db_datadict;
412 SELECT table_name FROM information_schema.tables
413 WHERE table_name = 't1_my_tablex';
414 TABLE_NAME
415 ########################################################################
416 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
417 # DDL on INFORMATION_SCHEMA tables are not supported
418 ########################################################################
419 DROP DATABASE IF EXISTS db_datadict;
420 CREATE DATABASE db_datadict charset latin1;
421 CREATE TABLE db_datadict.t1 (f1 BIGINT)
422 ENGINE = <engine_type>;
423 ANALYZE TABLE db_datadict.t1;
424 Table Op Msg_type Msg_text
425 db_datadict.t1 analyze status Table is already up to date
426 INSERT INTO information_schema.tables
427 SELECT * FROM information_schema.tables;
428 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
429 UPDATE information_schema.tables SET table_schema = 'test'
430 WHERE table_name = 't1';
431 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
432 DELETE FROM information_schema.tables WHERE table_name = 't1';
433 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
434 TRUNCATE information_schema.tables;
435 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
436 CREATE INDEX my_idx_on_tables ON information_schema.tables(table_schema);
437 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
438 ALTER TABLE information_schema.tables DROP PRIMARY KEY;
439 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
440 ALTER TABLE information_schema.tables ADD f1 INT;
441 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
442 DROP TABLE information_schema.tables;
443 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
444 ALTER TABLE information_schema.tables RENAME db_datadict.tables;
445 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
446 ALTER TABLE information_schema.tables RENAME information_schema.xtables;
447 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
448 DROP DATABASE db_datadict;