Bug #14676111 WRONG PAGE_LEVEL WRITTEN FOR UPPER THAN FATHER PAGE AT BTR_LIFT_PAGE_UP()

btr_lift_page_up() writes wrong page number (different by -1) for upper than father page.
But in almost all of the cases, the father page should be root page, no upper
pages. It is very rare path.

In addition the leaf page should not be lifted unless the father page is root.
Because the branch pages should not become the leaf pages.

rb://1336 approved by Marko Makela.

4 files changed, 362 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_bug14676111.result b/mysql-test/suite/innodb/r/innodb_bug14676111.result
new file mode 100644
index 00000000000..ebecd1d00cb
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_bug14676111.result
@@ -0,0 +1,53 @@
+drop table if exists t1;
+CREATE TABLE t1 (a int not null primary key) engine=InnoDB;
+set global innodb_limit_optimistic_insert_debug = 2;
+insert into t1 values (1);
+insert into t1 values (5);
+insert into t1 values (4);
+insert into t1 values (3);
+insert into t1 values (2);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+10.0000
+delete from t1 where a=4;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+8.0000
+delete from t1 where a=5;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+5.0000
+set global innodb_limit_optimistic_insert_debug = 10000;
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+3.0000
+insert into t1 values (2);
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+2.0000
+insert into t1 values (2);
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+1.0000
+drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb_bug14676111.test b/mysql-test/suite/innodb/t/innodb_bug14676111.test
new file mode 100644
index 00000000000..fadd111fdc9
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_bug14676111.test
@@ -0,0 +1,128 @@
+# Test for bug #14676111: WRONG PAGE_LEVEL WRITTEN FOR UPPER THAN FATHER PAGE AT BTR_LIFT_PAGE_UP()
+
+-- source include/have_innodb.inc
+-- source include/have_debug.inc
+
+if (`select count(*)=0 from information_schema.global_variables where variable_name = 'INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG'`)
+{
+  --skip Test requires InnoDB built with UNIV_DEBUG definition.
+}
+
+--disable_query_log
+set @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
+--enable_query_log
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+CREATE TABLE t1 (a int not null primary key) engine=InnoDB;
+
+let $wait_condition=
+  SELECT VARIABLE_VALUE < 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE VARIABLE_NAME = 'INNODB_PURGE_TRX_ID_AGE';
+
+#
+# make 4 leveled straight tree
+#
+set global innodb_limit_optimistic_insert_debug = 2;
+insert into t1 values (1);
+insert into t1 values (5);
+#current tree form
+# (1, 5)
+
+insert into t1 values (4);
+#records in a page is limited to 2 artificially. root rise occurs
+#current tree form
+#   (1, 5)
+#(1, 4) (5)
+
+insert into t1 values (3);
+#current tree form
+#    (1, 5)
+#  (1, 4) (5)
+#(1, 3) (4) (5)
+
+insert into t1 values (2);
+#current tree form
+#      (1, 5)
+#    (1, 4) (5)
+#  (1, 3) (4) (5)
+#(1, 2) (3) (4) (5)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+delete from t1 where a=4;
+--source include/wait_condition.inc
+#deleting 1 record of 2 records don't cause merge artificially.
+#current tree form
+#      (1, 5)
+#    (1)    (5)
+#  (1, 3)     (5)
+#(1, 2) (3)     (5)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+delete from t1 where a=5;
+--source include/wait_condition.inc
+#deleting 1 record of 2 records don't cause merge artificially.
+#current tree form
+#      (1)
+#    (1)
+#  (1, 3) <- lift up this level next, when deleting node ptr
+#(1, 2) (3) <- merged next
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+#
+# cause merge at level 0
+#
+
+#disable the artificial limitation of records in a page
+set global innodb_limit_optimistic_insert_debug = 10000;
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#merge page occurs. and lift up occurs.
+#current tree form
+#      (1)
+#    (1)
+#  (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+insert into t1 values (2);
+#current tree form
+#      (1)
+#    (1) <- lift up this level next, because it is not root
+#  (1, 2, 3)
+
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#current tree form
+#      (1)
+#    (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+insert into t1 values (2);
+#current tree form
+#      (1)
+#    (1, 2, 3) <- lift up this level next, because the father is root
+
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#current tree form
+#      (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+drop table t1;
+
+--disable_query_log
+set global innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
+--enable_query_log
diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug14676111.result b/mysql-test/suite/innodb_plugin/r/innodb_bug14676111.result
new file mode 100644
index 00000000000..ebecd1d00cb
--- /dev/null
+++ b/mysql-test/suite/innodb_plugin/r/innodb_bug14676111.result
@@ -0,0 +1,53 @@
+drop table if exists t1;
+CREATE TABLE t1 (a int not null primary key) engine=InnoDB;
+set global innodb_limit_optimistic_insert_debug = 2;
+insert into t1 values (1);
+insert into t1 values (5);
+insert into t1 values (4);
+insert into t1 values (3);
+insert into t1 values (2);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+10.0000
+delete from t1 where a=4;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+8.0000
+delete from t1 where a=5;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+5.0000
+set global innodb_limit_optimistic_insert_debug = 10000;
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+3.0000
+insert into t1 values (2);
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+2.0000
+insert into t1 values (2);
+delete from t1 where a=2;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+DATA_LENGTH / 16384
+1.0000
+drop table t1;
diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug14676111.test b/mysql-test/suite/innodb_plugin/t/innodb_bug14676111.test
new file mode 100644
index 00000000000..ae871e3b63e
--- /dev/null
+++ b/mysql-test/suite/innodb_plugin/t/innodb_bug14676111.test
@@ -0,0 +1,128 @@
+# Test for bug #14676111: WRONG PAGE_LEVEL WRITTEN FOR UPPER THAN FATHER PAGE AT BTR_LIFT_PAGE_UP()
+
+-- source include/have_innodb_plugin.inc
+-- source include/have_debug.inc
+
+if (`select count(*)=0 from information_schema.global_variables where variable_name = 'INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG'`)
+{
+  --skip Test requires InnoDB built with UNIV_DEBUG definition.
+}
+
+--disable_query_log
+set @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
+--enable_query_log
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+CREATE TABLE t1 (a int not null primary key) engine=InnoDB;
+
+let $wait_condition=
+  SELECT VARIABLE_VALUE < 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
+  WHERE VARIABLE_NAME = 'INNODB_PURGE_TRX_ID_AGE';
+
+#
+# make 4 leveled straight tree
+#
+set global innodb_limit_optimistic_insert_debug = 2;
+insert into t1 values (1);
+insert into t1 values (5);
+#current tree form
+# (1, 5)
+
+insert into t1 values (4);
+#records in a page is limited to 2 artificially. root rise occurs
+#current tree form
+#   (1, 5)
+#(1, 4) (5)
+
+insert into t1 values (3);
+#current tree form
+#    (1, 5)
+#  (1, 4) (5)
+#(1, 3) (4) (5)
+
+insert into t1 values (2);
+#current tree form
+#      (1, 5)
+#    (1, 4) (5)
+#  (1, 3) (4) (5)
+#(1, 2) (3) (4) (5)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+delete from t1 where a=4;
+--source include/wait_condition.inc
+#deleting 1 record of 2 records don't cause merge artificially.
+#current tree form
+#      (1, 5)
+#    (1)    (5)
+#  (1, 3)     (5)
+#(1, 2) (3)     (5)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+delete from t1 where a=5;
+--source include/wait_condition.inc
+#deleting 1 record of 2 records don't cause merge artificially.
+#current tree form
+#      (1)
+#    (1)
+#  (1, 3) <- lift up this level next, when deleting node ptr
+#(1, 2) (3) <- merged next
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+#
+# cause merge at level 0
+#
+
+#disable the artificial limitation of records in a page
+set global innodb_limit_optimistic_insert_debug = 10000;
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#merge page occurs. and lift up occurs.
+#current tree form
+#      (1)
+#    (1)
+#  (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+insert into t1 values (2);
+#current tree form
+#      (1)
+#    (1) <- lift up this level next, because it is not root
+#  (1, 2, 3)
+
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#current tree form
+#      (1)
+#    (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+insert into t1 values (2);
+#current tree form
+#      (1)
+#    (1, 2, 3) <- lift up this level next, because the father is root
+
+delete from t1 where a=2;
+--source include/wait_condition.inc
+#current tree form
+#      (1, 3)
+
+analyze table t1;
+select DATA_LENGTH / 16384 from information_schema.TABLES where TABLE_SCHEMA = 'test' and TABLE_NAME = 't1';
+
+drop table t1;
+
+--disable_query_log
+set global innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
+--enable_query_log