From: sasha
Date: March 21 2000 8:34pm
Subject: Re: Bug crashes SMP mySQL Server
List-Archive: http://lists.mysql.com/bugs/55
Message-Id: <38D7DCC8.D685193F@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Philip Brown wrote:
> 
> 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)
> );
> 

Can you make a package that will include the source + binary of the crashing
program, .sql file that creates the tables, and a README file that explains what
needs to be done to repeat the problem and drop it off in
ftp://ftp.mysql.com/pub/mysql/secret ?

-- 
Sasha Pachev

+------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___     ==  mysql@stripped        |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sasha Pachev            |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sasha@stripped  |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Provo, Utah, USA        |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                         |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                        |
|             /*/             \*\ Developers Team                  |
+------------------------------------------------------------------+