MySQL Bugs: #69554: SQL_ATTR_MAX_ROWS breaks catalog functions

Bug #69554	SQL_ATTR_MAX_ROWS breaks catalog functions
Submitted:	23 Jun 2013 18:39	Modified:	14 Aug 2013 10:05
Reporter:	Sam Varshavchik	Email Updates:
Status:	Patch pending	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.11, 5.2.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Setting SQL_ATTR_MAX_ROWS to a less-than-unlimited value appears to break all catalog functions.

How to repeat:
1) Set SQL_ATTR_MAX_ROWS on a statement handle to one row.
2) Execute a SELECT query on the statement handle.
3) Invoke SQLTables to list all tables with a match "prefix%"

SQLTables() will return one table, instead of listing all tables in the database.

Suggested fix:
This is because set_sql_select_limit() gets called to set @@sql_select_limit before executing the query, and the catalog function call mysql_real_query() without calling set_sql_select_limit() to reset it back to unlimited.

Need to audit all queries executed by all the catalog functions, and make sure that set_sql_select_limit() gets called before each one.

Hi Sam,

Thank you very much for reporting the problem in MySQL Connector/ODBC.
I was able to repeat the bug in ODBC driver versions 5.1.11 and 5.2.5.
Setting the bug status to Verified.

C test case in my_catalog2.c (t_bug69554)

Attachment: my_catalog2.c (text/plain), 46.86 KiB.

Hello Sam,

It is not a bug. SQL_ATTR_MAX_ROWS applies to catalog function as well.

Please check SQL_ATTR_MAX_ROWS from below msdn link:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712631(v=vs.85).aspx

"SQL_ATTR_MAX_ROWS applies to all result sets on the Statement, including
those returned by catalog functions."

That applies to the /same/ statement handle only.

The bug is when you use a completely different statement handle, from the same connection, to execute a catalog function.

All statement handles share the same SQL connection.

The connector implements SQL_ATTR_MAX_ROWS by setting @@sql_select_limit connection setting, before executing a query.

The SQL_ATTR_MAX_ROWS value is kept in the statement handle, but the catalog functions fail to set @@sql_select_limit before executing the catalog query, which ends up using whatever @@sql_select_limit was set for the last query executed on the same connection.