Bug #31475 Oracle Hetrogeneous services returns wrong value for count(*) with MySQL
Submitted: 9 Oct 2007 9:27 Modified: 16 Oct 2007 15:57
Reporter: Jonathan Barber Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.20 OS:Linux (Centos 4.4)
Assigned to: CPU Architecture:Any

[9 Oct 2007 9:27] Jonathan Barber
Description:
When a database link is created in Oracle to MySQL using Oracle's hetrogeneous services, the following query returns the wrong value:

SQL> select count(*) from "foo"@DBLINK;

But counting on a specific column works, e.g.:

SQL> select count("bar") from "foo"@DBLINK;

The MySQL server is running centos 4.4:
mysql-4.1.20-2.RHEL4.1.0.1

The Oracle host is running centos 4.4 with ODBC packages:
unixODBC-2.2.11-1.RHEL4.1
mysql-connector-odbc-3.51.20-0 (from mysql.com)

And Oracle 10.2.0.1.0.

Both servers are VMware guests.

I have raised a call with Oracle, and they claim that the issue is not within Oracle or MySQL servers, but within the MySQL ODBC driver.

They have asked me to raise the issue with MySQL, they suspect that the problem is related to using BIND variables.

Quote from support: "We used SQL_BIGINT and with odbc.ini variable Option=16384 switched to SQL_INT, but that made no difference."

How to repeat:
Oracle support have tried this with a varity of MySQL server versions and ODBC drivers:
MyODBC 2.5.39-21
MyODBC-unixODBC-3.51.10-3
MyODBC 3.51

I've tried:
MyODBC-2.50.39-21.RHEL4.1
mysql-connector-odbc-3.51.20-0
[9 Oct 2007 9:35] Tonci Grgin
Hi Jonathan and thanks for your report. I don't quite follow their logic... If the problem is with MyODBC it should be fairly easy to prove, I'll make a test without their bridge as see. In the meantime, please attach DM (UnixODBC) traces so we can examine them. Also, try to provide as much info as possible as we are free to download while they are not... Is there a way to get a free copy of this bridge (with sources preferably) as this will ease the process of finding the problem?
[9 Oct 2007 9:39] Tonci Grgin
Ah and
> Quote from support: "We used SQL_BIGINT and with odbc.ini variable Option=16384 switched to SQL_INT, but that made no difference."

This option was introduced for MS compatibility as, for example, Access can't handle BIGINT. Also, much more people use Access than some Oracle bridge so we were focused on making MS stuff work. Can Oracle handle BIGINT directly?
[9 Oct 2007 12:21] Jonathan Barber
Oracle support have been kind enough to contribute the following:
-----------------------------------------------------------------
Oracle Generic Connectivity (aka HSODBC) is an Oracle database option that allows interconnect to other non-Oracle databases like MySQL by using a proper ODBC Manager and Driver. Unfortunatly Oracle cannot provide you with the sources of this Oracle database option. The "select count(*)" is a rather straight forward statement and an Oracle HSODBC Agent trace also shows that the complete statement is being sent:
...
Accessing Database 'hsmysql' with SQL:
SELECT COUNT(*) AS c000 FROM `child_rolls` `A1`
...
An ODBC trace shows exactly the same:
...
[ODBC][28803][SQLPrepare.c][189]
Entry:
Statement = 0x8505f90
SQL = [ SELECT COUNT(*) AS c000 FROM `child_rolls` `A1`][length = 49 (SQL_NTS)]
[ODBC][28803][SQLPrepare.c][364]
Exit:[SQL_SUCCESS]
...

We have tried the same statement using 'native' tools like iODBC and isql and it has to be said; they return the correct result. But looking at an ODBC trace it shows that a value is being returned. Whereas Oracle HSODBC usage shows in the ODBC trace that a BIND variable is used, which we cannot look into.

