MDEV-11336: Enable defragmentation on 10.2 when tests passbb-10.2-MDEV-11336

WIP: Problem was that we could take page latches on different
order causing deadlock.

btr_defragment_merge_pages
	Fix parameter value.

btr_defragment_thread
	Acquire X-lock to dict_index_t::lock before restoring
	cursor position and continuing defragmentation.

ha_innobase::optimize
	Restore defragment feature.

Testing
	Add GIS-index and FT-index to table being defragmented.

	Defragmentation is not done to GIS-indexes and FT auxiliary
	tables.

5 files changed, 63 insertions, 20 deletions

diff --git a/mysql-test/suite/innodb/disabled.def b/mysql-test/suite/innodb/disabled.def
index 9a92e99df2e..c435de278b9 100644
--- a/mysql-test/suite/innodb/disabled.def
+++ b/mysql-test/suite/innodb/disabled.def
@@ -10,14 +10,5 @@
 #
 ##############################################################################
 
-innodb_defragment_fill_factor : MDEV-11336 Fix and enable innodb_defragment
-innodb.defrag_mdl-9155 : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defrag_concurrent : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defrag_stats : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defrag_stats_many_tables : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defragment : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defragment_fill_factor : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defragment_small : MDEV-11336 Fix and enable innodb_defragment
-innodb.innodb_defrag_binlog : MDEV-11336 Fix and enable innodb_defragment
 innodb-wl5980-alter : MDEV-9469 / MDEV-13668 extra crash in 10.2
 create-index-debug : MDEV-13680 InnoDB may crash when btr_page_alloc() fails
diff --git a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
index ff32bf694cb..89adeeb2867 100644
--- a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
+++ b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
@@ -3,7 +3,15 @@ select @@global.innodb_stats_persistent;
 @@global.innodb_stats_persistent
 0
 set global innodb_defragment_stats_accuracy = 80;
-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c INT, KEY second(a, b),KEY third(c)) ENGINE=INNODB;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
+b VARCHAR(256),
+c INT,
+g GEOMETRY NOT NULL,
+t VARCHAR(256),
+KEY second(a, b),
+KEY third(c),
+SPATIAL gk(g),
+FULLTEXT INDEX fti(t)) ENGINE=INNODB;
 connect  con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
 connect  con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
 connect  con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
@@ -19,6 +27,12 @@ count(*)
 select count(*) from t1 force index (third);
 count(*)
 20000
+select count(*) from t1 force index (gk);
+count(*)
+20000
+select count(*) from t1 force index (fti);
+count(*)
+20000
 select count(*) from t1;
 count(*)
 15800
@@ -28,6 +42,12 @@ count(*)
 select count(*) from t1 force index (third);
 count(*)
 15800
+select count(*) from t1 force index (gk);
+count(*)
+15800
+select count(*) from t1 force index (fti);
+count(*)
+15800
 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
 count(stat_value) > 0
 0
@@ -40,9 +60,9 @@ count(stat_value) > 0
 connection con1;
 optimize table t1;;
 connection default;
-INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000);;
+INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000, Point(1,1),'More like a test but different.');;
 connection con2;
-INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000);;
+INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000, Point(1,1),'Totally different text book.');;
 connection con3;
 DELETE FROM t1 where a between 1 and 100;;
 connection con4;
@@ -68,6 +88,12 @@ count(*)
 select count(*) from t1 force index (third);
 count(*)
 15723
+select count(*) from t1 force index (gk);
+count(*)
+15723
+select count(*) from t1 force index (fti);
+count(*)
+15723
 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
 count(stat_value) > 0
 1
diff --git a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
index f596fab2a15..e2a6f58499a 100644
--- a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
+++ b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
@@ -16,7 +16,26 @@ select @@global.innodb_stats_persistent;
 set global innodb_defragment_stats_accuracy = 80;
 
 # Create table.
