MySQL Bugs: #41664: ExecuteReader() sometimes changes driver.ServerStatus to InTransaction

Bug #41664	ExecuteReader() sometimes changes driver.ServerStatus to InTransaction
Submitted:	21 Dec 2008 15:11	Modified:	22 Jul 2009 8:22
Reporter:	Pavel Bazanov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	BeginTransaction, ExecuteReader, InTransaction, Nested transactions are not supported, serverStatus

Description:
Hello,
 We experience a serious bug once in a couple of weeks: 

Sometimes when we try to call BeginTransaction() we get exception "Nested transactions are not supported" thought there is no active transaction currently. 

Of course this is because driver.ServerStatus contains InTransaction flag.
During further investigation we found out that it is ExecuteReader() function that results in including InTransaction flag into ServerStatus enum. 

The queue is the following:
ExecuteReader() -> reader.NextResult() -> fields = driver.ReadColumnMetadata((int)fieldCount) -> NativeDriver.GetFieldMetadata(). I didn't try to go deeper because I didn't have much time.

A couple of times server restart helped. But other times it didn't help.

During further investigation we found out that a very simple change in a query makes ExecuteReader() behave differently (I mean to include or exclude InTransaction flag):

The query that made ExecuteReader() include InTransaction flag was:

"SELECT Price FROM manprice WHERE PartN = 'BLABLAPARTNUMBER' AND ManufacturerID = 24 AND SupplierID = 6"

But! If we simply change SupplierID to 29 ExecuteReader() does not include InTransaction flag, and even excludes it if it was previously set by the query you see above.

So, this query result in InTransaction flag being excluded from ServerStatus:

"SELECT Price FROM manprice WHERE PartN = 'BLABLAPARTNUMBER' AND ManufacturerID = 24 AND SupplierID = 29"

So, maybe the problem is somehow caused by locking mechanisms? It's just whatever first comes up to my mind...

Table `manprice` is InnoDB, but it doesn't have any foreign key constraints and its fields are not used anywhere as FK.

Also, the problem is repeatable for us in that sense if we catch it once, we can press a button in our software again and the exceptions appears again. Software restart doesn't change anything.

We also experienced this problem in 5.1.5. A couple of weeks ago our software could execute a function properly because of this, we tried to restart the server as before, but that time it didn't help. Then we tried to recompile software with Connector 5.2.5 and it helped. But today we experienced this problem again, a couple of times (with 5.2.5).

How to repeat:
This will not really help you, it's just to make a simple imagination how it looks in our software:

...
MySqlCommand commnad = new MySqlCommand("", connection);
command.CommandText = "SELECT Price FROM manprice WHERE PartN = 'BLABLAPARTNUMBER' AND ManufacturerID = 24 AND SupplierID = 6";
using(MySqlDataReader reader = command.ExecuteReader())
while(reader.Read())
{
...
}
...
MySqlCommand commnad = new MySqlCommand("", connection);
command.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); // <--- Exception here

But, as I said above, if we simple change the query to "SELECT Price FROM manprice WHERE PartN = 'BLABLAPARTNUMBER' AND ManufacturerID = 24 AND SupplierID = 29" - no exception occurrs.

A couple of weeks ago our software could NOT
execute a function properly because of this *** (misstyped)

PS. MySQL Server 6.0.2
PPS. The following bug report seems to have the same problem:
http://bugs.mysql.com/bug.php?id=36326
I've left a comment there.

Hi Pavel and thanks for your report. We are working on it.
You are right, many have similar problems but, so far, we were unable to repeat it. It could be related to Bug#26333 though.

Ok, I made some testing..

The exception throws on:
1) My home computer (MySQL Server 6.0.2, WinXP SP3)
2) Server in my company (6.0.2 also, WinXP SP3)

The exception doesn't throw on:
1) VMWare on my computer (6.0.2, WinXP SP3)
2) My friend's computer (MySQL Server 5.0.45, WinXP )

I made a simple testing app, maybe you will find it useful to save several minutes of your time:
http://bazanov.net/MySqlBugRepeater.zip

Also, I can host TeamViewer (teamviewer.com) on my computer and you will be able to connect to my computer and debug in my Visual Studio. I am available for live chat via ICQ. This bug is very serious for our company as we can't do some very important things when the bug appears, so I will be glad to help fixing the bug.

This function changes ServerStatus property:

        private void CheckEOF()
        {
            if (!stream.IsLastPacket)
                throw new MySqlException("Expected end of data packet");

            stream.ReadByte(); // read off the 254

            if (version.isAtLeast(3, 0, 0) && !version.isAtLeast(4, 1, 0))
                serverStatus = 0;
            if (stream.HasMoreData && version.isAtLeast(4, 1, 0))
            {
                warningCount = stream.ReadInteger(2);
                // HERE *************************************************
                serverStatus = (ServerStatusFlags) stream.ReadInteger(2); // READINTEGER() RETURNS 3 !!!!! (3 = AutoCommitMode + InTransaction)

                // if we are at the end of this cursor based resultset, then we remove
                // the last row sent status flag so our next fetch doesn't abort early
                // and we remove this command result from our list of active CommandResult objects.
                //                if ((serverStatus & ServerStatusFlags.LastRowSent) != 0)
                //              {
                //                serverStatus &= ~ServerStatusFlags.LastRowSent;
                //              commandResults.Remove(lastCommandResult);
                //        }
            }
        }

So, the finishing queue is:

ReadColumnMetadata() -> ReadEOF() -> CheckEOF()
in CheckEOF() serverStatus changes as I wrote in the previous comment.

a simple workaround:
1) Do "SELECT 1" before BeginTransaction, this way the 'serverStatus' flag is set corectly. (this workaround works just fine).
2) Add a cronjob to restart the server every night, if that is possible.

I even debugged the server to see what is the problem and nothing. I'm pretty sure that the problem is within the server.

Workaround (1) can be added directly in the connector.

Pavel, thanks for following up.
Vlad, great! Thanks for tip, might help others. However we should fix this properly.

Any news on this bug?

Working on this.

Seems server status needs to be reset on each query, that's why "SELECT 1" is helping. Please bear with me for a while longer.

Guys,

we've discussed this and general opinion is to try your code on 5.0.78, 5.1.32 or 6.0.10 (preferably) cause of Bug#36326:

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).

Can you please do this and report back with result?

I will try to test it on 6.0.10 in a few days.
Please, remind me in a week if I forget, because I have much work and don't have much time for testing :(

Guys, where can I download MySQL 6.0.10? =)

It is available on http://dev.mysql.com/downloads/mysql/6.0.html. So, any news?

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".

In 6.0.3.0,why still being this problem?
I've test .
Now, i only use 'select 1' reslove this problem

The bug doesn't appear in MySql server 5.1.36

According to Bogdan in Bug#36326, this is not a connector bug:
 [5 Jan 3: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.

Closing the report.