| Bug #36326 | nested transaction and select | ||
|---|---|---|---|
| Submitted: | 24 Apr 2008 20:55 | Modified: | 6 Feb 2009 20:59 |
| Reporter: | Renaud Paquay | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | 5.0.54 | OS: | Windows (Vista) |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | nested, REPEATABLE-READ, SELECT, transaction | ||
[17 Jul 2008 23:03]
Vlad Untu
I tried your test case but on my machine it seems to work. But, I have the same in some weird situations. I investigated the source code of the connector and I also saw that the server reports ServerState.InTransaction after a select statement. The server reports InTransaction but the server is not in transaction.
[18 Jul 2008 7:34]
Renaud Paquay
which version did you use in order to test the problem ? I suppose you agree it's a strange behaviour... I had to find a workaround to this problem and the only thing I found was to create a dummy record and to delete it before beginning any transaction. I'm really disappointed that you cannot reproduce the problem. I would like to help you in any way as I would really need a fix to this problem ! I can test different os and different connector versions... Thank you in advance, Renaud
[18 Jul 2008 21:26]
Reggie Burnett
Renaud I can't seem to reproduce this. Can you send me your connection string (sans password of course) and your server settings?
[22 Jul 2008 7:01]
Renaud Paquay
Hello,
Here's my connection string :
<connectionStrings>
<add name="MyNameSpace.Properties.Settings.MyConnectionString"
connectionString="server=localhost;user id=xxx;database=zzz"
providerName="MySql.Data.MySqlClient" />
</connectionStrings>
What would you like to know exactly about my Server ? Tell me an I'll find what you excpect for.
This is a standard WAMP5 installation on a Vista Ultimate.
My database is a latin1_swedish_ci innodb one.
Regards,
Renaud
[8 Aug 2008 8:58]
Vlad Untu
The problem seems to be within the server. The problem occurs (at least where I tested) on Windows Vista w/ MySql 5.0.41-community-nt-log and on Debian Linux w/ 5.0.32-Dotdeb_1.dotdeb.1-log. The thing is: 1) Nested transactions are not supported is thrown systematicaly. 2) Restart the server and run the same code. 3) No exception is thrown. 4) Work sometime w/ the software and again the exception but this time in another place. 5) Restart the server and run the same code. 6) and so on. The conclusion is: after you restart the server the problem no longer occurs.
[11 Aug 2008 13:40]
Renaud Paquay
Hello, Thank you for these accurate tests. I'll test and let you know what's happening on my side. Regards, Renaud
[19 Aug 2008 20:04]
Renaud Paquay
Hello, I have been testing on a 5.0.51b and 5.0.45 mysql community server. You said that the problem is server side. Did you try with a .net application or with the mysql command line client ? I cannot reproduce your behaviour nor under a .net application nor with the command line client. How did you proceed exactly ? Regards,
[21 Dec 2008 15:33]
Pavel Bazanov
I could easily reproduce the problem. MySQL Server v6.0.2 Connector/Net 5.2.5 Also, today I have posted the following bug report: http://bugs.mysql.com/bug.php?id=41664 which seems to have the same problem. In this bug report (36326) ExecuteReader() also changes ServerStatus to InTransaction.
[2 Jan 2009 12:22]
Bogdan Degtyariov
this seems to be the server bug triggered by query_cache_size option. Removing query_cache_size option from my.cnf/my.ini solves the problem. Also if use query_cache_type = 2 (CACHE ON DEMAND = Cache only SELECT SQL_CACHE ... queries.)
[5 Jan 2009 2:42]
Bogdan Degtyariov
Test case that shows [InTransaction] server status for queries from the query cache (the status before query is [AutoCommit])
Attachment: _issue32123d.c (text/plain), 2.70 KiB.
[5 Jan 2009 2:48]
Bogdan Degtyariov
It is the Query cache problem. When execute a SELECT query from within a transaction it is cached with [InTransaction] status. So, even though the new transaction is not started (status is [AutoCommitMode]) executing EXACTLY the same query from the cache will add [InTransaction] to the server status.
[12 Jan 2009 14:33]
Davi Arnaut
Indeed, its a query cache bug. The cache stores the server_status together with the entire packet and serves it back if there is a cache hit. This means that the server status sent when serving from the query cache is that of the time when the statement was cached and not the actual server status.
[12 Jan 2009 17:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/63017 2728 Davi Arnaut 2009-01-12 Bug#36326: nested transaction and select The problem is that the query cache stores packets containing the server status of the time when the cached statement was run. This might lead to a wrong transaction status in the client side if a statement is cached during a transaction and is later served outside a transaction context (and vice-versa). The solution is to take into account the transaction status when storing and serving statement in and from the query cache.
[13 Jan 2009 22:07]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/63183 2728 Davi Arnaut 2009-01-13 Bug#36326: nested transaction and select The problem is that the query cache stores packets containing the server status of the time when the cached statement was run. This might lead to a wrong transaction status in the client side if a statement is cached during a transaction and is later served outside a transaction context (and vice-versa). The solution is to take into account the transaction status when storing in and serving from the query cache.
[15 Jan 2009 9:53]
Davi Arnaut
Queued to 5.0-bugteam
[15 Jan 2009 13:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/63359 2738 Davi Arnaut 2009-01-15 Post-merge fix for Bug#36326
[20 Jan 2009 18:55]
Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:timothy.smith@sun.com-20090116165151-xtp5e4z6qsmxyvy0) (merge vers: 6.0.10-alpha) (pib:6)
[31 Jan 2009 17:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64734 2765 Davi Arnaut 2009-01-31 Post-merge fix for Bug#36326
[3 Feb 2009 9:11]
Bugs System
Pushed into 5.0.78 (revid:joro@sun.com-20090203090422-v91rh3gmx2ulhbu9) (version source revid:chad@mysql.com-20090126173455-ia9503yxdqi30guh) (merge vers: 5.0.77) (pib:6)
[3 Feb 2009 9:40]
Bugs System
Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:azundris@mysql.com-20090202131035-zuf2kdg8u8r177fi) (merge vers: 5.1.32) (pib:6)
[4 Feb 2009 11:15]
Bugs System
Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:davi.arnaut@sun.com-20090131210048-1pnort096e2cgqgw) (merge vers: 6.0.10-alpha) (pib:6)
[6 Feb 2009 20:20]
Paul DuBois
Noted in 5.0.78, 5.1.32, 6.0.10 changelogs. The query cache stored packets containing the server status of the time when the cached statement was run. This might lead to an incorrect transaction status on the client side if a statement was cached during a transaction and later served outside a transaction context (or vice versa).
[17 Feb 2009 14:56]
Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:43]
Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:20]
Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090202111723-1zzwax187rtls913) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
Description: Sometimes my application needs to commit a repeatable-read transaction without executing any update or insert command. In this case, it throws a "Nested transactions are not supported" exception. I think my bug is the same as the #28264 one, but I can now reproduce it. The scenario is like the following : 1) repeatable read transaction 2) Begin 3) Select statement 4) Commit 5) Select statement 6) Begin Transaction --> nested transaction exception How to repeat: /////// This code is buggy //////// MySqlConnection conn = new MySqlConnection(builder.ConnectionString); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "DROP TABLE IF EXISTS `nested`"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE `nested` (`ID` DOUBLE NOT NULL PRIMARY KEY ,`Value` DOUBLE NOT NULL) ENGINE = innodb"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO `nested` (`ID`,`Value`) VALUES (1,1)"; cmd.ExecuteNonQuery(); MySqlTransaction trans = conn.BeginTransaction(); cmd.CommandText = "SELECT * FROM nested WHERE ID=1"; MySqlDataReader rd = cmd.ExecuteReader(); rd.Close(); trans.Commit(); cmd.CommandText = "SELECT * FROM nested WHERE ID=1"; rd = cmd.ExecuteReader(); rd.Close(); trans = conn.BeginTransaction(); /////////// This code reproduces the problem anytime.... If I try to insert a record between the first select statement and the commit, no exception will be thrown. ///// This code throws no exception ///////// MySqlConnection conn = new MySqlConnection(builder.ConnectionString); conn.Open(); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = conn; cmd.CommandText = "DROP TABLE IF EXISTS `nested`"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE `nested` (`ID` DOUBLE NOT NULL PRIMARY KEY ,`Value` DOUBLE NOT NULL) ENGINE = innodb"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO `nested` (`ID`,`Value`) VALUES (1,1)"; cmd.ExecuteNonQuery(); MySqlTransaction trans = conn.BeginTransaction(); cmd.CommandText = "SELECT * FROM nested WHERE ID=1"; MySqlDataReader rd = cmd.ExecuteReader(); rd.Close(); cmd.CommandText = "INSERT INTO `nested` (`ID`,`Value`) VALUES (2,1)"; cmd.ExecuteNonQuery(); trans.Commit(); cmd.CommandText = "SELECT * FROM nested WHERE ID=1"; rd = cmd.ExecuteReader(); rd.Close(); trans = conn.BeginTransaction(); /////////////////////// Suggested fix: I read the source code and I saw it has something to deal with the driver.ServerStatus which is in ServerStatusFlags.InTransaction status. It seems that this status is not updated when using readers ?? I don't know if it's possible or nonsense to update the status when a select statement is executed.