MySQL Bugs: #69041: Driver sends "set @@sql_select

Bug #69041	Driver sends "set @@sql_select_limit=1" when executing SELECT
Submitted:	23 Apr 2013 13:42	Modified:	30 Apr 2013 10:12
Reporter:	Steve Short	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.30	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
SELECT statements executed from a .NET/C# application using the Microsoft System.Data.Odbc namespace in the System.Data assembly only returns one row in the resultset.

When logging is enabled on the MySQL server a "set @@sql_select_limit=1" statement can be seen immediately prior to the execution of the SELECT statement. This is not sent by the client application but appears to have been inserted by the ODBC 3.51.30 driver.

Note that the application is retrieving the rows affected count, e.g.:

OdbcCommand command = new OdbcCommand(sql, this.connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = Settings.Default.CommandTimeout;
int rowsAffected = command.ExecuteNonQuery();

How to repeat:
Use an ODBC client application to execute a SQL SELECT statement and retrieve the rows affected.

Suggested fix:
The ODBC 3.51.30 driver should not send the "set @@sql_select_limit=1" statement.

Hi Steve,

Please note that the driver is supposed to execute "set @@sql_select_limit=1" in order to make sure the application gets the number of rows it specified in the preceding call of

SQLSetStmtAttr(hstmt, SQL_ATTR_MAX_ROWS, ...);

I am most certain that the application had set the max rows attribute because the value for @@sql_select_limit was 1. Otherwise you would see DEFAULT instead of 1.

Another important thing is that MySQL ODBC Driver 3.51 is now deprecated, so no new patches or releases are planned for this version. For better compatibility with the version 3.51 try Connector/ODBC 5.2a (ANSI).

I am looking forward to hearing back from you. 
The bug will be automatically closed if you don't provide your feedback within next 15 days.

Apologies, the C# code shown in the original posting is incorrect. The actual code is executing is:

OdbcCommand command = new OdbcCommand(sql, this.connection);
command.CommandType = CommandType.Text;
command.CommandTimeout = Settings.Default.CommandTimeout;
OdbcDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);

The value of the 'sql' variable is a SELECT statement such as "SELECT * FROM p_person;"

The MySQL log shows this:

130429 17:13:04	     20 Connect     root@HELIOS on serpranon
		     20 Query       SET SQL_AUTO_IS_NULL = 0
		     20 Query       set @@sql_select_limit=1
		     20 Query       SELECT * FROM p_person
		     20 Field List  p_person 
		     20 Field List  p_person 

My code is NOT sending "set @@sql_select_limit=1" so I believe that the ODBC Driver is sending this command.

Please note that this application executes successfully with earlier versions of the ODBC Connector. However, installing 3.51.30 always causes this problem.

After some further investigation, I think the problem is the way that the MySQL ODBC/Connector 3.51.30 driver is handling the CommandBehavior.KeyInfo specified on the call to command.ExecuteReader().

If I remove this or set CommandBehaviour.Default then all rows are returned.

Note that this C#/.NET application is designed to work with any ODBC database including Microsoft SQL Server, etc., and it uses the CommandBehavior.KeyInfo option to ensure that primary key information is returned with the resultset. 

Previous versions of the MySQL ODBC/Connector return all rows for the query but 3.51.30 only returns one row when CommandBehavior.KeyInfo is set.

I am not saying that your code sent "set @@sql_select_limit=1" to the MySQL server. It was done by the driver of course. However, as I explained before, the reason the driver acted so was the row limit set to 1 by the following ODBC call:

SQLSetStmtAttr(hstmt, SQL_ATTR_MAX_ROWS, 1, ...);

I think this call was made by the NET/ODBC wrapper in response to using CommandBehavior.KeyInfo for the command behavior. Here is what MSDN says about this parameter (http://msdn.microsoft.com/en-AU/library/system.data.commandbehavior.aspx):

----------------------------------------------------------------------
The query returns column and primary key information.

When KeyInfo is used for command execution, the provider will append extra columns to the result set for existing primary key and timestamp columns.

When using KeyInfo, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY OFF and SET NO_BROWSETABLE ON. The user should be aware of potential side effects, such as interference with the use of SET FMTONLY ON statements. See SQL Server Books Online for more information.

----------------------------------------------------------------------

CommandBehavior.KeyInfo parameter was designed specifically for MS SQL Server and therefore it is not fitting well into MySQL data model and MySQL protocol.
MySQL Server strictly distinguishes metadata (column information etc) and the actual data. In other words, they cannot be sent together. The NET/ODBC wrapper internals are not available for the detailed study and I can only speculate why it decided to limit the number of returned rows. Apparently it decided that the client application wants to have the metadata only, so it does not make sense to read all records from the table. In any case, the ODBC driver did as it was asked: limited the number of rows to 1.

The reason it worked with the MySQL ODBC Driver version 3.51 is that this old version does not support SQL_ATTR_MAX_ROWS parameter. The ODBC driver version 3.51 would return all rows from the table even if the client application needed just one record.

The behavior you observe with CommandBehavior.KeyInfo is not a bug, but a side effect of differences between Microsoft SQL an MySQL databases. This can only be resolved on the application side.

When talking about the version 3.51 in general I assumed the versions older than 3.51.30.

OK, it looks like I need to remove the KeyInfo option and find another way to get the primary key information along with the query resultset.

Thank you for all your help and clarification on this problem.