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