MySQL Bugs: #46411: SQLPrepare causes Prefetch of table

Bug #46411	SQLPrepare causes Prefetch of table
Submitted:	27 Jul 2009 16:43	Modified:	27 Dec 2011 21:11
Reporter:	Cyrill Müller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	5.1/3.51	OS:	Linux
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	Prefetch, SQLNumResultCols, SQLPrepare

Description:
Using HSODBC from Oracle, connecting to a MySQL Database via ODBC Driver from MySQL. A describe to a View/Table which is not small (~30Mio Rows) does in the SQLPrepare a "select * from table" this causes a long initialization, which is not acceptable.
Tried with several option in MySQL odbc.ini

How to repeat:
ODBC Connection from Linux to MySQL. Describe table.

Suggested fix:
No FTS at SQLPrepare (where 1=2) would be a solution. Is SQLCancel in implemented inn ODBC Driver on Linux?

Sorry forgotten the version from the odbc driver.

Hi Cyrill and thanks for your report.

As there is *no* test case attached, I'll have to fish for solution. I'd start from result-set caching, so please activate "Don't cache ..." and "Forward-only cursors ... " options and see if it helps.

Hello
Thanks for your information. We tested alread with the following options:
(FLAG_FIELD_LENGTH,FLAG_FOUND_ROWS,FLAG_NO_CACHE,FLAG_FORWARD_CURSOR)
I try to create a testcase.
regards
Cyrill

Thanks Cyrill and please do not forget to attach ODBC DM trace for test case too.

Hi Tonci

Following testcase:
Create hsodbc connection from oracle
(Using SLES 10 SP2 / mysql-connector-odbc-5.1.5-linux-x86-32bit.tar.gz / unixODBC-32bit-2.2.11-21.4)

MySQL Table: Create table test

TestConnection from Oracle:

desc "globi"@mysql
=> Works well

Insert some data:
declare
k varchar2(10);
begin
for i in 1..10000000 LOOP
  k:=i;
  insert into "globi"@mysql ("name","id") values(k,i);
end loop;
end;
/

Close Database Link (close ODBC Connection).

desc "globi"@mysql
=> takes much longer

We tried the following Options
option = 3145731
=> FLAG_FIELD_LENGTH,FLAG_FOUND_ROWS,FLAG_NO_CACHE,FLAG_FORWARD_CURSOR

If we don't set FLAG_NO_CACHE we receive "MySQL client ran out of memory" 

Also I've tried to enable trace
option = 4
=> seems not to work anymore

Enabled odbc dm trace. uploaded

regards
Cyrill

odbc dm trace

Attachment: sqtraceodbc.trc (text/plain), 21.80 KiB.

Thanks for attaching trace Cyrill.

Now for the problem, I was more thinking of *standalone* test case, one *not* involving 3rd party ODBC bridges. Are you able to repeat the same problem without Oracle bridge?

Hi Tonci
I can't create a standalone testcase without third party tools.
sorry
regards
Cyrill

Cyrill, tracing is left to DM for quite some time now, so that option is not in effect nor it is needed.
As for stand alone test case it is really necessary because I need to distinguish between MySQL server problem, connector problem and 3rd party tool problem. For example, Microsoft does not recognize any column bigger than 2^32 and so on. Usually, I discard reports based solely on 3rd party tools problems but since you've attached ODBC DM trace, I'll take a look at it first.
In the meantime, I strongly suggest you try to come up with scenario not using HSODBC bridge.

test case

Attachment: bug46411.c (text/plain), 3.91 KiB.

Cyrill,

Please try to compile and run the test case (bug46411.c) attached above then copy/paste the program output as a comment for the current bug report.

Also, try adding OPTION=1048576 to the connection string (parameter 1).

Because SQLPrepare does not execute the query being prepared, I suspect the delay occurs in SQLNumResultCol(). The number of resulting columns can be only obtained after executing the query (and buffering the result unless you use OPTION=1048576).

Thanks.

Hi Bogdan and Tonci 

Sorry at the moment I'm a bit confused (compiled 32bit). When I try with your testcase everything works as expected with OPTION=1048576 it is fast. I tried also with a DSN (exactly the same as I use for Oracle Connection) and it works also as expected. But not with Oracle, also when I set OPTION=1048576 it is slow... The OPTION are read as when I set (4+524288) /tmp/myodbc.sql is written (see below).

regards
Cyrill

/tools/oracle/tmp/bug46411/bug46411_32 "DRIVER={MySQL5};SERVER=lnx99912;USER=test;PASSWORD=manager;DATABASE=test;PORT=33005" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   3.22 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

/tools/oracle/tmp/bug46411/bug46411_32 "DRIVER={MySQL5};SERVER=lnx99912;USER=test;PASSWORD=manager;DATABASE=test;PORT=33005;OPTION=1048576" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   0.00 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

OPTION=0
/tools/oracle/tmp/bug46411/bug46411_32 "DSN=mysql" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   3.22 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

