2
DROP TABLE IF EXISTS t1, t2;
3
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
4
INSERT INTO t1 (b) VALUES (10),(20),(30),(40),(50),(60);
6
Check transaction_log_entries
7
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
11
Check transaction_log_transactions
12
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
15
Check transaction log contents
16
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
17
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
68
SET GLOBAL transaction_log_truncate_debug= true;
70
Testing simple DELETE with WHERE
71
DROP TABLE IF EXISTS t1, t2;
72
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
73
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
74
DELETE FROM t1 WHERE a%2=0 ;
75
Check transaction_log_entries
76
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
80
Check transaction_log_transactions
81
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
84
Check transaction log contents
85
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
86
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
128
SET GLOBAL transaction_log_truncate_debug= true;
130
Testing simple DELETE with WHERE + LIMIT
131
DROP TABLE IF EXISTS t1, t2;
132
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
133
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
134
DELETE FROM t1 WHERE a%2=0 LIMIT 1;
135
Check transaction_log_entries
136
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
140
Check transaction_log_transactions
141
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
144
Check transaction log contents
145
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
146
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
147
transaction_context {
182
SET GLOBAL transaction_log_truncate_debug= true;
184
Testing simple DELETE with WHERE + LIMIT + ORDER BY1
185
DROP TABLE IF EXISTS t1, t2;
186
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
187
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
188
DELETE FROM t1 WHERE a%2=0 ORDER BY a DESC LIMIT 1;
189
Check transaction_log_entries
190
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
194
Check transaction_log_transactions
195
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
198
Check transaction log contents
199
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
200
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
201
transaction_context {
236
SET GLOBAL transaction_log_truncate_debug= true;
238
Testing simple DELETE with WHERE + LIMIT + ORDER BY2
239
DROP TABLE IF EXISTS t1, t2;
240
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
241
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
242
DELETE FROM t1 WHERE a%2=0 ORDER BY a DESC LIMIT 10000;
243
Check transaction_log_entries
244
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
248
Check transaction_log_transactions
249
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
252
Check transaction log contents
253
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
254
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
255
transaction_context {
296
SET GLOBAL transaction_log_truncate_debug= true;
298
Testing DELETE - Foreign Key constraints CASCADE
299
DROP TABLE IF EXISTS t1, t2;
300
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
301
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
302
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE CASCADE ON UPDATE CASCADE);
303
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
304
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
305
DELETE FROM t1 WHERE b%2=0;
306
Check transaction_log_entries
307
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
311
Check transaction_log_transactions
312
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
315
Check transaction log contents
316
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
317
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
318
transaction_context {
369
SET GLOBAL transaction_log_truncate_debug= true;
371
SET GLOBAL transaction_log_truncate_debug= true;
373
Testing DELETE - Foreign Key constraints SET NULL
374
DROP TABLE IF EXISTS t1, t2;
375
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
376
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
377
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE SET NULL ON UPDATE CASCADE);
378
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
379
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
380
DELETE FROM t1 WHERE b%2=0;
381
Check transaction_log_entries
382
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
386
Check transaction_log_transactions
387
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
390
Check transaction log contents
391
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
392
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
393
transaction_context {
444
SET GLOBAL transaction_log_truncate_debug= true;
446
SET GLOBAL transaction_log_truncate_debug= true;
448
Testing DELETE - Foreign Key constraints CASCADE
449
DROP TABLE IF EXISTS t1, t2;
450
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, PRIMARY KEY(a), KEY b_key1 (b));
451
CREATE TABLE t2(a INT NOT NULL AUTO_INCREMENT, b INT , PRIMARY KEY(a), KEY b_key (b),
452
CONSTRAINT fk_constraint_t2 FOREIGN KEY (b) REFERENCES t1(b) ON DELETE RESTRICT ON UPDATE CASCADE);
453
INSERT INTO t1 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
454
INSERT INTO t2 (b) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(100),(101);
455
DELETE FROM t1 WHERE b%2=0;
456
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_constraint_t2` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON UPDATE CASCADE)
457
Check transaction_log_entries
458
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
462
Check transaction_log_transactions
463
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
466
Check transaction log contents
467
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
468
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
469
transaction_context {
578
SET GLOBAL transaction_log_truncate_debug= true;
580
Testing DELETE basic2
581
DROP TABLE IF EXISTS t1;
584
, padding VARCHAR(200) NOT NULL
587
INSERT INTO t1 VALUES (1, "I love testing.");
588
INSERT INTO t1 VALUES (2, "I hate testing.");
589
DELETE FROM t1 where id = 1;
590
Check transaction_log_entries
591
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
595
Check transaction_log_transactions
596
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
599
Check transaction log contents
600
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
601
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
602
transaction_context {
637
SET GLOBAL transaction_log_truncate_debug= true;
639
Testing DELETE / TRUNCATE optimization
645
INSERT INTO t1 VALUES (1, 1);
646
INSERT INTO t1 VALUES (2, 2);
647
INSERT INTO t1 VALUES (3, 3);
648
INSERT INTO t1 VALUES (4, 4);
649
INSERT INTO t1 VALUES (5, 5);
650
INSERT INTO t1 VALUES (6, 6);
651
INSERT INTO t1 VALUES (7, 7);
652
INSERT INTO t1 VALUES (8, 8);
653
This should produce a TRUNCATE event
655
Check transaction_log_entries
656
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
660
Check transaction_log_transactions
661
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
664
Check transaction log contents
665
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
666
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
667
transaction_context {
723
SET GLOBAL transaction_log_truncate_debug= true;
725
Testing DELETE Bug#496101
726
DROP TABLE IF EXISTS t1;
729
, padding VARCHAR(200) NOT NULL
733
INSERT INTO t1 VALUES (1, "I love testing.");
734
INSERT INTO t1 VALUES (2, "I hate testing.");
735
DELETE FROM t1 where id = 1;
737
Check transaction_log_entries
738
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
742
Check transaction_log_transactions
743
SELECT COUNT(*) FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
746
Check transaction log contents
747
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
748
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
749
transaction_context {
778
insert_value: "I love testing."
807
insert_value: "I hate testing."
842
SET GLOBAL transaction_log_truncate_debug= true;