I have encountered a bug which crashes the mySQL server in a reproducible fashion. I have checked the manual and mail archives to the best of my ability to satisfy myself that it has not already been found/addressed/fixed.

Background:

A mySQL-based program runs on a machine which implements a simple batch queue. Other applications create a record in a database table which represents a new job to be processed. The batch processor polls the table every 10 seconds and identifies any job that needs running (that will typically do some calculations and maybe some other mysql SQL commands). While this job is running the database table is periodically updated to reflect progress through the job, so that client applications can monitor progress. Progress updates to the table happen about once every second.

Client programs can run a SQL query on the batch queue table to monitor progress. At some stage, while the client is running the above queries, the mySQL server program crashes. This does not always happen the first time the query is issued, but if the monitoring queries are run repeatedly a crash will definitely happen. The crash happens more quickly if there are many records in the batch queue table. A typical number of records in the table would be about 200, but the bug is repeatable with a single record, although it took many monitoring queries on the client to cause it to happen.

SQL command summary:

Client 1: query repeated every second UPDATE BatchJob SET Percent=50 WHERE ID=5;

Client 2: query issued on user demand, causes mysqld-nt server to crash

Query1 := SELECT * FROM BatchJob;

Reproducability:

I have identified a combination of queries that causes the problem, although it appears that as soon as one application makes many updates to a table (one record at a time), other combinations also fail.

I have written a test program (in Delphi) that shows the problem.

Application 1: run this to make continual UPDATE commands:

if mysql_Init (@MySQL) = nil then RaiseMySQLError ('Failed to initialise client.'); if mysql_Real_Connect (@MySQL, 'Dogbert', '', '', 'Transact', MySQLPort, '', 0) = nil then RaiseMySQLError ('Failed to connect.'); repeat if mysql_Query (@MySQL, 'UPDATE BatchJob SET Percent=50 WHERE ID=1') <> 0 then RaiseMySQLError ('Failed to execute update'); Application.ProcessMessages; Sleep (10); until Application.Terminated; mysql_Close (@MySQL);

Application 2: run this to crash the server

if mysql_init (@MySQL) = nil then RaiseMySQLError ('Failed to initialise client.'); if mysql_real_connect (@MySQL, 'Dogbert', '', '', 'Transact', MySQLPort, '', 0) = nil then RaiseMySQLError ('Failed to connect.'); for i := 0 to 500 do begin if mysql_query (@MySQL, 'SELECT * FROM BatchJob') <> 0 then RaiseMySQLError ('Failed to execute query.'); Q1 := mysql_store_result (@MySQL); mysql_free_result (Q1); Sleep (8); end; mysql_close (@MySQL);

Environment:

Tested and reproduced under MySQL 3.22.32 and 3.22.24. Reproduced under Windows 98, Windows NT4, Windows 2000 Platform: Windows NT4 SP5, dual processor server Manufacturer: Dell PowerEdge 1300, 2xPII/500, 256Mb RAM Environment: mysqld-nt running as service Application: written in Delphi, makes mySQL API calls directly (no third party stuff/ZEOS etc used)

I have reproduced this problem on another Windows NT4 SP5 dual processor machine (2xPentium MMX/233) but could NOT reproduce the problem on a single CPU Windows 2000 server.

C:\my.cnf follows:

# The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking skip-grant-tables set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 set-variable = tmp_table_size=128M

# Uncomment the following row if you move the MySQL distribution to another # location basedir = h:/progra~1/mysql/ datadir = i:/transact/database

This is an example table which fails when reproducing the problem:

# MySQL dump 6.5 # # Host: dogbert Database: transact #-------------------------------------------------------- # Server version 3.22.32

# # Table structure for table 'batchjob' # CREATE TABLE batchjob ( ID int(11) DEFAULT '0' NOT NULL auto_increment, ClassID int(11) DEFAULT '0' NOT NULL, Updated timestamp(14), Status tinyint(4) DEFAULT '0' NOT NULL, JobType tinyint(4), Description char(60), Scheduled datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, User char(30), Started datetime, Finished datetime, Parameters char(240), Info char(240), LogFileName char(200), Feedback char(30), Percent int(11), PRIMARY KEY (ID), KEY Status (Status), KEY Scheduled (Scheduled) );