MySQL tracing shows that MySQL sends the correct result to the ODBC driver as well. We do not have the tools however to have a closer look at the ODBC Manager/Driver level to see why/where this correct result is being turned into an incorrect result. It could well be an Oracle Generic Connectivity bug, but first we want to have it checked at the ODBC level. It would be very appreciated if this problem can be looked at the ODBC level.

Honesty needs us to say that even using OpenLink ODBC Manager/Driver also returns the same incorrect result for the count(*). However we also do have to mention that if the same OpenLink software is used against an Microsoft SQL Server database the result of an count(*) is correct. It only returns incorrect when using an MySQL database as source. The code used by Oracle HSODBC to process an count(*) is the same for SQL Server, Sybase, MySQL, DB2/UDB, etc... it is the ODBC Manager/Driver and source database that differs.

When needed we can provide you with the ODBC traces.

I hope this is helpfull, please let me know if you need something else.

Kind regards,
Ed
Oracle Global Customer Services
-----------------------------------------------------------------

I've asked for the traces.
[9 Oct 2007 16:17] Jonathan Barber
ODBC trace of 'select count (*) from "child_rolls"@hsmysql'

Attachment: odbctrace.out.1 (application/octet-stream, text), 40.82 KiB.

[9 Oct 2007 16:18] Jonathan Barber
ODBC trace of 'select * from "counting"@hsmylsq'

Attachment: odbctrace.out.2 (application/octet-stream, text), 29.01 KiB.

[9 Oct 2007 16:19] Jonathan Barber
Attachments odbctrace.out.{1,2} were generated by Oracle support using:
- Oracle 10.2.0.3.0
- HSODBC 10.2.0.3.0
- unixODBC driver manager 2.2.11
- MyODBC 3.51
- MySQL 5.0
[15 Oct 2007 19:24] Bogdan Degtyariov
In order to fix this problem we need to reproduce it on some local machine.
Unfortunately, I am experiencing issues with setting up the heterogeneous services for ODBC data source name. Can you review the .ora files uploaded below and tell me what is wrong there?
This command works just ok:
> tnsping mysql

Then I do "create public database link mysqllink using 'mysql';"
But for some reason "select * from test1@mysqllink;" fails.
------------------------------------------------------------
ERROR at line 1:
ORA-28545: Net8 *******************************************
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQLLINK

Thanks.
[15 Oct 2007 19:24] Bogdan Degtyariov
.ora config files

Attachment: initMYSQL.zip (application/x-zip-compressed, text), 1.38 KiB.

[16 Oct 2007 11:50] Jonathan Barber
The select statement has to be quoted, oracle seems to think tables are uppercase as a matter of course:
SQL> create database link foo connect to "username" identified by "password" using 'MYSQL';
SQL> select count(*) from "table"@foo

I don't think this is the problem you're seeing though. FYI, I've uploaded my config.

However, Oracle came up with a solution, from a related bug (metalink bug 5550614) - it is to do with bigints afflicting the results of selects.

Support recommended applying patch 4547817 (to get to oracle 10.2.0.2) and 5550614 (to fix the problem). Apparently the fix has made it into the 10.2.0.4 patchset so will be fixed when that is released.

I've applied the patches 4547817/5550614 and can confirm that it's fixed.

Thank you for your assistance.
[16 Oct 2007 11:51] Jonathan Barber
Selection of working config files for HSODBC

Attachment: config.tar.gz (application/x-gzip, text), 789 bytes.

[16 Oct 2007 12:03] Jonathan Barber
I should point out that in my previous comment, those are oracle metalink document IDs, and that the problem was at the oracle end, not in the MySQL RDMBs/ODBC software.
[16 Oct 2007 12:53] Bogdan Degtyariov
Jonathan,

So, you got it working now and we can close the bug?
(I've got some problems with my testing virtual machine and cannot give your config a try for next couple of days)
[16 Oct 2007 14:38] Jonathan Barber
Yes, it's fine to close.