From eeb8ebb15201c69787ea632df0cd3bc9d1bfed46 Mon Sep 17 00:00:00 2001 From: Daniele Sciascia Date: Mon, 16 Jan 2023 17:02:16 +0100 Subject: MDEV-29774 BF abort no longer wakes up debug_sync waiters MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Since commit d7d3ad698abde16978e094b825e17a6ecd8604e, "hard" kill is required to interrupt debug sync waits. Affected the following tests: - galera_var_retry_autocommit, - galera_bf_abort_at_after_statement - galera_parallel_apply_3nodes Reviewed-by: Jan Lindström --- mysql-test/suite/galera/disabled.def | 1 - mysql-test/suite/galera_3nodes/disabled.def | 2 -- 2 files changed, 3 deletions(-) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index 0210afb7e0a..d15f2d271ca 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -11,7 +11,6 @@ ############################################################################## galera_as_slave_ctas : MDEV-28378 timeout -galera_bf_abort_at_after_statement : Timeout in wait_condition.inc for SELECT COUNT(*) = 1 FROM t1 where id = 1 and val = 3 galera_pc_recovery : MDEV-25199 cluster fails to start up galera_sst_encrypted : MDEV-29876 Galera test failure on galera_sst_encrypted MW-284 : MDEV-29861 Galera test case hangs diff --git a/mysql-test/suite/galera_3nodes/disabled.def b/mysql-test/suite/galera_3nodes/disabled.def index e092b445d0a..22ff54ed504 100644 --- a/mysql-test/suite/galera_3nodes/disabled.def +++ b/mysql-test/suite/galera_3nodes/disabled.def @@ -13,8 +13,6 @@ galera_2_cluster : MDEV-29877 Galera test failure on galera_2_cluster galera_gtid_2_cluster : MDEV-29877 Galera test failure on galera_2_cluster -galera_parallel_apply_3nodes : MDEV-29368 DEBUG_SYNC timeout -galera_parallel_apply_3nodes : MDEV-29774 Galera test galera_parallel_apply_3nodes is unstable galera_vote_rejoin_mysqldump : MDEV-24481: galera_3nodes.galera_vote_rejoin_mysqldump MTR failed: mysql_shutdown failed galera_2_cluster : MDEV-29877 Galera test failure on galera_2_cluster galera_gtid_2_cluster : MDEV-29877 Galera test failure on galera_2_cluster -- cgit v1.2.1 From f9cac8d2cbf82d4d616905fb3dfab34a9901179d Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 19 Jan 2023 17:19:18 +0200 Subject: MDEV-30400 Assertion height == btr_page_get_level(...) on INSERT This also fixes part of MDEV-29835 Partial server freeze which is caused by violations of the latching order that was defined in https://dev.mysql.com/worklog/task/?id=6326 (WL#6326: InnoDB: fix index->lock contention). Unless the current thread is holding an exclusive dict_index_t::lock, it must acquire page latches in a strict parent-to-child, left-to-right order. Not all cases are fixed yet. Failure to follow the correct latching order will cause deadlocks of threads due to lock order inversion. As part of these changes, the BTR_MODIFY_TREE mode is modified so that an Update latch (U a.k.a. SX) will be acquired on the root page, and eXclusive latches (X) will be acquired on all pages leading to the leaf page, as well as any left and right siblings of the pages along the path. The test innodb.innodb_wl6326 will be removed, because at the time the DEBUG_SYNC point is hit, the thread is actually holding several page latches that will be blocking a concurrent SELECT statement. We also remove double bookkeeping that was caused due to excessive information hiding in mtr_t::m_memo. We simply let mtr_t::m_memo store information of latched pages, and ensure that mtr_memo_slot_t::object is never a null pointer. The tree_blocks[] and tree_savepoints[] were redundant. mtr_t::get_already_latched(): Look up a latched page in mtr_t::m_memo. This avoids many redundant entries in mtr_t::m_memo, as well as redundant calls to buf_page_get_gen() for blocks that had already been looked up in a mini-transaction. btr_get_latched_root(): Return a pointer to an already latched root page. This replaces btr_root_block_get() in cases where the mini-transaction has already latched the root page. btr_page_get_parent(): Fetch a parent page that was already latched in BTR_MODIFY_TREE, by invoking mtr_t::get_already_latched(). If needed, upgrade the root page U latch to X. This avoids bloating mtr_t::m_memo as well as redundant buf_pool.page_hash lookups. For non-QUICK CHECK TABLE as well as for B-tree defragmentation, we will invoke btr_cur_search_to_nth_level(). btr_cur_search_to_nth_level(): This will only be used for non-leaf (level>0) B-tree searches that were formerly named BTR_CONT_SEARCH_TREE or BTR_CONT_MODIFY_TREE. In MDEV-29835, this function could be removed altogether, or retained for the case of CHECK TABLE without QUICK. btr_cur_t::search_leaf(): Replaces btr_cur_search_to_nth_level() for searches to level=0 (the leaf level). btr_cur_t::pessimistic_search_leaf(): Implement the new BTR_MODIFY_TREE latching logic in the case that page splits or merges will be needed. The parent pages (and their siblings) should already be latched on the first dive to the leaf and be present in mtr_t::m_memo; there should be no need for BTR_CONT_MODIFY_TREE. This pre-latching almost suffices; MDEV-29835 will have to revise it and remove work-arounds where mtr_t::get_already_latched() fails to find a block. rtr_search_to_nth_level(): A SPATIAL INDEX version of btr_search_to_nth_level() that can search to any level (including the leaf level). rtr_search_leaf(), rtr_insert_leaf(): Wrappers for rtr_search_to_nth_level(). rtr_search(): Replaces rtr_pcur_open(). rtr_cur_restore_position(): Remove an unused constant parameter. btr_pcur_open_on_user_rec(): Remove the constant parameter mode=PAGE_CUR_GE. btr_cur_latch_leaves(): Update a pre-existing mtr_t::m_memo entry for the current leaf page. row_ins_clust_index_entry_low(): Use a new mode=BTR_MODIFY_ROOT_AND_LEAF to gain access to the root page when mode!=BTR_MODIFY_TREE, to write the PAGE_ROOT_AUTO_INC. btr_cur_t::open_leaf(): Some clean-up. mtr_t::lock_register(): Register a page latch on a buffer-fixed block. BTR_SEARCH_TREE, BTR_CONT_SEARCH_TREE: Remove. BTR_CONT_MODIFY_TREE: Note that this is only used by rtr_search_to_nth_level(). btr_pcur_optimistic_latch_leaves(): Replaces btr_cur_optimistic_latch_leaves(). ibuf_delete_rec(): Acquire ibuf.index->lock.u_lock() in order to avoid a deadlock with ibuf_insert_low(BTR_MODIFY_PREV). Tested by: Matthias Leich --- mysql-test/suite/innodb/r/innodb_wl6326.result | 405 ------------------ mysql-test/suite/innodb/t/innodb_wl6326.opt | 1 - mysql-test/suite/innodb/t/innodb_wl6326.test | 519 ----------------------- mysql-test/suite/innodb_gis/r/rtree_split.result | 12 + mysql-test/suite/innodb_gis/t/rtree_split.test | 15 + 5 files changed, 27 insertions(+), 925 deletions(-) delete mode 100644 mysql-test/suite/innodb/r/innodb_wl6326.result delete mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.opt delete mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/innodb_wl6326.result b/mysql-test/suite/innodb/r/innodb_wl6326.result deleted file mode 100644 index fcd58aedafe..00000000000 --- a/mysql-test/suite/innodb/r/innodb_wl6326.result +++ /dev/null @@ -1,405 +0,0 @@ -SET GLOBAL innodb_adaptive_hash_index = false; -SET GLOBAL innodb_stats_persistent = false; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connect con3,localhost,root,,; -CREATE TABLE t1 ( -a00 CHAR(255) NOT NULL DEFAULT 'a', -a01 CHAR(255) NOT NULL DEFAULT 'a', -a02 CHAR(255) NOT NULL DEFAULT 'a', -a03 CHAR(255) NOT NULL DEFAULT 'a', -a04 CHAR(255) NOT NULL DEFAULT 'a', -a05 CHAR(255) NOT NULL DEFAULT 'a', -a06 CHAR(255) NOT NULL DEFAULT 'a', -b INT NOT NULL DEFAULT 0 -) ENGINE = InnoDB; -ALTER TABLE t1 ADD PRIMARY KEY( -a00, -a01, -a02, -a03, -a04, -a05, -a06 -); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -1 -SET GLOBAL innodb_limit_optimistic_insert_debug = 7; -BEGIN; -INSERT INTO t1 (a00) VALUES ('aa'); -INSERT INTO t1 (a00) VALUES ('ab'); -INSERT INTO t1 (a00) VALUES ('ac'); -INSERT INTO t1 (a00) VALUES ('ad'); -INSERT INTO t1 (a00) VALUES ('ae'); -INSERT INTO t1 (a00) VALUES ('af'); -INSERT INTO t1 (a00) VALUES ('ag'); -INSERT INTO t1 (a00) VALUES ('ah'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -3 -BEGIN; -INSERT INTO t1 (a00) VALUES ('ai'); -INSERT INTO t1 (a00) VALUES ('aj'); -INSERT INTO t1 (a00) VALUES ('ak'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -4 -BEGIN; -INSERT INTO t1 (a00) VALUES ('al'); -INSERT INTO t1 (a00) VALUES ('am'); -INSERT INTO t1 (a00) VALUES ('an'); -INSERT INTO t1 (a00) VALUES ('ao'); -INSERT INTO t1 (a00) VALUES ('ap'); -INSERT INTO t1 (a00) VALUES ('aq'); -INSERT INTO t1 (a00) VALUES ('ar'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -5 -BEGIN; -INSERT INTO t1 (a00) VALUES ('as'); -INSERT INTO t1 (a00) VALUES ('at'); -INSERT INTO t1 (a00) VALUES ('au'); -INSERT INTO t1 (a00) VALUES ('av'); -INSERT INTO t1 (a00) VALUES ('aw'); -INSERT INTO t1 (a00) VALUES ('ax'); -INSERT INTO t1 (a00) VALUES ('ay'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -6 -BEGIN; -INSERT INTO t1 (a00) VALUES ('az'); -INSERT INTO t1 (a00) VALUES ('ba'); -INSERT INTO t1 (a00) VALUES ('bb'); -INSERT INTO t1 (a00) VALUES ('bc'); -INSERT INTO t1 (a00) VALUES ('bd'); -INSERT INTO t1 (a00) VALUES ('be'); -INSERT INTO t1 (a00) VALUES ('bf'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -7 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bg'); -INSERT INTO t1 (a00) VALUES ('bh'); -INSERT INTO t1 (a00) VALUES ('bi'); -INSERT INTO t1 (a00) VALUES ('bj'); -INSERT INTO t1 (a00) VALUES ('bk'); -INSERT INTO t1 (a00) VALUES ('bl'); -INSERT INTO t1 (a00) VALUES ('bm'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -8 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bn'); -INSERT INTO t1 (a00) VALUES ('bo'); -INSERT INTO t1 (a00) VALUES ('bp'); -INSERT INTO t1 (a00) VALUES ('bq'); -INSERT INTO t1 (a00) VALUES ('br'); -INSERT INTO t1 (a00) VALUES ('bs'); -INSERT INTO t1 (a00) VALUES ('bt'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -11 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bu'); -INSERT INTO t1 (a00) VALUES ('bv'); -INSERT INTO t1 (a00) VALUES ('bw'); -INSERT INTO t1 (a00) VALUES ('bx'); -INSERT INTO t1 (a00) VALUES ('by'); -INSERT INTO t1 (a00) VALUES ('bz'); -INSERT INTO t1 (a00) VALUES ('ca'); -INSERT INTO t1 (a00) VALUES ('cb'); -INSERT INTO t1 (a00) VALUES ('cc'); -INSERT INTO t1 (a00) VALUES ('cd'); -INSERT INTO t1 (a00) VALUES ('ce'); -INSERT INTO t1 (a00) VALUES ('cf'); -INSERT INTO t1 (a00) VALUES ('cg'); -INSERT INTO t1 (a00) VALUES ('ch'); -INSERT INTO t1 (a00) VALUES ('ci'); -INSERT INTO t1 (a00) VALUES ('cj'); -INSERT INTO t1 (a00) VALUES ('ck'); -INSERT INTO t1 (a00) VALUES ('cl'); -INSERT INTO t1 (a00) VALUES ('cm'); -INSERT INTO t1 (a00) VALUES ('cn'); -INSERT INTO t1 (a00) VALUES ('co'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -15 -BEGIN; -INSERT INTO t1 (a00) VALUES ('cp'); -INSERT INTO t1 (a00) VALUES ('cq'); -INSERT INTO t1 (a00) VALUES ('cr'); -INSERT INTO t1 (a00) VALUES ('cs'); -INSERT INTO t1 (a00) VALUES ('ct'); -INSERT INTO t1 (a00) VALUES ('cu'); -INSERT INTO t1 (a00) VALUES ('cv'); -INSERT INTO t1 (a00) VALUES ('cw'); -INSERT INTO t1 (a00) VALUES ('cx'); -INSERT INTO t1 (a00) VALUES ('cy'); -INSERT INTO t1 (a00) VALUES ('cz'); -INSERT INTO t1 (a00) VALUES ('da'); -INSERT INTO t1 (a00) VALUES ('db'); -INSERT INTO t1 (a00) VALUES ('dc'); -INSERT INTO t1 (a00) VALUES ('dd'); -INSERT INTO t1 (a00) VALUES ('de'); -INSERT INTO t1 (a00) VALUES ('df'); -INSERT INTO t1 (a00) VALUES ('dg'); -INSERT INTO t1 (a00) VALUES ('dh'); -INSERT INTO t1 (a00) VALUES ('di'); -INSERT INTO t1 (a00) VALUES ('dj'); -INSERT INTO t1 (a00) VALUES ('dk'); -INSERT INTO t1 (a00) VALUES ('dl'); -INSERT INTO t1 (a00) VALUES ('dm'); -INSERT INTO t1 (a00) VALUES ('dn'); -INSERT INTO t1 (a00) VALUES ('do'); -INSERT INTO t1 (a00) VALUES ('dp'); -INSERT INTO t1 (a00) VALUES ('dq'); -INSERT INTO t1 (a00) VALUES ('dr'); -INSERT INTO t1 (a00) VALUES ('ds'); -INSERT INTO t1 (a00) VALUES ('dt'); -INSERT INTO t1 (a00) VALUES ('du'); -INSERT INTO t1 (a00) VALUES ('dv'); -INSERT INTO t1 (a00) VALUES ('dw'); -INSERT INTO t1 (a00) VALUES ('dx'); -INSERT INTO t1 (a00) VALUES ('dy'); -INSERT INTO t1 (a00) VALUES ('dz'); -INSERT INTO t1 (a00) VALUES ('ea'); -INSERT INTO t1 (a00) VALUES ('eb'); -INSERT INTO t1 (a00) VALUES ('ec'); -INSERT INTO t1 (a00) VALUES ('ed'); -INSERT INTO t1 (a00) VALUES ('ee'); -INSERT INTO t1 (a00) VALUES ('ef'); -INSERT INTO t1 (a00) VALUES ('eg'); -INSERT INTO t1 (a00) VALUES ('eh'); -INSERT INTO t1 (a00) VALUES ('ei'); -INSERT INTO t1 (a00) VALUES ('ej'); -INSERT INTO t1 (a00) VALUES ('ek'); -INSERT INTO t1 (a00) VALUES ('el'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -23 -BEGIN; -INSERT INTO t1 (a00) VALUES ('em'); -INSERT INTO t1 (a00) VALUES ('en'); -INSERT INTO t1 (a00) VALUES ('eo'); -INSERT INTO t1 (a00) VALUES ('ep'); -INSERT INTO t1 (a00) VALUES ('eq'); -INSERT INTO t1 (a00) VALUES ('er'); -INSERT INTO t1 (a00) VALUES ('es'); -INSERT INTO t1 (a00) VALUES ('et'); -INSERT INTO t1 (a00) VALUES ('eu'); -INSERT INTO t1 (a00) VALUES ('ev'); -INSERT INTO t1 (a00) VALUES ('ew'); -INSERT INTO t1 (a00) VALUES ('ex'); -INSERT INTO t1 (a00) VALUES ('ey'); -INSERT INTO t1 (a00) VALUES ('ez'); -INSERT INTO t1 (a00) VALUES ('fa'); -INSERT INTO t1 (a00) VALUES ('fb'); -INSERT INTO t1 (a00) VALUES ('fc'); -INSERT INTO t1 (a00) VALUES ('fd'); -INSERT INTO t1 (a00) VALUES ('fe'); -INSERT INTO t1 (a00) VALUES ('ff'); -INSERT INTO t1 (a00) VALUES ('fg'); -INSERT INTO t1 (a00) VALUES ('fh'); -INSERT INTO t1 (a00) VALUES ('fi'); -INSERT INTO t1 (a00) VALUES ('fj'); -INSERT INTO t1 (a00) VALUES ('fk'); -INSERT INTO t1 (a00) VALUES ('fl'); -INSERT INTO t1 (a00) VALUES ('fm'); -INSERT INTO t1 (a00) VALUES ('fn'); -INSERT INTO t1 (a00) VALUES ('fo'); -INSERT INTO t1 (a00) VALUES ('fp'); -INSERT INTO t1 (a00) VALUES ('fq'); -INSERT INTO t1 (a00) VALUES ('fr'); -INSERT INTO t1 (a00) VALUES ('fs'); -INSERT INTO t1 (a00) VALUES ('ft'); -INSERT INTO t1 (a00) VALUES ('fu'); -INSERT INTO t1 (a00) VALUES ('fv'); -INSERT INTO t1 (a00) VALUES ('fw'); -INSERT INTO t1 (a00) VALUES ('fx'); -INSERT INTO t1 (a00) VALUES ('fy'); -INSERT INTO t1 (a00) VALUES ('fz'); -INSERT INTO t1 (a00) VALUES ('ga'); -INSERT INTO t1 (a00) VALUES ('gb'); -INSERT INTO t1 (a00) VALUES ('gc'); -INSERT INTO t1 (a00) VALUES ('gd'); -INSERT INTO t1 (a00) VALUES ('ge'); -INSERT INTO t1 (a00) VALUES ('gf'); -INSERT INTO t1 (a00) VALUES ('gg'); -INSERT INTO t1 (a00) VALUES ('gh'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -29 -SET GLOBAL innodb_limit_optimistic_insert_debug = 0; -# Test start -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('bfa'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('bfb'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -a00 a01 -aa a -SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; -a00 a01 -aq a -SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; -a00 a01 -cp a -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -a00 a01 -el a -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -ar a -connection con3; -a00 a01 -cn a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -30 -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('coa'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('cob'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -aa a -connection con3; -a00 a01 -el a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -31 -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('gba'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('gbb'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -a00 a01 -aa a -SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; -a00 a01 -ek a -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -el a -connection con3; -a00 a01 -gb a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -32 -SET DEBUG_SYNC = 'RESET'; -connection default; -disconnect con1; -disconnect con2; -disconnect con3; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.opt b/mysql-test/suite/innodb/t/innodb_wl6326.opt deleted file mode 100644 index 99bf0e5a28b..00000000000 --- a/mysql-test/suite/innodb/t/innodb_wl6326.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.test b/mysql-test/suite/innodb/t/innodb_wl6326.test deleted file mode 100644 index 1cf98cd1c7b..00000000000 --- a/mysql-test/suite/innodb/t/innodb_wl6326.test +++ /dev/null @@ -1,519 +0,0 @@ -# -# WL#6326: InnoDB: fix index->lock contention -# - ---source include/have_innodb.inc ---source include/have_debug.inc ---source include/have_debug_sync.inc ---source include/have_innodb_16k.inc - ---disable_query_log -SET @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug; -SET @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index; -SET @old_innodb_stats_persistent = @@innodb_stats_persistent; ---enable_query_log - -# Save the initial number of concurrent sessions ---source include/count_sessions.inc - -SET GLOBAL innodb_adaptive_hash_index = false; -SET GLOBAL innodb_stats_persistent = false; - ---connect (con1,localhost,root,,) ---connect (con2,localhost,root,,) ---connect (con3,localhost,root,,) - -CREATE TABLE t1 ( - a00 CHAR(255) NOT NULL DEFAULT 'a', - a01 CHAR(255) NOT NULL DEFAULT 'a', - a02 CHAR(255) NOT NULL DEFAULT 'a', - a03 CHAR(255) NOT NULL DEFAULT 'a', - a04 CHAR(255) NOT NULL DEFAULT 'a', - a05 CHAR(255) NOT NULL DEFAULT 'a', - a06 CHAR(255) NOT NULL DEFAULT 'a', - b INT NOT NULL DEFAULT 0 -) ENGINE = InnoDB; - -ALTER TABLE t1 ADD PRIMARY KEY( - a00, - a01, - a02, - a03, - a04, - a05, - a06 -); - -# -# Prepare primary key index tree to be used for this test. -# - -# Only root (1) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -# Make the first records sparse artificially, -# not to cause modify_tree by single node_ptr insert operation. -# * (7 - 2) records should be larger than a half of the page size -# * (7 + 2) records should be fit to the page -# (above t1 definition is already adjusted) -SET GLOBAL innodb_limit_optimistic_insert_debug = 7; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('aa'); -INSERT INTO t1 (a00) VALUES ('ab'); -INSERT INTO t1 (a00) VALUES ('ac'); -INSERT INTO t1 (a00) VALUES ('ad'); -INSERT INTO t1 (a00) VALUES ('ae'); -INSERT INTO t1 (a00) VALUES ('af'); -INSERT INTO t1 (a00) VALUES ('ag'); -INSERT INTO t1 (a00) VALUES ('ah'); -COMMIT; -# Raise root (1-2) -# (aa,ad) -# (aa,ab,ac)(ad,ae,af,ag,ah) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('ai'); -INSERT INTO t1 (a00) VALUES ('aj'); -INSERT INTO t1 (a00) VALUES ('ak'); -COMMIT; -# Split leaf (1-3) -# (aa,ad,ak) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('al'); -INSERT INTO t1 (a00) VALUES ('am'); -INSERT INTO t1 (a00) VALUES ('an'); -INSERT INTO t1 (a00) VALUES ('ao'); -INSERT INTO t1 (a00) VALUES ('ap'); -INSERT INTO t1 (a00) VALUES ('aq'); -INSERT INTO t1 (a00) VALUES ('ar'); -COMMIT; -# Split leaf (1-4) -# (aa,ad,ak,ar) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('as'); -INSERT INTO t1 (a00) VALUES ('at'); -INSERT INTO t1 (a00) VALUES ('au'); -INSERT INTO t1 (a00) VALUES ('av'); -INSERT INTO t1 (a00) VALUES ('aw'); -INSERT INTO t1 (a00) VALUES ('ax'); -INSERT INTO t1 (a00) VALUES ('ay'); -COMMIT; -# Split leaf (1-5) -# (aa,ad,ak,ar,ay) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar,as,at,au,av,aw,ax)(ay) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('az'); -INSERT INTO t1 (a00) VALUES ('ba'); -INSERT INTO t1 (a00) VALUES ('bb'); -INSERT INTO t1 (a00) VALUES ('bc'); -INSERT INTO t1 (a00) VALUES ('bd'); -INSERT INTO t1 (a00) VALUES ('be'); -INSERT INTO t1 (a00) VALUES ('bf'); -COMMIT; -# Split leaf (1-6) -# (aa,ad,ak,ar,ay,bf) -# (aa,ab,ac)(ad..)(ak..)(ar,as,at,au,av,aw,ax)(ay,az,ba,bb,bc,bd,be)(bf) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bg'); -INSERT INTO t1 (a00) VALUES ('bh'); -INSERT INTO t1 (a00) VALUES ('bi'); -INSERT INTO t1 (a00) VALUES ('bj'); -INSERT INTO t1 (a00) VALUES ('bk'); -INSERT INTO t1 (a00) VALUES ('bl'); -INSERT INTO t1 (a00) VALUES ('bm'); -COMMIT; -# Split leaf (1-7) -# (aa,ad,ak,ar,ay,bf,bm) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay,az,ba,bb,bc,bd,be)(bf,bg,bh,bi,bj,bk,bl)(bm) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bn'); -INSERT INTO t1 (a00) VALUES ('bo'); -INSERT INTO t1 (a00) VALUES ('bp'); -INSERT INTO t1 (a00) VALUES ('bq'); -INSERT INTO t1 (a00) VALUES ('br'); -INSERT INTO t1 (a00) VALUES ('bs'); -INSERT INTO t1 (a00) VALUES ('bt'); -COMMIT; -# Raise root (1-2-8) -# (aa,ar) -# (aa,ad,ak) (ar,ay,bf,bm,bt) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bu'); -INSERT INTO t1 (a00) VALUES ('bv'); -INSERT INTO t1 (a00) VALUES ('bw'); -INSERT INTO t1 (a00) VALUES ('bx'); -INSERT INTO t1 (a00) VALUES ('by'); -INSERT INTO t1 (a00) VALUES ('bz'); -INSERT INTO t1 (a00) VALUES ('ca'); - -INSERT INTO t1 (a00) VALUES ('cb'); -INSERT INTO t1 (a00) VALUES ('cc'); -INSERT INTO t1 (a00) VALUES ('cd'); -INSERT INTO t1 (a00) VALUES ('ce'); -INSERT INTO t1 (a00) VALUES ('cf'); -INSERT INTO t1 (a00) VALUES ('cg'); -INSERT INTO t1 (a00) VALUES ('ch'); - -INSERT INTO t1 (a00) VALUES ('ci'); -INSERT INTO t1 (a00) VALUES ('cj'); -INSERT INTO t1 (a00) VALUES ('ck'); -INSERT INTO t1 (a00) VALUES ('cl'); -INSERT INTO t1 (a00) VALUES ('cm'); -INSERT INTO t1 (a00) VALUES ('cn'); -INSERT INTO t1 (a00) VALUES ('co'); -COMMIT; -# Split also at level 1 (1-3-11) -# (aa,ar,co) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('cp'); -INSERT INTO t1 (a00) VALUES ('cq'); -INSERT INTO t1 (a00) VALUES ('cr'); -INSERT INTO t1 (a00) VALUES ('cs'); -INSERT INTO t1 (a00) VALUES ('ct'); -INSERT INTO t1 (a00) VALUES ('cu'); -INSERT INTO t1 (a00) VALUES ('cv'); - -INSERT INTO t1 (a00) VALUES ('cw'); -INSERT INTO t1 (a00) VALUES ('cx'); -INSERT INTO t1 (a00) VALUES ('cy'); -INSERT INTO t1 (a00) VALUES ('cz'); -INSERT INTO t1 (a00) VALUES ('da'); -INSERT INTO t1 (a00) VALUES ('db'); -INSERT INTO t1 (a00) VALUES ('dc'); - -INSERT INTO t1 (a00) VALUES ('dd'); -INSERT INTO t1 (a00) VALUES ('de'); -INSERT INTO t1 (a00) VALUES ('df'); -INSERT INTO t1 (a00) VALUES ('dg'); -INSERT INTO t1 (a00) VALUES ('dh'); -INSERT INTO t1 (a00) VALUES ('di'); -INSERT INTO t1 (a00) VALUES ('dj'); - -INSERT INTO t1 (a00) VALUES ('dk'); -INSERT INTO t1 (a00) VALUES ('dl'); -INSERT INTO t1 (a00) VALUES ('dm'); -INSERT INTO t1 (a00) VALUES ('dn'); -INSERT INTO t1 (a00) VALUES ('do'); -INSERT INTO t1 (a00) VALUES ('dp'); -INSERT INTO t1 (a00) VALUES ('dq'); - -INSERT INTO t1 (a00) VALUES ('dr'); -INSERT INTO t1 (a00) VALUES ('ds'); -INSERT INTO t1 (a00) VALUES ('dt'); -INSERT INTO t1 (a00) VALUES ('du'); -INSERT INTO t1 (a00) VALUES ('dv'); -INSERT INTO t1 (a00) VALUES ('dw'); -INSERT INTO t1 (a00) VALUES ('dx'); - -INSERT INTO t1 (a00) VALUES ('dy'); -INSERT INTO t1 (a00) VALUES ('dz'); -INSERT INTO t1 (a00) VALUES ('ea'); -INSERT INTO t1 (a00) VALUES ('eb'); -INSERT INTO t1 (a00) VALUES ('ec'); -INSERT INTO t1 (a00) VALUES ('ed'); -INSERT INTO t1 (a00) VALUES ('ee'); - -INSERT INTO t1 (a00) VALUES ('ef'); -INSERT INTO t1 (a00) VALUES ('eg'); -INSERT INTO t1 (a00) VALUES ('eh'); -INSERT INTO t1 (a00) VALUES ('ei'); -INSERT INTO t1 (a00) VALUES ('ej'); -INSERT INTO t1 (a00) VALUES ('ek'); -INSERT INTO t1 (a00) VALUES ('el'); -COMMIT; -# Split also at level 1 (1-4-18) -# (aa,ar,co,el) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('em'); -INSERT INTO t1 (a00) VALUES ('en'); -INSERT INTO t1 (a00) VALUES ('eo'); -INSERT INTO t1 (a00) VALUES ('ep'); -INSERT INTO t1 (a00) VALUES ('eq'); -INSERT INTO t1 (a00) VALUES ('er'); -INSERT INTO t1 (a00) VALUES ('es'); - -INSERT INTO t1 (a00) VALUES ('et'); -INSERT INTO t1 (a00) VALUES ('eu'); -INSERT INTO t1 (a00) VALUES ('ev'); -INSERT INTO t1 (a00) VALUES ('ew'); -INSERT INTO t1 (a00) VALUES ('ex'); -INSERT INTO t1 (a00) VALUES ('ey'); -INSERT INTO t1 (a00) VALUES ('ez'); - -INSERT INTO t1 (a00) VALUES ('fa'); -INSERT INTO t1 (a00) VALUES ('fb'); -INSERT INTO t1 (a00) VALUES ('fc'); -INSERT INTO t1 (a00) VALUES ('fd'); -INSERT INTO t1 (a00) VALUES ('fe'); -INSERT INTO t1 (a00) VALUES ('ff'); -INSERT INTO t1 (a00) VALUES ('fg'); - -INSERT INTO t1 (a00) VALUES ('fh'); -INSERT INTO t1 (a00) VALUES ('fi'); -INSERT INTO t1 (a00) VALUES ('fj'); -INSERT INTO t1 (a00) VALUES ('fk'); -INSERT INTO t1 (a00) VALUES ('fl'); -INSERT INTO t1 (a00) VALUES ('fm'); -INSERT INTO t1 (a00) VALUES ('fn'); - -INSERT INTO t1 (a00) VALUES ('fo'); -INSERT INTO t1 (a00) VALUES ('fp'); -INSERT INTO t1 (a00) VALUES ('fq'); -INSERT INTO t1 (a00) VALUES ('fr'); -INSERT INTO t1 (a00) VALUES ('fs'); -INSERT INTO t1 (a00) VALUES ('ft'); -INSERT INTO t1 (a00) VALUES ('fu'); - -INSERT INTO t1 (a00) VALUES ('fv'); -INSERT INTO t1 (a00) VALUES ('fw'); -INSERT INTO t1 (a00) VALUES ('fx'); -INSERT INTO t1 (a00) VALUES ('fy'); -INSERT INTO t1 (a00) VALUES ('fz'); -INSERT INTO t1 (a00) VALUES ('ga'); -INSERT INTO t1 (a00) VALUES ('gb'); - -INSERT INTO t1 (a00) VALUES ('gc'); -INSERT INTO t1 (a00) VALUES ('gd'); -INSERT INTO t1 (a00) VALUES ('ge'); -INSERT INTO t1 (a00) VALUES ('gf'); -INSERT INTO t1 (a00) VALUES ('gg'); -INSERT INTO t1 (a00) VALUES ('gh'); -COMMIT; - -# Current tree form (1-4-24) -# (aa,ar,co,el) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el..,gb) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el..)..(gb..) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -# Insert the rest of records normally -SET GLOBAL innodb_limit_optimistic_insert_debug = 0; - ---echo # Test start - -# (1) Insert records to leaf page (bf..) and cause modify_page. -# - root page is not X latched -# - latched from level 1 page (ar,ay,bf,bm,bt,ca,ch) - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (bf..) -INSERT INTO t1 (a00) VALUES ('bfa'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('bfb'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# Not blocked searches -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; -# "where a00 = 'co'" is blocked because searching from smaller ('co','a','a',..). -SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default - -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# (2) Insert records to leaf page (co..) and cause modify_page -# - root page is X latched, because node_ptr for 'co' -# is 1st record for (co,cv,dc,dj,dq,dx,ee) -# -# * ordinary pessimitic insert might be done by pessistic update -# and we should consider possibility node_ptr to be deleted. - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (co..) -INSERT INTO t1 (a00) VALUES ('coa'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('cob'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# All searches are blocked because root page is X latched - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default - -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# (3) Insert records to rightmost leaf page (gb..) and cause modify_page -# - root page is not X latched, because node_ptr for 'gb' is the last record -# of the level 1 though it is last record in the page. -# - lathed from level 1 page (el..,gb) - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (gb..) -INSERT INTO t1 (a00) VALUES ('gba'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('gbb'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# Not blocked searches -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# Cleanup -SET DEBUG_SYNC = 'RESET'; - ---connection default ---disconnect con1 ---disconnect con2 ---disconnect con3 - -DROP TABLE t1; - ---disable_query_log -SET GLOBAL innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug; -SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index; -SET GLOBAL innodb_stats_persistent = @old_innodb_stats_persistent; ---enable_query_log - -# Wait till all disconnects are completed. ---source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb_gis/r/rtree_split.result b/mysql-test/suite/innodb_gis/r/rtree_split.result index 8e475776ce0..10262f0220b 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_split.result +++ b/mysql-test/suite/innodb_gis/r/rtree_split.result @@ -61,3 +61,15 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) 57344 drop table t1; +# +# MDEV-30400 Assertion height == btr_page_get_level ... on INSERT +# +CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; +SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +BEGIN; +INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_366; +ROLLBACK; +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +DROP TABLE t1; +# End of 10.6 tests diff --git a/mysql-test/suite/innodb_gis/t/rtree_split.test b/mysql-test/suite/innodb_gis/t/rtree_split.test index 6f285187508..de7fc676e0e 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_split.test +++ b/mysql-test/suite/innodb_gis/t/rtree_split.test @@ -73,3 +73,18 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); # Clean up. drop table t1; + +--echo # +--echo # MDEV-30400 Assertion height == btr_page_get_level ... on INSERT +--echo # + +CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; +SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +BEGIN; +INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_366; +ROLLBACK; +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +DROP TABLE t1; + +--echo # End of 10.6 tests -- cgit v1.2.1 From 647a7232ff12361a271c33223f1e1bbee6da5675 Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Fri, 20 Jan 2023 16:07:43 +0530 Subject: MDEV-30438 innodb.undo_truncate,4k fails when innodb-immediate-scrub-data-uncompressed is enabled - InnoDB fails to clear the freed ranges during truncation of innodb undo log tablespace. During shutdown, InnoDB flushes the freed page ranges and throws the out of bound error. mtr_t::commit_shrink(): clear the freed ranges while doing undo tablespace truncation --- mysql-test/suite/innodb/t/undo_truncate.opt | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/t/undo_truncate.opt b/mysql-test/suite/innodb/t/undo_truncate.opt index f4d78725c6e..1459ec5db74 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.opt +++ b/mysql-test/suite/innodb/t/undo_truncate.opt @@ -1 +1,2 @@ --innodb-buffer-pool-size=24M +--innodb-immediate-scrub-data-uncompressed=ON -- cgit v1.2.1 From 1bbf37e0db459f6368b6a48e366e3f2558853f68 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 23 Jan 2023 13:05:52 +0200 Subject: MDEV-515: Improve test coverage Cover dict_index_t::clear() for TEMPORARY TABLE --- mysql-test/suite/innodb/r/insert_into_empty.result | 4 ++++ mysql-test/suite/innodb/t/insert_into_empty.test | 4 ++++ 2 files changed, 8 insertions(+) (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/insert_into_empty.result b/mysql-test/suite/innodb/r/insert_into_empty.result index eeff9428a49..e7f051d3e56 100644 --- a/mysql-test/suite/innodb/r/insert_into_empty.result +++ b/mysql-test/suite/innodb/r/insert_into_empty.result @@ -45,6 +45,10 @@ SET tx_read_only=1; BEGIN; INSERT INTO t2 VALUES(0); INSERT INTO t VALUES(0); +ROLLBACK; +BEGIN; +INSERT INTO t2 VALUES(0); +INSERT INTO t VALUES(0); COMMIT; INSERT INTO t VALUES(0); DROP TEMPORARY TABLE t,t2; diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test index 58b3cdce335..af8ed8be90d 100644 --- a/mysql-test/suite/innodb/t/insert_into_empty.test +++ b/mysql-test/suite/innodb/t/insert_into_empty.test @@ -51,6 +51,10 @@ SET tx_read_only=1; BEGIN; INSERT INTO t2 VALUES(0); INSERT INTO t VALUES(0); +ROLLBACK; +BEGIN; +INSERT INTO t2 VALUES(0); +INSERT INTO t VALUES(0); COMMIT; INSERT INTO t VALUES(0); -- cgit v1.2.1 From e41fb3697c9f10b3db2e35b6a5704a03b1041c7e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 23 Jan 2023 14:52:49 +0200 Subject: Revert "MDEV-30400 Assertion height == btr_page_get_level(...) on INSERT" This reverts commit f9cac8d2cbf82d4d616905fb3dfab34a9901179d which was accidentally pushed prematurely. --- mysql-test/suite/innodb/r/innodb_wl6326.result | 405 ++++++++++++++++++ mysql-test/suite/innodb/t/innodb_wl6326.opt | 1 + mysql-test/suite/innodb/t/innodb_wl6326.test | 519 +++++++++++++++++++++++ mysql-test/suite/innodb_gis/r/rtree_split.result | 12 - mysql-test/suite/innodb_gis/t/rtree_split.test | 15 - 5 files changed, 925 insertions(+), 27 deletions(-) create mode 100644 mysql-test/suite/innodb/r/innodb_wl6326.result create mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.opt create mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/innodb_wl6326.result b/mysql-test/suite/innodb/r/innodb_wl6326.result new file mode 100644 index 00000000000..fcd58aedafe --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_wl6326.result @@ -0,0 +1,405 @@ +SET GLOBAL innodb_adaptive_hash_index = false; +SET GLOBAL innodb_stats_persistent = false; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connect con3,localhost,root,,; +CREATE TABLE t1 ( +a00 CHAR(255) NOT NULL DEFAULT 'a', +a01 CHAR(255) NOT NULL DEFAULT 'a', +a02 CHAR(255) NOT NULL DEFAULT 'a', +a03 CHAR(255) NOT NULL DEFAULT 'a', +a04 CHAR(255) NOT NULL DEFAULT 'a', +a05 CHAR(255) NOT NULL DEFAULT 'a', +a06 CHAR(255) NOT NULL DEFAULT 'a', +b INT NOT NULL DEFAULT 0 +) ENGINE = InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY( +a00, +a01, +a02, +a03, +a04, +a05, +a06 +); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +1 +SET GLOBAL innodb_limit_optimistic_insert_debug = 7; +BEGIN; +INSERT INTO t1 (a00) VALUES ('aa'); +INSERT INTO t1 (a00) VALUES ('ab'); +INSERT INTO t1 (a00) VALUES ('ac'); +INSERT INTO t1 (a00) VALUES ('ad'); +INSERT INTO t1 (a00) VALUES ('ae'); +INSERT INTO t1 (a00) VALUES ('af'); +INSERT INTO t1 (a00) VALUES ('ag'); +INSERT INTO t1 (a00) VALUES ('ah'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +3 +BEGIN; +INSERT INTO t1 (a00) VALUES ('ai'); +INSERT INTO t1 (a00) VALUES ('aj'); +INSERT INTO t1 (a00) VALUES ('ak'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +4 +BEGIN; +INSERT INTO t1 (a00) VALUES ('al'); +INSERT INTO t1 (a00) VALUES ('am'); +INSERT INTO t1 (a00) VALUES ('an'); +INSERT INTO t1 (a00) VALUES ('ao'); +INSERT INTO t1 (a00) VALUES ('ap'); +INSERT INTO t1 (a00) VALUES ('aq'); +INSERT INTO t1 (a00) VALUES ('ar'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +5 +BEGIN; +INSERT INTO t1 (a00) VALUES ('as'); +INSERT INTO t1 (a00) VALUES ('at'); +INSERT INTO t1 (a00) VALUES ('au'); +INSERT INTO t1 (a00) VALUES ('av'); +INSERT INTO t1 (a00) VALUES ('aw'); +INSERT INTO t1 (a00) VALUES ('ax'); +INSERT INTO t1 (a00) VALUES ('ay'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +6 +BEGIN; +INSERT INTO t1 (a00) VALUES ('az'); +INSERT INTO t1 (a00) VALUES ('ba'); +INSERT INTO t1 (a00) VALUES ('bb'); +INSERT INTO t1 (a00) VALUES ('bc'); +INSERT INTO t1 (a00) VALUES ('bd'); +INSERT INTO t1 (a00) VALUES ('be'); +INSERT INTO t1 (a00) VALUES ('bf'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +7 +BEGIN; +INSERT INTO t1 (a00) VALUES ('bg'); +INSERT INTO t1 (a00) VALUES ('bh'); +INSERT INTO t1 (a00) VALUES ('bi'); +INSERT INTO t1 (a00) VALUES ('bj'); +INSERT INTO t1 (a00) VALUES ('bk'); +INSERT INTO t1 (a00) VALUES ('bl'); +INSERT INTO t1 (a00) VALUES ('bm'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +8 +BEGIN; +INSERT INTO t1 (a00) VALUES ('bn'); +INSERT INTO t1 (a00) VALUES ('bo'); +INSERT INTO t1 (a00) VALUES ('bp'); +INSERT INTO t1 (a00) VALUES ('bq'); +INSERT INTO t1 (a00) VALUES ('br'); +INSERT INTO t1 (a00) VALUES ('bs'); +INSERT INTO t1 (a00) VALUES ('bt'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +11 +BEGIN; +INSERT INTO t1 (a00) VALUES ('bu'); +INSERT INTO t1 (a00) VALUES ('bv'); +INSERT INTO t1 (a00) VALUES ('bw'); +INSERT INTO t1 (a00) VALUES ('bx'); +INSERT INTO t1 (a00) VALUES ('by'); +INSERT INTO t1 (a00) VALUES ('bz'); +INSERT INTO t1 (a00) VALUES ('ca'); +INSERT INTO t1 (a00) VALUES ('cb'); +INSERT INTO t1 (a00) VALUES ('cc'); +INSERT INTO t1 (a00) VALUES ('cd'); +INSERT INTO t1 (a00) VALUES ('ce'); +INSERT INTO t1 (a00) VALUES ('cf'); +INSERT INTO t1 (a00) VALUES ('cg'); +INSERT INTO t1 (a00) VALUES ('ch'); +INSERT INTO t1 (a00) VALUES ('ci'); +INSERT INTO t1 (a00) VALUES ('cj'); +INSERT INTO t1 (a00) VALUES ('ck'); +INSERT INTO t1 (a00) VALUES ('cl'); +INSERT INTO t1 (a00) VALUES ('cm'); +INSERT INTO t1 (a00) VALUES ('cn'); +INSERT INTO t1 (a00) VALUES ('co'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +15 +BEGIN; +INSERT INTO t1 (a00) VALUES ('cp'); +INSERT INTO t1 (a00) VALUES ('cq'); +INSERT INTO t1 (a00) VALUES ('cr'); +INSERT INTO t1 (a00) VALUES ('cs'); +INSERT INTO t1 (a00) VALUES ('ct'); +INSERT INTO t1 (a00) VALUES ('cu'); +INSERT INTO t1 (a00) VALUES ('cv'); +INSERT INTO t1 (a00) VALUES ('cw'); +INSERT INTO t1 (a00) VALUES ('cx'); +INSERT INTO t1 (a00) VALUES ('cy'); +INSERT INTO t1 (a00) VALUES ('cz'); +INSERT INTO t1 (a00) VALUES ('da'); +INSERT INTO t1 (a00) VALUES ('db'); +INSERT INTO t1 (a00) VALUES ('dc'); +INSERT INTO t1 (a00) VALUES ('dd'); +INSERT INTO t1 (a00) VALUES ('de'); +INSERT INTO t1 (a00) VALUES ('df'); +INSERT INTO t1 (a00) VALUES ('dg'); +INSERT INTO t1 (a00) VALUES ('dh'); +INSERT INTO t1 (a00) VALUES ('di'); +INSERT INTO t1 (a00) VALUES ('dj'); +INSERT INTO t1 (a00) VALUES ('dk'); +INSERT INTO t1 (a00) VALUES ('dl'); +INSERT INTO t1 (a00) VALUES ('dm'); +INSERT INTO t1 (a00) VALUES ('dn'); +INSERT INTO t1 (a00) VALUES ('do'); +INSERT INTO t1 (a00) VALUES ('dp'); +INSERT INTO t1 (a00) VALUES ('dq'); +INSERT INTO t1 (a00) VALUES ('dr'); +INSERT INTO t1 (a00) VALUES ('ds'); +INSERT INTO t1 (a00) VALUES ('dt'); +INSERT INTO t1 (a00) VALUES ('du'); +INSERT INTO t1 (a00) VALUES ('dv'); +INSERT INTO t1 (a00) VALUES ('dw'); +INSERT INTO t1 (a00) VALUES ('dx'); +INSERT INTO t1 (a00) VALUES ('dy'); +INSERT INTO t1 (a00) VALUES ('dz'); +INSERT INTO t1 (a00) VALUES ('ea'); +INSERT INTO t1 (a00) VALUES ('eb'); +INSERT INTO t1 (a00) VALUES ('ec'); +INSERT INTO t1 (a00) VALUES ('ed'); +INSERT INTO t1 (a00) VALUES ('ee'); +INSERT INTO t1 (a00) VALUES ('ef'); +INSERT INTO t1 (a00) VALUES ('eg'); +INSERT INTO t1 (a00) VALUES ('eh'); +INSERT INTO t1 (a00) VALUES ('ei'); +INSERT INTO t1 (a00) VALUES ('ej'); +INSERT INTO t1 (a00) VALUES ('ek'); +INSERT INTO t1 (a00) VALUES ('el'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +23 +BEGIN; +INSERT INTO t1 (a00) VALUES ('em'); +INSERT INTO t1 (a00) VALUES ('en'); +INSERT INTO t1 (a00) VALUES ('eo'); +INSERT INTO t1 (a00) VALUES ('ep'); +INSERT INTO t1 (a00) VALUES ('eq'); +INSERT INTO t1 (a00) VALUES ('er'); +INSERT INTO t1 (a00) VALUES ('es'); +INSERT INTO t1 (a00) VALUES ('et'); +INSERT INTO t1 (a00) VALUES ('eu'); +INSERT INTO t1 (a00) VALUES ('ev'); +INSERT INTO t1 (a00) VALUES ('ew'); +INSERT INTO t1 (a00) VALUES ('ex'); +INSERT INTO t1 (a00) VALUES ('ey'); +INSERT INTO t1 (a00) VALUES ('ez'); +INSERT INTO t1 (a00) VALUES ('fa'); +INSERT INTO t1 (a00) VALUES ('fb'); +INSERT INTO t1 (a00) VALUES ('fc'); +INSERT INTO t1 (a00) VALUES ('fd'); +INSERT INTO t1 (a00) VALUES ('fe'); +INSERT INTO t1 (a00) VALUES ('ff'); +INSERT INTO t1 (a00) VALUES ('fg'); +INSERT INTO t1 (a00) VALUES ('fh'); +INSERT INTO t1 (a00) VALUES ('fi'); +INSERT INTO t1 (a00) VALUES ('fj'); +INSERT INTO t1 (a00) VALUES ('fk'); +INSERT INTO t1 (a00) VALUES ('fl'); +INSERT INTO t1 (a00) VALUES ('fm'); +INSERT INTO t1 (a00) VALUES ('fn'); +INSERT INTO t1 (a00) VALUES ('fo'); +INSERT INTO t1 (a00) VALUES ('fp'); +INSERT INTO t1 (a00) VALUES ('fq'); +INSERT INTO t1 (a00) VALUES ('fr'); +INSERT INTO t1 (a00) VALUES ('fs'); +INSERT INTO t1 (a00) VALUES ('ft'); +INSERT INTO t1 (a00) VALUES ('fu'); +INSERT INTO t1 (a00) VALUES ('fv'); +INSERT INTO t1 (a00) VALUES ('fw'); +INSERT INTO t1 (a00) VALUES ('fx'); +INSERT INTO t1 (a00) VALUES ('fy'); +INSERT INTO t1 (a00) VALUES ('fz'); +INSERT INTO t1 (a00) VALUES ('ga'); +INSERT INTO t1 (a00) VALUES ('gb'); +INSERT INTO t1 (a00) VALUES ('gc'); +INSERT INTO t1 (a00) VALUES ('gd'); +INSERT INTO t1 (a00) VALUES ('ge'); +INSERT INTO t1 (a00) VALUES ('gf'); +INSERT INTO t1 (a00) VALUES ('gg'); +INSERT INTO t1 (a00) VALUES ('gh'); +COMMIT; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +29 +SET GLOBAL innodb_limit_optimistic_insert_debug = 0; +# Test start +SET DEBUG_SYNC = 'RESET'; +INSERT INTO t1 (a00) VALUES ('bfa'); +connection con1; +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +INSERT INTO t1 (a00) VALUES ('bfb'); +connection con2; +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; +a00 a01 +aa a +SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; +a00 a01 +aq a +SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; +a00 a01 +cp a +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; +a00 a01 +el a +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; +connection con3; +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +SET DEBUG_SYNC = 'now SIGNAL continue'; +connection con1; +connection con2; +a00 a01 +ar a +connection con3; +a00 a01 +cn a +connection default; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +30 +SET DEBUG_SYNC = 'RESET'; +INSERT INTO t1 (a00) VALUES ('coa'); +connection con1; +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +INSERT INTO t1 (a00) VALUES ('cob'); +connection con2; +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; +connection con3; +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +SET DEBUG_SYNC = 'now SIGNAL continue'; +connection con1; +connection con2; +a00 a01 +aa a +connection con3; +a00 a01 +el a +connection default; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +31 +SET DEBUG_SYNC = 'RESET'; +INSERT INTO t1 (a00) VALUES ('gba'); +connection con1; +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +INSERT INTO t1 (a00) VALUES ('gbb'); +connection con2; +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; +a00 a01 +aa a +SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; +a00 a01 +ek a +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; +connection con3; +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +SET DEBUG_SYNC = 'now SIGNAL continue'; +connection con1; +connection con2; +a00 a01 +el a +connection con3; +a00 a01 +gb a +connection default; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; +CLUST_INDEX_SIZE +32 +SET DEBUG_SYNC = 'RESET'; +connection default; +disconnect con1; +disconnect con2; +disconnect con3; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.opt b/mysql-test/suite/innodb/t/innodb_wl6326.opt new file mode 100644 index 00000000000..99bf0e5a28b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_wl6326.opt @@ -0,0 +1 @@ +--innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.test b/mysql-test/suite/innodb/t/innodb_wl6326.test new file mode 100644 index 00000000000..1cf98cd1c7b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_wl6326.test @@ -0,0 +1,519 @@ +# +# WL#6326: InnoDB: fix index->lock contention +# + +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_innodb_16k.inc + +--disable_query_log +SET @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug; +SET @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index; +SET @old_innodb_stats_persistent = @@innodb_stats_persistent; +--enable_query_log + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +SET GLOBAL innodb_adaptive_hash_index = false; +SET GLOBAL innodb_stats_persistent = false; + +--connect (con1,localhost,root,,) +--connect (con2,localhost,root,,) +--connect (con3,localhost,root,,) + +CREATE TABLE t1 ( + a00 CHAR(255) NOT NULL DEFAULT 'a', + a01 CHAR(255) NOT NULL DEFAULT 'a', + a02 CHAR(255) NOT NULL DEFAULT 'a', + a03 CHAR(255) NOT NULL DEFAULT 'a', + a04 CHAR(255) NOT NULL DEFAULT 'a', + a05 CHAR(255) NOT NULL DEFAULT 'a', + a06 CHAR(255) NOT NULL DEFAULT 'a', + b INT NOT NULL DEFAULT 0 +) ENGINE = InnoDB; + +ALTER TABLE t1 ADD PRIMARY KEY( + a00, + a01, + a02, + a03, + a04, + a05, + a06 +); + +# +# Prepare primary key index tree to be used for this test. +# + +# Only root (1) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +# Make the first records sparse artificially, +# not to cause modify_tree by single node_ptr insert operation. +# * (7 - 2) records should be larger than a half of the page size +# * (7 + 2) records should be fit to the page +# (above t1 definition is already adjusted) +SET GLOBAL innodb_limit_optimistic_insert_debug = 7; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('aa'); +INSERT INTO t1 (a00) VALUES ('ab'); +INSERT INTO t1 (a00) VALUES ('ac'); +INSERT INTO t1 (a00) VALUES ('ad'); +INSERT INTO t1 (a00) VALUES ('ae'); +INSERT INTO t1 (a00) VALUES ('af'); +INSERT INTO t1 (a00) VALUES ('ag'); +INSERT INTO t1 (a00) VALUES ('ah'); +COMMIT; +# Raise root (1-2) +# (aa,ad) +# (aa,ab,ac)(ad,ae,af,ag,ah) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('ai'); +INSERT INTO t1 (a00) VALUES ('aj'); +INSERT INTO t1 (a00) VALUES ('ak'); +COMMIT; +# Split leaf (1-3) +# (aa,ad,ak) +# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('al'); +INSERT INTO t1 (a00) VALUES ('am'); +INSERT INTO t1 (a00) VALUES ('an'); +INSERT INTO t1 (a00) VALUES ('ao'); +INSERT INTO t1 (a00) VALUES ('ap'); +INSERT INTO t1 (a00) VALUES ('aq'); +INSERT INTO t1 (a00) VALUES ('ar'); +COMMIT; +# Split leaf (1-4) +# (aa,ad,ak,ar) +# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('as'); +INSERT INTO t1 (a00) VALUES ('at'); +INSERT INTO t1 (a00) VALUES ('au'); +INSERT INTO t1 (a00) VALUES ('av'); +INSERT INTO t1 (a00) VALUES ('aw'); +INSERT INTO t1 (a00) VALUES ('ax'); +INSERT INTO t1 (a00) VALUES ('ay'); +COMMIT; +# Split leaf (1-5) +# (aa,ad,ak,ar,ay) +# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar,as,at,au,av,aw,ax)(ay) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('az'); +INSERT INTO t1 (a00) VALUES ('ba'); +INSERT INTO t1 (a00) VALUES ('bb'); +INSERT INTO t1 (a00) VALUES ('bc'); +INSERT INTO t1 (a00) VALUES ('bd'); +INSERT INTO t1 (a00) VALUES ('be'); +INSERT INTO t1 (a00) VALUES ('bf'); +COMMIT; +# Split leaf (1-6) +# (aa,ad,ak,ar,ay,bf) +# (aa,ab,ac)(ad..)(ak..)(ar,as,at,au,av,aw,ax)(ay,az,ba,bb,bc,bd,be)(bf) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('bg'); +INSERT INTO t1 (a00) VALUES ('bh'); +INSERT INTO t1 (a00) VALUES ('bi'); +INSERT INTO t1 (a00) VALUES ('bj'); +INSERT INTO t1 (a00) VALUES ('bk'); +INSERT INTO t1 (a00) VALUES ('bl'); +INSERT INTO t1 (a00) VALUES ('bm'); +COMMIT; +# Split leaf (1-7) +# (aa,ad,ak,ar,ay,bf,bm) +# (aa,ab,ac)(ad..)(ak..)(ar..)(ay,az,ba,bb,bc,bd,be)(bf,bg,bh,bi,bj,bk,bl)(bm) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('bn'); +INSERT INTO t1 (a00) VALUES ('bo'); +INSERT INTO t1 (a00) VALUES ('bp'); +INSERT INTO t1 (a00) VALUES ('bq'); +INSERT INTO t1 (a00) VALUES ('br'); +INSERT INTO t1 (a00) VALUES ('bs'); +INSERT INTO t1 (a00) VALUES ('bt'); +COMMIT; +# Raise root (1-2-8) +# (aa,ar) +# (aa,ad,ak) (ar,ay,bf,bm,bt) +# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('bu'); +INSERT INTO t1 (a00) VALUES ('bv'); +INSERT INTO t1 (a00) VALUES ('bw'); +INSERT INTO t1 (a00) VALUES ('bx'); +INSERT INTO t1 (a00) VALUES ('by'); +INSERT INTO t1 (a00) VALUES ('bz'); +INSERT INTO t1 (a00) VALUES ('ca'); + +INSERT INTO t1 (a00) VALUES ('cb'); +INSERT INTO t1 (a00) VALUES ('cc'); +INSERT INTO t1 (a00) VALUES ('cd'); +INSERT INTO t1 (a00) VALUES ('ce'); +INSERT INTO t1 (a00) VALUES ('cf'); +INSERT INTO t1 (a00) VALUES ('cg'); +INSERT INTO t1 (a00) VALUES ('ch'); + +INSERT INTO t1 (a00) VALUES ('ci'); +INSERT INTO t1 (a00) VALUES ('cj'); +INSERT INTO t1 (a00) VALUES ('ck'); +INSERT INTO t1 (a00) VALUES ('cl'); +INSERT INTO t1 (a00) VALUES ('cm'); +INSERT INTO t1 (a00) VALUES ('cn'); +INSERT INTO t1 (a00) VALUES ('co'); +COMMIT; +# Split also at level 1 (1-3-11) +# (aa,ar,co) +# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co) +# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('cp'); +INSERT INTO t1 (a00) VALUES ('cq'); +INSERT INTO t1 (a00) VALUES ('cr'); +INSERT INTO t1 (a00) VALUES ('cs'); +INSERT INTO t1 (a00) VALUES ('ct'); +INSERT INTO t1 (a00) VALUES ('cu'); +INSERT INTO t1 (a00) VALUES ('cv'); + +INSERT INTO t1 (a00) VALUES ('cw'); +INSERT INTO t1 (a00) VALUES ('cx'); +INSERT INTO t1 (a00) VALUES ('cy'); +INSERT INTO t1 (a00) VALUES ('cz'); +INSERT INTO t1 (a00) VALUES ('da'); +INSERT INTO t1 (a00) VALUES ('db'); +INSERT INTO t1 (a00) VALUES ('dc'); + +INSERT INTO t1 (a00) VALUES ('dd'); +INSERT INTO t1 (a00) VALUES ('de'); +INSERT INTO t1 (a00) VALUES ('df'); +INSERT INTO t1 (a00) VALUES ('dg'); +INSERT INTO t1 (a00) VALUES ('dh'); +INSERT INTO t1 (a00) VALUES ('di'); +INSERT INTO t1 (a00) VALUES ('dj'); + +INSERT INTO t1 (a00) VALUES ('dk'); +INSERT INTO t1 (a00) VALUES ('dl'); +INSERT INTO t1 (a00) VALUES ('dm'); +INSERT INTO t1 (a00) VALUES ('dn'); +INSERT INTO t1 (a00) VALUES ('do'); +INSERT INTO t1 (a00) VALUES ('dp'); +INSERT INTO t1 (a00) VALUES ('dq'); + +INSERT INTO t1 (a00) VALUES ('dr'); +INSERT INTO t1 (a00) VALUES ('ds'); +INSERT INTO t1 (a00) VALUES ('dt'); +INSERT INTO t1 (a00) VALUES ('du'); +INSERT INTO t1 (a00) VALUES ('dv'); +INSERT INTO t1 (a00) VALUES ('dw'); +INSERT INTO t1 (a00) VALUES ('dx'); + +INSERT INTO t1 (a00) VALUES ('dy'); +INSERT INTO t1 (a00) VALUES ('dz'); +INSERT INTO t1 (a00) VALUES ('ea'); +INSERT INTO t1 (a00) VALUES ('eb'); +INSERT INTO t1 (a00) VALUES ('ec'); +INSERT INTO t1 (a00) VALUES ('ed'); +INSERT INTO t1 (a00) VALUES ('ee'); + +INSERT INTO t1 (a00) VALUES ('ef'); +INSERT INTO t1 (a00) VALUES ('eg'); +INSERT INTO t1 (a00) VALUES ('eh'); +INSERT INTO t1 (a00) VALUES ('ei'); +INSERT INTO t1 (a00) VALUES ('ej'); +INSERT INTO t1 (a00) VALUES ('ek'); +INSERT INTO t1 (a00) VALUES ('el'); +COMMIT; +# Split also at level 1 (1-4-18) +# (aa,ar,co,el) +# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el) +# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +BEGIN; +INSERT INTO t1 (a00) VALUES ('em'); +INSERT INTO t1 (a00) VALUES ('en'); +INSERT INTO t1 (a00) VALUES ('eo'); +INSERT INTO t1 (a00) VALUES ('ep'); +INSERT INTO t1 (a00) VALUES ('eq'); +INSERT INTO t1 (a00) VALUES ('er'); +INSERT INTO t1 (a00) VALUES ('es'); + +INSERT INTO t1 (a00) VALUES ('et'); +INSERT INTO t1 (a00) VALUES ('eu'); +INSERT INTO t1 (a00) VALUES ('ev'); +INSERT INTO t1 (a00) VALUES ('ew'); +INSERT INTO t1 (a00) VALUES ('ex'); +INSERT INTO t1 (a00) VALUES ('ey'); +INSERT INTO t1 (a00) VALUES ('ez'); + +INSERT INTO t1 (a00) VALUES ('fa'); +INSERT INTO t1 (a00) VALUES ('fb'); +INSERT INTO t1 (a00) VALUES ('fc'); +INSERT INTO t1 (a00) VALUES ('fd'); +INSERT INTO t1 (a00) VALUES ('fe'); +INSERT INTO t1 (a00) VALUES ('ff'); +INSERT INTO t1 (a00) VALUES ('fg'); + +INSERT INTO t1 (a00) VALUES ('fh'); +INSERT INTO t1 (a00) VALUES ('fi'); +INSERT INTO t1 (a00) VALUES ('fj'); +INSERT INTO t1 (a00) VALUES ('fk'); +INSERT INTO t1 (a00) VALUES ('fl'); +INSERT INTO t1 (a00) VALUES ('fm'); +INSERT INTO t1 (a00) VALUES ('fn'); + +INSERT INTO t1 (a00) VALUES ('fo'); +INSERT INTO t1 (a00) VALUES ('fp'); +INSERT INTO t1 (a00) VALUES ('fq'); +INSERT INTO t1 (a00) VALUES ('fr'); +INSERT INTO t1 (a00) VALUES ('fs'); +INSERT INTO t1 (a00) VALUES ('ft'); +INSERT INTO t1 (a00) VALUES ('fu'); + +INSERT INTO t1 (a00) VALUES ('fv'); +INSERT INTO t1 (a00) VALUES ('fw'); +INSERT INTO t1 (a00) VALUES ('fx'); +INSERT INTO t1 (a00) VALUES ('fy'); +INSERT INTO t1 (a00) VALUES ('fz'); +INSERT INTO t1 (a00) VALUES ('ga'); +INSERT INTO t1 (a00) VALUES ('gb'); + +INSERT INTO t1 (a00) VALUES ('gc'); +INSERT INTO t1 (a00) VALUES ('gd'); +INSERT INTO t1 (a00) VALUES ('ge'); +INSERT INTO t1 (a00) VALUES ('gf'); +INSERT INTO t1 (a00) VALUES ('gg'); +INSERT INTO t1 (a00) VALUES ('gh'); +COMMIT; + +# Current tree form (1-4-24) +# (aa,ar,co,el) +# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el..,gb) +# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el..)..(gb..) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +# Insert the rest of records normally +SET GLOBAL innodb_limit_optimistic_insert_debug = 0; + +--echo # Test start + +# (1) Insert records to leaf page (bf..) and cause modify_page. +# - root page is not X latched +# - latched from level 1 page (ar,ay,bf,bm,bt,ca,ch) + +SET DEBUG_SYNC = 'RESET'; + +# Filling leaf page (bf..) +INSERT INTO t1 (a00) VALUES ('bfa'); + +--connection con1 +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +# Cause modify_tree +--send +INSERT INTO t1 (a00) VALUES ('bfb'); + +--connection con2 +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +# Not blocked searches +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; +SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; +# "where a00 = 'co'" is blocked because searching from smaller ('co','a','a',..). +SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; + +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; + +--connection con3 +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; + +--connection default +# FIXME: These occasionally time out! +--disable_warnings +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +--enable_warnings +SET DEBUG_SYNC = 'now SIGNAL continue'; + +--connection con1 +--reap + +--connection con2 +--reap + +--connection con3 +--reap + +--connection default + +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + + + +# (2) Insert records to leaf page (co..) and cause modify_page +# - root page is X latched, because node_ptr for 'co' +# is 1st record for (co,cv,dc,dj,dq,dx,ee) +# +# * ordinary pessimitic insert might be done by pessistic update +# and we should consider possibility node_ptr to be deleted. + +SET DEBUG_SYNC = 'RESET'; + +# Filling leaf page (co..) +INSERT INTO t1 (a00) VALUES ('coa'); + +--connection con1 +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +# Cause modify_tree +--send +INSERT INTO t1 (a00) VALUES ('cob'); + +--connection con2 +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +# All searches are blocked because root page is X latched + +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; + +--connection con3 +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; + +--connection default +# FIXME: These occasionally time out! +--disable_warnings +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +--enable_warnings +SET DEBUG_SYNC = 'now SIGNAL continue'; + +--connection con1 +--reap + +--connection con2 +--reap + +--connection con3 +--reap + +--connection default + +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + + + +# (3) Insert records to rightmost leaf page (gb..) and cause modify_page +# - root page is not X latched, because node_ptr for 'gb' is the last record +# of the level 1 though it is last record in the page. +# - lathed from level 1 page (el..,gb) + +SET DEBUG_SYNC = 'RESET'; + +# Filling leaf page (gb..) +INSERT INTO t1 (a00) VALUES ('gba'); + +--connection con1 +SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; +# Cause modify_tree +--send +INSERT INTO t1 (a00) VALUES ('gbb'); + +--connection con2 +SET DEBUG_SYNC = 'now WAIT_FOR reached'; +# Not blocked searches +SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; +SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; + +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'el'; + +--connection con3 +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +# Blocked +--send +SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; + +--connection default +# FIXME: These occasionally time out! +--disable_warnings +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; +--enable_warnings +SET DEBUG_SYNC = 'now SIGNAL continue'; + +--connection con1 +--reap + +--connection con2 +--reap + +--connection con3 +--reap + +--connection default +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + + + +# Cleanup +SET DEBUG_SYNC = 'RESET'; + +--connection default +--disconnect con1 +--disconnect con2 +--disconnect con3 + +DROP TABLE t1; + +--disable_query_log +SET GLOBAL innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index; +SET GLOBAL innodb_stats_persistent = @old_innodb_stats_persistent; +--enable_query_log + +# Wait till all disconnects are completed. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb_gis/r/rtree_split.result b/mysql-test/suite/innodb_gis/r/rtree_split.result index 10262f0220b..8e475776ce0 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_split.result +++ b/mysql-test/suite/innodb_gis/r/rtree_split.result @@ -61,15 +61,3 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) 57344 drop table t1; -# -# MDEV-30400 Assertion height == btr_page_get_level ... on INSERT -# -CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; -SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; -SET GLOBAL innodb_limit_optimistic_insert_debug=2; -BEGIN; -INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_366; -ROLLBACK; -SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; -DROP TABLE t1; -# End of 10.6 tests diff --git a/mysql-test/suite/innodb_gis/t/rtree_split.test b/mysql-test/suite/innodb_gis/t/rtree_split.test index de7fc676e0e..6f285187508 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_split.test +++ b/mysql-test/suite/innodb_gis/t/rtree_split.test @@ -73,18 +73,3 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); # Clean up. drop table t1; - ---echo # ---echo # MDEV-30400 Assertion height == btr_page_get_level ... on INSERT ---echo # - -CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; -SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; -SET GLOBAL innodb_limit_optimistic_insert_debug=2; -BEGIN; -INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_366; -ROLLBACK; -SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; -DROP TABLE t1; - ---echo # End of 10.6 tests -- cgit v1.2.1 From 39f46745995939e17678d3c2f030f625d5bc41c2 Mon Sep 17 00:00:00 2001 From: Denis Protivensky Date: Mon, 5 Dec 2022 17:03:32 +0300 Subject: MDEV-24623 Replicate bulk insert as table-level exclusive key MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit - introduce table key construction function in wsrep service interface - don't add row keys when replicating bulk insert - don't start bulk insert on applier or when transaction is not active - don't start bulk insert on system versioned tables - implement actual bulk insert table-level key replication Reviewed-by: Jan Lindström --- .../suite/galera/r/galera_insert_bulk.result | 30 ++++++++ mysql-test/suite/galera/t/galera_insert_bulk.test | 88 ++++++++++++++++++++++ 2 files changed, 118 insertions(+) create mode 100644 mysql-test/suite/galera/r/galera_insert_bulk.result create mode 100644 mysql-test/suite/galera/t/galera_insert_bulk.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/galera/r/galera_insert_bulk.result b/mysql-test/suite/galera/r/galera_insert_bulk.result new file mode 100644 index 00000000000..f4d4adf64e1 --- /dev/null +++ b/mysql-test/suite/galera/r/galera_insert_bulk.result @@ -0,0 +1,30 @@ +connection node_2; +connection node_1; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; +SET foreign_key_checks = 0; +SET unique_checks = 0; +START TRANSACTION; +connection node_2; +SET foreign_key_checks = 1; +SET unique_checks = 1; +INSERT INTO t1 VALUES (1001); +connection node_1; +COMMIT; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t1; +connection node_1; +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; +START TRANSACTION; +connection node_2; +SET foreign_key_checks = 1; +SET unique_checks = 1; +START TRANSACTION; +INSERT INTO t1 VALUES (1001); +connection node_1; +COMMIT; +2 +connection node_2; +COMMIT; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_insert_bulk.test b/mysql-test/suite/galera/t/galera_insert_bulk.test new file mode 100644 index 00000000000..f58870d5f74 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_insert_bulk.test @@ -0,0 +1,88 @@ +# +# Test that bulk insert replicates as table-level exclusive key and +# rolls back properly if needed. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc + +# +# Make bulk insert BF-abort, but regular insert succeed. +# + +--connection node_1 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; + +# Disable foreign and unique key checks to allow bulk insert. +SET foreign_key_checks = 0; +SET unique_checks = 0; + +START TRANSACTION; + +--let $count=0 +--disable_query_log +while ($count < 1000) +{ + --eval INSERT INTO t1 VALUES ($count) + --inc $count +} +--enable_query_log + +--connection node_2 + +# Disable bulk insert. +SET foreign_key_checks = 1; +SET unique_checks = 1; + +# Insert a value out of the bulk insert range. +INSERT INTO t1 VALUES (1001); + +--connection node_1 +--error ER_LOCK_DEADLOCK +COMMIT; + +DROP TABLE t1; + +# +# Make bulk insert succeed, but regular insert BF-abort. +# + +--connection node_1 +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB; + +--let $before_bulk_keys = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_repl_keys'` + +START TRANSACTION; + +--let $count=0 +--disable_query_log +while ($count < 1000) +{ + --eval INSERT INTO t1 VALUES ($count) + --inc $count +} +--enable_query_log + +--connection node_2 + +# Disable bulk insert. +SET foreign_key_checks = 1; +SET unique_checks = 1; + +START TRANSACTION; + +# Insert a value out of the bulk insert range. +INSERT INTO t1 VALUES (1001); + +--connection node_1 +COMMIT; + +# Expect two keys to be added for bulk insert: DB-level shared key and table-level exclusive key. +--let $bulk_keys_count = `SELECT VARIABLE_VALUE - $before_bulk_keys FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_repl_keys'` +--echo $bulk_keys_count + +--connection node_2 +--error ER_LOCK_DEADLOCK +COMMIT; + +DROP TABLE t1; -- cgit v1.2.1 From de4030e4d49805a7ded5c0bfee01cc3fd7623522 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 24 Jan 2023 14:09:21 +0200 Subject: MDEV-30400 Assertion height == btr_page_get_level(...) on INSERT This also fixes part of MDEV-29835 Partial server freeze which is caused by violations of the latching order that was defined in https://dev.mysql.com/worklog/task/?id=6326 (WL#6326: InnoDB: fix index->lock contention). Unless the current thread is holding an exclusive dict_index_t::lock, it must acquire page latches in a strict parent-to-child, left-to-right order. Not all cases of MDEV-29835 are fixed yet. Failure to follow the correct latching order will cause deadlocks of threads due to lock order inversion. As part of these changes, the BTR_MODIFY_TREE mode is modified so that an Update latch (U a.k.a. SX) will be acquired on the root page, and eXclusive latches (X) will be acquired on all pages leading to the leaf page, as well as any left and right siblings of the pages along the path. The DEBUG_SYNC test innodb.innodb_wl6326 will be removed, because at the time the DEBUG_SYNC point is hit, the thread is actually holding several page latches that will be blocking a concurrent SELECT statement. We also remove double bookkeeping that was caused due to excessive information hiding in mtr_t::m_memo. We simply let mtr_t::m_memo store information of latched pages, and ensure that mtr_memo_slot_t::object is never a null pointer. The tree_blocks[] and tree_savepoints[] were redundant. buf_page_get_low(): If innodb_change_buffering_debug=1, to avoid a hang, do not try to evict blocks if we are holding a latch on a modified page. The test innodb.innodb-change-buffer-recovery will be removed, because change buffering may no longer be forced by debug injection when the change buffer comprises multiple pages. Remove a debug assertion that could fail when innodb_change_buffering_debug=1 fails to evict a page. For other cases, the assertion is redundant, because we already checked that right after the got_block: label. The test innodb.innodb-change-buffering-recovery will be removed, because due to this change, we will be unable to evict the desired page. mtr_t::lock_register(): Register a change of a page latch on an unmodified buffer-fixed block. mtr_t::x_latch_at_savepoint(), mtr_t::sx_latch_at_savepoint(): Replaced by the use of mtr_t::upgrade_buffer_fix(), which now also handles RW_S_LATCH. mtr_t::set_modified(): For temporary tables, invoke buf_page_t::set_modified() here and not in mtr_t::commit(). We will never set the MTR_MEMO_MODIFY flag on other than persistent data pages, nor set mtr_t::m_modifications when temporary data pages are modified. mtr_t::commit(): Only invoke the buf_flush_note_modification() loop if persistent data pages were modified. mtr_t::get_already_latched(): Look up a latched page in mtr_t::m_memo. This avoids many redundant entries in mtr_t::m_memo, as well as redundant calls to buf_page_get_gen() for blocks that had already been looked up in a mini-transaction. btr_get_latched_root(): Return a pointer to an already latched root page. This replaces btr_root_block_get() in cases where the mini-transaction has already latched the root page. btr_page_get_parent(): Fetch a parent page that was already latched in BTR_MODIFY_TREE, by invoking mtr_t::get_already_latched(). If needed, upgrade the root page U latch to X. This avoids bloating mtr_t::m_memo as well as performing redundant buf_pool.page_hash lookups. For non-QUICK CHECK TABLE as well as for B-tree defragmentation, we will invoke btr_cur_search_to_nth_level(). btr_cur_search_to_nth_level(): This will only be used for non-leaf (level>0) B-tree searches that were formerly named BTR_CONT_SEARCH_TREE or BTR_CONT_MODIFY_TREE. In MDEV-29835, this function could be removed altogether, or retained for the case of CHECK TABLE without QUICK. btr_cur_t::left_block: Remove. btr_pcur_move_backward_from_page() can retrieve the left sibling from the end of mtr_t::m_memo. btr_cur_t::open_leaf(): Some clean-up. btr_cur_t::search_leaf(): Replaces btr_cur_search_to_nth_level() for searches to level=0 (the leaf level). We will never release parent page latches before acquiring leaf page latches. If we need to temporarily release the level=1 page latch in the BTR_SEARCH_PREV or BTR_MODIFY_PREV latch_mode, we will reposition the cursor on the child node pointer so that we will land on the correct leaf page. btr_cur_t::pessimistic_search_leaf(): Implement new BTR_MODIFY_TREE latching logic in the case that page splits or merges will be needed. The parent pages (and their siblings) should already be latched on the first dive to the leaf and be present in mtr_t::m_memo; there should be no need for BTR_CONT_MODIFY_TREE. This pre-latching almost suffices; it must be revised in MDEV-29835 and work-arounds removed for cases where mtr_t::get_already_latched() fails to find a block. rtr_search_to_nth_level(): A SPATIAL INDEX version of btr_search_to_nth_level() that can search to any level (including the leaf level). rtr_search_leaf(), rtr_insert_leaf(): Wrappers for rtr_search_to_nth_level(). rtr_search(): Replaces rtr_pcur_open(). rtr_latch_leaves(): Replaces btr_cur_latch_leaves(). Note that unlike in the B-tree code, there is no error handling in case the sibling pages are corrupted. rtr_cur_restore_position(): Remove an unused constant parameter. btr_pcur_open_on_user_rec(): Remove the constant parameter mode=PAGE_CUR_GE. row_ins_clust_index_entry_low(): Use a new mode=BTR_MODIFY_ROOT_AND_LEAF to gain access to the root page when mode!=BTR_MODIFY_TREE, to write the PAGE_ROOT_AUTO_INC. BTR_SEARCH_TREE, BTR_CONT_SEARCH_TREE: Remove. BTR_CONT_MODIFY_TREE: Note that this is only used by rtr_search_to_nth_level(). btr_pcur_optimistic_latch_leaves(): Replaces btr_cur_optimistic_latch_leaves(). ibuf_delete_rec(): Acquire exclusive ibuf.index->lock in order to avoid a deadlock with ibuf_insert_low(BTR_MODIFY_PREV). btr_blob_log_check_t(): Acquire a U latch on the root page, so that btr_page_alloc() in btr_store_big_rec_extern_fields() will avoid a deadlock. btr_store_big_rec_extern_fields(): Assert that the root page latch is being held. Tested by: Matthias Leich Reviewed by: Vladislav Lesin --- .../innodb/r/innodb-change-buffer-recovery.result | 55 --- mysql-test/suite/innodb/r/innodb_wl6326.result | 405 ---------------- .../t/innodb-change-buffer-recovery-master.opt | 1 - .../innodb/t/innodb-change-buffer-recovery.test | 82 ---- mysql-test/suite/innodb/t/innodb_wl6326.opt | 1 - mysql-test/suite/innodb/t/innodb_wl6326.test | 519 --------------------- mysql-test/suite/innodb_gis/r/rtree_split.result | 12 + mysql-test/suite/innodb_gis/t/rtree_split.test | 15 + 8 files changed, 27 insertions(+), 1063 deletions(-) delete mode 100644 mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result delete mode 100644 mysql-test/suite/innodb/r/innodb_wl6326.result delete mode 100644 mysql-test/suite/innodb/t/innodb-change-buffer-recovery-master.opt delete mode 100644 mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test delete mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.opt delete mode 100644 mysql-test/suite/innodb/t/innodb_wl6326.test (limited to 'mysql-test/suite') diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result deleted file mode 100644 index 670340f3583..00000000000 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ /dev/null @@ -1,55 +0,0 @@ -# -# Bug#69122 - INNODB DOESN'T REDO-LOG INSERT BUFFER MERGE -# OPERATION IF IT IS DONE IN-PLACE -# -call mtr.add_suppression("InnoDB: innodb_read_only prevents crash recovery"); -call mtr.add_suppression("Plugin initialization aborted at srv0start\\.cc"); -call mtr.add_suppression("Plugin 'InnoDB'"); -FLUSH TABLES; -CREATE TABLE t1( -a INT AUTO_INCREMENT PRIMARY KEY, -b CHAR(1), -c INT, -INDEX(b)) -ENGINE=InnoDB STATS_PERSISTENT=0; -SET GLOBAL innodb_change_buffering_debug = 1; -SET GLOBAL innodb_change_buffering = all; -INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; -BEGIN; -SELECT b FROM t1 LIMIT 3; -b -x -x -x -connect con1,localhost,root,,; -BEGIN; -DELETE FROM t1 WHERE a=1; -INSERT INTO t1 VALUES(1,'X',1); -SET DEBUG_DBUG='+d,crash_after_log_ibuf_upd_inplace'; -SELECT b FROM t1 LIMIT 3; -ERROR HY000: Lost connection to server during query -disconnect con1; -connection default; -FOUND 1 /Wrote log record for ibuf update in place operation/ in mysqld.1.err -# restart: --innodb-read-only -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check Error Unknown storage engine 'InnoDB' -test.t1 check error Corrupt -FOUND 1 /innodb_read_only prevents crash recovery/ in mysqld.1.err -# restart: --innodb-force-recovery=5 -SELECT * FROM t1 LIMIT 1; -a b c -1 X 1 -SHOW ENGINE INNODB STATUS; -Type Name Status -InnoDB insert 0, delete mark 0 -SET GLOBAL innodb_fast_shutdown=0; -# restart -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SHOW ENGINE INNODB STATUS; -Type Name Status -InnoDB -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_wl6326.result b/mysql-test/suite/innodb/r/innodb_wl6326.result deleted file mode 100644 index fcd58aedafe..00000000000 --- a/mysql-test/suite/innodb/r/innodb_wl6326.result +++ /dev/null @@ -1,405 +0,0 @@ -SET GLOBAL innodb_adaptive_hash_index = false; -SET GLOBAL innodb_stats_persistent = false; -connect con1,localhost,root,,; -connect con2,localhost,root,,; -connect con3,localhost,root,,; -CREATE TABLE t1 ( -a00 CHAR(255) NOT NULL DEFAULT 'a', -a01 CHAR(255) NOT NULL DEFAULT 'a', -a02 CHAR(255) NOT NULL DEFAULT 'a', -a03 CHAR(255) NOT NULL DEFAULT 'a', -a04 CHAR(255) NOT NULL DEFAULT 'a', -a05 CHAR(255) NOT NULL DEFAULT 'a', -a06 CHAR(255) NOT NULL DEFAULT 'a', -b INT NOT NULL DEFAULT 0 -) ENGINE = InnoDB; -ALTER TABLE t1 ADD PRIMARY KEY( -a00, -a01, -a02, -a03, -a04, -a05, -a06 -); -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -1 -SET GLOBAL innodb_limit_optimistic_insert_debug = 7; -BEGIN; -INSERT INTO t1 (a00) VALUES ('aa'); -INSERT INTO t1 (a00) VALUES ('ab'); -INSERT INTO t1 (a00) VALUES ('ac'); -INSERT INTO t1 (a00) VALUES ('ad'); -INSERT INTO t1 (a00) VALUES ('ae'); -INSERT INTO t1 (a00) VALUES ('af'); -INSERT INTO t1 (a00) VALUES ('ag'); -INSERT INTO t1 (a00) VALUES ('ah'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -3 -BEGIN; -INSERT INTO t1 (a00) VALUES ('ai'); -INSERT INTO t1 (a00) VALUES ('aj'); -INSERT INTO t1 (a00) VALUES ('ak'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -4 -BEGIN; -INSERT INTO t1 (a00) VALUES ('al'); -INSERT INTO t1 (a00) VALUES ('am'); -INSERT INTO t1 (a00) VALUES ('an'); -INSERT INTO t1 (a00) VALUES ('ao'); -INSERT INTO t1 (a00) VALUES ('ap'); -INSERT INTO t1 (a00) VALUES ('aq'); -INSERT INTO t1 (a00) VALUES ('ar'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -5 -BEGIN; -INSERT INTO t1 (a00) VALUES ('as'); -INSERT INTO t1 (a00) VALUES ('at'); -INSERT INTO t1 (a00) VALUES ('au'); -INSERT INTO t1 (a00) VALUES ('av'); -INSERT INTO t1 (a00) VALUES ('aw'); -INSERT INTO t1 (a00) VALUES ('ax'); -INSERT INTO t1 (a00) VALUES ('ay'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -6 -BEGIN; -INSERT INTO t1 (a00) VALUES ('az'); -INSERT INTO t1 (a00) VALUES ('ba'); -INSERT INTO t1 (a00) VALUES ('bb'); -INSERT INTO t1 (a00) VALUES ('bc'); -INSERT INTO t1 (a00) VALUES ('bd'); -INSERT INTO t1 (a00) VALUES ('be'); -INSERT INTO t1 (a00) VALUES ('bf'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -7 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bg'); -INSERT INTO t1 (a00) VALUES ('bh'); -INSERT INTO t1 (a00) VALUES ('bi'); -INSERT INTO t1 (a00) VALUES ('bj'); -INSERT INTO t1 (a00) VALUES ('bk'); -INSERT INTO t1 (a00) VALUES ('bl'); -INSERT INTO t1 (a00) VALUES ('bm'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -8 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bn'); -INSERT INTO t1 (a00) VALUES ('bo'); -INSERT INTO t1 (a00) VALUES ('bp'); -INSERT INTO t1 (a00) VALUES ('bq'); -INSERT INTO t1 (a00) VALUES ('br'); -INSERT INTO t1 (a00) VALUES ('bs'); -INSERT INTO t1 (a00) VALUES ('bt'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -11 -BEGIN; -INSERT INTO t1 (a00) VALUES ('bu'); -INSERT INTO t1 (a00) VALUES ('bv'); -INSERT INTO t1 (a00) VALUES ('bw'); -INSERT INTO t1 (a00) VALUES ('bx'); -INSERT INTO t1 (a00) VALUES ('by'); -INSERT INTO t1 (a00) VALUES ('bz'); -INSERT INTO t1 (a00) VALUES ('ca'); -INSERT INTO t1 (a00) VALUES ('cb'); -INSERT INTO t1 (a00) VALUES ('cc'); -INSERT INTO t1 (a00) VALUES ('cd'); -INSERT INTO t1 (a00) VALUES ('ce'); -INSERT INTO t1 (a00) VALUES ('cf'); -INSERT INTO t1 (a00) VALUES ('cg'); -INSERT INTO t1 (a00) VALUES ('ch'); -INSERT INTO t1 (a00) VALUES ('ci'); -INSERT INTO t1 (a00) VALUES ('cj'); -INSERT INTO t1 (a00) VALUES ('ck'); -INSERT INTO t1 (a00) VALUES ('cl'); -INSERT INTO t1 (a00) VALUES ('cm'); -INSERT INTO t1 (a00) VALUES ('cn'); -INSERT INTO t1 (a00) VALUES ('co'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -15 -BEGIN; -INSERT INTO t1 (a00) VALUES ('cp'); -INSERT INTO t1 (a00) VALUES ('cq'); -INSERT INTO t1 (a00) VALUES ('cr'); -INSERT INTO t1 (a00) VALUES ('cs'); -INSERT INTO t1 (a00) VALUES ('ct'); -INSERT INTO t1 (a00) VALUES ('cu'); -INSERT INTO t1 (a00) VALUES ('cv'); -INSERT INTO t1 (a00) VALUES ('cw'); -INSERT INTO t1 (a00) VALUES ('cx'); -INSERT INTO t1 (a00) VALUES ('cy'); -INSERT INTO t1 (a00) VALUES ('cz'); -INSERT INTO t1 (a00) VALUES ('da'); -INSERT INTO t1 (a00) VALUES ('db'); -INSERT INTO t1 (a00) VALUES ('dc'); -INSERT INTO t1 (a00) VALUES ('dd'); -INSERT INTO t1 (a00) VALUES ('de'); -INSERT INTO t1 (a00) VALUES ('df'); -INSERT INTO t1 (a00) VALUES ('dg'); -INSERT INTO t1 (a00) VALUES ('dh'); -INSERT INTO t1 (a00) VALUES ('di'); -INSERT INTO t1 (a00) VALUES ('dj'); -INSERT INTO t1 (a00) VALUES ('dk'); -INSERT INTO t1 (a00) VALUES ('dl'); -INSERT INTO t1 (a00) VALUES ('dm'); -INSERT INTO t1 (a00) VALUES ('dn'); -INSERT INTO t1 (a00) VALUES ('do'); -INSERT INTO t1 (a00) VALUES ('dp'); -INSERT INTO t1 (a00) VALUES ('dq'); -INSERT INTO t1 (a00) VALUES ('dr'); -INSERT INTO t1 (a00) VALUES ('ds'); -INSERT INTO t1 (a00) VALUES ('dt'); -INSERT INTO t1 (a00) VALUES ('du'); -INSERT INTO t1 (a00) VALUES ('dv'); -INSERT INTO t1 (a00) VALUES ('dw'); -INSERT INTO t1 (a00) VALUES ('dx'); -INSERT INTO t1 (a00) VALUES ('dy'); -INSERT INTO t1 (a00) VALUES ('dz'); -INSERT INTO t1 (a00) VALUES ('ea'); -INSERT INTO t1 (a00) VALUES ('eb'); -INSERT INTO t1 (a00) VALUES ('ec'); -INSERT INTO t1 (a00) VALUES ('ed'); -INSERT INTO t1 (a00) VALUES ('ee'); -INSERT INTO t1 (a00) VALUES ('ef'); -INSERT INTO t1 (a00) VALUES ('eg'); -INSERT INTO t1 (a00) VALUES ('eh'); -INSERT INTO t1 (a00) VALUES ('ei'); -INSERT INTO t1 (a00) VALUES ('ej'); -INSERT INTO t1 (a00) VALUES ('ek'); -INSERT INTO t1 (a00) VALUES ('el'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -23 -BEGIN; -INSERT INTO t1 (a00) VALUES ('em'); -INSERT INTO t1 (a00) VALUES ('en'); -INSERT INTO t1 (a00) VALUES ('eo'); -INSERT INTO t1 (a00) VALUES ('ep'); -INSERT INTO t1 (a00) VALUES ('eq'); -INSERT INTO t1 (a00) VALUES ('er'); -INSERT INTO t1 (a00) VALUES ('es'); -INSERT INTO t1 (a00) VALUES ('et'); -INSERT INTO t1 (a00) VALUES ('eu'); -INSERT INTO t1 (a00) VALUES ('ev'); -INSERT INTO t1 (a00) VALUES ('ew'); -INSERT INTO t1 (a00) VALUES ('ex'); -INSERT INTO t1 (a00) VALUES ('ey'); -INSERT INTO t1 (a00) VALUES ('ez'); -INSERT INTO t1 (a00) VALUES ('fa'); -INSERT INTO t1 (a00) VALUES ('fb'); -INSERT INTO t1 (a00) VALUES ('fc'); -INSERT INTO t1 (a00) VALUES ('fd'); -INSERT INTO t1 (a00) VALUES ('fe'); -INSERT INTO t1 (a00) VALUES ('ff'); -INSERT INTO t1 (a00) VALUES ('fg'); -INSERT INTO t1 (a00) VALUES ('fh'); -INSERT INTO t1 (a00) VALUES ('fi'); -INSERT INTO t1 (a00) VALUES ('fj'); -INSERT INTO t1 (a00) VALUES ('fk'); -INSERT INTO t1 (a00) VALUES ('fl'); -INSERT INTO t1 (a00) VALUES ('fm'); -INSERT INTO t1 (a00) VALUES ('fn'); -INSERT INTO t1 (a00) VALUES ('fo'); -INSERT INTO t1 (a00) VALUES ('fp'); -INSERT INTO t1 (a00) VALUES ('fq'); -INSERT INTO t1 (a00) VALUES ('fr'); -INSERT INTO t1 (a00) VALUES ('fs'); -INSERT INTO t1 (a00) VALUES ('ft'); -INSERT INTO t1 (a00) VALUES ('fu'); -INSERT INTO t1 (a00) VALUES ('fv'); -INSERT INTO t1 (a00) VALUES ('fw'); -INSERT INTO t1 (a00) VALUES ('fx'); -INSERT INTO t1 (a00) VALUES ('fy'); -INSERT INTO t1 (a00) VALUES ('fz'); -INSERT INTO t1 (a00) VALUES ('ga'); -INSERT INTO t1 (a00) VALUES ('gb'); -INSERT INTO t1 (a00) VALUES ('gc'); -INSERT INTO t1 (a00) VALUES ('gd'); -INSERT INTO t1 (a00) VALUES ('ge'); -INSERT INTO t1 (a00) VALUES ('gf'); -INSERT INTO t1 (a00) VALUES ('gg'); -INSERT INTO t1 (a00) VALUES ('gh'); -COMMIT; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -29 -SET GLOBAL innodb_limit_optimistic_insert_debug = 0; -# Test start -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('bfa'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('bfb'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -a00 a01 -aa a -SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; -a00 a01 -aq a -SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; -a00 a01 -cp a -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -a00 a01 -el a -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -ar a -connection con3; -a00 a01 -cn a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -30 -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('coa'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('cob'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -aa a -connection con3; -a00 a01 -el a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -31 -SET DEBUG_SYNC = 'RESET'; -INSERT INTO t1 (a00) VALUES ('gba'); -connection con1; -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -INSERT INTO t1 (a00) VALUES ('gbb'); -connection con2; -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -a00 a01 -aa a -SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; -a00 a01 -ek a -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; -connection con3; -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; -connection default; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; -SET DEBUG_SYNC = 'now SIGNAL continue'; -connection con1; -connection con2; -a00 a01 -el a -connection con3; -a00 a01 -gb a -connection default; -ANALYZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; -CLUST_INDEX_SIZE -32 -SET DEBUG_SYNC = 'RESET'; -connection default; -disconnect con1; -disconnect con2; -disconnect con3; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery-master.opt b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery-master.opt deleted file mode 100644 index e5d7090c883..00000000000 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb_buffer_pool_size=24M diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test deleted file mode 100644 index 129037e783b..00000000000 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ /dev/null @@ -1,82 +0,0 @@ ---echo # ---echo # Bug#69122 - INNODB DOESN'T REDO-LOG INSERT BUFFER MERGE ---echo # OPERATION IF IT IS DONE IN-PLACE ---echo # ---source include/have_innodb.inc -# innodb_change_buffering_debug option is debug only ---source include/have_debug.inc -# Embedded server does not support crashing ---source include/not_embedded.inc -# DBUG_SUICIDE() hangs under valgrind ---source include/not_valgrind.inc -# This test is slow on buildbot. ---source include/big_test.inc ---source include/have_sequence.inc - -call mtr.add_suppression("InnoDB: innodb_read_only prevents crash recovery"); -call mtr.add_suppression("Plugin initialization aborted at srv0start\\.cc"); -call mtr.add_suppression("Plugin 'InnoDB'"); -FLUSH TABLES; - -CREATE TABLE t1( - a INT AUTO_INCREMENT PRIMARY KEY, - b CHAR(1), - c INT, - INDEX(b)) -ENGINE=InnoDB STATS_PERSISTENT=0; - ---let $_server_id= `SELECT @@server_id` ---let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect - -# The flag innodb_change_buffering_debug is only available in debug builds. -# It instructs InnoDB to try to evict pages from the buffer pool when -# change buffering is possible, so that the change buffer will be used -# whenever possible. -SET GLOBAL innodb_change_buffering_debug = 1; -SET GLOBAL innodb_change_buffering = all; -let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; - -# Create enough rows for the table, so that the change buffer will be -# used for modifying the secondary index page. There must be multiple -# index pages, because changes to the root page are never buffered. -INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; - -BEGIN; -SELECT b FROM t1 LIMIT 3; - -connect (con1,localhost,root,,); -BEGIN; -DELETE FROM t1 WHERE a=1; -# This should be buffered, if innodb_change_buffering_debug = 1 is in effect. -INSERT INTO t1 VALUES(1,'X',1); - -SET DEBUG_DBUG='+d,crash_after_log_ibuf_upd_inplace'; ---exec echo "wait" > $_expect_file_name ---error 2013 -# This should force a change buffer merge -SELECT b FROM t1 LIMIT 3; -disconnect con1; -connection default; -let SEARCH_PATTERN=Wrote log record for ibuf update in place operation; ---source include/search_pattern_in_file.inc - ---let $restart_parameters= --innodb-read-only ---source include/start_mysqld.inc -CHECK TABLE t1; ---source include/shutdown_mysqld.inc -let SEARCH_PATTERN=innodb_read_only prevents crash recovery; ---source include/search_pattern_in_file.inc - ---let $restart_parameters= --innodb-force-recovery=5 ---source include/start_mysqld.inc -SELECT * FROM t1 LIMIT 1; -replace_regex /.*operations:.* (insert.*), delete \d.*discarded .*/\1/; -SHOW ENGINE INNODB STATUS; -# Slow shutdown will not merge the changes due to innodb_force_recovery=5. -SET GLOBAL innodb_fast_shutdown=0; ---let $restart_parameters= ---source include/restart_mysqld.inc -CHECK TABLE t1; -replace_regex /.*operations:.* insert [1-9][0-9]*, delete mark [1-9][0-9]*, delete \d.*discarded .*//; -SHOW ENGINE INNODB STATUS; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.opt b/mysql-test/suite/innodb/t/innodb_wl6326.opt deleted file mode 100644 index 99bf0e5a28b..00000000000 --- a/mysql-test/suite/innodb/t/innodb_wl6326.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb_wl6326.test b/mysql-test/suite/innodb/t/innodb_wl6326.test deleted file mode 100644 index 1cf98cd1c7b..00000000000 --- a/mysql-test/suite/innodb/t/innodb_wl6326.test +++ /dev/null @@ -1,519 +0,0 @@ -# -# WL#6326: InnoDB: fix index->lock contention -# - ---source include/have_innodb.inc ---source include/have_debug.inc ---source include/have_debug_sync.inc ---source include/have_innodb_16k.inc - ---disable_query_log -SET @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug; -SET @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index; -SET @old_innodb_stats_persistent = @@innodb_stats_persistent; ---enable_query_log - -# Save the initial number of concurrent sessions ---source include/count_sessions.inc - -SET GLOBAL innodb_adaptive_hash_index = false; -SET GLOBAL innodb_stats_persistent = false; - ---connect (con1,localhost,root,,) ---connect (con2,localhost,root,,) ---connect (con3,localhost,root,,) - -CREATE TABLE t1 ( - a00 CHAR(255) NOT NULL DEFAULT 'a', - a01 CHAR(255) NOT NULL DEFAULT 'a', - a02 CHAR(255) NOT NULL DEFAULT 'a', - a03 CHAR(255) NOT NULL DEFAULT 'a', - a04 CHAR(255) NOT NULL DEFAULT 'a', - a05 CHAR(255) NOT NULL DEFAULT 'a', - a06 CHAR(255) NOT NULL DEFAULT 'a', - b INT NOT NULL DEFAULT 0 -) ENGINE = InnoDB; - -ALTER TABLE t1 ADD PRIMARY KEY( - a00, - a01, - a02, - a03, - a04, - a05, - a06 -); - -# -# Prepare primary key index tree to be used for this test. -# - -# Only root (1) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -# Make the first records sparse artificially, -# not to cause modify_tree by single node_ptr insert operation. -# * (7 - 2) records should be larger than a half of the page size -# * (7 + 2) records should be fit to the page -# (above t1 definition is already adjusted) -SET GLOBAL innodb_limit_optimistic_insert_debug = 7; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('aa'); -INSERT INTO t1 (a00) VALUES ('ab'); -INSERT INTO t1 (a00) VALUES ('ac'); -INSERT INTO t1 (a00) VALUES ('ad'); -INSERT INTO t1 (a00) VALUES ('ae'); -INSERT INTO t1 (a00) VALUES ('af'); -INSERT INTO t1 (a00) VALUES ('ag'); -INSERT INTO t1 (a00) VALUES ('ah'); -COMMIT; -# Raise root (1-2) -# (aa,ad) -# (aa,ab,ac)(ad,ae,af,ag,ah) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('ai'); -INSERT INTO t1 (a00) VALUES ('aj'); -INSERT INTO t1 (a00) VALUES ('ak'); -COMMIT; -# Split leaf (1-3) -# (aa,ad,ak) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('al'); -INSERT INTO t1 (a00) VALUES ('am'); -INSERT INTO t1 (a00) VALUES ('an'); -INSERT INTO t1 (a00) VALUES ('ao'); -INSERT INTO t1 (a00) VALUES ('ap'); -INSERT INTO t1 (a00) VALUES ('aq'); -INSERT INTO t1 (a00) VALUES ('ar'); -COMMIT; -# Split leaf (1-4) -# (aa,ad,ak,ar) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('as'); -INSERT INTO t1 (a00) VALUES ('at'); -INSERT INTO t1 (a00) VALUES ('au'); -INSERT INTO t1 (a00) VALUES ('av'); -INSERT INTO t1 (a00) VALUES ('aw'); -INSERT INTO t1 (a00) VALUES ('ax'); -INSERT INTO t1 (a00) VALUES ('ay'); -COMMIT; -# Split leaf (1-5) -# (aa,ad,ak,ar,ay) -# (aa,ab,ac)(ad,ae,af,ag,ah,ai,aj)(ak,al,am,an,ao,ap,aq)(ar,as,at,au,av,aw,ax)(ay) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('az'); -INSERT INTO t1 (a00) VALUES ('ba'); -INSERT INTO t1 (a00) VALUES ('bb'); -INSERT INTO t1 (a00) VALUES ('bc'); -INSERT INTO t1 (a00) VALUES ('bd'); -INSERT INTO t1 (a00) VALUES ('be'); -INSERT INTO t1 (a00) VALUES ('bf'); -COMMIT; -# Split leaf (1-6) -# (aa,ad,ak,ar,ay,bf) -# (aa,ab,ac)(ad..)(ak..)(ar,as,at,au,av,aw,ax)(ay,az,ba,bb,bc,bd,be)(bf) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bg'); -INSERT INTO t1 (a00) VALUES ('bh'); -INSERT INTO t1 (a00) VALUES ('bi'); -INSERT INTO t1 (a00) VALUES ('bj'); -INSERT INTO t1 (a00) VALUES ('bk'); -INSERT INTO t1 (a00) VALUES ('bl'); -INSERT INTO t1 (a00) VALUES ('bm'); -COMMIT; -# Split leaf (1-7) -# (aa,ad,ak,ar,ay,bf,bm) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay,az,ba,bb,bc,bd,be)(bf,bg,bh,bi,bj,bk,bl)(bm) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bn'); -INSERT INTO t1 (a00) VALUES ('bo'); -INSERT INTO t1 (a00) VALUES ('bp'); -INSERT INTO t1 (a00) VALUES ('bq'); -INSERT INTO t1 (a00) VALUES ('br'); -INSERT INTO t1 (a00) VALUES ('bs'); -INSERT INTO t1 (a00) VALUES ('bt'); -COMMIT; -# Raise root (1-2-8) -# (aa,ar) -# (aa,ad,ak) (ar,ay,bf,bm,bt) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('bu'); -INSERT INTO t1 (a00) VALUES ('bv'); -INSERT INTO t1 (a00) VALUES ('bw'); -INSERT INTO t1 (a00) VALUES ('bx'); -INSERT INTO t1 (a00) VALUES ('by'); -INSERT INTO t1 (a00) VALUES ('bz'); -INSERT INTO t1 (a00) VALUES ('ca'); - -INSERT INTO t1 (a00) VALUES ('cb'); -INSERT INTO t1 (a00) VALUES ('cc'); -INSERT INTO t1 (a00) VALUES ('cd'); -INSERT INTO t1 (a00) VALUES ('ce'); -INSERT INTO t1 (a00) VALUES ('cf'); -INSERT INTO t1 (a00) VALUES ('cg'); -INSERT INTO t1 (a00) VALUES ('ch'); - -INSERT INTO t1 (a00) VALUES ('ci'); -INSERT INTO t1 (a00) VALUES ('cj'); -INSERT INTO t1 (a00) VALUES ('ck'); -INSERT INTO t1 (a00) VALUES ('cl'); -INSERT INTO t1 (a00) VALUES ('cm'); -INSERT INTO t1 (a00) VALUES ('cn'); -INSERT INTO t1 (a00) VALUES ('co'); -COMMIT; -# Split also at level 1 (1-3-11) -# (aa,ar,co) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('cp'); -INSERT INTO t1 (a00) VALUES ('cq'); -INSERT INTO t1 (a00) VALUES ('cr'); -INSERT INTO t1 (a00) VALUES ('cs'); -INSERT INTO t1 (a00) VALUES ('ct'); -INSERT INTO t1 (a00) VALUES ('cu'); -INSERT INTO t1 (a00) VALUES ('cv'); - -INSERT INTO t1 (a00) VALUES ('cw'); -INSERT INTO t1 (a00) VALUES ('cx'); -INSERT INTO t1 (a00) VALUES ('cy'); -INSERT INTO t1 (a00) VALUES ('cz'); -INSERT INTO t1 (a00) VALUES ('da'); -INSERT INTO t1 (a00) VALUES ('db'); -INSERT INTO t1 (a00) VALUES ('dc'); - -INSERT INTO t1 (a00) VALUES ('dd'); -INSERT INTO t1 (a00) VALUES ('de'); -INSERT INTO t1 (a00) VALUES ('df'); -INSERT INTO t1 (a00) VALUES ('dg'); -INSERT INTO t1 (a00) VALUES ('dh'); -INSERT INTO t1 (a00) VALUES ('di'); -INSERT INTO t1 (a00) VALUES ('dj'); - -INSERT INTO t1 (a00) VALUES ('dk'); -INSERT INTO t1 (a00) VALUES ('dl'); -INSERT INTO t1 (a00) VALUES ('dm'); -INSERT INTO t1 (a00) VALUES ('dn'); -INSERT INTO t1 (a00) VALUES ('do'); -INSERT INTO t1 (a00) VALUES ('dp'); -INSERT INTO t1 (a00) VALUES ('dq'); - -INSERT INTO t1 (a00) VALUES ('dr'); -INSERT INTO t1 (a00) VALUES ('ds'); -INSERT INTO t1 (a00) VALUES ('dt'); -INSERT INTO t1 (a00) VALUES ('du'); -INSERT INTO t1 (a00) VALUES ('dv'); -INSERT INTO t1 (a00) VALUES ('dw'); -INSERT INTO t1 (a00) VALUES ('dx'); - -INSERT INTO t1 (a00) VALUES ('dy'); -INSERT INTO t1 (a00) VALUES ('dz'); -INSERT INTO t1 (a00) VALUES ('ea'); -INSERT INTO t1 (a00) VALUES ('eb'); -INSERT INTO t1 (a00) VALUES ('ec'); -INSERT INTO t1 (a00) VALUES ('ed'); -INSERT INTO t1 (a00) VALUES ('ee'); - -INSERT INTO t1 (a00) VALUES ('ef'); -INSERT INTO t1 (a00) VALUES ('eg'); -INSERT INTO t1 (a00) VALUES ('eh'); -INSERT INTO t1 (a00) VALUES ('ei'); -INSERT INTO t1 (a00) VALUES ('ej'); -INSERT INTO t1 (a00) VALUES ('ek'); -INSERT INTO t1 (a00) VALUES ('el'); -COMMIT; -# Split also at level 1 (1-4-18) -# (aa,ar,co,el) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -BEGIN; -INSERT INTO t1 (a00) VALUES ('em'); -INSERT INTO t1 (a00) VALUES ('en'); -INSERT INTO t1 (a00) VALUES ('eo'); -INSERT INTO t1 (a00) VALUES ('ep'); -INSERT INTO t1 (a00) VALUES ('eq'); -INSERT INTO t1 (a00) VALUES ('er'); -INSERT INTO t1 (a00) VALUES ('es'); - -INSERT INTO t1 (a00) VALUES ('et'); -INSERT INTO t1 (a00) VALUES ('eu'); -INSERT INTO t1 (a00) VALUES ('ev'); -INSERT INTO t1 (a00) VALUES ('ew'); -INSERT INTO t1 (a00) VALUES ('ex'); -INSERT INTO t1 (a00) VALUES ('ey'); -INSERT INTO t1 (a00) VALUES ('ez'); - -INSERT INTO t1 (a00) VALUES ('fa'); -INSERT INTO t1 (a00) VALUES ('fb'); -INSERT INTO t1 (a00) VALUES ('fc'); -INSERT INTO t1 (a00) VALUES ('fd'); -INSERT INTO t1 (a00) VALUES ('fe'); -INSERT INTO t1 (a00) VALUES ('ff'); -INSERT INTO t1 (a00) VALUES ('fg'); - -INSERT INTO t1 (a00) VALUES ('fh'); -INSERT INTO t1 (a00) VALUES ('fi'); -INSERT INTO t1 (a00) VALUES ('fj'); -INSERT INTO t1 (a00) VALUES ('fk'); -INSERT INTO t1 (a00) VALUES ('fl'); -INSERT INTO t1 (a00) VALUES ('fm'); -INSERT INTO t1 (a00) VALUES ('fn'); - -INSERT INTO t1 (a00) VALUES ('fo'); -INSERT INTO t1 (a00) VALUES ('fp'); -INSERT INTO t1 (a00) VALUES ('fq'); -INSERT INTO t1 (a00) VALUES ('fr'); -INSERT INTO t1 (a00) VALUES ('fs'); -INSERT INTO t1 (a00) VALUES ('ft'); -INSERT INTO t1 (a00) VALUES ('fu'); - -INSERT INTO t1 (a00) VALUES ('fv'); -INSERT INTO t1 (a00) VALUES ('fw'); -INSERT INTO t1 (a00) VALUES ('fx'); -INSERT INTO t1 (a00) VALUES ('fy'); -INSERT INTO t1 (a00) VALUES ('fz'); -INSERT INTO t1 (a00) VALUES ('ga'); -INSERT INTO t1 (a00) VALUES ('gb'); - -INSERT INTO t1 (a00) VALUES ('gc'); -INSERT INTO t1 (a00) VALUES ('gd'); -INSERT INTO t1 (a00) VALUES ('ge'); -INSERT INTO t1 (a00) VALUES ('gf'); -INSERT INTO t1 (a00) VALUES ('gg'); -INSERT INTO t1 (a00) VALUES ('gh'); -COMMIT; - -# Current tree form (1-4-24) -# (aa,ar,co,el) -# (aa,ad,ak) (ar,ay,bf,bm,bt,ca,ch) (co,cv,dc,dj,dq,dx,ee) (el..,gb) -# (aa,ab,ac)(ad..)(ak..)(ar..)(ay..)(bf..)(bm..)(bt..)(ca..)(ch..)(co..)(cv..)(dc..)(dj..)(dq..)(dx..)(ee..)(el..)..(gb..) -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - -# Insert the rest of records normally -SET GLOBAL innodb_limit_optimistic_insert_debug = 0; - ---echo # Test start - -# (1) Insert records to leaf page (bf..) and cause modify_page. -# - root page is not X latched -# - latched from level 1 page (ar,ay,bf,bm,bt,ca,ch) - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (bf..) -INSERT INTO t1 (a00) VALUES ('bfa'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('bfb'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# Not blocked searches -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -SELECT a00,a01 FROM t1 WHERE a00 = 'aq'; -# "where a00 = 'co'" is blocked because searching from smaller ('co','a','a',..). -SELECT a00,a01 FROM t1 WHERE a00 = 'cp'; -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'ar'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'cn'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default - -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# (2) Insert records to leaf page (co..) and cause modify_page -# - root page is X latched, because node_ptr for 'co' -# is 1st record for (co,cv,dc,dj,dq,dx,ee) -# -# * ordinary pessimitic insert might be done by pessistic update -# and we should consider possibility node_ptr to be deleted. - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (co..) -INSERT INTO t1 (a00) VALUES ('coa'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('cob'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# All searches are blocked because root page is X latched - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default - -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# (3) Insert records to rightmost leaf page (gb..) and cause modify_page -# - root page is not X latched, because node_ptr for 'gb' is the last record -# of the level 1 though it is last record in the page. -# - lathed from level 1 page (el..,gb) - -SET DEBUG_SYNC = 'RESET'; - -# Filling leaf page (gb..) -INSERT INTO t1 (a00) VALUES ('gba'); - ---connection con1 -SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue'; -# Cause modify_tree ---send -INSERT INTO t1 (a00) VALUES ('gbb'); - ---connection con2 -SET DEBUG_SYNC = 'now WAIT_FOR reached'; -# Not blocked searches -SELECT a00,a01 FROM t1 WHERE a00 = 'aa'; -SELECT a00,a01 FROM t1 WHERE a00 = 'ek'; - -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'el'; - ---connection con3 -SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; -# Blocked ---send -SELECT a00,a01 FROM t1 WHERE a00 = 'gb'; - ---connection default -# FIXME: These occasionally time out! ---disable_warnings -SET DEBUG_SYNC = 'now WAIT_FOR lockwait1 TIMEOUT 1'; -SET DEBUG_SYNC = 'now WAIT_FOR lockwait2 TIMEOUT 1'; ---enable_warnings -SET DEBUG_SYNC = 'now SIGNAL continue'; - ---connection con1 ---reap - ---connection con2 ---reap - ---connection con3 ---reap - ---connection default -ANALYZE TABLE t1; -SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; - - - -# Cleanup -SET DEBUG_SYNC = 'RESET'; - ---connection default ---disconnect con1 ---disconnect con2 ---disconnect con3 - -DROP TABLE t1; - ---disable_query_log -SET GLOBAL innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug; -SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index; -SET GLOBAL innodb_stats_persistent = @old_innodb_stats_persistent; ---enable_query_log - -# Wait till all disconnects are completed. ---source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb_gis/r/rtree_split.result b/mysql-test/suite/innodb_gis/r/rtree_split.result index 8e475776ce0..97027bde865 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_split.result +++ b/mysql-test/suite/innodb_gis/r/rtree_split.result @@ -61,3 +61,15 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); count(*) 57344 drop table t1; +# +# MDEV-30400 Assertion height == btr_page_get_level ... on INSERT +# +CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; +SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +BEGIN; +INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_6; +ROLLBACK; +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +DROP TABLE t1; +# End of 10.6 tests diff --git a/mysql-test/suite/innodb_gis/t/rtree_split.test b/mysql-test/suite/innodb_gis/t/rtree_split.test index 6f285187508..a23315dc3f3 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_split.test +++ b/mysql-test/suite/innodb_gis/t/rtree_split.test @@ -73,3 +73,18 @@ select count(*) from t1 where MBRWithin(t1.c2, @g1); # Clean up. drop table t1; + +--echo # +--echo # MDEV-30400 Assertion height == btr_page_get_level ... on INSERT +--echo # + +CREATE TABLE t1 (c POINT NOT NULL,SPATIAL (c)) ENGINE=InnoDB; +SET @save_limit=@@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +BEGIN; +INSERT INTO t1 SELECT POINTFROMTEXT ('POINT(0 0)') FROM seq_1_to_6; +ROLLBACK; +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +DROP TABLE t1; + +--echo # End of 10.6 tests -- cgit v1.2.1