Bug #40788 Transaction didn't work
Submitted: 17 Nov 2008 15:21 Modified: 26 Dec 2008 19:10
Reporter: Pavel Bazanov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:6.0.2 OS:Microsoft Windows
Assigned to:
Tags: innodb, stored procedure, transaction

[17 Nov 2008 15:21] Pavel Bazanov
Description:
Hello, 
We experienced a very dangerous problem when used transaction in a stored procedure. We couldn't reproduce it on demand, but it happened sometimes, during real work, in, maybe, 1% of all cases. 

Here is the stored procedure we had the problem with. Yes, I know, it's a very bad design, but it's what our previous programmer left to us: 

------------------------------------------------------------------ 
CREATE PROCEDURE `DebitCustomer`(IN CustomerID_ INTEGER(11), IN Summ_ DOUBLE, IN Curr_ varCHAR(4), IN Rate_ DOUBLE, IN Source_ varCHAR(30), IN DebitDate_ DATETIME) 
BEGIN 
START TRANSACTION; 

INSERT INTO custdebits SET 
CustomerID = CustomerID_, 
Summ = Summ_, 
Curr = Curr_, 
Rate = Rate_, 
Source = Source_, 
DebitDate = DebitDate_; 

UPDATE customers SET Balans = ROUND(Balans + Summ_ / Rate_, 2) WHERE ID = CustomerID_; 

COMMIT; 
END 
------------------------------------------------------------------ 

Tables DDL:

CREATE TABLE `customers` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) DEFAULT NULL,
  `LastOrderN` int(10) DEFAULT '0',
  `Telephone` varchar(50) DEFAULT NULL,
  `Email` varchar(250) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `Fax` varchar(50) DEFAULT '',
  `Balans` double(15,5) DEFAULT '0.00000',
  `DirhamRate` double(15,5) DEFAULT '3.66000',
  `FinRate` double(15,5) DEFAULT '3.64000',
  `LastInvoiceN` int(10) DEFAULT '0',
  `InvoicesDirectory` varchar(500) NOT NULL DEFAULT '',
  `DoSendBalance` tinyint(4) DEFAULT '0',
  `LastBalanceSent` datetime DEFAULT '2001-01-01 00:00:00',
  `CustomerLocked` tinyint(1) DEFAULT '0',
  `AdditionalInfo` varchar(2000) DEFAULT NULL,
  `InvoicesEmail` varchar(250) DEFAULT '',
  `DoAutoNavar` tinyint(1) DEFAULT '1',
  `AutoNavarPercent` double(15,3) DEFAULT '0.950',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CustomerLevelID` int(11) DEFAULT '0',
  `AutoManagePercents` tinyint(1) DEFAULT '0',
  `LastTimePercentsRecalculated` date DEFAULT '2001-01-01',
  `DaysBetweenPercentsRecalculation` int(11) DEFAULT '90',
  `MultipleReportsInfo` varchar(2000) DEFAULT NULL,
  `SignalOnOrder` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;

CREATE TABLE `custdebits` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` int(10) DEFAULT '0',
  `Summ` double(15,5) DEFAULT '0.00000',
  `Curr` varchar(4) DEFAULT NULL,
  `Rate` double(15,5) DEFAULT '1.00000',
  `Source` varchar(30) DEFAULT NULL,
  `DebitDate` datetime DEFAULT NULL,
  `Checked` bit(1) DEFAULT '\0',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2425 DEFAULT CHARSET=utf8;

-------------------------------------------------------------------------------

So, this procedure had to add a debit record in custdebits table and then update (increase) customer balance value in customers table. 
But sometimes (very rare) we noticed that debit record was created and the balance didn't change. It's very serious. How can it be? 
Is there a mistake in the stored procedure above, that could lead to the bug? 
Or is it a problem of mysql engine?

