| Bug #27211 | Execution in a transaction conect (wihtout transaction) | ||
|---|---|---|---|
| Submitted: | 16 Mar 2007 13:14 | Modified: | 11 May 2007 16:04 |
| Reporter: | Luciano C | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.0.5 3/5/2007 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[9 Apr 2007 17:15]
Luciano C
Is there any plan to fix this? Is there any way to set the start/rollback/commit context in other way that calling Set commands directly? I mean, programmatically. If it is any way, i could hack this for you to fix it and to fix my internal problems too. Let me know.
[9 Apr 2007 17:58]
Tonci Grgin
Hi and thanks for your report. Please except my appologies for such a long delay. This report is next on my list and my part of work will be finished by the end of week. If there is a bug then we'll have to see what the connectors team has to say about fixing.
[10 Apr 2007 7:05]
Tonci Grgin
Hi.
I will set this to "Verified", although I think it's not a bug, and see what Reggie has to say but this is to be expected. Server starts transaction with given connection id, and both of your commands use the same one:
cmd1 = new MySqlCommand("INSERT INTO ...", Conn...
cmd2 = new MySqlCommand("INSERT INTO ...", Conn...
You will probably have to use another approach if you need code like this to work.
Reggie, will code like this ever function like reporter wants it to?
[10 Apr 2007 12:02]
Luciano C
Tonci: Thanks, maybe you are right, but i guess that if you have MySqlCommand with Transaction parameter, and you can set or not the transaction, the command could be and could not be executed under the same transaction, i guess that this approach is true under others connectors. I realize that it is a hard hack, but maybe it is the correct behavior, and yes, you are right, i can use another approach, that is another connection, but i guess that 2 channels open to the same database is useless when i can have commands within the same connection working. Thanks a lot. I wait for your message.
[10 May 2007 15:24]
Luciano C
Any hint or news about this?
[11 May 2007 16:04]
Reggie Burnett
This is expected behavior. If you attempt the same thing with Sql Server you will get an exception. Using Sql Server, when you execute the second command (the one without the transaction) you will get an exception. The text of the exception is this: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
[25 Feb 2008 7:44]
Tonci Grgin
A note has been added to manual clarifying this behavior.
Description: If i create a command associated to a transaction, and after that i create another commando, not associated to the transaction, executed the 2 commands, and rolled back the operation cause to rollback the 2 commands. In this case, if i don't assign the transaction object to one of the commands, it will not need to be transactional. How to repeat: using System; using MySql.Data.MySqlClient; namespace MySql.TestCase { class MySqlBug { static void Main () { MySqlConnection Conn = new MySqlConnection("...."); Conn.Open(); MySqlTransaction Tran = Conn.BeginTransaction(); MySqlCommand cmd1 = new MySqlCommand("INSERT INTO ...", Conn, Tran); MySqlCommand cmd2 = new MySqlCommand("INSERT INTO ...", Conn); cmd1.ExecuteNonQuery(); // This is in the transaction cmd2.ExecuteNonQuery(); // This is not under transaction // This rollback the 2 operations: cmd2 is not under // transaction context. Tran.Rollback(); Conn.Close(); } } } Suggested fix: The way of do this, in the MySql Connector code is to execute Set Transaction and Rollback and Commit command. It is needed another way, or it always be on this problem.