1--echo # 2--echo # Bug#32831765 SERVER HITS OOM CONDITION WHEN LOADING TWO 3--echo # INNODB TABLES WITH FTS INDEXES 4--echo # 5 6--source include/have_debug.inc 7 8create table t1 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, 9`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` 10text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, 11`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` 12text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, 13`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` 14text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY 15(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY 16(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY 17(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY 18(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY 19(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY 20(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY 21(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY 22(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY 23(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY 24(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; 25 26create table t2 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, 27`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` 28text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, 29`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` 30text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, 31`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` 32text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY 33(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY 34(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY 35(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY 36(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY 37(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY 38(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY 39(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY 40(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY 41(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY 42(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; 43 44 45create table t3 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, 46`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` 47text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, 48`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` 49text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, 50`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` 51text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY 52(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY 53(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY 54(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY 55(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY 56(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY 57(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY 58(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY 59(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY 60(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY 61(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; 62 63create table t4 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, 64`col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` 65text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, 66`col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` 67text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, 68`col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` 69text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY 70(`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY 71(`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY 72(`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY 73(`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY 74(`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY 75(`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY 76(`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY 77(`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY 78(`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY 79(`col29`), FULLTEXT KEY (`col30`)) engine=innodb; 80 81delimiter |; 82 83--echo #create procedure to inset into the table. 84CREATE PROCEDURE `proc_insert`(IN tab_name VARCHAR(40)) 85BEGIN 86 DECLARE i INT DEFAULT 1; 87 SET @insert_tbl =CONCAT('INSERT INTO ', tab_name, '( `col01`, `col02`, 88 `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, 89 `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, 90 `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, 91 `col27`, `col28`, `col29`, `col30`) 92 VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 93 MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 94 MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 95 MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 96 MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 97 MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), 98 MD5(RAND()))'); 99 PREPARE ins_stmt FROM @insert_tbl; 100 while (i <= 2000) DO 101 EXECUTE ins_stmt; 102 SET i = i + 1; 103 END WHILE; 104 DEALLOCATE PREPARE ins_stmt; 105END | 106 107delimiter ;| 108 109connect (con1,localhost,root,,); 110connect (con2,localhost,root,,); 111connect (con3,localhost,root,,); 112connect (con4,localhost,root,,); 113connect (con5,localhost,root,,); 114connect (con6,localhost,root,,); 115connect (con7,localhost,root,,); 116connect (con8,localhost,root,,); 117 118connection con1; 119--send call proc_insert('t1'); 120 121connection con2; 122--send call proc_insert('t1'); 123 124connection con3; 125--send call proc_insert('t2'); 126 127connection con4; 128--send call proc_insert('t2'); 129 130connection con5; 131--send call proc_insert('t3'); 132 133connection con6; 134--send call proc_insert('t3'); 135 136connection con7; 137--send call proc_insert('t4'); 138 139connection con8; 140--send call proc_insert('t4'); 141 142connection default; 143 144# Check if number of SYNC requests are more than 1000. 145SET GLOBAL DEBUG="+d,fts_optimize_wq_count_check"; 146 147# Wait for 1 minute to make sure INSERTs into FTS table will trigger 148# FTS_SYNC request 149let $loop=60; 150while($loop > 1) 151{ 152 sleep 1; 153 dec $loop; 154} 155 156SET GLOBAL DEBUG="-d,fts_optimize_wq_count_check"; 157 158connection con1; 159reap; 160disconnect con1; 161 162connection con2; 163reap; 164disconnect con2; 165 166connection con3; 167reap; 168disconnect con3; 169 170connection con4; 171reap; 172disconnect con4; 173 174connection con5; 175reap; 176disconnect con5; 177 178connection con6; 179reap; 180disconnect con6; 181 182connection con7; 183reap; 184disconnect con7; 185 186connection con8; 187reap; 188disconnect con8; 189 190connection default; 191--echo # clean up 192DROP TABLE t1; 193DROP TABLE t2; 194DROP TABLE t3; 195DROP TABLE t4; 196DROP PROCEDURE proc_insert; 197