MySQL Bugs: #7436: Problem with Multiple resultsets...

Bug #7436	Problem with Multiple resultsets...
Submitted:	20 Dec 2004 17:31	Modified:	17 Jan 2005 18:50
Reporter:	Nate Jones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.3	OS:	Windows (Windows XP Pro)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
There is a problem with the multiple resultset in the connector/net application under the following circumstances.
1) The Query Cache is turned on and being used on the MySQL Server.
2) You sent a multiple query to the server that has the query cache turned on (i.e. Select * From Table1; Select * From Table2;).
3) You call the NextResult property on the connector/net data provider, after sending a query like the above, after the 1st time running the query.
(What this means is that you run the above query, and then write a loop:
do while db.nextresult
/* Some stuff here */
loop

The first time you do this the nextresult works just fine, but if you run the query and code again (say from a button click event or something of the sort), when you call the nextresult, the connector freezes.)

The problem is caused because when the server is using a query cache and you send a multiple query, the server send multiple resultsets out, and then adds the query to the cache, The next time the query is run (the multiple query) it pulls it from the query cache and only sends out the resultset for the first query in the mutliple query. (so in this case it would only send out the resultset for "select * from table1", and it wouldn't send the resultset for "select * from table2".)

I can see that this is what the server is doing because I run ethereal and packet capture everything that comes back...and the first time through both resultsets are sent to the client, but the second time its run and pulled from the query cache, only the first resultset is sent to the client.

This appears to be a problem with both the MySQL Server and also the connector/net for not handling the situation correctly.

p.s. In order for this to work, you obvious have to send a multiple query to the server on tables that don't change, so that the query cache doesn't dirty the query.

How to repeat:
Look at the description to see how to repeat the problem...

Suggested fix:
There really needs to be 2 fixes.
1) The server needs to detect whether or not the query is a multiple query, and if it is, then send the resultsets from all the queries in the multiple back to the client...even when the query is in the query cache.
2) The connector/net needs to detect that the server only sent one resultset back to the client instead of all the resultsets that should be returned. (in the case above, it should be 2 resultsets)

I forgot to mention that I'm using version 4.1.7 of the mysql server.  I don't know if it matters or not, but maybe it handles the query cache a bit differently...don't really know.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I cannot repeat this.  Here is the test case I am using.  I tested this with 4.1.7 and 4.1.8 on windows

		[Test]
		public void TestMultipleResultsets()
		{
			MySqlDataReader reader =null;
			try 
			{
				MySqlCommand cmd = new MySqlCommand("", conn);
				// insert 100 records
				cmd.CommandText = "INSERT INTO Test (id,name) VALUES (?id, 'test')";
				cmd.Parameters.Add( new MySqlParameter("?id", 1));
				for (int i=1; i <= 100; i++)
				{
					cmd.Parameters[0].Value = i;
					cmd.ExecuteNonQuery();
				}
				
				// execute it one time
				cmd = new MySqlCommand("SELECT id FROM Test WHERE id<50; SELECT * FROM Test WHERE id >= 50;", conn);
				reader = cmd.ExecuteReader();
				Assert.IsNotNull( reader );
				Assert.AreEqual( true, reader.HasRows );
				Assert.IsTrue( reader.Read() );
				Assert.AreEqual( 1, reader.FieldCount );
				Assert.IsTrue( reader.NextResult() );
				Assert.AreEqual( true, reader.HasRows );
				Assert.AreEqual( 5, reader.FieldCount );
				reader.Close();

				// now do it again
				reader = cmd.ExecuteReader();
				Assert.IsNotNull( reader );
				Assert.AreEqual( true, reader.HasRows );
				Assert.IsTrue( reader.Read() );
				Assert.AreEqual( 1, reader.FieldCount );
				Assert.IsTrue( reader.NextResult() );
				Assert.AreEqual( true, reader.HasRows );
				Assert.AreEqual( 5, reader.FieldCount );
				reader.Close();
			}
			catch (Exception ex)
			{
				Assert.Fail( ex.Message );
			}
			finally 
			{
				if (reader != null) reader.Close();
			}
		}

