List:Bugs« Previous MessageNext Message »
From:Philip Brown Date:March 21 2000 3:34pm
Subject:Bug crashes SMP mySQL Server
View as plain text  
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)
);

Thread
Bug crashes SMP mySQL ServerPhilip Brown21 Mar
  • Re: Bug crashes SMP mySQL Serversasha21 Mar