MySQL Bugs: #2250: Crash on Mysql 5.0.0

Bug #2250	Crash on Mysql 5.0.0
Submitted:	1 Jan 2004 17:41	Modified:	5 Nov 2004 18:51
Reporter:	Alex Salc	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.0	OS:	Linux (linux)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
If Mysql server is started with option log when it executes a procedure containing a delete sentence it crashes.

Maybe the log option of the server is not yet implemented.

Thank you,
Alexandru Salceanu
Net-Connect Internet

How to repeat:
Start mysql server in log mode and issue some repeated delete queries i guess.

I cannot repeat this with DELETE statements (within a stored procedure or not).

Please submit a complete test case including the exact SQL statements needed to reproduce this behavior.

Thank you.

Have you started the mysql server with log ?
When you connect to it is has to show something like this:

[root@voipradius root]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117 to server version: 5.0.0-alpha-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The procedures are like this:

 add_voipuser | CREATE PROCEDURE `add_voipuser`(procCustomer varchar(30),procCompany varchar(10),procCustomerCode varchar(20),procPassword varchar(20),procBillingModel int(2),procBalance double(6,2),procStatus varchar(15),procRegistration date,procExpiration date,procANI varchar(30),procSalesExecutive varchar(30),procPlan varchar(10))
begin
    replace voipusers VALUES('',procCustomer,procCompany,procCustomerCode,procPassword,procBillingModel,procBalance,procStatus,procRegistration,procExpiration,procANI,procSalesExecutive,procPlan);
    IF procBillingModel='0' THEN insert into usergroup values('',procANI,'Postpaid');
    END IF;
    IF procStatus='Active' THEN insert into radcheck values('',procANI,'Password',procPassword);
    END IF;
    END |

| del_voipuser | CREATE PROCEDURE `del_voipuser`(procANI varchar(30))
begin
delete from voipusers where ANI=procANI;
delete from usergroup where USerNAme=procANI;
end |

Table structures are:

 voipusers | CREATE TABLE `voipusers` (
  `Id` int(11) NOT NULL auto_increment,
  `Customer` varchar(30) NOT NULL default '',
  `Company` varchar(10) NOT NULL default '',
  `CustomerCode` varchar(20) NOT NULL default '0',
  `Password` varchar(20) NOT NULL default '0',
  `BillingModel` int(2) NOT NULL default '0',
  `Balance` double(6,2) default NULL,
  `Status` varchar(15) NOT NULL default '',
  `Registration` date NOT NULL default '0000-00-00',
  `Expiration` date NOT NULL default '0000-00-00',
  `ANI` varchar(30) default NULL,
  `SalesExecutive` varchar(30) default NULL,
  `Plan` varchar(10) NOT NULL default '',
  UNIQUE KEY `Id` (`Id`),
  UNIQUE KEY `ANI` (`ANI`),
  KEY `Id_2` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 radcheck | CREATE TABLE `radcheck` (
  `id` int(10) NOT NULL auto_increment,
  `UserName` varchar(30) NOT NULL default '',
  `Attribute` varchar(30) default NULL,
  `Value` varchar(40) default NULL,
  PRIMARY KEY  (`id`),
  KEY `UserName` (`UserName`),
  CONSTRAINT `0_97` FOREIGN KEY (`UserName`) REFERENCES `voipusers` (`ANI`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 usergroup | CREATE TABLE `usergroup` (
  `id` int(10) NOT NULL auto_increment,
  `UserName` varchar(30) NOT NULL default '',
  `GroupName` varchar(30) default NULL,
  PRIMARY KEY  (`id`),
  KEY `UserName` (`UserName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Also MYSQL procedures are called from php5.0

The mysql server hangs with 98% processor usage on mysqld process.

Mysql server was installed from rpm.

The log option was activated specifiying the "log" into my.cnf

If you still can't reapeat-it i will try to set-up a second server with the same mysql server etc etc.

Thank you,
Alexandru Salceanu
Net-Connect Internet

Thank you for the additional information.

Issue occurs when deleting from an InnoDB table.

Hi!

This is probably a bug in stored procedures. I am reassigning this to PEM.

--Heikki

I need an actual test case to repeat this, i.e. the exact calls to do.

We are no longer able to repeat this against the current 5.0 development tree; please try a newer release and let us know if you still see this behavior.  We will need an updated test case if so.