# Uses GRANT commands that usually disabled in embedded server -- 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; drop database if exists mysqltest1; 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); --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata 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; --disable_metadata --error 1062 insert into t1 values (5,5,5); --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata optimize table t1; --disable_metadata optimize table t1; drop table t1; #show variables; --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata 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%"; --disable_metadata # # 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); --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata analyze table t1; --disable_metadata show index from t1; --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata repair table t1; --disable_metadata 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; # Test for BUG#93: 4.1 protocl crash on corupted frm and SHOW TABLE STATUS flush tables; # Create a junk frm file on disk system echo "this is a junk file for test" >> $MYSQLTEST_VARDIR/master-data/test/t1.frm ; --replace_column 6 # 7 # 8 # 9 # SHOW TABLE STATUS like 't1'; --error 1033 show create table t1; drop table t1; --echo End of 4.1 tests # # BUG 12183 - SHOW OPEN TABLES behavior doesn't match grammar # First we close all open tables with FLUSH tables and then we open some. CREATE TABLE txt1(a int); CREATE TABLE tyt2(a int); CREATE TABLE urkunde(a int); FLUSH TABLES; SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0; SHOW OPEN TABLES; SHOW OPEN TABLES FROM mysql; SHOW OPEN TABLES FROM mysql LIKE 'u%'; SHOW OPEN TABLES LIKE 't%'; SHOW OPEN TABLES LIKE '%o%'; FLUSH TABLES; SHOW OPEN TABLES; DROP TABLE txt1; DROP TABLE tyt2; DROP TABLE urkunde; # # BUG #12591 (SHOW TABLES FROM dbname produces wrong error message) # --error 1049 SHOW TABLES FROM non_existing_database; # # Bug#17203: "sql_no_cache sql_cache" in views created from prepared # statement # # The problem was that initial user setting was forgotten, and current # runtime-determined values of the flags were shown instead. # --disable_warnings DROP VIEW IF EXISTS v1; DROP PROCEDURE IF EXISTS p1; --enable_warnings # Check that SHOW CREATE VIEW shows SQL_CACHE flag exaclty as # specified by the user. CREATE VIEW v1 AS SELECT 1; SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE 1; SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE 1; SHOW CREATE VIEW v1; DROP VIEW v1; # Usage of NOW() disables caching, but we still have show what the # user have specified. CREATE VIEW v1 AS SELECT NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; # Check that SQL_NO_CACHE always wins. CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW(); SHOW CREATE VIEW v1; DROP VIEW v1; # Check CREATE VIEW in a prepared statement in a procedure. delimiter |; CREATE PROCEDURE p1() BEGIN SET @s= 'CREATE VIEW v1 AS SELECT SQL_CACHE 1'; PREPARE stmt FROM @s; EXECUTE stmt; DROP PREPARE stmt; END | delimiter ;| CALL p1(); SHOW CREATE VIEW v1; DROP PROCEDURE p1; DROP VIEW v1; # # Check that SHOW TABLES and SHOW COLUMNS give a error if there is no # referenced database and table respectively. # --error ER_BAD_DB_ERROR SHOW TABLES FROM no_such_database; --error ER_NO_SUCH_TABLE SHOW COLUMNS FROM no_such_table; # # Bug #19764: SHOW commands end up in the slow log as table scans # flush status; show status like 'slow_queries'; show tables; show status like 'slow_queries'; # Table scan query, to ensure that slow_queries does still get incremented # (mysqld is started with --log-queries-not-using-indexes) select 1 from information_schema.tables limit 1; show status like 'slow_queries'; # # BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT # FROM I_S. # # Ensure that all needed objects are dropped. --disable_warnings DROP DATABASE IF EXISTS mysqltest1; DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS v1; DROP PROCEDURE IF EXISTS p1; DROP FUNCTION IF EXISTS f1; --enable_warnings # Create objects. CREATE DATABASE mysqltest1; CREATE TABLE t1(c INT NOT NULL PRIMARY KEY); CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; CREATE VIEW v1 AS SELECT 1; CREATE PROCEDURE p1() SELECT 1; CREATE FUNCTION f1() RETURNS INT RETURN 1; # Test. set names utf8; --echo -- Here we enable metadata just to check that the collation of the --echo -- resultset is non-binary for string type. This should be changed --echo -- after Bug#29394 is implemented. --enable_metadata --echo ---------------------------------------------------------------- SHOW CHARACTER SET LIKE 'utf8'; --echo ---------------------------------------------------------------- SHOW COLLATION LIKE 'latin1_bin'; --echo ---------------------------------------------------------------- SHOW CREATE DATABASE mysqltest1; --echo ---------------------------------------------------------------- SHOW DATABASES LIKE 'mysqltest1'; --echo ---------------------------------------------------------------- SHOW CREATE TABLE t1; --echo ---------------------------------------------------------------- SHOW INDEX FROM t1; --echo ---------------------------------------------------------------- SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT, TABLE_COLLATION, CREATE_OPTIONS, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1'; --echo ---------------------------------------------------------------- SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA, PRIVILEGES, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 't1'; --echo ---------------------------------------------------------------- SHOW TABLES LIKE 't1'; --echo ---------------------------------------------------------------- SHOW COLUMNS FROM t1; --echo ---------------------------------------------------------------- SHOW TRIGGERS LIKE 't1'; --echo ---------------------------------------------------------------- SELECT TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, ACTION_CONDITION, ACTION_STATEMENT, ACTION_ORIENTATION, ACTION_TIMING, ACTION_REFERENCE_OLD_TABLE, ACTION_REFERENCE_NEW_TABLE, ACTION_REFERENCE_OLD_ROW, ACTION_REFERENCE_NEW_ROW, SQL_MODE, DEFINER FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 't1_bi'; --echo ---------------------------------------------------------------- SHOW CREATE VIEW v1; --echo ---------------------------------------------------------------- SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'; --echo ---------------------------------------------------------------- SHOW CREATE PROCEDURE p1; --echo ---------------------------------------------------------------- SELECT SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, ROUTINE_BODY, ROUTINE_DEFINITION, EXTERNAL_NAME, EXTERNAL_LANGUAGE, PARAMETER_STYLE, IS_DETERMINISTIC, SQL_DATA_ACCESS, SQL_PATH, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT, DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p1'; --echo ---------------------------------------------------------------- SHOW CREATE FUNCTION f1; --echo ---------------------------------------------------------------- SELECT SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, ROUTINE_BODY, ROUTINE_DEFINITION, EXTERNAL_NAME, EXTERNAL_LANGUAGE, PARAMETER_STYLE, IS_DETERMINISTIC, SQL_DATA_ACCESS, SQL_PATH, SECURITY_TYPE, SQL_MODE, ROUTINE_COMMENT, DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'f1'; --echo ---------------------------------------------------------------- --disable_metadata # Cleanup. DROP DATABASE mysqltest1; DROP TABLE t1; DROP VIEW v1; DROP PROCEDURE p1; DROP FUNCTION f1; --echo End of 5.0 tests