+DROP TABLE IF EXISTS t1;

+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 CONSTRAINT pkey PRIMARY KEY(

+a00,

+a01,

+a02,

+a03,

+a04,

+a05,

+a06

+);

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+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;

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+3

+INSERT INTO t1 (a00) VALUES ('ai');

+INSERT INTO t1 (a00) VALUES ('aj');

+INSERT INTO t1 (a00) VALUES ('ak');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+4

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+5

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+6

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+7

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+8

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+11

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+15

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+test.t1 analyze status OK

+SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_SYS_TABLESTATS WHERE NAME = 'test/t1';

+CLUST_INDEX_SIZE

+23

+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');

+ANALYZE TABLE t1;

+Table Op Msg_type Msg_text

+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';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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 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 ('cva');

+connection con1;

+SET DEBUG_SYNC = 'before_insert_pessimitic_row_ins_clust SIGNAL reached WAIT_FOR continue';

+INSERT INTO t1 (a00) VALUES ('cvb');

+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';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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 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';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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 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;

+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), '<-');

+CONCAT('->', f_thread_id( 1), '<-')

+->__1_<-

+SELECT CONCAT('->', f_thread_id(12), '<-');

+CONCAT('->', f_thread_id(12), '<-')

+->_12_<-

+SET @extra_int = 1;

+SET @extra_string = f_thread_id(@extra_int);

+SELECT @extra_int , @extra_string;

+@extra_int @extra_string

+1 __1_

+CREATE FUNCTION f_col_int1 (i INT) RETURNS INT(20) DETERMINISTIC

+RETURN i * 1000 + @extra_int ;

+SELECT f_col_int1(my_col) AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+1001

+12001

+123001

+1234001

+12345001

+CREATE FUNCTION f_col_int2 (i INT) RETURNS INT(20) DETERMINISTIC

+RETURN @extra_int * 10000000 + i ;

+SELECT f_col_int2(my_col) AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+10000001

+10000012

+10000123

+10001234

+10012345

+CREATE FUNCTION f_col_int3 (i INT) RETURNS INT(20) DETERMINISTIC

+RETURN @extra_int ;

+SELECT f_col_int3(my_col) AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+1

+1

+1

+1

+1

+CREATE FUNCTION f_col_blob (i INT) RETURNS BLOB DETERMINISTIC

+RETURN RPAD(@extra_string,(@@innodb_page_size / 2 ) + 1,'a');

