RE: Problems with catalog function "SQLColumns" with new ODBC Driver 3.51.09: SELECT may take a while and slow down server for tables with millions of rows

This can be corrected. Please submit this to bugs.mysql.com where it will
get addressed and you can be notified of the solution. 

Thanks!

Peter Harvey, Software Developer MySQL AB 
Office: +1 619 251-6923 
 
Are you MySQL certified? www.mysql.com/certification 

-----Original Message-----
From: emmanuel.kartmann@xxxxxxxxxxxx [mailto:emmanuel.kartmann@xxxxxxxxxxxx]

Sent: Tuesday, August 31, 2004 7:59 AM
To: myodbc@xxxxxxxxxxxxxxx
Subject: Problems with catalog function "SQLColumns" with new ODBC Driver
3.51.09: SELECT may take a while and slow down server for tables with
millions of rows

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?
 
Emmanuel KARTMANN
Web Consultant - Software Architect
Replay Software Development Company
 
Email:  <mailto:emmanuel.kartmann@xxxxxxxxxxxx>
emmanuel.kartmann@xxxxxxxxxxxx
 
 


-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@xxxxxxxxxxx