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();