mirror of
https://github.com/MariaDB/server.git
synced 2025-09-17 14:45:34 +02:00
05f9fd3dd2
Problem: ======= - InnoDB modifies the PAGE_ROOT_AUTO_INC value on clustered index root page. But before committing the PAGE_ROOT_AUTO_INC changes mini-transaction, InnoDB does bulk insert operation and calculates the page checksum and store as a part of redo log in mini-transaction. During recovery, InnoDB fails to validate the page checksum. Solution: ======== - Avoid writing the persistent auto increment value before doing bulk insert operation. - For bulk insert operation, persistent auto increment value is written via btr_write_autoinc while applying the buffered insert operation.
60 lines
1.9 KiB
Text
60 lines
1.9 KiB
Text
CREATE TABLE t1(f1 INT NOT NULL,f2 INT NOT NULL)ENGINE=InnoDB;
|
|
INSERT INTO t1 SELECT seq, seq from seq_1_to_131072;
|
|
INSERT INTO t1 VALUES(131073, 131073), (131074, 131073);
|
|
SELECT * INTO OUTFILE "VARDIR/tmp/t1.outfile" FROM t1;
|
|
SELECT * INTO OUTFILE "VARDIR/tmp/t.outfile" FROM t1 LIMIT 1;
|
|
# successful load statement using bulk insert
|
|
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 INT NOT NULL)ENGINE=InnoDB;
|
|
SET unique_checks=0, foreign_key_checks=0;
|
|
LOAD DATA INFILE 'VARDIR/tmp/t1.outfile' INTO TABLE t2;
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
131074
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t2;
|
|
CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1 DESC),
|
|
f2 INT NOT NULL)ENGINE=InnoDB;
|
|
LOAD DATA INFILE 'VARDIR/tmp/t1.outfile' INTO TABLE t2;
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
131074
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t2;
|
|
# load statement using bulk insert fails during secondary index
|
|
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 INT NOT NULL UNIQUE KEY)ENGINE=InnoDB;
|
|
LOAD DATA INFILE 'VARDIR/tmp/t1.outfile' INTO TABLE t2;
|
|
ERROR HY000: Got error 1 "Operation not permitted" during COMMIT
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
0
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t2;
|
|
# load statement using bulk insert fails during primary index
|
|
CREATE TABLE t2(f1 INT NOT NULL,
|
|
f2 INT NOT NULL PRIMARY KEY)ENGINE=InnoDB;
|
|
LOAD DATA INFILE 'VARDIR/tmp/t1.outfile' INTO TABLE t2;
|
|
ERROR 23000: Duplicate entry '131073' for key 'PRIMARY'
|
|
SELECT COUNT(*) FROM t2;
|
|
COUNT(*)
|
|
0
|
|
CHECK TABLE t2 EXTENDED;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 check status OK
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-37192 Crash recovery reports corruption after bulk load
|
|
#
|
|
CREATE TABLE t2(f1 INT AUTO_INCREMENT, f2 INT NOT NULL,
|
|
PRIMARY KEY(f1))ENGINE=InnoDB;
|
|
BEGIN;
|
|
LOAD DATA INFILE 'VARDIR/tmp/t.outfile' INTO TABLE t2;
|
|
# restart
|
|
DROP TABLE t2;
|