| Bug #37791 | Problem in innodb transaction | ||
|---|---|---|---|
| Submitted: | 2 Jul 2008 3:23 | Modified: | 6 Jul 2008 12:14 |
| Reporter: | newbie Shai | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Jul 2008 3:41]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW CREATE TABLE for both tblStock and tblTemp tables.
[2 Jul 2008 3:54]
newbie Shai
Dear Valeriy,
Below is what your required. Thanks. The funny part I dont understand why is this suddenly happening as it was ok with most of my data and it only happen for very few cases but still this makes my data incorrect then.
CREATE TABLE `tblstock` ( `stockID` int(10) NOT NULL auto_increment, `receiveOrderID` int(10) NOT NULL default '0', `receiveOrderDetailsID` int(10) NOT NULL default '0', `outletFromID` int(2) NOT NULL default '0', `outletID` int(2) NOT NULL default '0', `productID` int(5) NOT NULL default '0', `productType` enum('Accessory','Coupon','Imei') NOT NULL, `stockSerial` varchar(20) NOT NULL default '0', `stockBalance` int(10) NOT NULL default '0', `stockBooked` int(10) NOT NULL default '0', `costPrice` double(10,2) NOT NULL default '0.00', `stockPrice` double(10,2) NOT NULL default '0.00', `stockDate` date NOT NULL, `stockTime` time NOT NULL, `transactionType` enum('t','i','r','e','f','p') NOT NULL, `stockStatus` enum('b','s','t','y') NOT NULL default 'y', PRIMARY KEY (`stockID`,`receiveOrderID`,`outletFromID`,`transactionType`), KEY `stockSIQ` (`stockSIQ`), KEY `productID` (`productID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `tbltemp` ( `tempID` int(10) NOT NULL auto_increment, `productType` enum('Accessory','Coupon','Imei') NOT NULL, `productID` int(5) NOT NULL, `stockID` int(10) NOT NULL, `stockSerial` varchar(20) NOT NULL, `employeeID` int(2) NOT NULL, `tempReceiptTimeStamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`tempID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
[2 Jul 2008 8:51]
Susanne Ebrecht
This is a duplicate of bug #37790
[6 Jul 2008 12:14]
newbie Shai
Dear Valeriy,
I would like to find out did you manage to find out anything on why this problem happened ? Thanks.
Description: Dear All, I have this application which is using Innodb engine and .net connector 5.0.8.1.So I am using C# for my coding. I have also put this bug under the .net connector too. So I am not sure either is the engine or the connector problem? So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So why is this happening. I have separately attached below my function for the transactionConnectionLocal1 where I keep the connection details. int rollbackBoolean = 0; MySqlTransaction transactionLocal1 = null; MySqlConnection connectionLocal1 = null; transactionConnectionLocal1 callTransactionConnectionLocal1 = null; try { callTransactionConnectionLocal1 = new transactionConnectionLocal1(); connectionLocal1 = callTransactionConnectionLocal1.localConnection1; connectionLocal1.Open(); transactionLocal1 = connectionLocal1.BeginTransaction(); } catch (MySql.Data.MySqlClient.MySqlException ex) { rollbackBoolean = 1; MessageBox.Show("Error From Database Connection " + ex.Message); } catch (System.Net.Sockets.SocketException ex) { rollbackBoolean = 1; MessageBox.Show("Error Sockets From Database Connection " + ex.Message); } String myUpdateQuery1 = "Update tblStock " + "Set tblStock.stockStatus = 'b'" + "Where tblStock.stockSerial ='" + serial + "'" ; MySqlCommand myCommand1 = new MySqlCommand(myUpdateQuery1); try { myCommand1.Connection = connectionLocal1; myCommand1.Transaction = transactionLocal1; myCommand1.ExecuteNonQuery(); } catch (MySql.Data.MySqlClient.MySqlException ex) { rollbackBoolean = 1; MessageBox.Show("Error From myUpdateQuery1 " + ex.Message); } catch (System.Net.Sockets.SocketException ex) { rollbackBoolean = 1; MessageBox.Show("Error Sockets From myUpdateQuery1 " + ex.Message); } finally { myCommand1.Dispose(); } String myInsertQuery1 = "Insert into tblTemp " + "Set stockSerial ='" + serial+ "', " + "tempTimeStamp='" + DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';"; MySqlCommand myCommand2 = new MySqlCommand(myInsertQuery1); try { myCommand2.Connection = connectionLocal1; myCommand2.Transaction = transactionLocal1; myCommand2.ExecuteNonQuery(); } catch (MySql.Data.MySqlClient.MySqlException ex) { rollbackBoolean = 1; MessageBox.Show("Error From myInsertQuery1 " + ex.Message); } catch (System.Net.Sockets.SocketException ex) { rollbackBoolean = 1; MessageBox.Show("Error Sockets From myInsertQuery1 " + ex.Message); } finally { myCommand2.Dispose(); } if (rollbackBoolean == 1) { transactionLocal1.Rollback(); } else { try { transactionLocal1.Commit(); } catch (MySql.Data.MySqlClient.MySqlException ex) { try { transactionLocal1.Rollback(); } catch (MySqlException ex1) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while inserting the data."); if (transactionLocal1.Connection != null) { MessageBox.Show("An exception of type " + ex1.GetType() + " was encountered while attempting to roll back the transaction."); } } } catch (System.Net.Sockets.SocketException ex) { rollbackBoolean = 1; MessageBox.Show("Error Sockets From Commit Process " + ex.Message); } finally { connectionLocal1.Close(); } } // connection details function using System; using System.Collections.Generic; using System.Text; using MySql.Data.MySqlClient; namespace mSytemNonFranchise { public class transactionConnectionLocal1 { public MySqlConnection localConnection1; public MySqlCommand command; public transactionConnectionLocal1() { this.localConnection1 = new MySqlConnection("Address='localhost';Database='localDB';User Name='root';Password='local12';Pooling='false'"); // this.command = this.localConnection1.CreateCommand(); // this.localConnection1.Open(); } // destructor - explicitly ~transactionConnectionLocal1() { //this.command.Dispose(); this.localConnection1.Close(); this.localConnection1.Dispose(); } } } How to repeat: Not certain to repeat as this is rare cases.