-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), c INT, KEY second(a, b),KEY third(c)) ENGINE=INNODB;
+#
+# TODO: Currently we do not defragment  spatial indexes,
+# because doing it properly would require
+# appropriate logic around the SSN (split
+# sequence number).
+#
+# Also do not defragment auxiliary tables related to FULLTEXT INDEX.
+#
+# Both types added to this test to make sure they do not cause
+# problems.
+#
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
+b VARCHAR(256),
+c INT,
+g GEOMETRY NOT NULL,
+t VARCHAR(256),
+KEY second(a, b),
+KEY third(c),
+SPATIAL gk(g),
+FULLTEXT INDEX fti(t)) ENGINE=INNODB;
 
 connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
 connect (con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
@@ -36,7 +55,7 @@ let $i = $data_size;
 while ($i)
 {
   eval
-    INSERT INTO t1 VALUES ($data_size + 1 - $i, REPEAT('A', 256), $i);
+    INSERT INTO t1 VALUES ($data_size + 1 - $i, REPEAT('A', 256), $i, Point(1,1), 'This is a test message.');
   dec $i;
 }
 --enable_query_log
@@ -44,6 +63,8 @@ while ($i)
 select count(*) from t1; 
 select count(*) from t1 force index (second);
 select count(*) from t1 force index (third);
+select count(*) from t1 force index (gk);
+select count(*) from t1 force index (fti);
 
 # Delete some data
 --disable_query_log
@@ -59,6 +80,8 @@ while ($size)
 select count(*) from t1; 
 select count(*) from t1 force index (second);
 select count(*) from t1 force index (third);
+select count(*) from t1 force index (gk);
+select count(*) from t1 force index (fti);
 
 # Above delete will free some pages and insert causes page split and these could cause defrag
 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
@@ -69,10 +92,10 @@ connection con1;
 --send optimize table t1;
 
 connection default;
---send INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000);
+--send INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000, Point(1,1),'More like a test but different.');
 
 connection con2;
---send INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000);
+--send INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000, Point(1,1),'Totally different text book.');
 
 connection con3;
 --send DELETE FROM t1 where a between 1 and 100;
@@ -107,6 +130,8 @@ optimize table t1;
 select count(*) from t1;
 select count(*) from t1 force index (second);
 select count(*) from t1 force index (third);
+select count(*) from t1 force index (gk);
+select count(*) from t1 force index (fti);
 
 # Now pages are freed
 select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and  stat_name  in ('n_pages_freed');
diff --git a/storage/innobase/btr/btr0defragment.cc b/storage/innobase/btr/btr0defragment.cc
index 335b4fc220d..e499ce63969 100644
--- a/storage/innobase/btr/btr0defragment.cc
+++ b/storage/innobase/btr/btr0defragment.cc
@@ -564,7 +564,7 @@ btr_defragment_merge_pages(
 				page_get_infimum_rec(from_page));
 			node_ptr = dict_index_build_node_ptr(
 				index, rec, page_get_page_no(from_page),
-				heap, level + 1);
+				heap, level);
 			btr_insert_on_non_leaf_level(0, index, level+1,
 						     node_ptr, mtr);
 		}
@@ -797,11 +797,12 @@ DECLARE_THREAD(btr_defragment_thread)(void*)
 
 		now = ut_timer_now();
 		mtr_start(&mtr);
-		btr_pcur_restore_position(BTR_MODIFY_TREE, pcur, &mtr);
 		cursor = btr_pcur_get_btr_cur(pcur);
 		index = btr_cur_get_index(cursor);
-		first_block = btr_cur_get_block(cursor);
 		mtr.set_named_space(index->space);
+		mtr_x_lock(dict_index_get_lock(index), &mtr);
+		btr_pcur_restore_position(BTR_MODIFY_TREE, pcur, &mtr);
+		first_block = btr_cur_get_block(cursor);
 
 		last_block = btr_defragment_n_pages(first_block, index,
 						    srv_defragment_n_pages,
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 3374f46fb24..e7bf2b649f9 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -15066,7 +15066,7 @@ ha_innobase::optimize(
 	calls to OPTIMIZE, which is undesirable. */
 
 	/* TODO: Defragment is disabled for now */
-	if (0) {
+	if (srv_defragment) {
 		int err;
 
 		err = defragment_table(m_prebuilt->table->name.m_name, NULL, false);