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);
5
REPLACE INTO t1 VALUES (1,100),(3,300);
6
# check transaction_log
7
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
8
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
9
transaction.log 638 5 5 1 5 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
11
Check transaction_log_entries
12
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
13
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
20
Check transaction_log_transactions
21
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
22
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
23
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
24
75 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
25
150 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
26
351 4 1 START_TIMESTAMP END_TIMESTAMP 1 0
27
522 5 1 START_TIMESTAMP END_TIMESTAMP 1 0
28
Check transaction log contents
29
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
30
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
74
SET GLOBAL transaction_log_truncate_debug= true;
77
DROP TABLE IF EXISTS t1, t2;
78
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), PRIMARY KEY(a));
79
INSERT INTO t1 (b,c) VALUES (10,'a'),(20,'b'),(30,'c'),(40,'d'),(50,'e'),(60,'f');
80
REPLACE INTO t1 SET a=1,b=42,c='I have been replaced' ;
81
# check transaction_log
82
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
83
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
84
transaction.log 701 5 5 1 5 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
86
Check transaction_log_entries
87
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
88
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
95
Check transaction_log_transactions
96
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
97
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
98
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
99
75 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
100
150 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
101
359 4 1 START_TIMESTAMP END_TIMESTAMP 1 0
102
567 5 1 START_TIMESTAMP END_TIMESTAMP 1 0
103
Check transaction log contents
104
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
105
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
106
transaction_context {
140
after_value: "I have been replaced"
150
SET GLOBAL transaction_log_truncate_debug= true;
152
Testing REPLACE...SELECT
153
DROP TABLE IF EXISTS t1, t2 ;
154
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), d CHAR(20), PRIMARY KEY(a));
155
INSERT INTO t1 (b,c,d) VALUES (10,'a','f'),(20,'b','e'),(30,'c','d'),(40,'d','c'),(50,'e','b'),(60,'f','a');
156
CREATE TABLE t2 LIKE t1;
157
INSERT INTO t2 SELECT a, b*20, CONCAT(c,'replace'), CONCAT(d, 'replace_too') FROM t1;
158
REPLACE INTO t1 SELECT * FROM t2;
159
# check transaction_log
160
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
161
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
162
transaction.log 1596 7 7 1 7 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
164
Check transaction_log_entries
165
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
166
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
175
Check transaction_log_transactions
176
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
177
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
178
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
179
75 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
180
150 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
181
393 4 1 START_TIMESTAMP END_TIMESTAMP 1 0
182
639 5 1 START_TIMESTAMP END_TIMESTAMP 1 0
183
884 6 1 START_TIMESTAMP END_TIMESTAMP 1 0
184
1246 7 1 START_TIMESTAMP END_TIMESTAMP 1 0
185
Check transaction log contents
186
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
187
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
188
transaction_context {
226
after_value: "areplace"
227
after_value: "freplace_too"
235
after_value: "breplace"
236
after_value: "ereplace_too"
244
after_value: "creplace"
245
after_value: "dreplace_too"
253
after_value: "dreplace"
254
after_value: "creplace_too"
262
after_value: "ereplace"
263
after_value: "breplace_too"
271
after_value: "freplace"
272
after_value: "areplace_too"
283
SET GLOBAL transaction_log_truncate_debug= true;
285
Testing multi-row REPLACE
286
DROP TABLE IF EXISTS t1 ;
287
CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL, c CHAR(100), d CHAR(20), PRIMARY KEY(a), UNIQUE KEY(b), UNIQUE KEY(c));
288
INSERT INTO t1 (b,c,d) VALUES (10,'a','f'),(20,'b','e'),(30,'c','d'),(40,'d','c'),(50,'e','b'),(60,'f','a');
297
REPLACE INTO t1 VALUES (1, 20, 'd','x');
298
# check transaction_log
299
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG;
300
FILE_NAME FILE_LENGTH NUM_LOG_ENTRIES NUM_TRANSACTIONS MIN_TRANSACTION_ID MAX_TRANSACTION_ID MIN_END_TIMESTAMP MAX_END_TIMESTAMP INDEX_SIZE_IN_BYTES
301
transaction.log 860 4 4 1 4 START_TIMESTAMP END_TIMESTAMP INDEX_SIZE
303
Check transaction_log_entries
304
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_ENTRIES;
305
ENTRY_OFFSET ENTRY_TYPE ENTRY_LENGTH
311
Check transaction_log_transactions
312
SELECT * FROM DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS;
313
ENTRY_OFFSET TRANSACTION_ID SERVER_ID START_TIMESTAMP END_TIMESTAMP NUM_STATEMENTS CHECKSUM
314
0 1 1 START_TIMESTAMP END_TIMESTAMP 1 0
315
75 2 1 START_TIMESTAMP END_TIMESTAMP 1 0
316
367 3 1 START_TIMESTAMP END_TIMESTAMP 1 0
317
613 4 1 START_TIMESTAMP END_TIMESTAMP 3 0
318
Check transaction log contents
319
SELECT PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS));
320
PRINT_TRANSACTION_MESSAGE('transaction.log',(select max(entry_offset) from DATA_DICTIONARY.TRANSACTION_LOG_TRANSACTIONS))
321
transaction_context {
427
SET GLOBAL transaction_log_truncate_debug= true;