# Requires use of multiple simultaneous connections, not supported with # embedded server testing -- source include/not_embedded.inc # # Test of some show commands # --disable_warnings drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; --enable_warnings 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); check table t1 fast; check table t1 fast; check table t1 changed; insert into t1 values (5,5,5); check table t1 changed; check table t1 medium; check table t1 extended; show index from t1; --error 1062 insert into t1 values (5,5,5); optimize table t1; optimize table t1; drop table t1; #show variables; show variables like "wait_timeout%"; show variables like "WAIT_timeout%"; show variables like "this_doesn't_exists%"; show table status from test like "this_doesn't_exists%"; show databases; show databases like "test%"; # # 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; # # 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 '', name char(20) default 'O''Brien' comment 'O''Brien as default', c int not null comment 'int column', `c-b` int comment 'name with a minus', `space 2` int comment 'name with a space' ) comment = 'it\'s a table' ; show create table t1; set sql_quote_show_create=0; show create table t1; set sql_quote_show_create=1; show full columns from t1; drop table t1; 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; flush tables; show open tables; create table t1(n int); insert into t1 values (1); show open tables; drop table t1; 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; 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; 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; show full columns from t1; drop table t1; # # Do a create table that tries to cover all types and options # create table t1 ( type_bool bool not null default 0, 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 default '0000-00-00', type_time time not null default '00:00:00', type_datetime datetime not null default '0000-00-00 00:00:00', 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" ENGINE=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; # # 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; # 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; # # Test for Bug #2593 "SHOW CREATE TABLE doesn't properly double quotes" # SET @old_sql_mode= @@sql_mode, sql_mode= ''; SET @old_sql_quote_show_create= @@sql_quote_show_create, sql_quote_show_create= OFF; ######### hook for WL#1324 # --error 1103 CREATE TABLE `a/b` (i INT); # the above test should WORK when WL#1324 is done, # it should be removed and # the following part should be uncommented then ######################################################### # begin of part that must be uncommented when WL#1324 is done ######################################################### #CREATE TABLE ```ab``cd``` (i INT); #SHOW CREATE TABLE ```ab``cd```; #DROP TABLE ```ab``cd```; #CREATE TABLE ```ab````cd``` (i INT); #SHOW CREATE TABLE ```ab````cd```; #DROP TABLE ```ab````cd```; # #CREATE TABLE ```a` (i INT); #SHOW CREATE TABLE ```a`; #DROP TABLE ```a`; # #SET sql_mode= 'ANSI_QUOTES'; # #CREATE TABLE """a" (i INT); #SHOW CREATE TABLE """a"; #DROP TABLE """a"; # #Bug #4374 SHOW TABLE STATUS FROM ignores collation_connection #set names latin1; #create database `�`; #create table `�`.`�` (a int) engine=heap; #--replace_column 7 # 8 # 9 # #show table status from `�` LIKE '�'; #drop database `�`; ######################################################### # end of part that must be uncommented when WL#1324 is done ######################################################### # to test quotes around keywords.. : SET sql_mode= ''; SET sql_quote_show_create= OFF; CREATE TABLE t1 (i INT); SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE `table` (i INT); SHOW CREATE TABLE `table`; DROP TABLE `table`; SET sql_quote_show_create= @old_sql_quote_show_create; SET sql_mode= @old_sql_mode; # # Test for bug #2719 "Heap tables status shows wrong or missing data." # select @@max_heap_table_size; CREATE TABLE t1 ( a int(11) default NULL, KEY a USING BTREE (a) ) ENGINE=HEAP; CREATE TABLE t2 ( b int(11) default NULL, index(b) ) ENGINE=HEAP; CREATE TABLE t3 ( a int(11) default NULL, b int(11) default NULL, KEY a USING BTREE (a), index(b) ) ENGINE=HEAP; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1,1),(2,2); --replace_column 6 # 7 # 8 # 9 # show table status; insert into t1 values (3),(4); insert into t2 values (3),(4); insert into t3 values (3,3),(4,4); --replace_column 6 # 7 # 8 # 9 # show table status; insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); --replace_column 6 # 7 # 8 # 9 # show table status; delete from t1 where a=3; delete from t2 where b=3; delete from t3 where a=3; --replace_column 6 # 7 # 8 # 9 # 10 # show table status; delete from t1; delete from t2; delete from t3; --replace_column 6 # 7 # 8 # 9 # show table status; insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); --replace_column 6 # 7 # 8 # 9 # show table status; delete from t1 where a=5; delete from t2 where b=5; delete from t3 where a=5; --replace_column 6 # 7 # 8 # 9 # 10 # show table status; drop table t1, t2, t3; # # Test for bug #3342 SHOW CREATE DATABASE seems to require DROP privilege # create database mysqltest; show create database mysqltest; create table mysqltest.t1(a int); insert into mysqltest.t1 values(1); grant select on `mysqltest`.* to mysqltest_1@localhost; grant usage on `mysqltest`.* to mysqltest_2@localhost; grant drop on `mysqltest`.* to mysqltest_3@localhost; connect (con1,localhost,mysqltest_1,,mysqltest); connection con1; select * from t1; show create database mysqltest; --error 1142 drop table t1; --error 1044 drop database mysqltest; connect (con2,localhost,mysqltest_2,,test); connection con2; --error 1142 select * from mysqltest.t1; --error 1044 show create database mysqltest; --error 1142 drop table mysqltest.t1; --error 1044 drop database mysqltest; connect (con3,localhost,mysqltest_3,,test); connection con3; --error 1142 select * from mysqltest.t1; --error 1044 show create database mysqltest; drop table mysqltest.t1; drop database mysqltest; connection default; set names binary; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; # This test fails on MAC OSX, so it is temporary disabled. # This needs WL#1324 to be done. #set names latin1; #create database `�`; #create table `�`.`�` (a int) engine=heap; #--replace_column 7 # 8 # 9 # #show table status from `�` LIKE '�'; #drop database `�`; # Test that USING <keytype> is always shown in SHOW CREATE TABLE when it was # specified during table creation, but not otherwise. (Bug #7235) CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; DROP TABLE t1; # Test that when an index is created with the default key algorithm and # altered to another storage engine, it gets the default key algorithm # for that storage engine, but when it is specified, the specified type is # preserved. CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; ALTER TABLE t1 ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; ALTER TABLE t1 ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; # Test for BUG#9439 "Reporting wrong datatype for sub_part on show index" CREATE TABLE t1( field1 text NOT NULL, PRIMARY KEY(field1(1000)) ); --enable_metadata show index from t1; --disable_metadata drop table t1; # Test for BUG#11635: mysqldump exports TYPE instead of USING for HASH create table t1 ( c1 int NOT NULL, c2 int NOT NULL, PRIMARY KEY USING HASH (c1), INDEX USING BTREE(c2) ); SHOW CREATE TABLE t1; DROP TABLE t1;