MySQL Bugs: #11941: CommandBehavior.SingleRow causes reader.Close() to throw error.

Bug #11941	CommandBehavior.SingleRow causes reader.Close() to throw error.
Submitted:	14 Jul 2005 14:47	Modified:	11 Oct 2005 8:46
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	4.1.12-nt	OS:	Windows (Windows 2000 (up to date))
Assigned to:		CPU Architecture:	Any

Description:
From this forum discussion:
http://forums.mysql.com/read.php?38,34025,34025#msg-34025

If I use Command.ExecuteReader( CommandBehavior.SingleRow ), closing the reader causes a error: "...MySqlException : #42000Query was empty" 

Query was valid and returns valid data.  The query should only return one row because the where clause contains a primary key ("where userID=....")

--------Relevant stack Trace----------
MySql.Data.MySqlClient.MySqlException : #42000Query was empty
at MySql.Data.MySqlClient.PacketReader.CheckForError()
at MySql.Data.MySqlClient.PacketReader.ReadHeader()
at MySql.Data.MySqlClient.PacketReader.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64& lastInsertId)
at MySql.Data.MySqlClient.CommandResult.ReadNextResult(Boolean isFirst)
at MySql.Data.MySqlClient.MySqlCommand.GetNextResultSet(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlCommand.Consume()
at MySql.Data.MySqlClient.MySqlDataReader.Close() 

How to repeat:
Code snippet:
MySqlConnection conn = new MySqlConnection( connStr );
MySqlCommand selectCmd = new MySqlCommand( commandStr, conn );
try {	
conn.Open();
MySqlDataReader reader = selectCmd.ExecuteReader(CommandBehavior.SingleRow);
if( reader.Read() ) {
User usr = new User( reader.GetString(0), reader.GetString(1), reader.GetString(2) );
if( !reader.IsClosed ) reader.Close();
....
} 
}

Suggested fix:
If I remove the CommandBehavior parameter, or change it to CommandBehavior.SingleResult, no error is thrown.

What version of the connector are you using?  Here is a test case that I have tested against 1.0.4 and 1.0.5 (not yet released) and it works ok.

[Test()]
public void SingleRowBehavior() 
{
	execSQL("INSERT INTO Test(id,name) VALUES(1,'test1')");
	execSQL("INSERT INTO Test(id,name) VALUES(2,'test2')");
	execSQL("INSERT INTO Test(id,name) VALUES(3,'test3')");

	MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
	MySqlDataReader reader = null;
	try 
	{
		reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
		Assert.IsTrue(reader.Read(), "First read");
		Assert.IsFalse(reader.Read(), "Second read");
		Assert.IsFalse(reader.NextResult(), "Trying NextResult");
		reader.Close();

		cmd.CommandText = "SELECT * FROM test where id=1";
		reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                Assert.IsTrue(reader.Read());
		Assert.AreEqual("test1", reader.GetString(1));
		Assert.IsFalse(reader.Read());
		Assert.IsFalse(reader.NextResult());
		reader.Close();

		reader = null;
	}
	catch (Exception ex) 
	{
		Assert.Fail( ex.Message );
	}
	finally 
	{
		if (reader != null) reader.Close();
	}
}

Okay I figured it out.  The error was caused by a semicolon at the end of my select statement.  If you add a semicolon at the end of your select statement(s), the test will fail.  In fact it's causing SharpDevelop to freeze, so I can't see the exact error.  On my testcase it wasn't freezing, and removing the semicolon from my original code alleviated the problem.  

So it would appear that this is still a bug- it's certainly inconsistent behavior.  Is  there a reason why there should not be a semicolon at the end of my sql commands?  I just did so out of habit.

Thanks for your help!

If cmdText ends in a semicolon, (e.g. "SELECT * FROM MYTABLE;")
sql = "SET SQL_SELECT_LIMIT=0;" + cmdText + ";SET sql_select_limit=-1";
and 
sql = "SET SQL_SELECT_LIMIT=1;" + cmdText + ";SET sql_select_limit=-1";
produce an sql statement with a pair of semicolons together.
"SET SQL_SELECT_LIMIT=0;SELECT * FROM MYTABLE;;SET sql_select_limit=-1;"
Call stack:
CommandBuilder.GenerateSchema
CommandBuilder.GetInsertCommand

From Command.cs, lines 381-409
		public MySqlDataReader ExecuteReader(CommandBehavior behavior)
		{
			CheckState();

			string sql = cmdText;

			if (0 != (behavior & CommandBehavior.SchemaOnly))
			{
				sql = "SET SQL_SELECT_LIMIT=0;" + cmdText + ";SET sql_select_limit=-1";
			}

			if (0 != (behavior & CommandBehavior.SingleRow))
			{
				sql = "SET SQL_SELECT_LIMIT=1;" + cmdText + ";SET sql_select_limit=-1";
			}

			updateCount = -1;
			MySqlDataReader reader = new MySqlDataReader(this, behavior);

			// if we don't have a prepared statement, then prepare our sql for execution
			if (preparedStatement == null)
				sqlBuffers = PrepareSqlBuffers(sql);
			else
				preparedStatement.ExecutionCount = 0;

			reader.NextResult();
			connection.Reader = reader;
			return reader;
		}

I was not able to reproduce the bug on 1.0.6. Probably the bug was fixed.