+SELECT CONCAT('->', SUBSTR(f_col_blob(my_col) FROM 1 FOR 10),

+'<-.....->', SUBSTR(f_col_blob(my_col) FROM -10 FOR 10), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+->__1_aaaaaa<-.....->aaaaaaaaaa<-

+->__1_aaaaaa<-.....->aaaaaaaaaa<-

+->__1_aaaaaa<-.....->aaaaaaaaaa<-

+->__1_aaaaaa<-.....->aaaaaaaaaa<-

+->__1_aaaaaa<-.....->aaaaaaaaaa<-

+CREATE FUNCTION f_col_char0 (i INT) RETURNS CHAR(255) DETERMINISTIC

+RETURN LPAD(CAST(i AS CHAR),255,' ');

+SELECT CONCAT('->', f_col_char0(my_col), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+-> 1<-

+-> 12<-

+-> 123<-

+-> 1234<-

+-> 12345<-

+CREATE FUNCTION f_col_char1 (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') ;

+SELECT CONCAT('->', f_col_char1(my_col), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+->B __1_1 E<-

+->B 1__1_2 E<-

+->B 1__1_23 E<-

+->B 12__1_34 E<-

+->B 12__1_345 E<-

+CREATE FUNCTION f_col_char2 (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');

+SELECT CONCAT('->', f_col_char2(my_col), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+->B __1_ 1E<-

+->B1 __1_ 2E<-

+->B1 __1_ 23E<-

+->B12 __1_ 34E<-

+->B12 __1_ 345E<-

+CREATE FUNCTION f_col_char3 (i INT) RETURNS CHAR(26) DETERMINISTIC

+RETURN

+CONCAT('B',@extra_string,LPAD(CAST(i AS CHAR),20,' '),'E');

+SELECT CONCAT('->', f_col_char3(my_col), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+->B__1_ 1E<-

+->B__1_ 12E<-

+->B__1_ 123E<-

+->B__1_ 1234E<-

+->B__1_ 12345E<-

+CREATE FUNCTION f_col_char4 (i INT) RETURNS CHAR(26) DETERMINISTIC

+RETURN

+CONCAT('B',RPAD(CAST(i AS CHAR),20,' '),@extra_string,'E');

+SELECT CONCAT('->', f_col_char4(my_col), '<-') AS my_result

+FROM (SELECT 1 AS my_col UNION SELECT 12 UNION SELECT 123

+UNION SELECT 1234 UNION SELECT 12345) AS tx;

+my_result

+->B1 __1_E<-

+->B12 __1_E<-

+->B123 __1_E<-

+->B1234 __1_E<-

+->B12345 __1_E<-

+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);

+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;

+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);

+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|

+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|

+SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%";

+SET @pre_reset_ts = NOW();

+SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";

+SET @pre_enable_ts = NOW();

+SET GLOBAL innodb_monitor_enable = "innodb_rwlock_sx_%";

+SET @pre_collect_ts = NOW();

+DELETE FROM 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_%';

+# TC-01 There are exact three entries "innodb_rwlock_sx_%" with the

+# with the name which follow in innodb_metrics.

+# pass

+SELECT COUNT(*) INTO @sx_count FROM my_metrics;

+# TC-02 Counting is now enabled. ALL = @sx_count entries show that.

+# pass

+# TC-03 @pre_reset_ts < TIME_RESET. ALL = @sx_count entries show that.

+# pass

+# TC-04 @pre_enable_ts < TIME_ENABLED. ALL = @sx_count entries show that.

+# pass

+# TC-05 TIME_RESET < TIME_ENABLED AND TIME_ENABLED < @pre_collect_ts

+# AND TIME_ELAPSED > 0. ALL = @sx_count entries show that.

+# pass

+# TC-06 COUNT_RESET = MAX_COUNT_RESET. ALL = @sx_count entries show that.

+# pass

+SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";

+SHOW ENGINE INNODB STATUS;

+DELETE FROM 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_%';

+SET @extra_string = '__0_';

+SET @extra_int = 0;

+# TC-07 One session inserts some significant amount of rows into t1.

+# The system MUST survive that.

+SET @max_row_count = <max_row_count>;

+SET @load_unit = <load_unit>;

+SET @start_time = UNIX_TIMESTAMP();

+SET AUTOCOMMIT = OFF;

+CALL proc_fill_t1 (@max_row_count, @load_unit);

+# pass

+SET AUTOCOMMIT = ON;

+SELECT col_int0 INTO @t1_half FROM t1

+WHERE col_int0 >= (@val DIV 2) ORDER BY col_int0 LIMIT 1;

+SHOW ENGINE INNODB STATUS;

+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;

+col_int0 98

+col_int1 98000

+col_int2 98

+col_int3 0

+col_blobx ->__0_aaaaaa<-.....->aaaaaaaaaa<-

+col_char0x -> 98<-

+col_char1x ->B 9__0_8 E<-

+col_char2x ->B9 __0_ 8E<-

+col_char3x ->B__0_ 98E<-

+col_char4x ->B98 __0_E<-

+col_int0 99

+col_int1 99000

+col_int2 99

+col_int3 0

+col_blobx ->__0_aaaaaa<-.....->aaaaaaaaaa<-

+col_char0x -> 99<-

+col_char1x ->B 9__0_9 E<-

+col_char2x ->B9 __0_ 9E<-

+col_char3x ->B__0_ 99E<-

+col_char4x ->B99 __0_E<-

+col_int0 100

+col_int1 100000

+col_int2 100

+col_int3 0

+col_blobx ->__0_aaaaaa<-.....->aaaaaaaaaa<-

+col_char0x -> 100<-

+col_char1x ->B 1__0_00 E<-

+col_char2x ->B1 __0_ 00E<-

+col_char3x ->B__0_ 100E<-

+col_char4x ->B100 __0_E<-

+col_int0 101

+col_int1 101000

+col_int2 101

+col_int3 0

+col_blobx ->__0_aaaaaa<-.....->aaaaaaaaaa<-

+col_char0x -> 101<-

+col_char1x ->B 1__0_01 E<-

+col_char2x ->B1 __0_ 01E<-

+col_char3x ->B__0_ 101E<-

+col_char4x ->B101 __0_E<-

+col_int0 102

+col_int1 102000

+col_int2 102

+col_int3 0

+col_blobx ->__0_aaaaaa<-.....->aaaaaaaaaa<-

+col_char0x -> 102<-

+col_char1x ->B 1__0_02 E<-

+col_char2x ->B1 __0_ 02E<-

+col_char3x ->B__0_ 102E<-

+col_char4x ->B102 __0_E<-

+# TC-11 Several concurrent sessions perform updates in t1 like mad.

+# The system MUST survive this.

+# Printing of statements is partially suppressed.

+SET @start_time = UNIX_TIMESTAMP();

+SELECT 1 FROM t1 WHERE col_int0 = @t1_half FOR UPDATE;

+1

+1

+SELECT GET_LOCK('Blocker', 1000) ;

+GET_LOCK('Blocker', 1000)

+1

+RELEASE_LOCK('Blocker')

+1

+# pass

+SHOW ENGINE INNODB STATUS;

+# TC-13 One session performs ALTER TABLE t1 ADD KEY ... on the fat table t1.

+# The system MUST survive this.

+SET @start_time = UNIX_TIMESTAMP();

+ALTER TABLE t1 ADD KEY idx_col_char4_col_char0 (col_char4,col_char0);

+SHOW ENGINE INNODB STATUS;

+# pass

+# TC-15 One session performs a fat update on the fat table t1.

+# The system MUST survive this.

+SET @start_time = UNIX_TIMESTAMP();

+SET @extra_int = 13;

+SET @extra_string = f_thread_id(@extra_int);

+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;

+SHOW ENGINE INNODB STATUS;

+# pass

+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_%';

+# TC-16 The following activities happend after reset in innodb_metrics

+# - Insert some significant amount of rows into t1.

+# - Several concurrent users perform excessive updates in t1.

+# - ALTER TABLE ... ADD KEY <sufficient big enough structure>

+# - One UPDATE statement modifying a huge slice of t1.

+# Any of them causes heavy use of SX lock and therefore COUNT_RESET

+# must have grown for ALL = @sx_count entries.

+# pass

+# TC-09 Heavy activity after reset.

+# COUNT_RESET = MAX_COUNT_RESET for ALL = @sx_count entries

+# needs to stay valid though he counters will have grown.

+# pass

+DELETE FROM 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_%';

+SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";

+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_%';

+# TC-08 There was a reset. COUNT_RESET = MAX_COUNT_RESET for ALL

+# = @sx_count entries.

+# pass

+# TC-17 We had heavy activity causing big counters and after that a reset.

+# Reset causes COUNT > COUNT_RESET AND MAX_COUNT > MAX_COUNT_RESET

+# for ALL @sx_count entries.

+# pass

+# TC-18 We had some reset but this must not decrease COUNT or MAX_COUNT

+# after.COUNT >= before.COUNT AND

+# after.MAX_COUNT >= before.MAX_COUNT for ALL @sx_count entries.

+# pass

+# TC-19 We had some reset after heavy activity and this must cause

+# after.COUNT_RESET < before.COUNT_RESET

+# AND after.MAX_COUNT_RESET < before.MAX_COUNT_RESET AND

+# for ALL @sx_count entries.

+# pass

+connection con10;

+disconnect con10;

+connection con9;

+disconnect con9;

+connection con8;

+disconnect con8;

+connection con7;

+disconnect con7;

+connection con6;

+disconnect con6;

+connection con5;

+disconnect con5;

+connection con4;

+disconnect con4;

+connection con3;

+disconnect con3;

+connection con2;

+disconnect con2;

+connection con1;

+disconnect con1;

+connection default;

+USE test;

+DROP SCHEMA my_schema;

+SET GLOBAL innodb_monitor_disable = all;

+SET GLOBAL innodb_monitor_reset_all = all;

+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;

+SET GLOBAL innodb_monitor_disable = "innodb_rwlock_sx_%";

+SET GLOBAL innodb_monitor_reset = "innodb_rwlock_sx_%";

+--innodb-sys-tablestats

+#

+# 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

+--disable_warnings

+DROP TABLE IF EXISTS t1;

+--enable_warnings

+

+# 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 CONSTRAINT pkey 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

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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 (cv..) and cause modify_page

+# - root page is X latched, because node_ptr for 'cv'

+# is 2nd 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 (cv..)

+INSERT INTO t1 (a00) VALUES ('cva');

+

+--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 ('cvb');

+

+--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

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';

+SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';

+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

+# 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_%";

+