| Bug #43507 | Transaction in C# | ||
|---|---|---|---|
| Submitted: | 9 Mar 2009 15:58 | Modified: | 12 Mar 2009 14:48 |
| Reporter: | R Roberts | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 5.2.1 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[10 Mar 2009 10:23]
R Roberts
Hi
I even tried Sveta's example ages ago directly on the MySQL community Edition:
SET AUTOCOMMIT = 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING rollback;
start transaction;
DELETE FROM TEST1 WHERE NAME = 'ISSAC';
INSERT INTO TEST1X VALUES ('DIARMUID');
commit;
end
Now the TEST1X table doesn't exist so therefore it shouldn't explicitly delete the 'ISSAC' row and do a rollback on that DELETE statement but it does which just boggles me. Then I tried with AUTOCOMMIT to 1 and still doesn't work.
So in theory if I had INSERTS in the WHILE loop and I don't think that will either rollback on that as well.
Thanks
Romy
[10 Mar 2009 10:47]
Tonci Grgin
Hi Romy. First, I do not expect example from Sveta to work like you want it to as there is no error handler in stored procedure. Next, you might be hitting transaction-related server bugs too, see Bug#36326 for example. Finally, there are multitude of examples posted in BugsDB: Bug#27211, Bug #35330 (in general, Dennis and Vlad post good samples), tricks like in Bug#26754, Bug#42493 and so on.
[10 Mar 2009 11:20]
R Roberts
Hi I have read your comments. Firstly you stated "I do not expect example from Sveta to work like you want it to as there is no error handler in stored procedure." - I am not sure what you mean cos I thought I put the DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING rollback; - so really it should rollback on the DELETE statement to restore the row. But it doesn't. I have read other bugs notes but it doesn't show which examples is correct to use in C# for transactions. I know how busy you are BUT I could do really with a full working example that can work with DIRECT SQL, stored procs and XSD Datasets please. I have spent several hours this morning looking on the internet to find examples whether it can work in C# or I would have move everything to stored procs on the MySQL which is why I used Sveta's example to ensure it does work directly on MySQL Community Edition. Please can you help me on this as I really need to make this project work. Thanks for your efforts and time on this; really appreciate it. I am just new to MySQL and C#. Romy
[10 Mar 2009 11:40]
R Roberts
OK, I can see the connection needs to be closed before the rollback or commit is used.
I am still not sure whether to use transactionscope or the standard MySqlTransaction.
However, are you saying this is the approach??:
using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Required, topts))
{
try
{
MySqlHelper.ExecuteNonQuery(strconn,"INSERT INTO TEST1 VALUES ('DARMUID'));
//myTransaction.Rollback();
}
catch (MySqlException TransException)
{
try
{
tscope.Dispose();
MessageBox.Show("Transaction rollback!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (MySqlException RollbackException)
{
tscope.Dispose();
MessageBox.Show("Failed to rollback transaction:" + RollbackException.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
tscope.Complete(); //<-- this means COMMIT?
}
I am guessing it doesn't need or require a MySqlConnection AND MySqlCommand??
Thanks
Romy
[10 Mar 2009 11:47]
R Roberts
Here is my example which I borrowed off Dennis's bug notes:
DatabaseConn db = new DatabaseConn();
using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(5)))
{
try
{
MySqlHelper.ExecuteNonQuery(db.strConn,"INSERT INTO TEST1 VALUES ('DIARMUID')");
//myTransaction.Rollback();
}
catch (MySqlException TransException)
{
try
{
tscope.Dispose();
MessageBox.Show("Transaction rollback!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (MySqlException RollbackException)
{
tscope.Dispose();
MessageBox.Show("Failed to rollback transaction:" + RollbackException.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
tscope.Complete();
}
It didn't work, it either commit or rollback.
I have put AUTOCOMMIT to zero and the table is INNODB.
Thanks
Romy
[10 Mar 2009 12:19]
Tonci Grgin
Romy, I really shouldn't be teaching you how to write code but as I posted my test case for bug reported by Dennis publicly I see no harm in repeating it here. I just tested against MySQL server 5.1.30GA and latest c/NET 5.2 sources on my Win2K8 box. Everything works.
--<cut>--
Workaround is rather simple, replacing TimeSpan.FromSeconds(X) with
appropriate value of X (ie. time enough for transaction to finish, in my case ~10-15 seconds).
Test case:
MySqlConnection conn = new
MySqlConnection("DataSource=localhost;Database=test;UserID=root;Password=**;PORT=**;logging=true");
conn.Open();
MySqlCommand cmdsetup = new MySqlCommand("DROP TABLE IF EXISTS
bug35330", conn);
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "CREATE TABLE bug35330(Id int(10)
unsigned NOT NULL default '0', PRIMARY KEY (Id))ENGINE=InnoDB";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "SET autocommit = 0";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "SET sql_mode=TRADITIONAL";
cmdsetup.ExecuteNonQuery();
cmdsetup.Dispose();
conn.Close();
conn.Dispose();
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.RequiresNew,
TimeSpan.FromSeconds(5)))
{
for (int i = 0; i < 10000; i++)
{
MySqlHelper.ExecuteNonQuery("DataSource=localhost;Database=test;UserID=root;Password=**;PORT=**;logging=true",
String.Format("INSERT INTO bug35330 VALUES({0})", i));
}
}
--<cut>--
Using ts.Complete(); here will COMMIT
and using ts.Dispose(); here will ROLLBACK
Not using anything will ROLLBACK by default as shown below:
[10.3.2009 13:14:37] - Executing command QUERY with text ='INSERT INTO bug35330 VALUES(9998)'
[10.3.2009 13:14:37] - Executing command QUERY with text ='INSERT INTO bug35330 VALUES(9999)'
[10.3.2009 13:14:37] - Executing command QUERY with text ='ROLLBACK'
Server version: 5.1.30-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> select * from bug35330;
Empty set (0.00 sec)
[10 Mar 2009 12:41]
R Roberts
Sorry I wasn't asking you teach me the C# code, but I just couldn't figure out whereabouts the C# Code was not working.
However I tried your exact coding:
DatabaseConn db = new DatabaseConn();
MySqlConnection conn = new MySqlConnection(db.strConn);
conn.Open();
MySqlCommand cmdsetup = new MySqlCommand("DROP TABLE IF EXISTS bug35330", conn);
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "CREATE TABLE bug35330(Id int(10) unsigned NOT NULL default '0', PRIMARY KEY (Id))ENGINE=InnoDB";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "SET autocommit = 0";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "SET sql_mode=TRADITIONAL";
cmdsetup.ExecuteNonQuery();
cmdsetup.Dispose();
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew,TimeSpan.FromSeconds(60)))
{
for (int i = 0; i < 50; i++)
{
MySqlHelper.ExecuteNonQuery(conn, String.Format("INSERT INTO bug35330 VALUES({0})", i));
}
ts.Complete();
}
conn.Close();
conn.Dispose();
Unfortunately it didn't work, it did create the database, but no inserts was added in the table on the MySQL.
I am after all using MySqlConnector 5.2.1 and MySQL Yog Community Edition v6.16
I didn't put PORT and LOGGING=TRUE in the app.config connection string.
server=xxx.xxx.xxx.xx;user id=xxxx;Password=xxxxxx;database=xxxxxx;connect timeout=60000; auto enlist=false;pooling=false;Allow Zero Datetime=true;use procedure bodies=True;use old syntax=false;allow user variables=true
So I am not sure why your coding did not work... :-(
Something is wrong...
Thanks
Romy
[10 Mar 2009 12:45]
R Roberts
and using MySQL 5.0 (5.0.51a-community-nt) Does this shed any light as to why it is not working. Thanks Romy
[10 Mar 2009 12:47]
Tonci Grgin
Romy. I do not know why this test case does not work in your environment nor am I allowed to log into it and debug. All I can say is for you to try latest c/NET 5.2 version available as bugs against old versions are not acceptable in BugsDB. You can only ask for old version to work for your particular case if you have support contract... I see no bug here as my test case is rather old and it worked then just as it is working now (I changed two boxes in the meantime).
[10 Mar 2009 14:07]
R Roberts
I have downloaded SQL Connector/NET 5.2.5 - that doesn't work at all with VS2005. It seems 5.2.1 is only compatible. I am not sure what I am missing.... are you using VS2008?? Thanks
[10 Mar 2009 14:10]
R Roberts
I have used SQL Connector/NET 5.2.5 and it is now working with VS2005 but it doesn't work at all with transactions (your code). Thanks
[10 Mar 2009 14:58]
R Roberts
OK, I have tried MySQLConnector 5.2.5 and 5.2.1 but it breaks my XSD as it won't open up... and transactions won't either. Do you think it is my MySQL Server 5.0 or SQL Yog v6.16??
[10 Mar 2009 16:14]
R Roberts
Hello? Sorry to bother you. Can I really ask just one more question please? Thanks Romy
[10 Mar 2009 16:17]
Tonci Grgin
Romy, closing this report was just a sign I don't see a bug here. You are free to ask questions as I'm monitoring the report anyways, closed or not. Shoot :-)
[10 Mar 2009 16:44]
R Roberts
OK I know I am a pain in the neck all the time, I really thought I could get this thing to work based on your coding but I just cannot figure it out.
I am using:
Windows XP
VS2005 – C#
MySQL Connector Net 5.2.1
MySQL Server 5.1
SQL Community Edition v 6.16
Tables are in INNODB format and I have set AUTOCOMMIT to OFF
I have used MySqlTransaction and Transactionscope in C# but it doesn’t work, it is Tonci’s example and yet I think when I use the :
cmdsetup.CommandText = "SET autocommit = 0;";
cmdsetup.ExecuteNonQuery();
It doesn’t work at that point as it doesn’t set the autocommit =0 at all.
I have tried using MySQL Connector Net 5.2.3 and MySQL Connector Net 5.2.5 but messes up my dataset as it has an error on valid datatype etc. So back to MySQL Connector Net 5.2.1
I know you are not supposed to help, but I have tried every venue on the forum and getting no response on this.
I really could do with some help...please please.
Thanks
Romy
[10 Mar 2009 17:00]
R Roberts
When I downloaded the MySQL Server 5.1, I installed it selecting multi database and not transaction database etc. Is there a correct way of selecting the right options on the MySQL Server 5.1?? Or is it just down to the MySQL Connector/Net 5.2.1 being the cause of the problem?? I noticed when I set AUTOCOMMIT = 1 it does work, but it won't do the rollback etc.
[10 Mar 2009 17:01]
Tonci Grgin
Romy: > I know you are not supposed to help Yes this is true, but then again if I wasn't willing to help people I would not have been doing this job. Now, I can not remember exactly but Dennis reported Bug#35330 in March 2008 and I'm sure I worked on XP Pro SP2 in 17 Jun 2008 when I took over. Which version was actual on my box then I don't know but knowing 5.2.1 was released on Feb 27th I guess it could have been 5.2.2 at most. I also use VS2005Pro but will switch to VS2008 shortly. As for cmdsetup.CommandText = "SET autocommit = 0;"; cmdsetup.ExecuteNonQuery(); it's easy to check. Put breakpoint after cmdsetup.ExecuteNonQuery(); and check server variables, or echo autocommit valiable in your code (issue query like this one "select @@autocommit"). This might actually be a good idea in your case. Also, add logging option to your code and monitor the output. Havin MySQL server log turned on might help too. Finally, I do not know what could be possibly wrong in your environment as pasted code worked both for Dennis and me. We had problem regarding what's happening after transaction has expired, not if it works... Sorry I couldn't be of more help here. If I have time, I'll test some more transaction code I have (and which used to work for sure) but I don't think it'll help until you find out what's wrong in your environment...
[10 Mar 2009 17:09]
R Roberts
OK, will do this tonight and check it out and will let you know the results based on all your following suggestions. Will you be able to monitor this case tomorrow as well? I just worry too much that the case will be forgotten and I would be back to square 1. Thanks - really do appreciate your feedback on this. Romy
[10 Mar 2009 17:13]
Tonci Grgin
Romy, your worries are well founded as I alone work full time on BugsDB and *all* connectors... If I do forget, ping me by posting note to bug and I'll check. Some answers: > When I downloaded the MySQL Server 5.1, I installed it selecting multi database and not transaction database etc. Is there a correct way of selecting the right options on the MySQL Server 5.1?? This is neither good nor bad, depending on use-case, but definitely not the culprit in your case. > Or is it just down to the MySQL Connector/Net 5.2.1 being the cause of the problem?? Sorry, works for most of us. > I noticed when I set AUTOCOMMIT = 1 it does work, but it won't do the rollback etc. What works when you set AUTOCOMMIT to 1? The idea of AUTOCOMMIT=1 *is* in treating each command as separate transaction and committing as soon as command is done. So ROLLBACK will *never* work with AUTOCOMMIT=1, expected and documented.
[10 Mar 2009 18:59]
R Roberts
Right, I have rebooted my machine. I then put in my app.config - logging=true On the separate machine, I have the MySQL Yog database machine on there. I have set the autocommit to 1 on the actual database called Romy. On my local machine, I have the VS2005, MySQL Connector 5.2.1 and MySQL Server 5.1 on there. I have stopped all services for SQL Server 2005 which is also on my local machine. I have McAfee running on that machine as well. I then put breakpoint after cmdsetup.ExecuteNonQuery(); and checked on the MySQL yog database using SELECT @@AUTOCOMMIT and it turns out it is still showing as 1 and it is not changing to 0. I have VS2008 but I don't use it as it didn't work with my VS2005 project. When I put SET AUTOCOMMIT = 1 for the commandtext in C#, I found it does INSERT the rows of your test program. When I changed it to ts.dispose (rollback) and it doesn't rollback at all. So I am thinking something is wrong with the SET AUTOCOMMIT = 0 in C# and it can't seem to update it on the different machine but it does create the table according to your code. Unless the transactionscope doesn't work with AUTOCOMMIT=0. Does this shed any light? :-( Romy
[10 Mar 2009 19:22]
Tonci Grgin
Romy, transaction scope works great with AUTOCOMMIT as I tested again today before posting sample (several times). Things could be connected to SQL_MODE (thus I put "TRADITIONAL" in my sample!) so check that: http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html
[10 Mar 2009 20:53]
R Roberts
I don't understand how it could work on yours. So somewhere between MySQLConnector 5.2.1 to my separate machine is breaking on AUTOCOMMIT and SQL_MODE. Unless it is the app.config connection string that is causing it. I mean it can't be McAfee virus checker or whether the app.config connection string needs port number?? I have tried uninstalling and installing MySQL Connector/NET 5.2.1 and MySQL Server 5.1 few times and it made no difference.
[11 Mar 2009 8:53]
R Roberts
Hi I had another look and wondered if it is the way I have installed the components wrongly: local machine - VS2005, MySQL Connector/Net 5.2.1, MySQL Server 5.1 separate machine - SQL Yog, MySQL Server 5.1 - both of them held on the D:\ Drive I have noted down the configuration settings of MySQL Server 5.1: clicked "detailed configuration" clicked "deverloper machine" clicked "multifunctional database" clicked "Decision Support (DSS/OLAP)" clicked "Enable TCP/IP" clicked "Enable Strict Mode" clicked "Allow Standard character set" clicked "install as window service" Does this give any ideas out? Thanks
[11 Mar 2009 9:09]
Tonci Grgin
I might be missing something but I really can't see anything wrong with settings posted...
Try command line client test (with same account you use in code) and see if it works:
mysql> select @@SQL_MODE;
+----------------------------------------------------------------+
| @@SQL_MODE |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@SQL_MODE;
+-------------------------------------------------------------------------------
------------------------------------------------+
| @@SQL_MODE
|
+-------------------------------------------------------------------------------
------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_D
IVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
> I had another look and wondered if it is the way I have installed the components wrongly:
Hardly possible. Btw I do not use Yog nor do I know what it is but I don't think it has anything to do with the problem. For the sake of testing you might try uninstalling it.
As long as "multifunctional database", or any other option having transactional engines included, is clicked you should be fine.
Apart from all this, I have no clue what's happening.
My best guess is:
STRICT_TRANS_TABLES is in SQL_MODE
InnoDB is disabled and/or tables used in test are made with non-transactional engine (like MyISAM). Do show create table and check...
Something else is (also) messed up. You should be able to find out what. Do an install on a clean box (or even better a VM) and check.
[11 Mar 2009 9:47]
R Roberts
Hi I found that MySQL Server 5.1 doesn't work with SQL Yog community enterprise database very well and gives an unhandled win exception etc. So I have had to uninstall the MySQL Server 5.1 and installed the MySQL Server 5.0. I have also uninstalled SQL Yog community enterprise database v6.16 now and re-installed it back again. So going to try your suggestions directly on the MySQL Command client now.. Thanks
[11 Mar 2009 10:23]
R Roberts
Here is the results from the MySQL Command client:
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select @@SQL_MODE;
+----------------------------------------------------------------+
| @@SQL_MODE |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET SQL_MODE=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@SQL_MODE;
+-------------------------------------------------------------------------------
------------------------------------------------+
| @@SQL_MODE
|
+-------------------------------------------------------------------------------
------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_D
IVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
1 row in set (0.01 sec)
mysql>
[11 Mar 2009 10:29]
R Roberts
strangely after the MySQL Command Client, I went to the SQL Yog enterprise database and opened the new query and put in SELECT @@AUTOCOMMIT; This was 1 SELECT @@SQL_MODE; This was empty
[11 Mar 2009 10:32]
Tonci Grgin
So move away from Yog and retest C# test case... If everything works file Yog bug report?
[11 Mar 2009 11:32]
R Roberts
Oh my gosh, what I did was I went to the MySQL Yog Enterprise database and did SET AUTOCOMMIT = 0; SET SQL_MODE = TRADITIONAL; and then retested the C# app without the : //cmdsetup.CommandText = "SET autocommit = 0;"; //cmdsetup.ExecuteNonQuery(); //cmdsetup.CommandText = "SET sql_mode=TRADITIONAL"; //cmdsetup.ExecuteNonQuery(); So it worked populated all the records with ts.Complete(). Then with ts.Dispose(), it didn't insert the records. So it looks like MySqlCommand doesn't work in C# using SET autocommit = 0;" and SET sql_mode=TRADITIONAL which is very weird. I am not sure why.. So if I am right, that setting autocommit = 0 will do commit and rollback and not autocommit = 1. So I will have to add that to my stored procs as well. Will this work also with XSD dataset as well i.e. tableadapter from XSD?? if I put it in the transactionscope?? I will report a YOG bug or is it MySqlCommand bug? Do you know which is best forum to post this bug?? Thanks
[11 Mar 2009 11:33]
R Roberts
It won't work with MYISAM?? So all tables have to be INNODB? Thanks
[11 Mar 2009 12:14]
R Roberts
Tried it a few times - it stopped working :-( It worked first time...but now re-testing few times - it stopped working... just strange
[11 Mar 2009 12:33]
R Roberts
OK it does work but only twice respectively for Complete and Dispose. But after that it does stop working. The only way to make it working properly again, is to manually delete the table via Yog Enterprise database. Not sure what is happening there.... Thanks
[11 Mar 2009 12:59]
R Roberts
Hello? I found by debugging on the third time to see why it was not working after the 3rd attempt - on the ts.complete or ts.commit() shows this error: Transaction has already been committed or is not pending What does this mean? does something needs changing?? Thanks
[11 Mar 2009 14:03]
R Roberts
Hello again Sorry if I am harrassing you too much. I have tried several techniques based on the bugs report you gave me. It doesn't work after 3rd attempt which I am still not sure why. But I noticed if I commit, it works, and then if I put rollback, it works, and then if I put commit - it doesn't work. So it looks like something is happening after the rollback stage which is not allowing repeated tests to work properly. Romy
[11 Mar 2009 14:19]
Tonci Grgin
Romy, no you are not harassing me :) I hope other reporters will wait patiently. Now, [11 Mar 12:32] Will this work also with XSD dataset as well i.e. tableadapter from XSD?? if I put it in the transactionscope?? It should afaik. I will report a YOG bug or is it MySqlCommand bug? Do you know which is best forum to post this bug?? There is no bug in MySQL command and I don't know what Yog is, sorry for my ignorance. [11 Mar 12:33] It won't work with MYISAM?? So all tables have to be INNODB? Of course it wont! Check 'STRICT_TRANS_TABLES' present in SQL MODE. Anyways, how did you think non-transactional database engine like MyISAM will do transactions? [11 Mar 13:59] I found by debugging on the third time to see why it was not working after the 3rd attempt - on the ts.complete or ts.commit() shows this error: Transaction has already been committed or is not pending What does this mean? does something needs changing?? It means "inTransaction" property has remained set or there is a bug in your code or both. As for "inTransaction", see Bug#41664, for example. As for QCache problem related to transactions, see Bug#36326. And stop using me as search engine. [11 Mar 15:03] I have tried several techniques based on the bugs report you gave me. It doesn't work after 3rd attempt which I am still not sure why. But I noticed if I commit, it works, and then if I put rollback, it works, and then if I put commit - it doesn't work. So it looks like something is happening after the rollback stage which is not allowing repeated tests to work properly. See above.
[11 Mar 2009 16:32]
R Roberts
Sorry there is no MySQL expert in our office. However I have attached the latest testapp app for your perusal. So far I have turned off the query_cache_type = OFF and have_query_cache is always YES - this cannot be turned off. query_cache_size = 0 as usual. I am not sure I understand about the intransactions stuff. I am not an expert on this. But I have noticed the MySQL server hangs after two transactions is made on the test app. So when I restarted the MySQL Server and refresh the bug35330 table, it shows the records correctly. Just wondered if my test app works on yours or perhaps it needs something to kick start the transactions or MySQL server?? Thanks
[11 Mar 2009 16:33]
R Roberts
Bug35330
Attachment: TESTAPPBUG35330.zip (application/x-zip-compressed, text), 39.85 KiB.
[11 Mar 2009 17:08]
Tonci Grgin
Romy, can I make test case public? We do need to generate some added value for all here...
[11 Mar 2009 17:11]
R Roberts
Yes you can make it public as long as my connection string is not showing, I think I have omitted it. Please let me know if there is any remedy to this case i.e I am pretty sure the MySQL hangs after the 3rd attempt on transaction in C# or whether transaction hangs on it. I have really read your QCache and turned it off on MySQL Yog. But the in Transaction is quite out of my depth. It doesn't say where to fix it in the code. Romy
[11 Mar 2009 17:27]
Tonci Grgin
Romy, test case made public as you've hidden sensitive data. Also, can you try your code on MySQL server 5.0.78, 5.1.32 or 6.0.10(preferably) cause of Bug#36326 and inform me of result?
[11 Mar 2009 18:56]
R Roberts
Sorry I cannot find 5.0.78, and 6.0.10 download zip files on the www.MySQL.com website. I have downloaded the latest SQL YOG Community Enterprise database. Romy
[12 Mar 2009 7:39]
Tonci Grgin
There is 5.1.32 present in downloads. You can always build too.
[12 Mar 2009 9:25]
R Roberts
unfortunately I don't know how to build 5.0.78 and 6.0.10. But I installed 5.1.32 and kept getting unhandled win32 exception when I try to click on one of the stored procs folder. I tried 6.0.9 alpha and it is the same issue as well. It was the same with SQL Yog v6.16 and v8.0 as well when trying both versions of MySQL Server.
[12 Mar 2009 9:54]
R Roberts
OK, I am back to MySQL Server 6.0.9 alpha and having to re-add the stored procs as workaround to avoid the unhandled win32 exception. Will let you know the results on those versions on transactions...
[12 Mar 2009 10:19]
R Roberts
Here are the same results for each MySQL Server 5.0.77, 6.0.9 alpha and 5.1.32:
It works on two attempts with transaction in C#, but fails on the third attempt.
On the third attempt, I debugged through each command ExecuteNonQuery.
MySqlCommand cmdsetup = new MySqlCommand("DROP TABLE IF EXISTS `bug35330`", db.conn);
cmdsetup.ExecuteNonQuery(); <----this gives a value of 0
cmdsetup.CommandText = "CREATE TABLE IF NOT EXISTS `bug35330`(Id int(10) unsigned NOT NULL default '0', PRIMARY KEY (Id))ENGINE=InnoDB;";
cmdsetup.ExecuteNonQuery(); <----this gives a value of 0
MySqlHelper.ExecuteNonQuery(db.conn, String.Format("INSERT INTO bug35330 VALUES({0});", i)); <---- Duplicate primary key which thinks the table is already there... when clearly we used a DROP command.
When I restarted MySQL Server 6.0 via services, the table refreshes with the correct records.
Does this shed any light on this?
[12 Mar 2009 12:58]
R Roberts
OK, after playing around it turns out if you are using ts.complete, you need to have autocommit=1 if you want to commit a record. But if you are using ts.dispose, you need to have autocommit=0 if you want to rollback a record.
Not sure why, but it is a workaround to avoid the problems of whether it is transaction hanging or MySQLCommand hangs etc if you keep the default autocommit=0 as always.
However when using
MySqlCommand cmdsetup = new MySqlCommand();
cmdsetup.Connection = conn;
cmdsetup.CommandText = "set autocommit=0";
cmdsetup.CommandType = CommandType.Text;
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "set sql_mode=TRADITIONAL";
cmdsetup.CommandType = CommandType.Text;
cmdsetup.ExecuteNonQuery();
conn.Close();
conn.Dispose();
This doesn't update database for some reason.
Tried in stored proc and call it directly on the C# application and this didn't work. so stumped now...
Not a brilliant solution for the above, unless you know why and what better approach could be.
Thanks
[12 Mar 2009 13:45]
Tonci Grgin
Romy, this is hopeless...
You use some flavor of MySQL I've never heard of and fail to read what I post...
*If you do not use* ts.Complete() transaction on *transactional database engine* will be rolled-back *if not* AUTOCOMMIT=1 (as then each command is it's own transaction and committed automatically) as *NO ts.Complete()*==ts.Dispose()...
ts.Dispose() is same as ROLLBACK command if AUTOCOMMIT <> 1....
All of following makes no sense:
However when using
MySqlCommand cmdsetup = new MySqlCommand();
cmdsetup.Connection = conn;
cmdsetup.CommandText = "set autocommit=0";
cmdsetup.CommandType = CommandType.Text;
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "set sql_mode=TRADITIONAL";
cmdsetup.CommandType = CommandType.Text;
cmdsetup.ExecuteNonQuery();
conn.Close();
conn.Dispose();
This doesn't update database for some reason.
There is no command text, no transaction, nothing...
[12 Mar 2009 14:48]
R Roberts
Yes you are right it is hopeless. :-)
It looks like with my project I cannot use transactions due to the ongoing problems I have at the moment.
I understood what you said about ts.dispose and ts.complete in terms of autocommit being set. But it doesn't work at all with my C# Winform app - well only after 2 attempts when autocommit is zero. On the 3rd attempt it fails miserably. Then I found it only works when I use auto commit = 1 for ts.complete and auto commit = 0 for ts.dispose; doing it several times, nothing hung up.
I have replicated the same issue across all server versions 5.0.77, 5.1.32 and finally 6.0.9 alpha.
Upgrading my SQL Yog did not make a difference.
Yet:
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead;
options.Timeout = new TimeSpan(0, 0, 60);
DatabaseConn db = new DatabaseConn();
MySqlConnection conn = new MySqlConnection(db.strConn);
conn.Open();
MySqlCommand cmdsetup = new MySqlCommand("DROP TABLE IF EXISTS `bug35330`", db.conn);
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "CREATE TABLE IF NOT EXISTS `bug35330`(Id int(10) unsigned NOT NULL default '0', PRIMARY KEY (Id))ENGINE=InnoDB;";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "set autocommit=0";
cmdsetup.ExecuteNonQuery();
cmdsetup.CommandText = "set sql_mode=TRADITIONAL";
cmdsetup.ExecuteNonQuery();
conn.Close();
conn.Dispose();
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew, options))
{
for (int i = 0; i < 50; i++)
{
//SQLTEXT = "INSERT INTO bug35330 VALUES(" + i + ");";
//MySqlHelper.ExecuteNonQuery(db.conn, SQLTEXT);
MySqlHelper.ExecuteNonQuery(db.conn, String.Format("INSERT INTO bug35330 VALUES({0});", i));
//cmdsetup.CommandText = String.Format("INSERT INTO bug35330 VALUES({0})", i);
//cmdsetup.ExecuteNonQuery();
}
MySqlHelper.ExecuteNonQuery(db.conn, String.Format("INSERT INTO bug35330 VALUES({0});", 100));
ts.Complete();
db.conn.Close();
}
where I have set up set autocommit=0 and set sql_mode=TRADITIONAL as above in the code - this never does update the database which is really bizarre. I have tried doing it via stored procs - that didn't work either.
So it really throws the transactions concept out of the window!
I really need a glass of wine to calm myself down. :-)
[13 Mar 2009 7:35]
Tonci Grgin
Romy, I will try and look into your test case today. One correction about my previous comment ([12 Mar 14:45] Tonci Grgin) and working test case. A *using* block will call Dispose() and therefore Close() on it's controlling object thus *not* calling ts.Complete() will call ts.Dispose() by default behavior of *using* block not the transaction scope. Sorry if I wasn't clear enough.
[20 Mar 2009 7:08]
Tonci Grgin
Will have to come back to this next week, too many new reports coming in.
[14 Nov 2012 11:41]
Kristoffer Danielsson
I'm seeing this exact error in .NET connector 6.6.4 too: http://bugs.mysql.com/bug.php?id=67560 R Roberts, did you solve this?
Description: Hi A lot of my bugs have been closed down on transaction issue. I have spent four to five months in vain in trying to get the transaction to work with MySQL 5.0 and MySQL/Connector Net 5.2.1 in VS 2005. I still cannnot get it to work! I am using INNODB and AUTOCOMMIT is turned off. I have also posted on the forum and due to the lack of response is really frustrating me because there is such scant information and even on the manual documentation, I have followed it to the dot and it still won't work. Please please can you give us a working example of it using XSD datasets and also manual MySqlCommand with Insert command. I need my project to move forward.... Thanks Romy How to repeat: try { DatabaseConn db = new DatabaseConn(); MySqlConnection myConn = new MySqlConnection(db.strConn); MySqlCommand cmdCreateTable = myConn.CreateCommand(); MySqlTransaction myTransaction = null; try { myConn.Open(); myTransaction = myConn.BeginTransaction(); cmdCreateTable.Transaction = myTransaction; cmdCreateTable.CommandText = ("INSERT INTO TEST1 VALUES ('DARMUID');"); cmdCreateTable.ExecuteNonQuery(); myTransaction.Commit(); //myTransaction.Rollback(); } catch (MySqlException TransException) { try { myTransaction.Rollback(); MessageBox.Show("Transaction rollback!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } catch (MySqlException RollbackException) { myTransaction.Rollback(); MessageBox.Show("Failed to rollback transaction:" + RollbackException.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } myConn.Close(); db.conn.Close(); } catch (TransactionException) { MessageBox.Show("Transaction aborted!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }