~drizzle-trunk/drizzle/development

# check that CSV engine was compiled in, as the result of the test

# depends on the presence of the log tables (which are CSV-based).

--source include/have_csv.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;

--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 ER_DUP_ENTRY

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"

#

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`;

CREATE TABLE `a.1` (i INT);

SHOW CREATE TABLE `a.1`;

DROP TABLE `a.1`;

#

# 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;

truncate table t1;

truncate table t2;

truncate table 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 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 if exists t1;

--error 1,0

--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm

#

# BUG 12183 -  SHOW OPEN TABLES behavior doesn't match grammar

# First we close all open tables with FLUSH tables and then we open some.

#

--echo

--echo # Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar.

--echo

# NOTE: SHOW OPEN TABLES does not sort result list by database or table names.

# Tables are listed in the order they were opened. We can not use the system

# database (mysql) for the test here, because we have no control over the order

# of opening tables in it. Consequently, we can not use 'SHOW OPEN TABLES'.

--disable_warnings

DROP DATABASE IF EXISTS mysqltest1;

--enable_warnings

CREATE DATABASE mysqltest1;

use mysqltest1;

--echo

CREATE TABLE t1(a INT);

CREATE TABLE t2(a INT);

--echo

--disable_ps_protocol

FLUSH TABLES;

--echo

SELECT 1 FROM t1;

SELECT 1 FROM t2;

--echo

SHOW OPEN TABLES FROM mysqltest1;

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE 'z%';

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE 't1%';

--echo

SHOW OPEN TABLES FROM mysqltest1 LIKE '%1%';

--echo

FLUSH TABLES;

--enable_ps_protocol

--echo

DROP DATABASE mysqltest1;

use test;

--echo

#

# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message)

#

--error 1049

SHOW TABLES FROM non_existing_database;

--echo End of 4.1 tests

#

# 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 #28808: log_queries_not_using_indexes variable dynamic change is ignored

#

flush status;

show variables like "log_queries_not_using_indexes";

select 1 from information_schema.tables limit 1;

show status like 'slow_queries';

set global log_queries_not_using_indexes=OFF;

show variables like "log_queries_not_using_indexes";

select 1 from information_schema.tables limit 1;

show status like 'slow_queries';

set global log_queries_not_using_indexes=ON;

show variables like "log_queries_not_using_indexes";

select 1 from information_schema.tables limit 1;

show status like 'slow_queries';

#

# Bug #30088: Can't disable myisam-recover by a value of ""

#

show variables like 'myisam_recover_options';

--echo End of 5.0 tests

--disable_result_log

SHOW AUTHORS;

--enable_result_log

#

# Ensure that show plugin code is tested

#

--disable_result_log

show plugins;

--enable_result_log

#

# Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing

# \ incorrectly

#

create database `mysqlttest\1`;

create table `mysqlttest\1`.`a\b` (a int);

show tables from `mysqlttest\1`;

show fields from `mysqlttest\1`.`a\b`;

show columns from `a\b` from `mysqlttest\1`;

show keys from `mysqlttest\1`.`a\b`;

drop table `mysqlttest\1`.`a\b`;

drop database `mysqlttest\1`;

#

# Bug#24392: SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS

#

--error ER_UNKNOWN_STORAGE_ENGINE

show engine foobar status;

--error ER_UNKNOWN_STORAGE_ENGINE

show engine foobar logs;

--error ER_UNKNOWN_STORAGE_ENGINE

show engine foobar mutex;

--error ER_UNKNOWN_STORAGE_ENGINE

show engine mutex status;

show engine csv status;

show engine csv logs;

show engine csv mutex;

#

# Bug#25081 SHOW FULL TABLES on table with latin chars in name fails

#

set names utf8;

--disable_warnings

drop table if exists `été`;

--enable_warnings

create table `été` (field1 int);

show full tables;

drop table `été`;

set names latin1;

#

# Bug#26402 Server crashes with old-style named table

#

--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND

show columns from `#mysql50#????????`;

--echo End of 5.1 tests