MySQL Bugs: #3925: Server crashes on fulltext lookup

Bug #3925	Server crashes on fulltext lookup
Submitted:	28 May 2004 12:25	Modified:	29 Jun 2004 17:32
Reporter:	Richard Ulrich	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.14-max-nt	OS:	Windows (Windows)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
This problem is hard to track down, as it happens only occasionally. The mysql service sometimes just stops, it disappears from the active processes withouth any message. 
The clients then report errors of not being able to connect to the server. On the heavy loaded DB Sserver this happens about 5 times a day. On the development machines we're somtimes able to reproduce the behaviour, but it's often just luck. 

A trace file would be way too large, since it often takes hours before the crash happens.
Is there some other information I could provide?

The problem only happens when we do fulltext lookups on myisam tables. If we only run normal selects on innodb tables, everything is stable.

We have observed the same behaviour with different versions of mysqld.

How to repeat:
It's hard to reproduce. Sometimes it runs, and sometimes it doesn't. Sometimes we have to run tests for hours before the service crashes.
A testcase, that shows the statements can be found at: 
http://www.tcginf.ch/~tracking/file_download.php?file_id=19&type=bug

Suggested fix:
Prevent the service from crashing.

Shows the usage (real code is in C++ over OLEDB)

Attachment: ServerStressTest.html (text/html), 4.53 KiB.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

I tried it with version 4.0.20a-nt-max-log and the service crashes all so.

Hi Richard,

While we understand that you're having problems we need to be able to repeat it in
order to track it down and fix it.

Have you been able to reproduce the problem with any known actions?

Unfortunately we're not able to reproduce this but we would like to address it if
possible.

The service crashed when i tried the script which was added by Richard(ServerStressTest.html).

Hi,

A testcase, that shows the statements can be found at: 
http://www.tcginf.ch/~tracking/file_download.php?file_id=19&type=bug

I tried to get the above, subscribed for to login and until now no luck.
So the only way is you to provide us a test case.

Hi,

A testcase, that shows the statements can be found at: 
http://www.tcginf.ch/~tracking/file_download.php?file_id=19&type=bug

I tried to get the above, subscribed for to login and until now no luck.
So the only way is you to provide us a test case.

Look at the File added to this bug, ServerStress.html. I tried two or three times and the service crashed.

I ran 5 times the script attached in files tab without to crash the
server. At the bottom the 5th test output:

mysql> select version();
+----------------+
| version()      |
+----------------+
| 4.0.20a-nt-log |
+----------------+
1 row in set (0.00 sec)

MySQL Server StressTest
This Test should demonstrate how to bring the mySQL server service down.

Start 
opening the DB Connection
Delete the old table
Create the table
Fill the table
2000 Records imported
4000 Records imported
6000 Records imported
8000 Records imported
10000 Records imported
12000 Records imported
14000 Records imported
16000 Records imported
18000 Records imported
20000 Records imported
create the indexes
do some lookups
Search NR 15 Found : 238 - Conf: 0, 8.7913336704896, 8.7913336704896, 
1000 Records searched
Search NR 1721 Found : 10041 - Conf: 8.882006481279, 0, 9.0690820564037, 
Search NR 1823 Found : 5458 - Conf: 0, 9.2642074282221, 7.7370171475998, 
2000 Records searched
Search NR 2934 Found : 13123 - Conf: 0, 9.467913268233, 9.3649529625417, 
3000 Records searched
Search NR 3003 Found : 18109 - Conf: 0, 9.0690820564037, 8.882006481279, 
Search NR 3174 Found : 7586 - Conf: 0, 8.6157535483594, 8.7024937128743, 
Search NR 3292 Found : 7353 - Conf: 0, 9.3649529625417, 9.0690820564037, 
Search NR 3411 Found : 885 - Conf: 0, 8.6154305307996, 8.1759788792713, 
Search NR 3562 Found : 4485 - Conf: 8.9745694034575, 0, 8.6154305307996, 
Search NR 3853 Found : 4032 - Conf: 8.9745694034575, 8.7094473372259, 0, 
4000 Records searched
Search NR 4094 Found : 8648 - Conf: 8.7094473372259, 8.7913336704896, 0, 
Search NR 4266 Found : 16523 - Conf: 0, 8.6154305307996, 9.3649529625417, 
Search NR 4358 Found : 1952 - Conf: 8.5300927689593, 0, 8.9745694034575, 
5000 Records searched
Search NR 5290 Found : 633 - Conf: 8.5300927689593, 8.2603049910678, 0, 
Search NR 5391 Found : 16806 - Conf: 8.8052008731838, 0, 8.5240541787847, 
Search NR 5409 Found : 928 - Conf: 0, 8.7024937128743, 7.9330236948397, 
Search NR 5896 Found : 7474 - Conf: 8.1759788792713, 9.3649529625417, 0, 
6000 Records searched
Search NR 6138 Found : 11121 - Conf: 0, 8.5300927689593, 7.9789080759866, 
Search NR 6283 Found : 11324 - Conf: 0, 8.7024937128743, 8.6154305307996, 
Search NR 6655 Found : 7729 - Conf: 8.7024937128743, 7.8160009766194, 0, 
7000 Records searched
Search NR 7273 Found : 9965 - Conf: 8.2603049910678, 8.2603049910678, 0, 
Search NR 7594 Found : 18979 - Conf: 8.882006481279, 0, 9.467913268233, 
Search NR 7938 Found : 14441 - Conf: 0, 8.1487509654716, 8.4342860965947, 
8000 Records searched
Search NR 8034 Found : 15822 - Conf: 7.6596129741684, 8.882006481279, 0, 
Search NR 8139 Found : 14733 - Conf: 0, 9.1656064206597, 8.4342860965947, 
Search NR 8770 Found : 5949 - Conf: 8.882006481279, 0, 8.6154305307996, 
Search NR 8799 Found : 6740 - Conf: 9.1656064206597, 8.9745694034575, 0, 
Search NR 8952 Found : 11145 - Conf: 0, 8.6154305307996, 7.9330236948397, 
9000 Records searched
Search NR 9053 Found : 13825 - Conf: 8.882006481279, 0, 9.1656064206597, 
Search NR 9784 Found : 9979 - Conf: 0, 8.093357328967, 8.5240541787847, 
Search NR 9812 Found : 9274 - Conf: 9.3649529625417, 8.1759788792713, 0, 
10000 Records searched
done with the test

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 4.0.20a-nt-max-log |
+--------------------+
1 row in set (0.03 sec)

Could you try it more, i'm sure the service will crash

I tried again now 10 times:

mysql> show status like "com_in%";
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Com_insert        | 200000 |
| Com_insert_select | 0      |
+-------------------+--------+
2 rows in set (0.00 sec)

mysql> show status like "com_se%";
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| Com_select     | 100010 |
| Com_set_option | 0      |
+----------------+--------+
2 rows in set (0.00 sec)

without to repeat the server crash reported. Could you please provide me
your my.ini/my.cnf file ?

Thanks

My.ini configuration file from the production machine

Attachment: my.ini (application/octet-stream, text), 2.92 KiB.

My.ini configuration file from my development machine

Attachment: my.ini (application/octet-stream, text), 2.30 KiB.

Richi added tow ini files to this bug. The first(2988 bytes) is on my PC. And the second is form the customers DBServer PC(2 * 3.0 Xeon, 1024 RAM, Win2000 Server SP4). 
Can you see some settings in the ini file, which we could change for a better performance for fulltext searches?