diff options
Diffstat (limited to 'mysql-test/suite/innodb/t')
14 files changed, 1482 insertions, 11 deletions
diff --git a/mysql-test/suite/innodb/t/blob-update-debug.test b/mysql-test/suite/innodb/t/blob-update-debug.test index 948cd749148..8d35dbfc70e 100644 --- a/mysql-test/suite/innodb/t/blob-update-debug.test +++ b/mysql-test/suite/innodb/t/blob-update-debug.test @@ -11,7 +11,9 @@ create table t1 (f1 int primary key, f2 blob) engine = innodb; insert into t1 values (1, repeat('*', 50000)); select f1, substring(f2, 1, 40) from t1; -set debug_dbug = 'd,row_ins_index_entry_timeout'; +set @saved_debug = @@session.debug_dbug; +SET debug_dbug = 'd,row_ins_index_entry_timeout'; update t1 set f1 = 3; select f1, substring(f2, 1, 40) from t1; drop table t1; +SET debug_dbug= @saved_debug; diff --git a/mysql-test/suite/innodb/t/innodb-replace-debug.test b/mysql-test/suite/innodb/t/innodb-replace-debug.test index 7e710ae154c..823712a01f1 100644 --- a/mysql-test/suite/innodb/t/innodb-replace-debug.test +++ b/mysql-test/suite/innodb/t/innodb-replace-debug.test @@ -9,8 +9,8 @@ create table t1 (f1 int primary key, f2 int, f3 int, unique key k1(f2), key k2(f3)) engine=innodb; insert into t1 values (14, 24, 34); set @old_dbug= @@session.debug_dbug; -set debug_dbug = '+d,row_ins_sec_index_entry_timeout'; +SET debug_dbug = '+d,row_ins_sec_index_entry_timeout'; replace into t1 values (14, 25, 34); select * from t1; drop table t1; -set debug_dbug = @old_dbug; +SET debug_dbug = @old_dbug; diff --git a/mysql-test/suite/innodb/t/innodb-stats-initialize-failure.test b/mysql-test/suite/innodb/t/innodb-stats-initialize-failure.test index e480f0caf07..d5d04190b8a 100644 --- a/mysql-test/suite/innodb/t/innodb-stats-initialize-failure.test +++ b/mysql-test/suite/innodb/t/innodb-stats-initialize-failure.test @@ -6,6 +6,7 @@ call mtr.add_suppression("InnoDB: Warning: Index.*"); # This caused crash earlier +SET @saved_dbug = @@SESSION.debug_dbug; set DEBUG_DBUG='+d,ib_ha_innodb_stat_not_initialized'; create table t1(a int not null primary key, b int, c int, key(b), key(c)) engine=innodb; @@ -32,8 +33,8 @@ select count(1) from t1 where a between 5 and 100; select count(1) from t1 where b between 5 and 256; select count(1) from t1 where c between 7 and 787; -set DEBUG_DBUG=NULL; + drop procedure innodb_insert_proc; drop table t1; - +SET debug_dbug= @saved_dbug; diff --git a/mysql-test/suite/innodb/t/innodb_bug11754376.test b/mysql-test/suite/innodb/t/innodb_bug11754376.test index a7f35c1a960..64547d409b3 100644 --- a/mysql-test/suite/innodb/t/innodb_bug11754376.test +++ b/mysql-test/suite/innodb/t/innodb_bug11754376.test @@ -8,7 +8,8 @@ CREATE TABLE bug11754376 (c INT) ENGINE=INNODB; # This will invoke test_normalize_table_name_low() in debug builds - +SET @saved_dbug = @@SESSION.debug_dbug; SET SESSION DEBUG_DBUG='+d,test_normalize_table_name_low'; DROP TABLE bug11754376; +SET debug_dbug= @saved_dbug; diff --git a/mysql-test/suite/innodb/t/innodb_bug30113362.opt b/mysql-test/suite/innodb/t/innodb_bug30113362.opt new file mode 100644 index 00000000000..99bf0e5a28b --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug30113362.opt @@ -0,0 +1 @@ +--innodb-sys-tablestats diff --git a/mysql-test/suite/innodb/t/innodb_bug30113362.test b/mysql-test/suite/innodb/t/innodb_bug30113362.test new file mode 100644 index 00000000000..6d8833a38fc --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug30113362.test @@ -0,0 +1,236 @@ +# +# Test for Bug#30113362 : BTR_CUR_WILL_MODIFY_TREE() IS INSUFFICIENT FOR HIGHER TREE LEVEL +# + +--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 (purge_control,localhost,root,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connect (con2,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', + b INT NOT NULL DEFAULT 0, + PRIMARY KEY(a00, a01, a02) +) charset latin1 ENGINE = InnoDB COMMENT='MERGE_THRESHOLD=45'; + +# +# Prepare primary key index tree to be used for this test. +# + +SET GLOBAL innodb_limit_optimistic_insert_debug = 3; + +delimiter |; +CREATE PROCEDURE data_load_t1() +BEGIN + DECLARE c1 INT DEFAULT 97; + DECLARE c2 INT DEFAULT 97; + DECLARE c3 INT DEFAULT 97; + + WHILE c1 < 102 DO + WHILE c2 < 123 DO + WHILE c3 < 123 DO + INSERT INTO t1 (a00) VALUES (CHAR(c1,c2,c3)); + SET c3 = c3 + 1; + END WHILE; + SET c3 = 97; + SET c2 = c2 + 1; + END WHILE; + SET c2 = 97; + SET c1 = c1 + 1; + END WHILE; +END | +delimiter ;| +call data_load_t1(); +DROP PROCEDURE data_load_t1; + +# all node pages are sparse (max 3 node_ptrs) +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +connection con2; +DELETE FROM t1 WHERE a00 = 'cnm'; +COMMIT; +BEGIN; +INSERT INTO t1 SET a00 = 'cnm'; +# causes "domino falling" merges to upper level +connection purge_control; +COMMIT; +connection con2; +SET GLOBAL innodb_limit_optimistic_insert_debug = 0; +ROLLBACK; + +# at this moment, in the tree, +# ... +# level 4: ...(ast,avw,ayz)(bcc,bff,bii,bll,boo,brr,buu,bxx,cba,ced,cqp,cts)(cwv,czy,ddb)... +# ... + +--echo # Test start + +# (1) Similar case to the first reported corefile at bug#30113362 +# - Deleting 'bii' causes "domino falling" merges and the node_ptr becomes left_most of level 4. +# So, the operation needs upper level pages' X-latch, though doesn't cause merge more. + +connection purge_control; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection con2; +DELETE FROM t1 WHERE a00 = 'bii'; +COMMIT; +BEGIN; +INSERT INTO t1 SET a00 = 'bii'; +SET DEBUG_SYNC = 'rollback_undo_pk SIGNAL roll1_wait WAIT_FOR roll2'; +SET DEBUG_SYNC = 'rollback_purge_clust SIGNAL rollback_waiting WAIT_FOR resume'; +send ROLLBACK; + +connection purge_control; +SET DEBUG_SYNC = 'now WAIT_FOR roll1_wait'; +COMMIT; +SET DEBUG_SYNC = 'now SIGNAL roll2'; + +connect (con1,localhost,root,,); +SET DEBUG_SYNC = 'now WAIT_FOR rollback_waiting'; +SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +send SELECT a00 FROM t1 WHERE a00 = 'bii'; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR lockwait1'; +# bug#30113362 caused deadlock +SET DEBUG_SYNC = 'now SIGNAL resume'; + +connection con1; +reap; +connection con2; +reap; +connection default; + +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + +# (2) Confirm blocking domain caused by DELETE modify_tree for tall index tree + +# at this moment, in the tree, +# ... +# level 4: ...(ajk,amn,apq)(ast,avw,ayz,bll,boo,brr,buu,bxx,cba,ced,cqp,cts)(cwv,czy,ddb)(dge,djh,dmk)(dpn,dsq,dvt)(dyw,ebz,efc)... +# ... + +# makes >17 records in level4 [(2^(4-1))*2 + 1]. (causes never left_most records) +DELETE FROM t1 WHERE a00 = 'dpn'; +COMMIT; +INSERT INTO t1 SET a00 = 'dpn'; +ROLLBACK; + +# at this moment, in the tree, +# (* before "]" and after "[" records are treated as left_most possible records) +# ... +# level 4: ...(ajk,amn,apq)(ast,avw,ayz,bll,boo,brr,buu,bxx],cba,ced,[cqp,cts,cwv,czy,ddb,dge,dsq,dvt)(dyw,ebz,efc)... +# level 3: ...(cba,ccb,cdc)(ced,cfe,cgf,chg],cih,cji,[ckj,clk,con,cpo)(cqp,crq,csr)... +# level 2: ...(ckj,cks,clb)(clk,clt],cmc,cml,cmu,cnd,[cnv,coe)(con,cow,cpf)... +# level 1: ...(cmu,cmx,cna)(cnd],cng,cnj,cnp,[cns)(cnv,cny,cob)... +# level 0: ...(cnd,cne,cnf)(cng,cnh,cni)(cnj,cnk,cnl,cnn,cno)(cnp,cnq,cnr)... + +# deletes just 'ced' node_ptr only from level 4. doesn't cause merge and never left_most. +# adjusts MERGE_THRESHOLD to do so. +ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=35'; + +connection purge_control; +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection con2; +DELETE FROM t1 WHERE a00 = 'cnd'; +COMMIT; +BEGIN; +INSERT INTO t1 SET a00 = 'cnd'; +SET DEBUG_SYNC = 'rollback_undo_pk SIGNAL roll1_wait WAIT_FOR roll2'; +SET DEBUG_SYNC = 'rollback_purge_clust SIGNAL rollback_waiting WAIT_FOR resume EXECUTE 2'; +send ROLLBACK; + +connection purge_control; +SET DEBUG_SYNC = 'now WAIT_FOR roll1_wait'; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +SET DEBUG_SYNC = 'now SIGNAL roll2'; + +connection con1; +# FIXME: For some reason, we will not always receive these signals! +--disable_warnings +# An optimistic row_undo_mod_remove_clust_low() will fail. +SET DEBUG_SYNC = 'now WAIT_FOR rollback_waiting TIMEOUT 1'; +SET DEBUG_SYNC = 'now SIGNAL resume'; +# Wait for the pessimistic row_undo_mod_remove_clust_low() attempt. +SET DEBUG_SYNC = 'now WAIT_FOR rollback_waiting TIMEOUT 1'; +--enable_warnings +disconnect purge_control; + +# The expectation should be... +# level 0: (#cnd#,cne,cnf): causes merge +# level 1: (#cnd#],cng,cnj,cnp,[cns): left_most +# level 2: (clk,clt],cmc,cml,cmu,#cnd#,[cnv,coe): causes merge +# level 3: (ced,cfe,cgf,chg],cih,cji,[ckj,#clk#,con,cpo): left_most possible (not cause merge) +# level 4: (ast,avw,ayz,bll,boo,brr,buu,bxx],cba,#ced#,[cqp,cts,cwv,czy,ddb,dge,dsq,dvt): no merge, not left_most possible +# So, the top X-latch page is at level4. (ast~dvt) + +# blocking domain based on whether its ancestor is latched or not. +# (*[]: ancestor is X-latched) +# level 0: ...(asq,asr,ass) [(ast,asu,asv)...(dyt,dyu,dyv)] (dyw,dyx,dyy)... + +# Not blocked searches +## In MariaDB, both these will block, because we use different DEBUG_SYNC +## instrumentation (in rollback, not purge) and the root page (number 3) +## is being latched in row_undo_mod_remove_clust_low(). +## SELECT a00 FROM t1 WHERE a00 = 'ass'; +## SELECT a00 FROM t1 WHERE a00 = 'dyx'; + +## SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1'; +## send SELECT a00 FROM t1 WHERE a00 = 'ast'; + +## connection con2; +## SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2'; +## send SELECT a00 FROM t1 WHERE a00 = 'dyw'; + +connection default; +## SET DEBUG_SYNC = 'now WAIT_FOR lockwait1'; +## SET DEBUG_SYNC = 'now WAIT_FOR lockwait2'; +SET DEBUG_SYNC = 'now SIGNAL resume'; + +## connection con1; +## reap; +disconnect con1; + +connection con2; +reap; +disconnect con2; + +connection default; +ANALYZE TABLE t1; +SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1'; + + +# Cleanup +SET DEBUG_SYNC = 'RESET'; + +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 + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test index 84c5e70e1b5..ce64f1a8322 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56947.test +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -7,6 +7,7 @@ SET GLOBAL innodb_file_per_table=0; create table bug56947(a int not null) engine = innodb; +SET @saved_dbug = @@SESSION.debug_dbug; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; --replace_regex /"[^"]*"/"xxx"/ --error ER_GET_ERRNO @@ -15,3 +16,4 @@ check table bug56947; drop table bug56947; SET @@global.innodb_file_per_table=DEFAULT; +SET debug_dbug= @saved_dbug; diff --git a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test index 1d723c0bbc3..f1fd7f3c56a 100644 --- a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test +++ b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test @@ -36,7 +36,7 @@ select count(*) from corrupt_bit_test_ā; SET @save_dbug = @@SESSION.debug_dbug; SET debug_dbug = '+d,dict_set_index_corrupted'; check table corrupt_bit_test_ā; -SET debug_dbug = @save_dbug; +SET @@SESSION.debug_dbug = @save_dbug; # Cannot create new indexes while corrupted indexes exist --error ER_INDEX_CORRUPT diff --git a/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test b/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test index e7acb98b0d0..ccd9e3d70f4 100644 --- a/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test +++ b/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test @@ -17,6 +17,7 @@ eval create table t1 (id integer, x integer) engine = InnoDB; insert into t1 values(0, 0); # Enable the debug injection. +SET @saved_dbug = @@SESSION.debug_dbug; set DEBUG_DBUG='+d,fatal-semaphore-timeout'; set autocommit=0; @@ -107,7 +108,7 @@ source include/wait_until_connected_again.inc; --echo # Cleaning up before exit --disable_warnings -set DEBUG_DBUG=NULL; +SET debug_dbug = @saved_dbug; drop table if exists t1; --enable_warnings 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..71eba94be04 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_wl6326.test @@ -0,0 +1,506 @@ +# +# 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; + +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'); +# 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'; + +INSERT INTO t1 (a00) VALUES ('ai'); +INSERT INTO t1 (a00) VALUES ('aj'); +INSERT INTO t1 (a00) VALUES ('ak'); +# 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'; + +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'); +# 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'; + +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'); +# 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'; + +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'); +# 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'; + + +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'); +# 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'; + +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'); +# 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'; + + +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'); +# 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'; + + +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'); +# 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'; + +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'); + + +# 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/t/innodb_wl6326_big.test b/mysql-test/suite/innodb/t/innodb_wl6326_big.test new file mode 100644 index 00000000000..6d4b07e7cb6 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_wl6326_big.test @@ -0,0 +1,716 @@ +# This is a script for MTR with hybrid use. +# a) As regression test +# Mostly some brute force attempt to stress the internal sx locks of +# InnoDB which were introduced by WL#6326+WL#6363. +# The file with expected results fits to this variant. +# The impact on code coverage is quite good. +# b) As testbed for attempts to extend or improve the RQG test wl6326_sql.yy. +# The MTR based test uses +# - a table t1 with the same layout +# - the same stored functions +# - the same stored procedure proc_fill_t1 for inserting a configurable +# amount of records into t1 +# like the RQG test wl6326_sql.yy. +# Feel free to modify parameters like $max_row_count, $max_con, +# $high_load_duration or switch debugging on (let $test_debug= 1). +# But please be aware that MTR will most probably report that the test +# failed because it got a difference to expected results. +# Reasons: +# - In general: The file with expected results fits to a) only. +# - The actual results might dependend on $max_row_count. +# - Additional result sets might be printed. +# + +# WL#6326 is about the sx locks (InnoDB feature only). +--source include/have_innodb.inc +# Runtime properties: +# Notebook i5 dual core with HT, MySQL binaries compiled with debug, +# max_row_count=10000 rows +# vardir on tmpfs : ~ 375 +# vardir on disk : ~ 546 +--source include/big_test.inc +# We go with "--send" and "--reap" and that fails with the embedded server. +--source include/not_embedded.inc +# Its intentional to not take the risk that a run with valgrind times out. +--source include/not_valgrind.inc + +# FIXME: +# Increase the code coverage provided by the current test by +# trying "InnoDB Tablespace Monitor" as soon as some bug is fixed +# or wait till the deprecated "InnoDB Tablespace Monitor" is +# removed. + +# Setup of some parameters +# ------------------------ +# Number of records within every chunk to be added to t1. +let $load_unit= 10000; +# +# Rough number of records in t1 to achieve. +# We add chunks of $load_unit rows till the actual number +# of rows in the table t1 exceeds $max_row_count. +# let $max_row_count= 1000000; +# let $max_row_count= 300000; +# let $max_row_count= 100000; +# let $max_row_count= 30000; + let $max_row_count= 10000; # ~ 322s on tmpfs (NB) +# +# Determine which variant to run. +let $test_debug= 0; +# +# Number of concurrent sessions to be used in the high load test. +let $max_con= 10; +# Duration of the high load test in seconds. +let $high_load_duration= 60; + +# Putting all objects into the SCHEMA my_schema makes the final cleanup easier. +# We simply run than DROP SCHEMA my_schema. +CREATE SCHEMA my_schema; +USE my_schema; +CREATE FUNCTION f_thread_id (i INT) RETURNS CHAR(4) DETERMINISTIC +RETURN CONCAT(LPAD(CAST(i AS CHAR),3,'_'),'_') ; +SELECT CONCAT('->', f_thread_id( 1), '<-'); +SELECT CONCAT('->', f_thread_id(12), '<-'); + +# Definition of parameters used in functions. +# We use here a "1" in order to make the impact on the results of the functions +# good visible. +SET @extra_int = 1; +SET @extra_string = f_thread_id(@extra_int); +SELECT @extra_int , @extra_string; + +# The different functions are used later when filling t1 and also during +# RQG testing. They serve to generate the difference between column values +# in different rows in different areas of the column. +# Fictional example: +# row 1 col_int0=1 colx='1abcdefgh' coly='abcd1efgh' colz='abcdefgh1' +# row 2 col_int0=2 colx='2abcdefgh' coly='abcd2efgh' colz='abcdefgh2' +# The function f_<pattern> is for the column with the name <pattern>. +# There is a function +# - for every column except col_int0 +# - even if the SQL for generating the value is simple. +# The reason for this is the architecture of the RQG test. + +let $part= AS my_result +FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123 +UNION SELECT 1234 UNION SELECT 12345) AS tx; + +let $function_name= f_col_int1; +eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC +RETURN i * 1000 + @extra_int ; +eval SELECT $function_name(my_col) $part; + +let $function_name= f_col_int2; +eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC +RETURN @extra_int * 10000000 + i ; +eval SELECT $function_name(my_col) $part; + +let $function_name= f_col_int3; +eval CREATE FUNCTION $function_name (i INT) RETURNS INT(20) DETERMINISTIC +RETURN @extra_int ; +eval SELECT $function_name(my_col) $part; + +let $function_name= f_col_blob; +eval CREATE FUNCTION $function_name (i INT) RETURNS BLOB DETERMINISTIC +RETURN RPAD(@extra_string,(@@innodb_page_size / 2 ) + 1,'a'); +eval SELECT CONCAT('->', SUBSTR($function_name(my_col) FROM 1 FOR 10), + '<-.....->', SUBSTR($function_name(my_col) FROM -10 FOR 10), '<-') $part; + +let $function_name= f_col_char0; +eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(255) DETERMINISTIC +RETURN LPAD(CAST(i AS CHAR),255,' '); +eval SELECT CONCAT('->', $function_name(my_col), '<-') $part; + +let $function_name= f_col_char1; +eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC +RETURN +CONCAT('B', + LPAD(SUBSTR(CAST(i AS CHAR),1,(LENGTH(CAST(i AS CHAR)) DIV 2)),10,' '), + @extra_string, + RPAD(SUBSTR(CAST(i AS CHAR), -((LENGTH(CAST(i AS CHAR)) + 1) DIV 2)),10,' '), + 'E') ; +eval SELECT CONCAT('->', $function_name(my_col), '<-') $part; + +let $function_name= f_col_char2; +eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC +RETURN +CONCAT('B', + RPAD(SUBSTR(CAST(i AS CHAR),1,(LENGTH(CAST(i AS CHAR)) DIV 2)),10,' '), + @extra_string, + LPAD(SUBSTR(CAST(i AS CHAR), -((LENGTH(CAST(i AS CHAR)) + 1) DIV 2)),10,' '), + 'E'); +eval SELECT CONCAT('->', $function_name(my_col), '<-') $part; + +let $function_name= f_col_char3; +eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC +RETURN +CONCAT('B',@extra_string,LPAD(CAST(i AS CHAR),20,' '),'E'); +eval SELECT CONCAT('->', $function_name(my_col), '<-') $part; + +let $function_name= f_col_char4; +eval CREATE FUNCTION $function_name (i INT) RETURNS CHAR(26) DETERMINISTIC +RETURN +CONCAT('B',RPAD(CAST(i AS CHAR),20,' '),@extra_string,'E'); +eval SELECT CONCAT('->', $function_name(my_col), '<-') $part; + +# Auxiliary table for figuring out the impact of scenarios on +# information_schema.innodb_metrics content. +CREATE TABLE my_metrics LIKE information_schema.innodb_metrics; +ALTER TABLE my_metrics ADD COLUMN phase ENUM('after', 'before'), +DROP COLUMN SUBSYSTEM, DROP COLUMN TYPE, DROP COLUMN COMMENT, +ADD PRIMARY KEY (NAME,phase); +let $empty_my_metrics= DELETE FROM my_metrics; +let $before_my_metrics= INSERT INTO my_metrics +SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT, +COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET, +TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET, +STATUS, 'before' +FROM information_schema.innodb_metrics +WHERE NAME LIKE 'innodb_rwlock_sx_%'; +let $after_my_metrics= INSERT INTO my_metrics +SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT, +COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET, +TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET, +STATUS, 'after' +FROM information_schema.innodb_metrics +WHERE NAME LIKE 'innodb_rwlock_sx_%'; +let $print_metrics= SELECT NAME, COUNT, MAX_COUNT, MIN_COUNT, AVG_COUNT, +COUNT_RESET, MAX_COUNT_RESET, MIN_COUNT_RESET, AVG_COUNT_RESET, +TIME_ENABLED, TIME_DISABLED, TIME_ELAPSED, TIME_RESET, STATUS +FROM information_schema.innodb_metrics +WHERE NAME LIKE 'innodb_rwlock_sx_%' +ORDER BY NAME; + +# The main table for testing. +CREATE TABLE t1 ( + col_int0 BIGINT, + col_int1 BIGINT, + col_int2 BIGINT, + col_int3 BIGINT, + col_blob BLOB, + col_char0 VARCHAR(255), + col_char1 VARCHAR(30), + col_char2 VARCHAR(30), + col_char3 VARCHAR(30), + col_char4 VARCHAR(30) +) ENGINE = InnoDB; + +# Use many indexes with mostly significant size in order to cause +# some heavy use of sx locks during data generation. +ALTER TABLE t1 ADD UNIQUE KEY uidx_col_int0 (col_int0), +ADD UNIQUE KEY uidx1 (col_int1, col_char0), +ADD UNIQUE KEY uidx2 (col_int2, col_char0, col_int1), +ADD UNIQUE KEY uidx3 (col_int3, col_int2, col_char0), +ADD UNIQUE KEY uidx4 (col_char1, col_char0), +ADD UNIQUE KEY uidx5 (col_char2, col_char0, col_char1), +ADD UNIQUE KEY uidx6 (col_char3, col_char2, col_char0), +ADD UNIQUE KEY uidx7 (col_int1, col_int2, col_int3, col_char4, + col_char1, col_char2, col_char3, col_char0), +ADD KEY idx8 (col_blob(10), col_char4); + +delimiter |; +CREATE PROCEDURE proc_fill_t1 (max_row_count INT, load_unit INT) +BEGIN + DECLARE my_count INTEGER DEFAULT 0; + DECLARE max_load_count INTEGER DEFAULT 0; + DROP TABLE IF EXISTS t0; + CREATE TEMPORARY TABLE t0 (col_int0 BIGINT, PRIMARY KEY(col_int0)); + WHILE (my_count < load_unit ) DO + SET my_count = my_count + 1; + INSERT INTO t0 SET col_int0 = my_count; + END WHILE; + SET max_load_count = (SELECT (max_row_count DIV load_unit) + 1 ); + SELECT COUNT(col_int0) INTO @val FROM t1; + SET my_count = 0; + REPEAT + INSERT INTO t1 (col_int0, col_int1, col_int2, col_int3, col_blob, + col_char0, col_char1, col_char2,col_char3,col_char4) + SELECT col_int0 + @val, + f_col_int1(col_int0 + @val), + f_col_int2(col_int0 + @val), + f_col_int3(col_int0 + @val), + f_col_blob(col_int0 + @val), + f_col_char0(col_int0 + @val), + f_col_char1(col_int0 + @val), + f_col_char2(col_int0 + @val), + f_col_char3(col_int0 + @val), + f_col_char4(col_int0 + @val) + FROM t0; + COMMIT; + SELECT MAX(col_int0) INTO @val FROM t1; + SET my_count = my_count + 1; + UNTIL( my_count > max_load_count OR @val >= max_row_count ) + END REPEAT; + DROP TEMPORARY TABLE t0; +END| +delimiter ;| + +delimiter |; +CREATE PROCEDURE proc_dml (max_duration INT, t1_stripe_half INT) +BEGIN + DECLARE aux INTEGER DEFAULT 0; + DECLARE start_time INT; + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN END; + + SET @extra_int = CONNECTION_ID(); + SET @extra_string = f_thread_id(@extra_int); + SELECT ROUND(MAX(col_int0) / 2 ) INTO @t1_half FROM t1; + # The user lock 'Blocker' should be already set by some other session S1. + # S1 starts the race by releasing that lock. + # Wait till the lock is released and the lock can be obtained. + # In order to prevent endless waiting in case of non foreseen problems + # limit the timespan to 30 seconds. + SELECT GET_LOCK('Blocker', 30) INTO @aux; + # Release the lock immediate so that the other "runner" sessions start too. + SELECT RELEASE_LOCK('Blocker') INTO @aux; + SET start_time = UNIX_TIMESTAMP(); + + WHILE (UNIX_TIMESTAMP() - start_time < max_duration) DO + SET @aux = @t1_half - t1_stripe_half + ROUND(RAND() * t1_stripe_half * 2); + UPDATE t1 SET + col_int1 = f_col_int1(col_int0), + col_int2 = f_col_int2(col_int0), + col_int3 = f_col_int3(col_int0), + col_blob = f_col_blob(col_int0), + col_char0 = f_col_char0(col_int0), + col_char1 = f_col_char1(col_int0), + col_char2 = f_col_char2(col_int0), + col_char3 = f_col_char3(col_int0), + col_char4 = f_col_char4(col_int0) + WHERE col_int0 = @aux; + COMMIT; + END WHILE; +END| +delimiter ;| + +SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%"; +SET @pre_reset_ts = NOW(); +--sleep 1.1 +SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%"; +SET @pre_enable_ts = NOW(); +--sleep 1.1 +SET GLOBAL innodb_monitor_enable = "innodb_rwlock_sx_%"; +--sleep 1.1 +SET @pre_collect_ts = NOW(); +eval $empty_my_metrics; +eval $before_my_metrics; +--echo # TC-01 There are exact three entries "innodb_rwlock_sx_%" with the +--echo # with the name which follow in innodb_metrics. +let $check_statement= +SELECT COUNT(*) <> 3 FROM my_metrics +WHERE NAME IN ('innodb_rwlock_sx_spin_waits', + 'innodb_rwlock_sx_spin_rounds', + 'innodb_rwlock_sx_os_waits'); +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +SELECT COUNT(*) INTO @sx_count FROM my_metrics; + +--echo # TC-02 Counting is now enabled. ALL = @sx_count entries show that. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE STATUS = 'enabled'; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME, STATUS FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +--echo # TC-03 @pre_reset_ts < TIME_RESET. ALL = @sx_count entries show that. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE @pre_reset_ts < TIME_RESET; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME, @pre_reset_ts, TIME_RESET FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +--echo # TC-04 @pre_enable_ts < TIME_ENABLED. ALL = @sx_count entries show that. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE @pre_enable_ts < TIME_ENABLED; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME, @pre_enable_ts, TIME_ENABLED FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +--echo # TC-05 TIME_RESET < TIME_ENABLED AND TIME_ENABLED < @pre_collect_ts +--echo # AND TIME_ELAPSED > 0. ALL = @sx_count entries show that. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE TIME_RESET < TIME_ENABLED AND TIME_ENABLED < @pre_collect_ts + AND TIME_ELAPSED > 0; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME, @pre_collect_ts, TIME_RESET, TIME_ENABLED, TIME_ELAPSED + FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +--echo # TC-06 COUNT_RESET = MAX_COUNT_RESET. ALL = @sx_count entries show that. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE COUNT_RESET = MAX_COUNT_RESET; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT NAME, COUNT_RESET, MAX_COUNT_RESET FROM my_metrics + ORDER BY NAME; + exit; +} +--echo # pass + +SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%"; +--disable_result_log +if($test_debug) +{ + --enable_result_log + eval $print_metrics; +} +SHOW ENGINE INNODB STATUS; +--enable_result_log + +eval $empty_my_metrics; +eval $before_my_metrics; +# These values (the "0") help to identify later if some record is in its +# initial state or already modified. +SET @extra_string = '__0_'; +SET @extra_int = 0; + +--echo # TC-07 One session inserts some significant amount of rows into t1. +--echo # The system MUST survive that. +--replace_result $max_row_count <max_row_count> +eval SET @max_row_count = $max_row_count; +--replace_result $load_unit <load_unit> +eval SET @load_unit = $load_unit; +SET @start_time = UNIX_TIMESTAMP(); +SET AUTOCOMMIT = OFF; +CALL proc_fill_t1 (@max_row_count, @load_unit); +--echo # pass +SET AUTOCOMMIT = ON; +SELECT col_int0 INTO @t1_half FROM t1 +WHERE col_int0 >= (@val DIV 2) ORDER BY col_int0 LIMIT 1; + +--disable_result_log +if($test_debug) +{ + --enable_result_log + SELECT COUNT(*) AS table_row_count, + UNIX_TIMESTAMP() - @start_time AS fill_run_time + FROM t1; + eval $print_metrics; +} +SHOW ENGINE INNODB STATUS; +--enable_result_log + +# Show that the value distribution is according to the plan. +--vertical_results +SELECT col_int0, col_int1, col_int2, col_int3, + CONCAT('->', SUBSTR(col_blob FROM 1 FOR 10), + '<-.....->', SUBSTR(col_blob FROM -10 FOR 10), '<-') AS col_blobx, + CONCAT('->',col_char0,'<-') AS col_char0x, + CONCAT('->',col_char1,'<-') AS col_char1x, + CONCAT('->',col_char2,'<-') AS col_char2x, + CONCAT('->',col_char3,'<-') AS col_char3x, + CONCAT('->',col_char4,'<-') AS col_char4x +FROM t1 WHERE col_int0 between 98 AND 102; +--horizontal_results + +# For experiments/interest only. Please do not remove that. +if (0) +{ + ANALYZE TABLE t1; + SELECT n_rows, clustered_index_size, sum_of_other_index_sizes + FROM mysql.innodb_table_stats; + + # SELECT * FROM mysql.innodb_index_stats; + # idx_col_int3_int0 n_diff_pfx01 1 col_int3 + # idx_col_int3_int0 n_diff_pfx02 10000 col_int3,col_int0 + # idx_col_int3_int0 n_diff_pfx03 10000 col_int3,col_int0,DB_ROW_ID + # idx_col_int3_int0 n_leaf_pages 19 Number of leaf pages in the index + # idx_col_int3_int0 size 20 Number of pages in the index + + --vertical_results + SELECT t1.index_name, t1.stat_value AS idx_pages, t2.stat_value AS idx_leaf_pages, + (t1.stat_value - t2.stat_value - 1) / t1.stat_value AS sx_page_ratio + FROM mysql.innodb_index_stats t1, mysql.innodb_index_stats t2 + WHERE t1.index_name = t2.index_name + AND t1.stat_name = 'size' AND t2.stat_name = 'n_leaf_pages' + ORDER BY t1.index_name; + --horizontal_results +} + +--echo # TC-11 Several concurrent sessions perform updates in t1 like mad. +--echo # The system MUST survive this. +--echo # Printing of statements is partially suppressed. +SET @start_time = UNIX_TIMESTAMP(); +SELECT 1 FROM t1 WHERE col_int0 = @t1_half FOR UPDATE; +SELECT GET_LOCK('Blocker', 1000) ; +--disable_query_log +let $num= $max_con; +while ($num) +{ + --connect (con$num,localhost,root,,) + USE my_schema; + # The second parameter of the procedure is size of the affected stripe / 2. + # A smaller stripe causes some smaller counter growth but most probably + # also more stress around locking in general. + # Example # (nnnn) = half stripe size + # NAME | COUNT_RESET (5000) | COUNT_RESET (100) + # -----------------------------+--------------------+---------------- + # innodb_rwlock_sx_os_waits | 1412 | 486 + # innodb_rwlock_sx_spin_rounds | 44061 | 17031 + # innodb_rwlock_sx_spin_waits | 996 | 515 + --send + eval CALL proc_dml($high_load_duration,@t1_half); + dec $num; +} +--connection default +SELECT RELEASE_LOCK('Blocker') ; +--sleep 3 +COMMIT; +let $num= $max_con; +while ($num) +{ + --connection con$num + --reap + dec $num; +} +--echo # pass +--connection default +--enable_query_log + +# let $wait_timeout= 181; +# --source include/wait_condition.inc +# eval $after_my_metrics; +--disable_result_log +if($test_debug) +{ + --enable_result_log + SELECT UNIX_TIMESTAMP() - @start_time AS update_battle_run_time; + eval $print_metrics; +} +SHOW ENGINE INNODB STATUS; +--enable_result_log + +--echo # TC-13 One session performs ALTER TABLE t1 ADD KEY ... on the fat table t1. +--echo # The system MUST survive this. +SET @start_time = UNIX_TIMESTAMP(); +ALTER TABLE t1 ADD KEY idx_col_char4_col_char0 (col_char4,col_char0); +--disable_result_log +if($test_debug) +{ + --enable_result_log + SELECT UNIX_TIMESTAMP() - @start_time AS add_key_run_time; + eval $print_metrics; +} +SHOW ENGINE INNODB STATUS; +--enable_result_log +--echo # pass + +--echo # TC-15 One session performs a fat update on the fat table t1. +--echo # The system MUST survive this. +SET @start_time = UNIX_TIMESTAMP(); +SET @extra_int = 13; +SET @extra_string = f_thread_id(@extra_int); +eval UPDATE t1 SET + col_int1 = f_col_int1(col_int0), col_int2 = f_col_int2(col_int0), + col_int3 = f_col_int3(col_int0), col_blob = f_col_blob(col_int0), + col_char0 = f_col_char0(col_int0), col_char1 = f_col_char1(col_int0), + col_char2 = f_col_char2(col_int0), col_char3 = f_col_char3(col_int0), + col_char4 = f_col_char4(col_int0) +WHERE col_int0 BETWEEN @t1_half - 2500 AND @t1_half + 2500; +COMMIT; +--disable_result_log +if($test_debug) +{ + --enable_result_log + SELECT UNIX_TIMESTAMP() - @start_time AS total_update_run_time; + eval $print_metrics; +} +SHOW ENGINE INNODB STATUS; +--enable_result_log +--echo # pass + +# Basically every of the big activities causes some counter growth. +# But caused by +# - the architecture of InnoDB (certain things happen asynchronous) +# - the actual test configuration (server/InnoDB options) +# - conditions like parallel (./mtr --parallel=auto?) load on the testing box +# this might be not fulfilled per single big activity every time except +# we go with huge waits or similar. +# Observation: +# - non debug binaries: expectation frequent not fulfilled +# - debug binaries: expectation rare not fulfilled +# +let $wait_timeout= 121; +let $wait_condition= +SELECT COUNT(*) = @sx_count +FROM information_schema.innodb_metrics t_after +JOIN my_metrics t_before +ON t_after.COUNT_RESET > t_before.COUNT_RESET AND t_after.NAME = t_before.NAME; +--source include/wait_condition.inc +eval $after_my_metrics; + +--echo # TC-16 The following activities happend after reset in innodb_metrics +--echo # - Insert some significant amount of rows into t1. +--echo # - Several concurrent users perform excessive updates in t1. +--echo # - ALTER TABLE ... ADD KEY <sufficient big enough structure> +--echo # - One UPDATE statement modifying a huge slice of t1. +--echo # Any of them causes heavy use of SX lock and therefore COUNT_RESET +--echo # must have grown for ALL = @sx_count entries. +# The former testcases TC-10 and TC12 had to be made a part of this testcase +# because their results were unstable. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics t_after JOIN my_metrics t_before +ON t_after.COUNT_RESET > t_before.COUNT_RESET AND t_after.NAME = t_before.NAME +WHERE t_after.phase = 'after' AND t_before.phase = 'before'; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +--echo # TC-09 Heavy activity after reset. +--echo # COUNT_RESET = MAX_COUNT_RESET for ALL = @sx_count entries +--echo # needs to stay valid though he counters will have grown. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE phase = 'after' AND COUNT_RESET = MAX_COUNT_RESET; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +eval $empty_my_metrics; +eval $before_my_metrics; +SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%"; +eval $after_my_metrics; +--echo # TC-08 There was a reset. COUNT_RESET = MAX_COUNT_RESET for ALL +--echo # = @sx_count entries. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE phase = 'before' AND COUNT_RESET = MAX_COUNT_RESET; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +--echo # TC-17 We had heavy activity causing big counters and after that a reset. +--echo # Reset causes COUNT > COUNT_RESET AND MAX_COUNT > MAX_COUNT_RESET +--echo # for ALL @sx_count entries. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics +WHERE phase = 'after' + AND COUNT > COUNT_RESET + AND MAX_COUNT > MAX_COUNT_RESET; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +--echo # TC-18 We had some reset but this must not decrease COUNT or MAX_COUNT +--echo # after.COUNT >= before.COUNT AND +--echo # after.MAX_COUNT >= before.MAX_COUNT for ALL @sx_count entries. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics t_after JOIN my_metrics t_before +ON t_after.COUNT >= t_before.COUNT AND t_after.MAX_COUNT >= t_before.MAX_COUNT + AND t_after.NAME = t_before.NAME +WHERE t_after.phase = 'after' AND t_before.phase = 'before'; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +--echo # TC-19 We had some reset after heavy activity and this must cause +--echo # after.COUNT_RESET < before.COUNT_RESET +--echo # AND after.MAX_COUNT_RESET < before.MAX_COUNT_RESET AND +--echo # for ALL @sx_count entries. +let $check_statement= +SELECT COUNT(*) <> @sx_count FROM my_metrics t_after JOIN my_metrics t_before +ON t_after.COUNT_RESET < t_before.COUNT_RESET + AND t_after.MAX_COUNT_RESET < t_before.MAX_COUNT_RESET + AND t_after.NAME = t_before.NAME +WHERE t_after.phase = 'after' AND t_before.phase = 'before'; +if(`$check_statement`) +{ + --echo # fail + eval $check_statement; + SELECT * FROM my_metrics + ORDER BY NAME, phase; + exit; +} +--echo # pass + +# Cleanup +let $num= $max_con; +while ($num) +{ + --connection con$num + --disconnect con$num + --source include/wait_until_disconnected.inc + dec $num; +} +--connection default +USE test; +DROP SCHEMA my_schema; +SET GLOBAL innodb_monitor_disable = all; +SET GLOBAL innodb_monitor_reset_all = all; +--disable_warnings +SET GLOBAL innodb_monitor_enable = default; +SET GLOBAL innodb_monitor_disable = default; +SET GLOBAL innodb_monitor_reset = default; +SET GLOBAL innodb_monitor_reset_all = default; +--enable_warnings +SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%"; +SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%"; + diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index 2a2bd6660c3..8473984afba 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -826,6 +826,10 @@ ALTER TABLE t1 ADD vb INT AS (b); SELECT * FROM t1; DROP TABLE t1; +eval CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) $engine; +ALTER TABLE t1 MODIFY b INT FIRST; +DROP TABLE t1; + dec $format; let $redundant_4k= 0; } diff --git a/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test b/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test index 21c3c72da1c..645ae8c7855 100644 --- a/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test +++ b/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test @@ -34,8 +34,8 @@ while ($i) --let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect --exec echo "wait" > $_expect_file_name -set debug_dbug = '+d,increase_mtr_checkpoint_size'; -set debug_dbug = '+d,crash_after_checkpoint'; +SET debug_dbug = '+d,increase_mtr_checkpoint_size'; +SET debug_dbug = '+d,crash_after_checkpoint'; --error 2013 set global innodb_log_checkpoint_now = 1; @@ -65,7 +65,7 @@ while ($i) --enable_query_log --exec echo "wait" > $_expect_file_name -set debug_dbug = '+d,crash_after_checkpoint'; +SET debug_dbug = '+d,crash_after_checkpoint'; --error 2013 set global innodb_log_checkpoint_now = 1; |