diff options
Diffstat (limited to 'mysql-test/main/show_check.test')
| -rw-r--r-- | mysql-test/main/show_check.test | 1392 |
1 files changed, 1392 insertions, 0 deletions
diff --git a/mysql-test/main/show_check.test b/mysql-test/main/show_check.test new file mode 100644 index 00000000000..a24fa632ea5 --- /dev/null +++ b/mysql-test/main/show_check.test @@ -0,0 +1,1392 @@ +################################################################################ +# +# NOTICE: +# +# This file unfortunately contains characters in various different encodings. +# Be careful when editing this file to ensure that you (or your editor) do +# not change things (such as encodings) on lines that you did not mean to +# modify. +# +################################################################################ + + +# Uses GRANT commands that usually disabled in embedded server +-- source include/not_embedded.inc + +# 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; + +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 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; +--sorted_result +show open tables; +create table t1(n int); +insert into t1 values (1); +--sorted_result +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 default current_timestamp on update current_timestamp, +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: 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; + +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`; + +SET sql_mode= 'ANSI_QUOTES'; + +CREATE TABLE """a" (i INT); +SHOW CREATE TABLE """a"; +DROP TABLE """a"; + +# 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 # 12 # +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 # 12 # +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 # 12 # +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 # 12 # +show table status; +truncate table t1; +truncate table t2; +truncate table t3; +--replace_column 6 # 7 # 8 # 9 # 12 # +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 # 12 # +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 # 12 # +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); +create user mysqltest_1@localhost; +grant select on `mysqltest`.* to mysqltest_1@localhost; +create user mysqltest_2@localhost; +grant usage on `mysqltest`.* to mysqltest_2@localhost; +create user mysqltest_3@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 ER_TABLEACCESS_DENIED_ERROR +drop table t1; +--error ER_DBACCESS_DENIED_ERROR +drop database mysqltest; +disconnect con1; +--source include/wait_until_disconnected.inc + +connect (con2,localhost,mysqltest_2,,test); +connection con2; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest.t1; +--error ER_DBACCESS_DENIED_ERROR +show create database mysqltest; +--error ER_TABLEACCESS_DENIED_ERROR +drop table mysqltest.t1; +--error ER_DBACCESS_DENIED_ERROR +drop database mysqltest; +disconnect con2; +--source include/wait_until_disconnected.inc + +connect (con3,localhost,mysqltest_3,,test); +connection con3; +--error ER_TABLEACCESS_DENIED_ERROR +select * from mysqltest.t1; +show create database mysqltest; +drop table mysqltest.t1; +drop database mysqltest; +disconnect con3; +--source include/wait_until_disconnected.inc + +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; + +# 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 protocol crash on corrupted frm and SHOW TABLE STATUS + +flush tables; + +# Create a junk frm file on disk +let $MYSQLD_DATADIR= `select @@datadir`; +system echo "this is a junk file for test" >> $MYSQLD_DATADIR/test/t1.frm ; +--replace_column 6 # 7 # 8 # 9 # 12 # +SHOW TABLE STATUS like 't1'; +--error ER_NOT_FORM_FILE +show create table t1; +--disable_warnings +drop table if exists t1; +--enable_warnings +--error 0,1 +--remove_file $MYSQLD_DATADIR/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 ER_BAD_DB_ERROR +SHOW TABLES FROM non_existing_database; + +--echo End of 4.1 tests + +# +# 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; + +--error ER_WRONG_USAGE +CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE NOW(); + +--error ER_WRONG_USAGE +CREATE VIEW v1 AS SELECT SQL_NO_CACHE SQL_CACHE NOW(); + +--error ER_WRONG_USAGE +CREATE VIEW v1 AS SELECT SQL_CACHE SQL_NO_CACHE SQL_CACHE NOW(); + +# 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'; + +create table t1 (a int); +create trigger tr1 before insert on t1 for each row +begin +end; +create view v1 as select a from t1; +create procedure p1() +begin +end; +create function f1() +returns int +return 0; +create event e1 on schedule every 1 year starts now() + ends date_add(now(), interval 5 hour) do +begin +end; + +--disable_result_log +flush status; +show databases; +show tables; +show events; +show table status; +--sorted_result +show open tables; +show plugins; +show columns in t1; +show slave hosts; +show keys in t1; +show storage engines; +show authors; +show contributors; +show privileges; +show count(*) warnings; +show count(*) errors; +show warnings; +show status; +show processlist; +show variables; +show charset; +show collation; +show grants; +show create database test; +show create table t1; +show create view v1; +show master status; +show slave status; +show create procedure p1; +show create function f1; +show create trigger tr1; +show procedure status; +show create event e1; +--enable_result_log + +show status like 'slow_queries'; + +drop view v1; +drop table t1; +drop procedure p1; +drop function f1; +drop event e1; + +# +# Bug#10491 Server returns data as charset binary SHOW CREATE TABLE or SELECT +# FROM I_S. +# + +# +# Part 1: check that meta-data specifies not-binary character set. +# + +# 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 ---------------------------------------------------------------- + +--replace_column 6 # +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'; + +# Metadata is different for the field "CREATED" with and without --ps +# So test it separately. + +--disable_ps_protocol +--replace_column 1 # +SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name='t1_bi'; +--enable_ps_protocol + +--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; + +# +# Part 2: check that table with non-latin1 characters are dumped/restored +# correctly. +# +# See MDEV-14651 for details +# Ensure that all needed objects are dropped. + +#set names koi8r; + +#--disable_warnings +#DROP DATABASE IF EXISTS mysqltest1; +#--enable_warnings + +# Create objects. + +#CREATE DATABASE mysqltest1; + +#use mysqltest1; + +#CREATE TABLE t1(ËÏÌÏÎËÁ1 INT); + +# Check: +# - Dump mysqltest1; + +#--let $outfile1=$MYSQLTEST_VARDIR/tmp/show_check.mysqltest1.sql + +#--source include/count_sessions.inc +#--echo +#--echo ---> Dumping mysqltest1 to outfile1 +#--exec $MYSQL_DUMP --default-character-set=latin1 --character-sets-dir=$MYSQL_SHAREDIR/charsets --databases mysqltest1 > $outfile1 +# Take care that the additional session caused by MYSQL_DUMP has disappeared. +#--source include/wait_until_count_sessions.inc + +# - Clean mysqltest1; + +#--echo +#--echo + +#DROP DATABASE mysqltest1; + +# - Restore mysqltest1; + +#--echo +#--echo + +#--source include/count_sessions.inc +#--echo ---> Restoring mysqltest1... +#--exec $MYSQL test < $outfile1 +#--remove_file $outfile1 +# Take care that the additional session caused by MYSQL has disappeared. +#--source include/wait_until_count_sessions.inc + +# - Check definition of the table. + +#SHOW CREATE TABLE mysqltest1.t1; + +# Cleanup. + +#DROP DATABASE mysqltest1; +#use test; + +# +# 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 session 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 session 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'; + +# +# Bug#37301 Length and Max_length differ with no obvious reason +# +CREATE TABLE t1 ( + Codigo int(10) unsigned NOT NULL auto_increment, + Nombre varchar(255) default NULL, + Telefono varchar(255) default NULL, + Observaciones longtext, + Direccion varchar(255) default NULL, + Dni varchar(255) default NULL, + CP int(11) default NULL, + Provincia varchar(255) default NULL, + Poblacion varchar(255) default NULL, + PRIMARY KEY (Codigo) +) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; +--enable_metadata +show create table t1; +--disable_metadata +drop table t1; + +--echo End of 5.0 tests + +--disable_result_log +SHOW AUTHORS; +--enable_result_log + +# +# Bug#9785 SELECT privilege for the whole database is needed to do +# SHOW CREATE DATABASE +# +create database mysqltest; +show create database mysqltest; +create table mysqltest.t1(a int); +insert into mysqltest.t1 values(1); +create user mysqltest_4@localhost; +grant select on `mysqltest`.`t1` to mysqltest_4@localhost; +connect (con4,localhost,mysqltest_4,,mysqltest); +connection con4; +show create database mysqltest; +disconnect con4; +--source include/wait_until_disconnected.inc +connection default; +delete from mysql.user where user='mysqltest_4'; +delete from mysql.db where user='mysqltest_4'; +delete from mysql.tables_priv where user='mysqltest_4'; +flush privileges; +drop database mysqltest; + +# +# 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; + +--echo # +--echo # Bug#4374 SHOW TABLE STATUS FROM ignores collation_connection +--echo # Character set: Latin-1 (ISO-8859-1) +--echo # +SET NAMES latin1; +CREATE DATABASE `ä`; +CREATE TABLE `ä`.`ä` (a int) ENGINE=Memory; +--replace_column 6 # 7 # 8 # 9 # 12 # +SHOW TABLE STATUS FROM `ä` LIKE 'ä'; +DROP DATABASE `ä`; + +# +# Bug#26402 Server crashes with old-style named table +# +--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND +show columns from `#mysql50#????????`; +call mtr.add_suppression("Can.t find file: '.\\\\test\\\\\\?{8}.frm'"); + +# +# SHOW CREATE TRIGGER test. +# + +# Prepare. + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE TABLE t1(c1 INT); + +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1; + +# Test. + +--replace_column 7 # +SHOW CREATE TRIGGER t1_bi; + +CREATE PROCEDURE p1() SHOW CREATE TRIGGER t1_bi; + +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); +--replace_column 7 # +CALL p1(); + +PREPARE stmt1 FROM 'SHOW CREATE TRIGGER t1_bi'; + +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; +--replace_column 7 # +EXECUTE stmt1; + +# Cleanup. + +DROP TABLE t1; +DROP PROCEDURE p1; +DEALLOCATE PREPARE stmt1; + +# +# Bug#10491 Server returns data as charset binary SHOW CREATE TABLE or SELECT +# FROM INFORMATION_SCHEMA. +# +# Before the change performed to fix the bug, the metadata of the output of +# SHOW CREATE statements would always describe the result as 'binary'. That +# would ensure that the result is never converted to character_set_client +# (which was essential to mysqldump). Now we return to the client the actual +# character set of the object -- which is character_set_client of the +# connection that issues the CREATE statement, and this triggers an automatic +# conversion to character_set_results of the connection that issues SHOW CREATE +# statement. +# +# This test demonstrates that this conversion indeed is taking place. +# + +# Prepare: create objects in a one character set. + +set names koi8r; + +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +DROP EVENT IF EXISTS ev1; +--enable_warnings + +CREATE VIEW v1 AS SELECT 'ÔÅÓÔ' AS test; + +CREATE PROCEDURE p1() SELECT 'ÔÅÓÔ' AS test; + +CREATE FUNCTION f1() RETURNS CHAR(10) RETURN 'ÔÅÓÔ'; + +CREATE TABLE t1(c1 CHAR(10)); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 + FOR EACH ROW + SET NEW.c1 = 'ÔÅÓÔ'; + +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT 'ÔÅÓÔ' AS test; + +# Test: switch the character set and show that SHOW CREATE output is +# automatically converted to the new character_set_client. + +set names utf8; + +SHOW CREATE VIEW v1; + +SHOW CREATE PROCEDURE p1; + +SHOW CREATE FUNCTION f1; + +--replace_column 7 # +SHOW CREATE TRIGGER t1_bi; + +SHOW CREATE EVENT ev1; + +# Cleanup. + +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t1; +DROP EVENT ev1; + +# +# Bug#30036 SHOW TABLE TYPES causes the debug client to crash +# +--disable_result_log +SHOW STORAGE ENGINES; +--enable_result_log + + +# +# Bug#32710 SHOW INNODB STATUS requires SUPER +# + +CREATE USER test_u@localhost; +GRANT PROCESS ON *.* TO test_u@localhost; + +connect (conn1, localhost, test_u,,); + +--disable_result_log +SHOW ENGINE MYISAM MUTEX; +SHOW ENGINE MYISAM STATUS; +--enable_result_log + +disconnect conn1; +--source include/wait_until_disconnected.inc +connection default; +DROP USER test_u@localhost; + +--echo End of 5.1 tests + +--echo # +--echo # Bug#52593 SHOW CREATE TABLE is blocked if table is locked +--echo # for write by another connection + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +connect(con1, localhost,root); +connection default; + +CREATE TABLE t1 (i INT PRIMARY KEY); +LOCK TABLE t1 WRITE; +connection con1; +--echo # This statement used to be blocked. +SHOW CREATE TABLE t1; + +disconnect con1; +--source include/wait_until_disconnected.inc + +connection default; +UNLOCK TABLES; +DROP TABLE t1; + + +--echo # +--echo # Bug#54905 Connection with WRITE lock cannot ALTER table due to +--echo # concurrent SHOW CREATE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT); + +connect (con1,localhost,root); +LOCK TABLE t1 WRITE; + +connection default; +START TRANSACTION; +SHOW CREATE TABLE t1; + +connection con1; +# Used to block +ALTER TABLE t1 CHARACTER SET = utf8; +UNLOCK TABLES; + +connection default; +COMMIT; +disconnect con1; +DROP TABLE t1; + + +--echo # +--echo # Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; + +--echo # Test 1: SHOW CREATE TRIGGER with WRITE locked table. + +connect (con1, localhost, root); +LOCK TABLE t1 WRITE; + +connection default; +# Should not block. +--replace_column 7 # +SHOW CREATE TRIGGER t1_bi; + +connection con1; +UNLOCK TABLES; + +--echo # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction + +connection default; +START TRANSACTION; +--replace_column 7 # +SHOW CREATE TRIGGER t1_bi; + +connection con1; +# Should not block. +ALTER TABLE t1 CHARACTER SET = utf8; + +disconnect con1; +--source include/wait_until_disconnected.inc + +connection default; +COMMIT; +DROP TRIGGER t1_bi; +DROP TABLE t1; + + +--echo # +--echo # Bug#57306 SHOW PROCESSLIST does not display string literals well. +--echo # + +SET NAMES latin1; +SELECT GET_LOCK('t', 1000); +--connect (con1,localhost,root,,) +--connection con1 +SET NAMES latin1; +--send SELECT GET_LOCK('t',1000) AS 'óóóó'; +--connection default +# Make sure con1 has switched from "SET NAMES" to "SELECT GET_LOCK" +let $wait_timeout= 10; +let $wait_condition= SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO LIKE '%GET_LOCK%' AND ID != CONNECTION_ID(); +--source include/wait_condition.inc +--replace_column 1 ### 3 ### 6 ### 7 ### +SHOW PROCESSLIST; +SET NAMES utf8; +--replace_column 1 ### 3 ### 6 ### 7 ### +SHOW PROCESSLIST; +SELECT RELEASE_LOCK('t'); +--connection con1 +--reap +--disconnect con1 +--source include/wait_until_disconnected.inc +--connection default +SET NAMES latin1; + + +--echo # +--echo # Test SHOW AUTHORS and SHOW CONTRIBUTORS +--echo # + +--disable_result_log +SHOW AUTHORS; +--enable_result_log +SHOW WARNINGS; + +--disable_result_log +SHOW CONTRIBUTORS; +--enable_result_log +SHOW WARNINGS; |