Reggie,

     It doesn't look like you followed my instructions very carefully, looking at your example that you tried.  Anyways, there are a couple of things wrong with your example, and there is one other thing to make sure you check.

1.  Make sure the Query cache is turned on.  If it's turned off, and you activate the Query Cache, you must restart the server for it to take effect.
2.  In your example, the function that you call will ALWAYS dirty the query cache, because any time there are changes to the table the query cache is dirtied, so your example will always work.
3.  I'm modifying your example so that you can see the problem.  See below:

[Test]

public void TestMultipleResutlsWithQueryCacheOn()
     {
          FillTestTableWithData();   //Fill the table with some information ONLY ONCE.

          int i;

          for (int i = 0; i <= 5; =++)
          {
                TestMultipleResultsets();   //Now to the multiple query a few times
          }
     }

public void FillTestTableWithData()
     {
          MySqlCommand cmd = new MySqlCommand("", conn);

          // insert 100 records

          cmd.CommandText = "INSERT INTO Test (id,name) VALUES (?id, 'test')";

          cmd.Parameters.Add( new MySqlParameter("?id", 1));

          for (int i=1; i <= 100; i++)
	{
  	     cmd.Parameters[0].Value = i;
  	     cmd.ExecuteNonQuery();
	}

     cmd = null;
     }

public void TestMultipleResultsets()
     {

          MySqlDataReader reader =null;

	   try 
  	        {

                       	// execute it one time
		cmd = new MySqlCommand("SELECT id FROM Test WHERE id<50; SELECT * FROM Test WHERE id >= 50;", conn);

		reader = cmd.ExecuteReader();

		Assert.IsNotNull( reader );
		Assert.AreEqual( true, reader.HasRows );
		Assert.IsTrue( reader.Read() );
		Assert.AreEqual( 1, reader.FieldCount );
		Assert.IsTrue( reader.NextResult() );
		Assert.AreEqual( true, reader.HasRows );
		Assert.AreEqual( 5, reader.FieldCount );

		reader.Close();

		// now do it again
		reader = cmd.ExecuteReader();
		Assert.IsNotNull( reader );
		Assert.AreEqual( true, reader.HasRows );
		Assert.IsTrue( reader.Read() );
		Assert.AreEqual( 1, reader.FieldCount );
		Assert.IsTrue( reader.NextResult() );
		Assert.AreEqual( true, reader.HasRows );
		Assert.AreEqual( 5, reader.FieldCount );

		reader.Close();
 	     }

	catch (Exception ex)

	     {
		Assert.Fail( ex.Message );
  	     }

	finally 
	     {
		if (reader != null) reader.Close();
  	     }

}

     As you can see from my example, there are a couple of major differences in the testing functions.  Yours will ALWAYS dirty the cache every time the function runs, and thus it will ALWAYS work.
     In my example, you fill the table with information ONE time, and then call multiple queries (requesting multiple resultsets) in a loop that runs 5 times.  It shouldn't get past the second time through the loop, because as long as that table doesn't get dirtied in the cache (changed), then it will try to pull stuff from the cache, and will fail because the server only sends the first resultset to you instead of both of the resultsets like it should.  The adapter doesn't handle this problem...it just locks up.  

p.s.  The second time through the loop where it calls the TestMultipleResultsets function, it should fail on the reader.NextResult() part.   Please test using this code and let me know when you fix the problem.   

Thanks again for the great work that you do.

Nate Thornton
Senior Software Engineer
Avail Software
nate@availsoftware.com

Nate

I did enable the query cache and did restart mysql after doing so.

Also, my code inserts data into the tables only once and then proceeds to do the same pair of selects twice in a row.  If the problem can be seen by executing the same select batch twice in a row, this SQL should do it.  

When I get a second, I will test the code you sent me to see if it shows the problem here.

Thanks

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I verified this bug as a problem with the server.  I have added code to the connector to work around this issue.  Basically the connector now disables multi-statement execution when the query cache is enabled.  I will make this version sensitive when the server bug is fixed.  The server bug has been reported.  Bug #7966