1
# check that CSV engine was compiled in, as the result of the test
2
# depends on the presence of the log tables (which are CSV-based).
3
--source include/have_csv.inc
6
# Test of some show commands
10
drop table if exists t1,t2;
11
drop table if exists t1aa,t2aa;
12
drop database if exists mysqltest;
13
drop database if exists mysqltest1;
17
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
18
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
20
--echo -- Here we enable metadata just to check that the collation of the
21
--echo -- resultset is non-binary for string type. This should be changed
22
--echo -- after Bug#29394 is implemented.
27
check table t1 changed;
28
insert into t1 values (5,5,5);
29
check table t1 changed;
30
check table t1 medium;
31
check table t1 extended;
35
insert into t1 values (5,5,5);
37
--echo -- Here we enable metadata just to check that the collation of the
38
--echo -- resultset is non-binary for string type. This should be changed
39
--echo -- after Bug#29394 is implemented.
49
--echo -- Here we enable metadata just to check that the collation of the
50
--echo -- resultset is non-binary for string type. This should be changed
51
--echo -- after Bug#29394 is implemented.
54
show variables like "wait_timeout%";
55
show variables like "WAIT_timeout%";
56
show variables like "this_doesn't_exists%";
57
show table status from test like "this_doesn't_exists%";
59
show databases like "test%";
65
create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4));
66
insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1);
68
--echo -- Here we enable metadata just to check that the collation of the
69
--echo -- resultset is non-binary for string type. This should be changed
70
--echo -- after Bug#29394 is implemented.
77
--echo -- Here we enable metadata just to check that the collation of the
78
--echo -- resultset is non-binary for string type. This should be changed
79
--echo -- after Bug#29394 is implemented.
92
create temporary table t1 (a int not null);
94
alter table t1 rename t2;
99
test_set set( 'val1', 'val2', 'val3' ) not null default '',
100
name char(20) default 'O''Brien' comment 'O''Brien as default',
101
c int not null comment 'int column',
102
`c-b` int comment 'name with a minus',
103
`space 2` int comment 'name with a space'
104
) comment = 'it\'s a table' ;
105
show create table t1;
106
set sql_quote_show_create=0;
107
show create table t1;
108
set sql_quote_show_create=1;
109
show full columns from t1;
112
create table t1 (a int not null, unique aa (a));
113
show create table t1;
115
create table t1 (a int not null, primary key (a));
116
show create table t1;
121
create table t1(n int);
122
insert into t1 values (1);
126
create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
127
show create table t1;
128
alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
129
show create table t1;
130
ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
131
show create table t1;
134
create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
135
show columns from t1;
136
show full columns from t1;
140
# Do a create table that tries to cover all types and options
143
type_bool bool not null default 0,
144
type_tiny int not null auto_increment primary key,
146
type_mediumint mediumint,
148
type_decimal decimal(5,2),
149
type_numeric numeric(5,2),
152
type_varchar varchar(10),
153
type_timestamp timestamp not null,
154
type_date date not null default '0000-00-00',
155
type_time time not null default '00:00:00',
156
type_datetime datetime not null default '0000-00-00 00:00:00',
158
type_enum enum ('red', 'green', 'blue'),
159
type_set enum ('red', 'green', 'blue'),
160
type_tinyblob tinyblob,
162
type_medium_blob mediumblob,
163
type_long_blob longblob,
165
) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;
167
# Not tested above: RAID_# UNION INSERT_METHOD DATA DIRECTORY INDEX DIRECTORY
168
show create table t1;
169
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
176
create table t1 (a int not null);
177
create table t2 select max(a) from t1;
178
show columns from t2;
181
# Check auto conversions of types
183
create table t1 (c decimal, d double, f float, r real);
184
show columns from t1;
187
create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
188
show columns from t1;
192
# Test for Bug #2593 "SHOW CREATE TABLE doesn't properly double quotes"
195
CREATE TABLE ```ab``cd``` (i INT);
196
SHOW CREATE TABLE ```ab``cd```;
197
DROP TABLE ```ab``cd```;
199
CREATE TABLE ```ab````cd``` (i INT);
200
SHOW CREATE TABLE ```ab````cd```;
201
DROP TABLE ```ab````cd```;
203
CREATE TABLE ```a` (i INT);
204
SHOW CREATE TABLE ```a`;
207
CREATE TABLE `a.1` (i INT);
208
SHOW CREATE TABLE `a.1`;
213
# Test for bug #2719 "Heap tables status shows wrong or missing data."
216
select @@max_heap_table_size;
219
a int(11) default NULL,
220
KEY a USING BTREE (a)
224
b int(11) default NULL,
229
a int(11) default NULL,
230
b int(11) default NULL,
231
KEY a USING BTREE (a),
235
insert into t1 values (1),(2);
236
insert into t2 values (1),(2);
237
insert into t3 values (1,1),(2,2);
238
--replace_column 6 # 7 # 8 # 9 #
240
insert into t1 values (3),(4);
241
insert into t2 values (3),(4);
242
insert into t3 values (3,3),(4,4);
243
--replace_column 6 # 7 # 8 # 9 #
245
insert into t1 values (5);
246
insert into t2 values (5);
247
insert into t3 values (5,5);
248
--replace_column 6 # 7 # 8 # 9 #
250
delete from t1 where a=3;
251
delete from t2 where b=3;
252
delete from t3 where a=3;
253
--replace_column 6 # 7 # 8 # 9 # 10 #
258
--replace_column 6 # 7 # 8 # 9 #
260
insert into t1 values (5);
261
insert into t2 values (5);
262
insert into t3 values (5,5);
263
--replace_column 6 # 7 # 8 # 9 #
265
delete from t1 where a=5;
266
delete from t2 where b=5;
267
delete from t3 where a=5;
268
--replace_column 6 # 7 # 8 # 9 # 10 #
271
drop table t1, t2, t3;
273
# Test that USING <keytype> is always shown in SHOW CREATE TABLE when it was
274
# specified during table creation, but not otherwise. (Bug #7235)
275
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;
276
SHOW CREATE TABLE t1;
278
CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;
279
SHOW CREATE TABLE t1;
281
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;
282
SHOW CREATE TABLE t1;
284
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
285
SHOW CREATE TABLE t1;
287
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
288
SHOW CREATE TABLE t1;
290
# Test that when an index is created with the default key algorithm and
291
# altered to another storage engine, it gets the default key algorithm
292
# for that storage engine, but when it is specified, the specified type is
294
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
295
SHOW CREATE TABLE t1;
296
ALTER TABLE t1 ENGINE=MEMORY;
297
SHOW CREATE TABLE t1;
299
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
300
SHOW CREATE TABLE t1;
301
ALTER TABLE t1 ENGINE=MEMORY;
302
SHOW CREATE TABLE t1;
305
# Test for BUG#9439 "Reporting wrong datatype for sub_part on show index"
307
field1 text NOT NULL,
308
PRIMARY KEY(field1(1000))
315
# Test for BUG#11635: mysqldump exports TYPE instead of USING for HASH
319
PRIMARY KEY USING HASH (c1),
320
INDEX USING BTREE(c2)
322
SHOW CREATE TABLE t1;
325
# Test for BUG#93: 4.1 protocl crash on corupted frm and SHOW TABLE STATUS
329
# Create a junk frm file on disk
330
system echo "this is a junk file for test" >> $MYSQLTEST_VARDIR/master-data/test/t1.frm ;
331
--replace_column 6 # 7 # 8 # 9 #
332
SHOW TABLE STATUS like 't1';
334
show create table t1;
335
drop table if exists t1;
337
--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm
340
# BUG 12183 - SHOW OPEN TABLES behavior doesn't match grammar
341
# First we close all open tables with FLUSH tables and then we open some.
345
--echo # Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar.
348
# NOTE: SHOW OPEN TABLES does not sort result list by database or table names.
349
# Tables are listed in the order they were opened. We can not use the system
350
# database (mysql) for the test here, because we have no control over the order
351
# of opening tables in it. Consequently, we can not use 'SHOW OPEN TABLES'.
354
DROP DATABASE IF EXISTS mysqltest1;
357
CREATE DATABASE mysqltest1;
362
CREATE TABLE t1(a INT);
363
CREATE TABLE t2(a INT);
367
--disable_ps_protocol
377
SHOW OPEN TABLES FROM mysqltest1;
381
SHOW OPEN TABLES FROM mysqltest1 LIKE 'z%';
385
SHOW OPEN TABLES FROM mysqltest1 LIKE 't1%';
389
SHOW OPEN TABLES FROM mysqltest1 LIKE '%1%';
398
DROP DATABASE mysqltest1;
404
# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message)
407
SHOW TABLES FROM non_existing_database;
409
--echo End of 4.1 tests
412
# Check that SHOW TABLES and SHOW COLUMNS give a error if there is no
413
# referenced database and table respectively.
415
--error ER_BAD_DB_ERROR
416
SHOW TABLES FROM no_such_database;
417
--error ER_NO_SUCH_TABLE
418
SHOW COLUMNS FROM no_such_table;
422
# Bug #28808: log_queries_not_using_indexes variable dynamic change is ignored
425
show variables like "log_queries_not_using_indexes";
426
select 1 from information_schema.tables limit 1;
427
show status like 'slow_queries';
428
set global log_queries_not_using_indexes=OFF;
429
show variables like "log_queries_not_using_indexes";
430
select 1 from information_schema.tables limit 1;
431
show status like 'slow_queries';
432
set global log_queries_not_using_indexes=ON;
433
show variables like "log_queries_not_using_indexes";
434
select 1 from information_schema.tables limit 1;
435
show status like 'slow_queries';
438
# Bug #30088: Can't disable myisam-recover by a value of ""
440
show variables like 'myisam_recover_options';
442
--echo End of 5.0 tests
450
# Ensure that show plugin code is tested
458
# Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing
461
create database `mysqlttest\1`;
462
create table `mysqlttest\1`.`a\b` (a int);
463
show tables from `mysqlttest\1`;
464
show fields from `mysqlttest\1`.`a\b`;
465
show columns from `a\b` from `mysqlttest\1`;
466
show keys from `mysqlttest\1`.`a\b`;
467
drop table `mysqlttest\1`.`a\b`;
468
drop database `mysqlttest\1`;
471
# Bug#24392: SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS
474
--error ER_UNKNOWN_STORAGE_ENGINE
475
show engine foobar status;
476
--error ER_UNKNOWN_STORAGE_ENGINE
477
show engine foobar logs;
478
--error ER_UNKNOWN_STORAGE_ENGINE
479
show engine foobar mutex;
481
--error ER_UNKNOWN_STORAGE_ENGINE
482
show engine mutex status;
484
show engine csv status;
485
show engine csv logs;
486
show engine csv mutex;
488
# Bug#25081 SHOW FULL TABLES on table with latin chars in name fails
492
drop table if exists `été`;
494
create table `été` (field1 int);
500
# Bug#26402 Server crashes with old-style named table
502
--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND
503
show columns from `#mysql50#????????`;
505
--echo End of 5.1 tests
added
removed
tests/t/show_check.test
