2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
DROP DATABASE IF EXISTS mysqltest;
8
DROP TABLE IF EXISTS t1, t2, t3, t4, v1;
9
DROP VIEW IF EXISTS t1, t2, t3, t4, v1;
10
DROP PROCEDURE IF EXISTS lock_t1_excl;
11
DROP PROCEDURE IF EXISTS count_t2;
12
DROP PROCEDURE IF EXISTS update_t2;
13
DROP TRIGGER IF EXISTS t1_ai;
15
# WL3561 - transactional LOCK TABLE - Syntax tests
16
# ================================================
17
CREATE TABLE t1 (c1 INT ) ENGINE=MyISAM;
18
CREATE TABLE t2 (c2 INT ) ENGINE=MyISAM;
19
CREATE TABLE t3 (c3 INT ) ENGINE=MyISAM;
21
# Valid syntax for non-transactional locks.
22
LOCK TABLE t1 READ, t2 WRITE;
24
LOCK TABLE t1 READ LOCAL, t2 LOW_PRIORITY WRITE;
27
# Valid syntax for transactional locks.
28
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
30
Warning 1613 Converted to non-transactional lock on 't1'
31
Warning 1613 Converted to non-transactional lock on 't2'
34
# Valid syntax for aliases with and without 'AS'.
35
LOCK TABLE t1 AS a1 READ, t2 a2 WRITE;
37
LOCK TABLE t1 AS a1 IN SHARE MODE, t2 a2 IN EXCLUSIVE MODE;
39
Warning 1613 Converted to non-transactional lock on 'a1'
40
Warning 1613 Converted to non-transactional lock on 'a2'
43
# Transactional locks taken on a view.
44
CREATE VIEW v1 AS SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
45
LOCK TABLE v1 IN SHARE MODE;
47
Warning 1613 Converted to non-transactional lock on 't1'
48
Warning 1613 Converted to non-transactional lock on 't2'
49
LOCK TABLE v1 IN EXCLUSIVE MODE;
51
Warning 1613 Converted to non-transactional lock on 'v1'
54
# Locking INFORMATION_SCHEMA fails on missing privileges.
55
LOCK TABLE information_schema.tables IN SHARE MODE;
56
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
57
LOCK TABLE information_schema.tables IN EXCLUSIVE MODE;
58
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
59
LOCK TABLE information_schema.tables READ;
60
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
61
LOCK TABLE information_schema.tables WRITE;
62
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
64
# The new keywords EXCLUSIVE and NOWAIT are not reserved words.
65
CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=MyISAM;
69
# Syntax errors for misspelled modes or left out symbols.
70
##-------------------------------------------------------
71
LOCK TABLE t1 IN SHARED MODE;
72
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE' at line 1
73
LOCK TABLE t1 SHARE MODE;
74
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
75
LOCK TABLE t1 IN SHARE;
76
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
77
LOCK TABLE t1 IN MODE;
78
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
79
LOCK TABLE t1 READ NOWAIT, t2 WRITE NOWAIT;
80
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 WRITE NOWAIT' at line 1
81
LOCK TABLE t1 READ NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;
82
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT' at line 1
83
LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 WRITE NOWAIT;
84
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
85
LOCK TABLE t1 IN SHARED MODE NOWAIT;
86
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE NOWAIT' at line 1
87
LOCK TABLE t1 SHARE MODE NOWAIT;
88
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
89
LOCK TABLE t1 IN SHARE NOWAIT;
90
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
91
LOCK TABLE t1 IN MODE NOWAIT;
92
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
93
##----------------------
94
## End of syntax errors.
97
# WL3561 - transactional LOCK TABLE - Lock method conversion
98
# ==========================================================
100
# Implicit lock method conversion due to mix in statement.
101
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
103
Warning 1613 Converted to non-transactional lock on 't2'
105
# Lock t1 share (converted to read), t2 write.
106
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
108
Warning 1613 Converted to non-transactional lock on 't1'
109
# Show t1 is read locked, t2 write locked.
110
INSERT INTO t1 SELECT * FROM t2;
111
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
112
INSERT INTO t2 SELECT * FROM t1;
114
# Implicit lock method conversion due to existing non-transact. locks.
115
# Implicitly unlock existing non-transactional locks and take new ones.
116
# Lock t1 exclusive (converted to write), t2 share (converted to read).
117
LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE;
119
Warning 1613 Converted to non-transactional lock on 't1'
120
Warning 1613 Converted to non-transactional lock on 't2'
121
# Show t1 is write locked, t2 read locked.
122
INSERT INTO t1 SELECT * FROM t2;
123
INSERT INTO t2 SELECT * FROM t1;
124
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
127
# Reject lock method conversion in strict mode.
129
SET @wl3561_save_sql_mode= @@SQL_MODE;
130
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
131
# Try mixed mode locks.
132
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
133
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't2'
134
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
135
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
136
# Lock non-transactional.
137
LOCK TABLE t1 READ, t2 WRITE;
138
# Try transactional locks on top of the existing non-transactional locks.
139
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
140
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
141
## Error is reported on first table only. Show both errors:
144
Error 1614 Cannot convert to non-transactional lock in strict mode on 't1'
145
Error 1614 Cannot convert to non-transactional lock in strict mode on 't2'
147
SET @@SQL_MODE= @wl3561_save_sql_mode;
149
# Reject lock method conversion in an active transaction.
152
# Try mixed mode locks.
153
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
154
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't2'
155
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
156
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't1'
159
# Implicit lock method conversion for non-transactional storage engine.
160
# Create a non-transactional table.
161
CREATE TABLE t4 (c4 INT) ENGINE= MyISAM;
162
# Request a transactional lock, which is converted to non-transactional.
163
LOCK TABLE t4 IN SHARE MODE;
165
Warning 1613 Converted to non-transactional lock on 't4'
166
# Try a conflict with the existing non-transactional lock.
167
INSERT INTO t4 VALUES(444);
168
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
171
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
172
# Try a transactional lock, which would need a conversion.
173
LOCK TABLE t4 IN SHARE MODE;
174
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't4'
175
SET @@SQL_MODE= @wl3561_save_sql_mode;
177
# View with transactional and non-transactional storage engine.
178
CREATE VIEW v1 AS SELECT * FROM t3, t4 WHERE t3.c3 = t4.c4;
179
# Request a share lock on the view, which is converted to read locks.
180
LOCK TABLE v1 IN SHARE MODE;
182
Warning 1613 Converted to non-transactional lock on 't3'
183
Warning 1613 Converted to non-transactional lock on 't4'
184
# Show that read locks on the base tables prohibit writing ...
185
INSERT INTO t3 SELECT * FROM t4;
186
ERROR HY000: Table 't3' was locked with a READ lock and can't be updated
187
INSERT INTO t4 SELECT * FROM t3;
188
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
189
# ... but allow reading.
190
SELECT COUNT(*) FROM t3, t4 WHERE t3.c3 = t4.c4;
193
SELECT COUNT(*) FROM v1;
196
## Report conversion on view due to existing non-transactional locks.
197
LOCK TABLE v1 IN EXCLUSIVE MODE;
199
Warning 1613 Converted to non-transactional lock on 'v1'
200
INSERT INTO t3 VALUES(333);
201
INSERT INTO t4 VALUES(444);
202
INSERT INTO t1 VALUES(111);
203
ERROR HY000: Table 't1' was not locked with LOCK TABLES
205
## Now report conversion on base table again.
206
LOCK TABLE v1 IN EXCLUSIVE MODE;
208
Warning 1613 Converted to non-transactional lock on 't3'
209
Warning 1613 Converted to non-transactional lock on 't4'
210
INSERT INTO t3 VALUES(333);
211
INSERT INTO t4 VALUES(444);
212
INSERT INTO t1 VALUES(111);
213
ERROR HY000: Table 't1' was not locked with LOCK TABLES
218
# Insufficient privileges do not unlock tables nor end transactions.
219
# Prepare database, tables and an user with insufficient privileges.
220
# Make a new connection with this user.
221
CREATE DATABASE mysqltest;
222
CREATE TABLE mysqltest.t5 (c5 INT) ENGINE=MyISAM;
223
CREATE TABLE mysqltest.t6 (c6 INT) ENGINE=MyISAM;
224
CREATE USER mysqltest_1@localhost;
225
GRANT SELECT, INSERT ON mysqltest.* TO mysqltest_1@localhost;
227
# Show sufficient privileges to lock tables in the test database.
228
LOCK TABLE t1 READ, t2 WRITE;
229
# Show insufficient privileges in the mysqltest database.
230
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
231
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
232
# Show that the locks in 'test' still exist.
233
INSERT INTO t1 SELECT * FROM t2;
234
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
235
INSERT INTO t2 SELECT * FROM t1;
241
INSERT INTO t1 VALUES(111);
242
# Try a lock that fails on privileges.
243
LOCK TABLE mysqltest.t5 READ;
244
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
245
# Rollback transaction.
248
Warning 1196 Some non-transactional changed tables couldn't be rolled back
249
# Show that the inserted value has gone.
253
# Drop the connection with the unprivileged user.
254
# connection default.
256
# Sufficient privileges do unlock tables and end transactions.
257
# Grant sufficient privileges to the user.
258
# Make a new connection with this user.
259
GRANT SELECT, INSERT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
261
# Lock tables in the test database.
262
LOCK TABLE t1 READ, t2 WRITE;
263
# Lock tables in the mysqltest database.
264
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
265
# Show that the locks in 'test' have been replaced ...
266
INSERT INTO t1 SELECT * FROM t2;
267
ERROR HY000: Table 't1' was not locked with LOCK TABLES
268
INSERT INTO t2 SELECT * FROM t1;
269
ERROR HY000: Table 't2' was not locked with LOCK TABLES
270
# ... by the locks in 'mysqltest'.
271
INSERT INTO mysqltest.t5 SELECT * FROM mysqltest.t6;
272
ERROR HY000: Table 't5' was locked with a READ lock and can't be updated
273
INSERT INTO mysqltest.t6 SELECT * FROM mysqltest.t5;
279
INSERT INTO t1 VALUES(111);
281
LOCK TABLE mysqltest.t5 READ;
282
# Rollback transaction.
285
# Show that the inserted value had been committed.
291
# connection default.
293
DROP USER mysqltest_1@localhost;
294
DROP DATABASE mysqltest;
295
DROP TABLE t1, t2, t3, t4;
297
# WL3594 - transactional LOCK TABLE Testing - Functional tests
298
# ============================================================
299
# Prepare tables and connections.
300
# Set AUTOCOMMIT= 0 in each connection.
302
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
303
CREATE TABLE t2 (c2 INT) ENGINE=MyISAM;
308
# connection default.
310
# Normal WRITE locks go before readers (autocommit).
314
INSERT INTO t1 VALUES(111);
315
# Take a non-transactional lock.
320
# Take a non-transactional WRITE lock,
321
# which waits in background until first read lock is released.
323
# connection default.
324
# Wait for the helper thread to sit on its lock.
328
# Take a non-transactional READ lock,
329
# which waits in background until the WRITE lock is released.
331
# connection default.
332
# Wait for the helper threads to sit on their locks.
333
# Unlock this connections non-transactional lock.
336
# Now the WRITE lock is taken.
338
INSERT INTO t1 VALUES(1111);
342
# Now the READ lock is taken.
343
# Select from the table.
350
# connection default.
353
# LOW_PRIORITY WRITE locks wait for readers (autocommit).
355
INSERT INTO t1 VALUES(111);
356
# Take a non-transactional lock.
359
# Take a non-transactional LOW_PRIORITY WRITE lock,
360
# which waits in background until all read locks are released.
361
LOCK TABLE t1 LOW_PRIORITY WRITE;
362
# connection default.
363
# Wait for the helper thread to sit on its lock.
365
# Take a non-transactional READ lock,
366
# which goes before the LOW_PRIORITY WRITE lock.
368
# The READ lock could be taken immediately.
369
# Select from the table.
376
# connection default.
377
# Unlock this connections non-transactional lock.
380
# Now the LOW_PRIORITY WRITE lock is taken.
382
INSERT INTO t1 VALUES(1111);
386
# connection default.
391
# LOCK TABLE is prohibited in stored procedure.
392
CREATE PROCEDURE lock_t1_excl()
393
LOCK TABLE t1 IN EXCLUSIVE MODE;
394
ERROR 0A000: LOCK is not allowed in stored procedures
396
# LOCK TABLE is prohibited in trigger.
397
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
398
LOCK TABLE t2 IN EXCLUSIVE MODE;
399
ERROR 0A000: LOCK is not allowed in stored procedures
406
Master_Host 127.0.0.1
408
Master_Port MASTER_PORT
410
Master_Log_File master-bin.000001
411
Read_Master_Log_Pos #
414
Relay_Master_Log_File master-bin.000001
416
Slave_SQL_Running Yes
420
Replicate_Ignore_Table
421
Replicate_Wild_Do_Table
422
Replicate_Wild_Ignore_Table
426
Exec_Master_Log_Pos #
431
Master_SSL_Allowed No
437
Seconds_Behind_Master #
438
Master_SSL_Verify_Server_Cert No
added
removed
mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
