summaryrefslogtreecommitdiff
path: root/mysql-test/t/schema.test
blob: 8f9047e2e3f705138e31eb23e3d0be3f7b6e5c14 (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
#
# Just a couple of tests to make sure that schema works.
#
# Drop mysqltest1 database, as it can left from the previous tests.
#

# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

--disable_warnings
drop database if exists mysqltest1;
--enable_warnings

create schema foo;
show create schema foo;
show schemas;
drop schema foo;


--echo #
--echo # Bug #48940 MDL deadlocks against mysql_rm_db
--echo #

--disable_warnings
DROP SCHEMA IF EXISTS schema1;
--enable_warnings

connect(con2, localhost, root);

connection default;

CREATE SCHEMA schema1;
CREATE TABLE schema1.t1 (a INT);

SET autocommit= FALSE;
INSERT INTO schema1.t1 VALUES (1);

connection con2;
--send DROP SCHEMA schema1

connection default;
let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
                     WHERE state= 'Waiting for table metadata lock' 
                     AND info='DROP SCHEMA schema1';
--source include/wait_condition.inc
# Error 1 is from ALTER DATABASE when the database does not exist.
# Listing the error twice to prevent result diffences based on filename.
--error 1,1
ALTER SCHEMA schema1 DEFAULT CHARACTER SET utf8;
SET autocommit= TRUE;

connection con2;
--reap

connection default;
disconnect con2;


--echo #
--echo # Bug #49988 MDL deadlocks with mysql_create_db, reload_acl_and_cache
--echo #

--disable_warnings
DROP SCHEMA IF EXISTS schema1;
--enable_warnings

connect (con2, localhost, root);

connection default;
CREATE SCHEMA schema1;
CREATE TABLE schema1.t1 (id INT);
LOCK TABLE schema1.t1 WRITE;

connection con2;
--send DROP SCHEMA schema1

connection default;
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 
  WHERE state='Waiting for schema metadata lock' and info='DROP SCHEMA schema1';
--source include/wait_condition.inc

--echo # CREATE SCHEMA used to give a deadlock.
--echo # Now we prohibit CREATE SCHEMA in LOCK TABLES mode.
--error ER_LOCK_OR_ACTIVE_TRANSACTION
CREATE SCHEMA IF NOT EXISTS schema1;

--echo # UNLOCK TABLES so DROP SCHEMA can continue.
UNLOCK TABLES;

connection con2;
--reap

connection default;
disconnect con2;


--echo #
--echo # Bug#54360 Deadlock DROP/ALTER/CREATE DATABASE with open HANDLER
--echo #

CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT);
INSERT INTO db1.t1 VALUES (1), (2);

connect (con1, localhost, root);
HANDLER db1.t1 OPEN;

connection default;
--echo # Sending:
--send DROP DATABASE db1

connect (con2, localhost, root);
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
  WHERE state='Waiting for table metadata lock' AND info='DROP DATABASE db1';
--source include/wait_condition.inc

connection con1;
# All these statements before resulted in deadlock.
CREATE DATABASE db2;
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
DROP DATABASE db2;
HANDLER t1 CLOSE;

connection default;
--echo # Reaping: DROP DATABASE db1
--reap
disconnect con1;
disconnect con2;


--echo #
--echo # Tests for increased CREATE/ALTER/DROP DATABASE concurrency with
--echo # database name locks.
--echo # 

--disable_warnings
DROP DATABASE IF EXISTS db1;
DROP DATABASE IF EXISTS db2;
--enable_warnings

connect (con2, localhost, root);
connect (con3, localhost, root);

connection default;
CREATE DATABASE db1;
CREATE TABLE db1.t1 (id INT);
START TRANSACTION;
INSERT INTO db1.t1 VALUES (1);

connection con2;
--echo # DROP DATABASE should block due to the active transaction
--echo # Sending:
--send DROP DATABASE db1

connection con3;
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist 
  WHERE state='Waiting for table metadata lock' and info='DROP DATABASE db1';
--source include/wait_condition.inc
--echo # But it should still be possible to CREATE/ALTER/DROP other databases.
CREATE DATABASE db2;
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
DROP DATABASE db2;

connection default;
--echo # End the transaction so DROP DATABASE db1 can continue
COMMIT;

connection con2;
--echo # Reaping: DROP DATABASE db1
--reap

connection default;
disconnect con2;
disconnect con3;


# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc