summaryrefslogtreecommitdiff
path: root/mysql-test/main/drop.test
blob: fe8d39c0d7e1caf3dc6be2c09e8617e027dfa9d9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
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_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;

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

###########################################################################

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