| Bug #53215 | Some query commit but no updates | ||
|---|---|---|---|
| Submitted: | 27 Apr 2010 17:05 | Modified: | 29 Jul 2010 16:08 |
| Reporter: | newbie Shai | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | mysql-connector-net-5.2.3 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
[28 Apr 2010 6:15]
Tonci Grgin
Hi Shai and thanks for your report. Although you reported no actual bug but only your thoughts, I'll give it a shot. First of all, you use way too old version of c/NET. Please upgrade to 6.2 at least. Then, you probably have differences in setup between servers. I guess there is "autocommit" set for some of them. Then you have nested transactions (shows in log). Please try avoiding them and see what happens. As there is no repeatable test case attached, there is nothing much I can do for you.
[28 Apr 2010 16:55]
newbie Shai
Commit Problem Test Case 3
Attachment: problemOfCommit3.txt (text/plain), 71.07 KiB.
[28 Apr 2010 16:56]
newbie Shai
Dear Tonci,
Thank you. Ok I will upgrade the .net connector. Now all my servers are using the same version of mysql and .net connector. Must I off the 'autocommit' is it. I dont really get you when you say nesting transaction because below is how I commit finally. I commit one after another because there is not support distributed transaction. Offcourse the final transaction is consist of a number of different query which are based on a for loop.
transactionLocal.Commit();//localDb
transactionCentralCopy.Commit();//central copy of localDB
transactionCentral.Commit();//centralDB
I have also attached another repeated case of the same problem in the file named as problemOfCommit3.txt where both the queries run but the results where not there.
You can see my queries
1088 Query Update tblProduct Set tblProduct.productTotalStock = 1, tblProduct.productPrice = 117, tblProduct.productTotalAmount = 117 Where tblProduct.productID=2798
1089 Query Update tblProduct Set tblProduct.branch4TotalStock = 1, tblProduct.branch4TotalAmount = 117 Where tblProduct.productID=2798
[5 May 2010 5:37]
Tonci Grgin
Shai, first I'd turn "autocommit" off (if it's on, see manual for details) then I would upgrade c/NET as your version is no longer supported. Please do so and rerun your tests. If they fail after that, you have to attach small but complete test case so I can check.
[5 May 2010 16:55]
newbie Shai
Dear Tonci,
OK I will change both .net connector and mysql db and also set it to automcommit in the config file. I need to ask you is it advisable to use the latest .net connector to commit few connection on different db at one gone or must I change to another method? The problem is because I notice another case I have attached there is "9790 Query BEGIN" but then at last is just quit not commit when the programm actually run the commit statement. Do you think is rite to do with .net connector?
Below is the final code I used to launch multiples commit
try
{
transactionLocal.Commit();
if (backUpCentralCopy == 0)
{
transactionCentralCopy.Commit();
}
if (backUpCentral == 0)
{
transactionCentral.Commit();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal.Rollback();
if (backUpCentralCopy == 0)
{
transactionCentralCopy.Rollback();
}
if (backUpCentral == 0)
{
transactionCentral.Rollback();
}
}
catch (MySqlException ex1)
{
MessageBox.Show("An exception of type " + ex.GetType() +
" was encountered while inserting the data.");
if (transactionLocal.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
{
connectionLocal.Close();
if (backUpCentralCopy == 0)
{
connectionCentralCopy.Close();
}
if (backUpCentral == 0)
{
connectionCentral.Close();
}
}
[5 May 2010 16:57]
newbie Shai
No commit just Quit
Attachment: problemOfNoCommit.txt (text/plain), 3.72 KiB.
[10 May 2010 8:54]
Tonci Grgin
Shai, I suggest you to take a look at discussion in Bug#50773 and my test case attached there (I need something like that from you, no custom classes/calls and such). Also, you need, at least, mysqlcmd.CommandText = "SET autocommit = 0"; mysqlcmd.ExecuteNonQuery(); to be executed if MySQL server is in AUTOCOMMIT mode (to turn it off).
[10 May 2010 13:52]
newbie Shai
Dear Tonci,
Following your advice I have updated to mysql5.1.46 and .net connector 6.2.3. So be on the save site I have set autocommit=0 in the .inf file itself. Ok I have seen you sample code, is that code you asking me to follow to allow multiple different transaction commit is it? Off course it will take some time for me to modify first my codes as I have a for loop and in it I do all the insert and update query for three differen databases. So now I have to copy the loop into 3 copies to do the same work. Thank you.
[10 May 2010 13:58]
Tonci Grgin
Shai, mentioned report bears relevance to your question so you should read it carefully. My test case in that bug report is a prototype of how to write a test you want me to check. No external references, everything in it etc). You can also search bugsdb and find many more test cases regarding your problem attached. Feel free to check on code. After that, if problem is not solved, you can attach a meaningful test case here so I can check.
[10 May 2010 15:02]
newbie Shai
Dear Tonci,
Thank you for the sample code. Just to confirm with your prototype method is the correct method for my scenario is it using the transactionScope. Infact I have been using for one of my other program which does only work fine but that was just for a single db. So I let me try with transactionScope for multiple db updates. Thank you.
[11 May 2010 3:20]
newbie Shai
Dear Tonci,
Just to share with you this link http://social.msdn.microsoft.com/Forums/en/windowstransactionsprogramming/thread/184d9aa5-... . I know it referring to ms sql but if I run your sample script (using transaction scope) would I need to play around with the MSDTC for MySQL DB.
[14 May 2010 8:24]
Tonci Grgin
Shai, AFAIK we do not support MSDTC-like transactions. So, please attach *complete* test case that I can use to check on your problem.
[14 Jun 2010 23:00]
Bugs System
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".
[15 Jun 2010 1:15]
newbie Shai
Dear Tonci,
Just to confirm with you can the latest version of the .net connector support distributed transaction. Is it via the "using" method thx?
[19 Jul 2010 19:55]
Vladislav Vaintroub
@Shai, nope, there is no distributed transactions yet in Connector/NET.
[29 Jul 2010 16:08]
newbie Shai
Dear Tonci,
I was wandering how did your run your test case coding in Bug#50773. I have tried something below too but it gives me the error as "Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.". It works only when both the connection are same. I was referring to test case in Bug#50773. Thank you.
try
{
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, TimeSpan.FromSeconds(25)))
{
using (MySqlConnection conn1 = new MySqlConnection("DataSource='localhost';Database='test';UserID='user1';Password='pwd123';PORT=3306;logging=true"))
{
conn1.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "INSERT INTO tblbank VALUES(100,'test','test','y')";
cmd.ExecuteNonQuery();
using (MySqlConnection conn2 = new MySqlConnection("DataSource='192.168.2.102';Database='test';UserID='user1';Password='pwd123';PORT=3306;logging=true"))
{
conn2.Open();
MySqlCommand cmd2 = new MySqlCommand();
cmd2.Connection = conn2;
cmd2.CommandText = "INSERT INTO tblbank VALUES(200,'test','test','y')";
cmd2.ExecuteNonQuery();
}
}
ts.Complete();
ts.Dispose();
}
}
catch (TransactionAbortedException ex)
{
Console.Out.WriteLine("TransactionAbortedException Message: " + ex.Message + "\n" + ex.StackTrace);
}
catch (ApplicationException ex)
{
Console.Out.WriteLine("ApplicationException Message: " + ex.Message + "\n" + ex.StackTrace);
}
Description: Dear All, I am using mysql version 5.0.67 innodb and mysql-connector-net-5.2.3. I have an application where I have to update local db then one central db and one copy of local db where the latter are both on a remote server. The problem as we dont have support for distributed transaction I will do separate commit first local db then centraldb followed by copy of local db. The problem here is that I notice for some of the commit done on the remote server is fully committed but when I check the database the update is not there as it is suppose to be. So if you say my application is buggy then it should be working for any of the commits rite. This case only happens for very rare cases. I have attached my log file. Looking at the lines below both have run but the updates are not in the db as it suppose to be. 3929 Query Update tblProduct Set tblProduct.productTotalStock = 2, tblProduct.productPrice = 13, tblProduct.productTotalAmount = 26 Where tblProduct.productID=3189 3930 Query Update tblProduct Set tblProduct.branch3TotalStock = 2, tblProduct.branch3TotalAmount = 26 Where tblProduct.productID=3189 How to repeat: Difficult not repeat as it does not happen always very rare cases.