MySQL Bugs: #5322: Problems with catalog function "SQLColumns" with new ODBC Driver 3.51.09

Bug #5322	Problems with catalog function "SQLColumns" with new ODBC Driver 3.51.09
Submitted:	31 Aug 2004 18:10	Modified:	11 Apr 2005 19:07
Reporter:	Emmanuel KARTMANN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.09	OS:	Windows (Windows 2000)
Assigned to:	Vasily Kishkin	CPU Architecture:	Any

Description:
I'm using MySQL Database Server 4.0.20d on Windows 2000 with MyODBC 3.51.06 and everything works fine.
 
I downloaded the latest ODBC Driver (3.51.09); When I try to get information on a SQL Table (catalog function "SQLColumns" from ODBC 1.0 API is called), the CPU goes up to 100% for some time, and the function succeed (if the CPU/Memory doesn't run out - crashes!).

Looking at the mysql log file, I see the requests issued by the new ODBC Driver (for database "mydatabase", table "mytable"):

040831 16:21:28      53 Connect      <mailto:root@localhost> root@localhost
on mydatabase
       53 Query       SHOW DATABASES
       53 Query       SELECT DATABASE()
040831 16:21:37      53 Query       show tables
040831 16:21:48      53 Query       SHOW KEYS FROM mydatabase.`mytable`
       53 Query       show table status from `mydatabase` like 'mytable%'
       53 Query       SHOW FIELDS FROM mydatabase.`mytable` like '%'
       53 Query       SELECT f1,f2,f3,f4,f5 FROM mydatabase.`mytable`

The last query (a SELECT with neither a "WHERE" clause nor a "LIMIT" clause) causes the CPU/Memory to be wasted and the machine to slow down or even crash - if your table contains many rows (I have tables with millions of rows!).

Looking at the source code, I found that function "SQLColumns" (file MyODBC-3.51.09\driver\catalog.c, line 687) calls function "mysql_list_dbcolumns" (file MyODBC-3.51.09\driver\catalog.c, line 610), which generates the SELECT statement.
 
Is this statement normal? Can it be changed to "SELECT f1,f2,f3,f4,f5 FROM mydatabase.`mytable` LIMIT 1"? I would have patched the code - but I can't find Windows source download for MyODBC version 3.51.09 - is this available and where is it?

How to repeat:
Create a SQL table
Fill it with millions of rows
Use an ODBC client to get a table description (e.g. Query Tool from George Poulose: http://gpoulose.home.att.net/)  - click on table to display table structure...

The CPU goes up to 100% if your table is big (not if it's a table with only a few rows).

Suggested fix:
Add a "LIMIT 1" clause to the SELECT statement in function "mysql_list_dbcolumns" (file MyODBC-3.51.09\driver\catalog.c).

This should be enough to get table structure without ALL THE TABLE ROWS!

It appears that the "select everything from the table" behaviour is a hack that was thrown in to deal with the fact that mysql_list_fields doesn't support having a database name being passed to it.

If there is no database name, mysql_list_fields is called which appears to behave properly.  If there is a database name included then this hack is taken, which makes the driver completely unusable for me.

The hack doesn't even work correctly, because for things such as varchar columns it returns the length as the length of the largest value currently stored, which may be much smaller than the length permitted... especially if you are starting with an empty table!  This would break even more if you did a LIMIT 1.

This is a pretty serious bug for any real use of the ODBC driver.

I see the reason why there is a SELECT - thanks for the hint.

What about patching the code with a reasonable LIMIT value (e.g. LIMIT 100). The hack would work, and have reasonable value for most cases (unless you have huge tables with bigger values at the end...).

We could just add this line:
  strxmov(select,select," LIMIT 100",NullS);

After line 658 in catalog.c (I tried it - it solves MY problem i.e. no more CPU 100% for hours...).

Regards,

Emmanuel

Seems like the bug has been fixed in MyODBC 3.51.11 - I don't crash my system anymore with this version... Great!

Regards,

E.

MyODBC 3.51.11 has fixed the bug - surprisingly mentioned as number 8198:

  http://bugs.mysql.com/bug.php?id=8198

Duplicated bugs - but fixed!

Thanks!

E.