Bug #67712 Pooled connection after FillSchema() returns no rows and no error
Submitted: 26 Nov 2012 15:51 Modified: 29 Jan 2013 20:16
Reporter: Tom Price Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.2.2 OS:Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any

[26 Nov 2012 15:51] Tom Price
Description:
The FillSchema method for the MySqlDataAdapter sets the SQL_SELECT_LIMIT to -1. While this has the desired effect of returning no rows, it results in a warning in the trace log. 

mysql Warning: 12 : 8: MySql Warning: Level=Warning, Code=1292, Message=Truncated incorrect sql_select_limit value: '-1'

After the method is complete, the limit is never changed back to the default. While this is not a problem if connections are not pooled, in a pooled environment, the setting is retained for future queries that may happen to get that connection. These queries return zero rows. In my situation, a "SELECT NOW() as curtime" query executed later in a different thread returned no rows.

mysql Information: 10 : 2: Set Database: eds
mysql Information: 3 : 2: Query Opened: select now() as curtime
mysql Information: 4 : 2: Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1
mysql Information: 5 : 2: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 2: Query Closed

How to repeat:
Set the connection up with "Pooling = True;" and "Logging = True;".

Both "LIMIT 1" and "WHERE 1=0" seem to exhibit the same behavior.

var da = new MySqlDataAdapter(string.Format("select * from {0} LIMIT 1", table), cn);
//	var da = new MySqlDataAdapter(string.Format("select * from {0} where 1=0",table), cn);
var mysqltable = new DataTable {TableName = table} ;
da.FillSchema(mysqltable, SchemaType.Source);
return mysqltable;

Observe the trace. The SQL_SELECT_LIMIT is never reset.

Suggested fix:
I worked around this by adding the following code to my procedure before the "return mysqltable;":

var cmd = new MySqlCommand("SET SQL_SELECT_LIMIT = DEFAULT;", cn);
cmd.ExecuteNonQuery();
[29 Jan 2013 20:16] Fernando Gonzalez.Sanchez
Thank you for your bug report. 

This has been already fixed, please try a latest version of Connector/NET like 6.6.5 (the recommended) or 6.5.5.

If don't officially support older versions.

If for any reason cannot use the latest versions (ie. using a very old .NET version), let me know and I can provide you with the fix code and instructions on how to rebuild it from source.