OPTION=1048576
/tools/oracle/tmp/bug46411/bug46411_32 "DSN=mysql" "SELECT * FROM globi"

Trying to connect...Connected
SQLDriverConnect() takes   0.00 seconds.
Preparing "SELECT * FROM globi"
SQLPrepare() takes   0.00 seconds.
Getting result colums count...[2]
SQLNumResultCols() takes   0.00 seconds.
Describing columns...
Column #: 1, Column Name: name, Data Type: 12, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Column #: 2, Column Name: id, Data Type: 4, Column Size: 10, Decimal Digits: 0, Nullable: 1
SQLDescribeCol() takes   0.00 seconds.
Disconnecting...

HSODBC Bridge (OPTION=1572868 / 4+524288+1048576):
cat /tmp/myodbc.sql
-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090730 15:52:33

COMMIT;
select * from `globi`;
SHOW KEYS FROM `globi`;
COMMIT;

With your testCase (OPTION=1572868 / 4+524288+1048576):
cat /tmp/myodbc.sql
-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090730 15:54:29

SELECT * FROM globi;

Hi
I've reread your post. Now my questions is, when I do a SQLPrepare() and NO execution of the statement but only do a SQLNumResultCol(), why is the Statment not a bit changed (rewritten), it could by something like "where 1=2" which will fast return no rows selected but the Number of Columns correct? But for sure this should only be done, when no SQLExecute() is done before SQLNumResultCol(). What I've missed?
thanks and regards
Cyrill

Hi
As the cycle of HSODBC for a statement execution is always the same and a "select * from table" never comes to an execution, as at least the projection is changed (* will be replaced to all columns). I've implemented for me a dirty workaround in MySQLPrepare. If a "select * from table" is received it will be changed to "select * from table where 1=2" which will be executed for SQLNumResultCols(). So this workaround works only as HSODBC will never send a "select * from table" directly to the driver. But I think there has to be a better fix for SQLNumResultCols() for not executed statements.
thanks a lot for your help!
regards
Cyrill

Cyrill, we've been thinking of this report and here's what we came up with...

  o I can verify this as a feature request (S4) if you agree since, theoretically, getting metadata with empty result-set should improve c/ODBC performance.
  o Implementing this will require additional man-power and is bound to cause other problems. We might not see it now but they will surely surface.

So, can I change it to S4?

Hi Tonci and Bogdan
Thanks a lot for your help again. great job! thanks! :-)
Changed severity to S4.
Just my thought, in our env if I couldn't change this behaviour it would be a performance killer as an init (SQLNumResultCols) over a table with >100Mio rows would never end and it would also not be for a single serial session. But for sure you are right if you say this is coming from the way HSODBC is working. And you are right that this could have some side effects, I've already thought of that and there was also some kind of solution (adding an additional queryWithoutRows to tagSTMT where there is an extra condition 1=2 (NoRows) and this queryWithoutRows is executed in SQLNumResultCols when there was no execution) But sure this is the end... 
Thanks and regards
Cyrill

I will verify this now on the basis of entire discussion and my opinion that this functionality is important to have in driver.

I have assigned this for Jess to fix but, unfortunately, he left...

Assigning to Lawrentiy now.

Patch for the bug. Uses statement MAX_ROWS attribute

Attachment: patch46411.diff (text/x-diff), 2.29 KiB.

My first intention was to use prepared statement resultset metadata. And then I (re)discovered Jess' branch lp:~myodbc-developers/myodbc/ssps_result_metadata that does exactly what i want. I merged latest revisions to that branch, but prepared statement use should be optional - either by connection option, or by server version(or both). So that branch require some more work. And proposed patch will be used anyway. if it gets approval :)

There is no actual testcase for the patch, but I added some code to the t_max_rows test to ensure that STMT_ATTR_MAX_RESULT functionality isn't affected.

p.s. "-  return rc;
+  return rc != 0;" is just an unrelated warning fix

This will address the performance issue with queries that generate a lot of data, but there's still a potential for delays with complex queries. (But dealing with that would require re-writing the query to add an impossible WHERE clause, so probably not worth it relative to this easy fix which addresses the most common problem.)

My bet is still to start using prepared statements here(and then in c/odbc prepared statements in general). However idea of writing query parser seizes me regulary(and i even started to write one while worked on some bug). It could be useful in many places. But then I think that ice is to thin to step on it- the problem is probably more complex than it appears at the moment i thinking "i'm gonna write it in couple of hours", and the result will be many bug reports.
and yes - this solution is good for most cases.

pushed as rev#880. if stars are aligned properly, it will go to next(current is 5.1.6) release(5.1.7?)

An entry has been added to the 5.1.7 changelog:

Describing a view or table caused SQLPrepare to prefetch table data. For large tables this created an intolerable performance hit.

merged to 3.51 tree as rev#772, next release is supposed to be 3.51.30

The 3.51.30 entry has been added.