1
# transaction_log_update
2
# test of various DELETE statements and
3
# how the transaction_log captures the relevant data
5
--echo Testing DELETE basic
7
DROP TABLE IF EXISTS t1, t2;
10
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
12
INSERT INTO t1 (b) VALUES (10),(20),(30),(40),(50),(60);
15
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
20
# Truncate the log file to reset for the next test
21
--source ../plugin/transaction_log/tests/t/truncate_log.inc
24
--echo Testing simple DELETE with WHERE
26
DROP TABLE IF EXISTS t1, t2;
29
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
31
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
34
DELETE FROM t1 WHERE a%2=0 ;
35
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
40
# Truncate the log file to reset for the next test
41
--source ../plugin/transaction_log/tests/t/truncate_log.inc
44
--echo Testing simple DELETE with WHERE + LIMIT
46
DROP TABLE IF EXISTS t1, t2;
49
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
51
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
53
DELETE FROM t1 WHERE a%2=0 LIMIT 1;
54
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
59
# Truncate the log file to reset for the next test
60
--source ../plugin/transaction_log/tests/t/truncate_log.inc
63
--echo Testing simple DELETE with WHERE + LIMIT + ORDER BY1
65
DROP TABLE IF EXISTS t1, t2;
68
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
70
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
72
DELETE FROM t1 WHERE a%2=0 ORDER BY a DESC LIMIT 1;
73
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
78
# Truncate the log file to reset for the next test
79
--source ../plugin/transaction_log/tests/t/truncate_log.inc
82
--echo Testing simple DELETE with WHERE + LIMIT + ORDER BY2
84
DROP TABLE IF EXISTS t1, t2;
87
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
89
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
91
DELETE FROM t1 WHERE a%2=0 ORDER BY a DESC LIMIT 10000;
92
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
97
# Truncate the log file to reset for the next test
98
--source ../plugin/transaction_log/tests/t/truncate_log.inc
101
--echo Testing DELETE - Foreign Key constraints CASCADE
103
DROP TABLE IF EXISTS t1, t2;
106
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
108
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
109
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE CASCADE ON UPDATE CASCADE);
111
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
113
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
115
DELETE FROM t1 WHERE b%2=0;
116
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
123
# Truncate the log file to reset for the next test
124
--source ../plugin/transaction_log/tests/t/truncate_log.inc
127
# Truncate the log file to reset for the next test
128
--source ../plugin/transaction_log/tests/t/truncate_log.inc
131
--echo Testing DELETE - Foreign Key constraints SET NULL
133
DROP TABLE IF EXISTS t1, t2;
136
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
138
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
139
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);
141
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
143
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
145
DELETE FROM t1 WHERE b%2=0;
146
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
153
# Truncate the log file to reset for the next test
154
--source ../plugin/transaction_log/tests/t/truncate_log.inc
157
# Truncate the log file to reset for the next test
158
--source ../plugin/transaction_log/tests/t/truncate_log.inc
161
--echo Testing DELETE - Foreign Key constraints CASCADE
163
DROP TABLE IF EXISTS t1, t2;
166
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
168
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
169
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE RESTRICT ON UPDATE CASCADE);
171
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
173
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
176
DELETE FROM t1 WHERE b%2=0;
177
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
184
# Truncate the log file to reset for the next test
185
--source ../plugin/transaction_log/tests/t/truncate_log.inc
188
--echo Testing DELETE basic2
190
DROP TABLE IF EXISTS t1;
195
, padding VARCHAR(200) NOT NULL
199
INSERT INTO t1 VALUES (1, "I love testing.");
200
INSERT INTO t1 VALUES (2, "I hate testing.");
202
DELETE FROM t1 where id = 1;
204
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
209
# Truncate the log file to reset for the next test
210
--source ../plugin/transaction_log/tests/t/truncate_log.inc
213
--echo Testing DELETE / TRUNCATE optimization
214
# Test the situation where no keys (WHERE clause)
215
# are specified in a DELETE statement. In the absence
216
# of triggers, this equates to a TRUNCATE TABLE statement,
217
# and this is what should be written to the transaction log,
218
# not multiple DeleteRecord events.
220
# However, right now this optimization does not occur. We
221
# write individual DeleteRecord message to the log. We will
222
# optimize this away once TableShare has been refactored
230
INSERT INTO t1 VALUES (1, 1);
231
INSERT INTO t1 VALUES (2, 2);
232
INSERT INTO t1 VALUES (3, 3);
233
INSERT INTO t1 VALUES (4, 4);
234
INSERT INTO t1 VALUES (5, 5);
235
INSERT INTO t1 VALUES (6, 6);
236
INSERT INTO t1 VALUES (7, 7);
237
INSERT INTO t1 VALUES (8, 8);
239
--echo This should produce a TRUNCATE event
241
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
246
# Truncate the log file to reset for the next test
247
--source ../plugin/transaction_log/tests/t/truncate_log.inc
250
--echo Testing DELETE Bug#496101
251
# Delete within a transaction does not generate the correct
252
# statements in the transaction log. We start a transaction
253
# and issue both inserts and deletes in the same transaction.
255
DROP TABLE IF EXISTS t1;
260
, padding VARCHAR(200) NOT NULL
266
INSERT INTO t1 VALUES (1, "I love testing.");
267
INSERT INTO t1 VALUES (2, "I hate testing.");
269
DELETE FROM t1 where id = 1;
273
--source ../plugin/transaction_log/tests/t/check_transaction_log.inc
278
# Truncate the log file to reset for the next test
279
--source ../plugin/transaction_log/tests/t/truncate_log.inc