MySQL Bugs: #44253: Running Stored Procedure Kills MySQL Server

Bug #44253	Running Stored Procedure Kills MySQL Server
Submitted:	14 Apr 2009 0:55	Modified:	4 Sep 2009 21:13
Reporter:	John Whittemore	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.1.33	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
This is the culprit (ran fine in 5.1.3.0):

IF ((SELECT COUNT(*) 
FROM 
perscourse 
WHERE 
uid = LCase(param1) 
AND 
companyid = param2 
AND 
courseid = param3 
AND 
datefinish IS NULL) = 0) 
THEN....

Part of a larger store procedure. I believe it is the SELECT COUNT in conjunction with IS NULL, as I have dozens of other stored procedures running this just fine, but none have the IS NULL.

Now, the first time this runs (when uid and companyid are not in this table) everything executes just fine. But the next time this part of the procedure runs with the uid and company id already in the table it kills MySQL, and gives back this error:

Error Message: Error Executing Database Query.
Error Description: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Connection reset STACKTRACE: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:168) at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1960) at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715) at com.mysql.jdbc.Connection.execSQL(Connection.java:3249) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268) at com.mysql.jdbc.PreparedStat

The entire procedure is here:

DELIMITER |

CREATE  PROCEDURE `spAddPersCourse`
(IN param1 VARCHAR(64), IN param2 MEDIUMINT(8), IN param3 SMALLINT(5))
MODIFIES SQL DATA
SQL SECURITY Definer
COMMENT 'Registration: Adds a person''s course'
/*
Called from spControlPersPurchase
Called from spControlAddNewUserAssignCourse
    
param1: uid
param2: companyid
param3: courseid 
*/

BEGIN
    SET AUTOCOMMIT = 0;
    
    IF ((SELECT COUNT(*) 
         FROM 
            perscourse 
         WHERE 
            uid = LCase(param1)
         AND   
            companyid = param2
         AND 
            courseid = param3
         AND
            datefinish IS NULL
         LIMIT 1) = 0) 
    THEN 
        INSERT INTO 
            perscourse(uid
                       , companyid
                       , courseid
                       , datereg)
        VALUES
            (LCase(param1)
             , param2
             , param3
             , Now());
             
        UPDATE 
            compcourse
        SET 
            licenseused = (licenseused + 1)
        WHERE
            companyid = param2
        AND
            courseid = param3;     
    END IF;
END|

DELIMITER ;
;

How to repeat:
Run the select count as described.

Thank you for the report.

Please also provide output of SHOW CREATE TABLE perscourse and full error log file.

CREATE TABLE `perscourse` (
  `uid` varchar(64) DEFAULT NULL,
  `perscrseid` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Person course id',
  `companyid` mediumint(8) NOT NULL,
  `courseid` smallint(5) NOT NULL,
  `datereg` datetime NOT NULL COMMENT 'Date registered',
  `datestart` datetime DEFAULT NULL COMMENT 'Date/time started',
  `datefinish` datetime DEFAULT NULL COMMENT 'Date/time finished',
  `bossispaid` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Boss Commerce has been paid',
  `crseownispaid` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Course owner is paid',
  `resellerispaid` tinyint(1) DEFAULT NULL COMMENT 'reseller has been paid (0 or 1)',
  `certnum` char(36) DEFAULT NULL COMMENT 'Certificate number (uuid)',
  PRIMARY KEY (`perscrseid`),
  KEY `perscourse_company_idx` (`companyid`),
  KEY `perscourse_courseid_idx` (`courseid`),
  KEY `Rel_person2perscourse` (`uid`),
  KEY `Rel_compcourse2perscourse` (`companyid`,`courseid`),
  CONSTRAINT `Rel_compcourse2perscourse` FOREIGN KEY (`companyid`, `courseid`) REFERENCES `compcourse` (`companyid`, `courseid`) ON DELETE CASCADE,
  CONSTRAINT `Rel_person2perscourse` FOREIGN KEY (`uid`) REFERENCES `person` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='Person''s courses'

Thank you for the feedback. Looks like is needed the table compcourse too, could you please provide a dump file with some insert data command if needed to test. Thanks in advance.

So I first tried comenting out some of the predicates like this:

IF ((SELECT COUNT(*) 
         FROM 
            perscourse 
         WHERE 
            uid = LCase(param1)
         AND   
            companyid = param2
         -- AND 
            -- courseid = param3
         AND
            datefinish IS NULL
         LIMIT 1) = 0) 
    THEN ....

AND IT WORKED

Then I tried changing the order of the predicates like this:

IF ((SELECT COUNT(*)
         FROM 
            perscourse 
         WHERE 
            uid = LCASE(param1)
         AND   
            companyid = param2
         AND
            datefinish IS NULL
         AND
	    courseid = param3
         
         LIMIT 1) = 0) 
    THEN ....

AND THAT ALSO WORKS

BUT if I go back to the original order it kills MySQL.

Thank you for the feedback. I couldn't repeat so I need more help to repeat the issue (data dump file and my.ini file). Could you please provide them?. Thanks in advance.

Thank you for the feedback. I asked for minimal insert command to populate the tables involved, but with your last comment I won`t be able to repeat because I am using the mysql client, so besides the data insert dump file we need the java script test case to be able to reproduce the server crash. Thanks in advance.

crash looks like bug #39320 if you use read committed transaction isolation level in your java app. workaround: use repeatable read instead.

Shane, thanks for your input. We have experienced additional, random problems relating with very simple SELECT stored procedures. I rolled our database back to 5.1.31 and the problems no longer appear. 5.1.33 has some very serious flaws.

Thank you for the feedback. Are you able to test 5.0.37 released version if still you get the server's crash. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".