A 5.1 testcase. Useful not only for this relatively minor bug, but for testing sibling prefetch on pessimistic B tree update code path too.
--source include/have_innodb_plugin.inc
CREATE TABLE t1 (a BIGINT UNSIGNED PRIMARY KEY,
b CHAR(255) NOT NULL,
c CHAR(255) NOT NULL,
d CHAR(255) NOT NULL,
e CHAR(255) NOT NULL) ENGINE=InnoDB;
SET @t1_space_id= (SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE INFORMATION_SCHEMA.INNODB_SYS_TABLES.SCHEMA='test' AND INFORMATION_SCHEMA.INNODB_SYS_TABLES.NAME='T1');
A 5.1 testcase. Useful not only for this relatively minor bug, but for testing sibling prefetch on pessimistic B tree update code path too.
--source include/ have_innodb_ plugin. inc
CREATE TABLE t1 (a BIGINT UNSIGNED PRIMARY KEY,
b CHAR(255) NOT NULL,
c CHAR(255) NOT NULL,
d CHAR(255) NOT NULL,
e CHAR(255) NOT NULL) ENGINE=InnoDB;
SET @t1_space_id= (SELECT SPACE FROM INFORMATION_ SCHEMA. INNODB_ SYS_TABLES
WHERE
INFORMATION _SCHEMA. INNODB_ SYS_TABLES. SCHEMA= 'test' AND
INFORMATION _SCHEMA. INNODB_ SYS_TABLES. NAME='T1' );
# Fill the root leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (1, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (2, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (3, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (4, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (5, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (6, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (7, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (8, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (9, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (10, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (11, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (12, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (13, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (14, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
SELECT COUNT(*) = 1 AS should_be_1 FROM INFORMATION_ SCHEMA. INNODB_ BUFFER_ POOL_PAGES WHERE SPACE_ID= @t1_space_ id AND PAGE_TYPE LIKE 'index';
# Split the roof leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (15, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
SELECT COUNT(*) = 3 AS should_be_1 FROM INFORMATION_ SCHEMA. INNODB_ BUFFER_ POOL_PAGES WHERE SPACE_ID= @t1_space_ id AND PAGE_TYPE LIKE 'index';
# Fill the right leaf
INSERT INTO t1 (a, b, c, d, e) VALUES (16, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (17, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (18, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (19, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (20, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
INSERT INTO t1 (a, b, c, d, e) VALUES (21, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
SELECT COUNT(*) = 3 AS should_be_1 FROM INFORMATION_ SCHEMA. INNODB_ BUFFER_ POOL_PAGES WHERE SPACE_ID= @t1_space_ id AND PAGE_TYPE LIKE 'index';
# Trigger bug 1192898: pessimistic descent to the right leaf. fake_changes= 1; DURING_ COMMIT fake_changes= 0;
SET innodb_
--error ER_ERROR_
INSERT INTO t1 (a, b, c, d, e) VALUES (22, REPEAT("b", 255), REPEAT("c", 255), REPEAT("d", 255), REPEAT("e", 255));
SET innodb_
DROP TABLE t1;