diff options
Diffstat (limited to 'mysql-test/main/drop.test')
-rw-r--r-- | mysql-test/main/drop.test | 363 |
1 files changed, 363 insertions, 0 deletions
diff --git a/mysql-test/main/drop.test b/mysql-test/main/drop.test new file mode 100644 index 00000000..5185ce30 --- /dev/null +++ b/mysql-test/main/drop.test @@ -0,0 +1,363 @@ +--source include/have_innodb.inc + +call mtr.add_suppression("table or database name 'abc`def'"); + +# Initialise +--disable_warnings +drop table if exists t1; +drop database if exists mysqltest; +# If earlier test failed +drop database if exists client_test_db; +--enable_warnings + +--error 1051 +drop table t1; +create table t1(n int); +insert into t1 values(1); +create temporary table t1( n int); +insert into t1 values(2); +--error 1050 +create table t1(n int); +drop table t1; +select * from t1; + +# now test for a bug in drop database - it is important that the name +# of the table is the same as the name of the database - in the original +# code this triggered a bug +create database mysqltest; +drop database if exists mysqltest; +create database mysqltest; +create table mysqltest.mysqltest (n int); +insert into mysqltest.mysqltest values (4); +select * from mysqltest.mysqltest; +--enable_info +drop database if exists mysqltest; +--disable_info +create database mysqltest; + +# +# drop many tables - bug#3891 +# we'll do it in mysqltest db, to be able to use longer table names +# (tableN instead on tN) +# +use mysqltest; +--error 1051 +drop table table1, table2, table3, table4, table5, table6, +table7, table8, table9, table10, table11, table12, table13, +table14, table15, table16, table17, table18, table19, table20, +table21, table22, table23, table24, table25, table26, table27, +table28; + +--error 1051 +drop table table1, table2, table3, table4, table5, table6, +table7, table8, table9, table10, table11, table12, table13, +table14, table15, table16, table17, table18, table19, table20, +table21, table22, table23, table24, table25, table26, table27, +table28, table29, table30; + +use test; +drop database mysqltest; + +# test drop/create database and FLUSH TABLES WITH READ LOCK +flush tables with read lock; +--error 1209,1223 +create database mysqltest; +unlock tables; +create database mysqltest; +show databases; +flush tables with read lock; +--error 1208,1223 +drop database mysqltest; +unlock tables; +drop database mysqltest; +show databases; +--error 1008 +drop database mysqltest; + +# test create table and FLUSH TABLES WITH READ LOCK +drop table t1; +flush tables with read lock; +--error 1223 +create table t1(n int); +unlock tables; +create table t1(n int); +show tables; +drop table t1; + +# End of 4.1 tests + + +# +# Test for bug#21216 "Simultaneous DROP TABLE and SHOW OPEN TABLES causes +# server to crash". Crash (caused by failed assertion in 5.0 or by null +# pointer dereference in 5.1) happened when one ran SHOW OPEN TABLES +# while concurrently doing DROP TABLE (or RENAME TABLE, CREATE TABLE LIKE +# or any other command that takes name-lock) in other connection. +# +# Also includes test for similar bug#12212 "Crash that happens during +# removing of database name from cache" reappeared in 5.1 as bug#19403 +# In its case crash happened when one concurrently executed DROP DATABASE +# and one of name-locking command. +# +--disable_service_connection +--disable_warnings +drop database if exists mysqltest; +drop table if exists t1; +--enable_warnings +create table t1 (i int); +create database mysqltest; +lock tables t1 read; +connect (addconroot1, localhost, root,,); +--send drop table t1 +connect (addconroot2, localhost, root,,); +# Server should not crash in any of the following statements +--disable_result_log +show open tables; +--enable_result_log +--send drop database mysqltest +connection default; +select 1; +unlock tables; +connection addconroot1; +--reap +connection addconroot2; +--reap +disconnect addconroot2; +--source include/wait_until_disconnected.inc +connection addconroot1; +disconnect addconroot1; +--source include/wait_until_disconnected.inc +connection default; +--enable_service_connection + +# +# Bug#25858 Some DROP TABLE under LOCK TABLES can cause deadlocks +# + +--disable_warnings +drop table if exists t1,t2; +--enable_warnings +create table t1 (a int); +create table t2 (a int); +lock table t1 read; +--error ER_TABLE_NOT_LOCKED +drop table t2; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t1; +unlock tables; +drop table t1,t2; +connect (addconroot, localhost, root,,); +connection default; +create table t1 (i int); +create table t2 (i int); +lock tables t1 read; +connection addconroot; +lock tables t2 read; +--error ER_TABLE_NOT_LOCKED +drop table t1; +connection default; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +drop table t1,t2; +disconnect addconroot; +connection default; +unlock tables; +drop table t1,t2; + +--echo End of 5.0 tests + +# +# Bug#30152 MySQLD crash duing alter table causes DROP DATABASE to FAIL due to temp file +# +create database mysql_test; +create table mysql_test.t1(f1 int); +create table mysql_test.`#sql-347f_7` (f1 int); +create table mysql_test.`#sql-347f_8` (f1 int); +drop table mysql_test.`#sql-347f_8`; +let $MYSQLD_DATADIR= `select @@datadir`; +copy_file $MYSQLD_DATADIR/mysql_test/t1.frm $MYSQLD_DATADIR/mysql_test/#sql-347f_6.frm; +drop database mysql_test; + +# +# Bug#26704: Failing DROP DATABASE brings mysql-client out of sync. +# + +create database mysql_test; +use mysql_test; +create table t1(c int); + +write_file $MYSQLD_DATADIR/mysql_test/do_not_delete; +do_not_delete +EOF + +replace_result $MYSQLD_DATADIR ./ \\ / 66 39 93 39 17 39 247 39 41 39 "File exists" "Directory not empty"; +error ER_DB_DROP_RMDIR; +drop database mysql_test; +select database(); +remove_file $MYSQLD_DATADIR/mysql_test/do_not_delete; +drop database mysql_test; +select database(); +use test; + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#29958: Weird message on DROP DATABASE if mysql.proc does not +--echo # -- exist. +--echo # -- + +--disable_warnings +DROP DATABASE IF EXISTS mysql_test; +--enable_warnings + +CREATE DATABASE mysql_test; + +let $MYSQLD_DATADIR= `select @@datadir`; + +--let $proc_frm = $MYSQLD_DATADIR/mysql/proc.frm +--let $proc_MAD = $MYSQLD_DATADIR/mysql/proc.MAD +--let $proc_MAI = $MYSQLD_DATADIR/mysql/proc.MAI + +--let $copy_of_proc_frm = $MYSQLTEST_VARDIR/tmp/bug29958.copy.frm +--let $copy_of_proc_MAD = $MYSQLTEST_VARDIR/tmp/bug29958.copy.MAD +--let $copy_of_proc_MAI = $MYSQLTEST_VARDIR/tmp/bug29958.copy.MAI + +--copy_file $proc_frm $copy_of_proc_frm +--copy_file $proc_MAD $copy_of_proc_MAD +--copy_file $proc_MAI $copy_of_proc_MAI + +DROP TABLE mysql.proc; + +DROP DATABASE mysql_test; + +--copy_file $copy_of_proc_frm $proc_frm +--copy_file $copy_of_proc_MAD $proc_MAD +--copy_file $copy_of_proc_MAI $proc_MAI + +--remove_file $copy_of_proc_frm +--remove_file $copy_of_proc_MAD +--remove_file $copy_of_proc_MAI + +--echo +--echo # -- +--echo # -- End of Bug#29958. +--echo # -- + +########################################################################### + +--echo +# +# Bug#26703: DROP DATABASE fails if database contains a #mysql50# table with backticks +# +create database mysqltestbug26703; +use mysqltestbug26703; +create table `#mysql50#abc``def` ( id int ); +--error ER_WRONG_TABLE_NAME +create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (a int); +create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (a int); +create table `#mysql50#aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1234` (a int); +--error ER_WRONG_TABLE_NAME +create table `#mysql50#aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12345` (a int); +show tables; +use test; +drop database mysqltestbug26703; + +--echo End of 5.1 tests + +########################################################################### + +--echo +--echo # -- +--echo # -- Bug#37431 (DROP TABLE does not report errors correctly). +--echo # -- + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +--error ER_BAD_TABLE_ERROR +DROP TABLE t1; + +SHOW WARNINGS; + +--echo +--echo # -- +--echo # -- End of Bug#37431. +--echo # -- + + +--echo # +--echo # Bug#54282 Crash in MDL_context::upgrade_shared_lock_to_exclusive +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); +LOCK TABLE t1 WRITE; +--error ER_NONUNIQ_TABLE +DROP TABLE t1, t1; + +UNLOCK TABLES; +DROP TABLE t1; + +--echo # +--echo # BUG#34750: Print database name in Unknown Table error message +--echo # + +--echo +--echo # Test error message when droping table/view + +--error ER_BAD_TABLE_ERROR +DROP TABLE table1; +--error ER_BAD_TABLE_ERROR +DROP TABLE table1,table2; +--error ER_UNKNOWN_VIEW +DROP VIEW view1,view2,view3,view4; +--echo +DROP TABLE IF EXISTS table1; +DROP TABLE IF EXISTS table1,table2; +DROP VIEW IF EXISTS view1,view2,view3,view4; + +--echo +--echo # Test error message when trigger does not find table + +CREATE TABLE table1(a int); +CREATE TABLE table2(b int); + +# Database name is only available (for printing) if specified in +# the trigger definition +CREATE TRIGGER trg1 AFTER INSERT ON table1 +FOR EACH ROW + INSERT INTO table2 SELECT t.notable.*; + +--error ER_BAD_TABLE_ERROR +INSERT INTO table1 VALUES (1); + +DROP TABLE table1,table2; + +--echo # End BUG#34750 + +--echo # +--echo # MDEV-11105 Table named 'db' has weird side effect. +--echo # + +CREATE DATABASE mysqltest; +CREATE TABLE mysqltest.db(id INT); +DROP DATABASE mysqltest; + +--echo # +--echo # Verify sql_if_exists +--echo # + +--error ER_DB_DROP_EXISTS +DROP DATABASE mysqltest; +CREATE DATABASE mysqltest; +--error ER_BAD_TABLE_ERROR +drop table mysqltest.does_not_exists; +set @@session.sql_if_exists=1; +drop table mysqltest.does_not_exists; +drop database mysqltest; +drop database mysqltest; +set @@session.sql_if_exists=0; |