How to repeat:
CREATE TABLE `customers` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) DEFAULT NULL,
  `LastOrderN` int(10) DEFAULT '0',
  `Telephone` varchar(50) DEFAULT NULL,
  `Email` varchar(250) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `Country` varchar(50) DEFAULT NULL,
  `Fax` varchar(50) DEFAULT '',
  `Balans` double(15,5) DEFAULT '0.00000',
  `DirhamRate` double(15,5) DEFAULT '3.66000',
  `FinRate` double(15,5) DEFAULT '3.64000',
  `LastInvoiceN` int(10) DEFAULT '0',
  `InvoicesDirectory` varchar(500) NOT NULL DEFAULT '',
  `DoSendBalance` tinyint(4) DEFAULT '0',
  `LastBalanceSent` datetime DEFAULT '2001-01-01 00:00:00',
  `CustomerLocked` tinyint(1) DEFAULT '0',
  `AdditionalInfo` varchar(2000) DEFAULT NULL,
  `InvoicesEmail` varchar(250) DEFAULT '',
  `DoAutoNavar` tinyint(1) DEFAULT '1',
  `AutoNavarPercent` double(15,3) DEFAULT '0.950',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CustomerLevelID` int(11) DEFAULT '0',
  `AutoManagePercents` tinyint(1) DEFAULT '0',
  `LastTimePercentsRecalculated` date DEFAULT '2001-01-01',
  `DaysBetweenPercentsRecalculation` int(11) DEFAULT '90',
  `MultipleReportsInfo` varchar(2000) DEFAULT NULL,
  `SignalOnOrder` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `Name` (`Name`)
) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;

CREATE TABLE `custdebits` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustomerID` int(10) DEFAULT '0',
  `Summ` double(15,5) DEFAULT '0.00000',
  `Curr` varchar(4) DEFAULT NULL,
  `Rate` double(15,5) DEFAULT '1.00000',
  `Source` varchar(30) DEFAULT NULL,
  `DebitDate` datetime DEFAULT NULL,
  `Checked` bit(1) DEFAULT '\0',
  `TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2425 DEFAULT CHARSET=utf8;

CREATE PROCEDURE `DebitCustomer`(IN CustomerID_ INTEGER(11), IN Summ_ DOUBLE, IN Curr_ varCHAR(4), IN Rate_ DOUBLE, IN Source_ varCHAR(30), IN DebitDate_ DATETIME) 
BEGIN 
START TRANSACTION; 

INSERT INTO custdebits SET 
CustomerID = CustomerID_, 
Summ = Summ_, 
Curr = Curr_, 
Rate = Rate_, 
Source = Source_, 
DebitDate = DebitDate_; 

UPDATE customers SET Balans = ROUND(Balans + Summ_ / Rate_, 2) WHERE ID = CustomerID_; 

COMMIT; 
END
[17 Nov 2008 19:59] Valerii Kravchuk
Do you really use MySQL 6.0.2? Please, check with a newer version, 6.0.7, and in case of the same problem get the results of:

show innodb status\G

immediately and send them to the issue.
[17 Nov 2008 21:08] Pavel Bazanov
Yes, we really use 6.0.2, but I can't check it with a newer version, because we had to redesign the database and logic to avoid using transactions in DebitCustomer procedure. Now we only insert rows to `custdebits` table and don't keep anything in `customers` table. Balance is now always calculated on the fly as a difference between customers debits and credits.

This is very hard to reproduce, we have a few thousand debits and credits in the database, and we detected this problem only maybe 10 times (so it's even less than 1% of all cases).
[21 Nov 2008 8:17] Sveta Smirnova
Thank you for the feedback.

In these rare cases when table customers is not updated is there any row where ID = CustomerID_ in the table customers?
[21 Nov 2008 11:43] Pavel Bazanov
Yes, of course, there is always a row with corresponding ID in customers table.
[25 Dec 2008 10:30] Pavel Bazanov
any news on this issue?
[25 Dec 2008 11:02] Sveta Smirnova
Thank you for the feedback.

This is actually duplicate of bug #12713 fixed in version 6.0.6
[26 Dec 2008 19:10] Pavel Bazanov
Are you sure it's a duplicate? Why have you decided so?
[29 Dec 2008 6:37] Sveta Smirnova
Pavel,

see title of bug #12713 and comment "[7 Feb 2006 14:37] Konstantin Osipov" with test case.