show_check.test 3.86 KB
Newer Older
1 2 3 4
#
# Test of some show commands
#

5
--disable_warnings
6
drop table if exists t1,t2;
7 8
--enable_warnings

9 10
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
unknown's avatar
unknown committed
11 12 13
check table t1 fast;
check table t1 fast;
check table t1 changed;
14
insert into t1 values (5,5,5);
unknown's avatar
unknown committed
15 16 17
check table t1 changed;
check table t1 medium;
check table t1 extended;
18
show index from t1;
19 20 21 22
!$1062 insert into t1 values (5,5,5);
optimize table t1;
optimize table t1;
drop table t1;
23 24 25

#show variables;
show variables like "wait_timeout%";
26
show variables like "WAIT_timeout%";
27 28 29 30
show variables like "this_doesn't_exists%";
show table status from test like "this_doesn't_exists%";
show databases;
show databases like "test%";
31 32 33 34 35 36 37 38 39 40 41

#
# Check of show index
#
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));
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);
analyze table t1;
show index from t1;
repair table t1;
show index from t1;
drop table t1;
42 43 44 45 46 47 48 49 50 51 52 53 54

#
# Test of SHOW CREATE
#

create temporary table t1 (a int not null);
show create table t1;
alter table t1 rename t2;
show create table t2;
drop table t2;

create table t1 (
  test_set set( 'val1', 'val2', 'val3' ) not null default '',
unknown's avatar
unknown committed
55 56
  name char(20) default 'O''Brien' comment 'O''Brien as default',
  c int not null comment 'int column'
57 58
  ) comment = 'it\'s a table' ;
show create table t1 ;
unknown's avatar
unknown committed
59
show full columns from t1;
60
drop table t1;
61 62 63 64 65 66 67

create table t1 (a int not null, unique aa (a));
show create table t1;
drop table t1;
create table t1 (a int not null, primary key (a));
show create table t1;
drop table t1;
unknown's avatar
unknown committed
68 69 70 71 72 73 74

flush tables;
show open tables;
create table t1(n int);
insert into t1 values (1);
show open tables;
drop table t1;
75 76 77 78 79 80 81 82

create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
show create table t1;
alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
show create table t1;
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;
show create table t1;
drop table t1;
unknown's avatar
unknown committed
83

84 85
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));
show columns from t1;
unknown's avatar
unknown committed
86
show full columns from t1;
87 88
drop table t1;

unknown's avatar
unknown committed
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
#
# Do a create table that tries to cover all types and options
#
create table t1 (
type_bool bool not null,
type_tiny tinyint not null auto_increment primary key,
type_short smallint(3),
type_mediumint mediumint,
type_bigint bigint,
type_decimal decimal(5,2),
type_numeric numeric(5,2),
empty_char char(0),
type_char char(2),
type_varchar varchar(10),
type_timestamp timestamp not null,
type_date date not null,
type_time time not null,
type_datetime datetime not null,
type_year year,
type_enum enum ('red', 'green', 'blue'),
type_set enum ('red', 'green', 'blue'),
type_tinyblob tinyblob,
type_blob blob,
type_medium_blob mediumblob,
type_long_blob longblob,
index(type_short)
) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;

# Not tested above: RAID_# UNION INSERT_METHOD DATA DIRECTORY INDEX DIRECTORY
show create table t1;
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
select * from t1;
drop table t1;

123 124 125 126 127 128 129
#
# Check metadata
#
create table t1 (a int not null);
create table t2 select max(a) from t1;
show columns from t2;
drop table t1,t2;
unknown's avatar
unknown committed
130

131 132 133 134 135 136 137 138 139
# Check auto conversions of types

create table t1 (c decimal, d double, f float, r real);
show columns from t1;
drop table t1;

create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
show columns from t1;
drop table t1;