MySQL Bugs: #42993: SQLConnect() returns SQL_ERROR when using MySQL ODBC 5.1.5

Bug #42993	SQLConnect() returns SQL_ERROR when using MySQL ODBC 5.1.5
Submitted:	18 Feb 2009 21:46	Modified:	27 Feb 2009 10:54
Reporter:	Jim Zawisza	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
I support an application that uses ODBC to do database connections, and I've had several customers report problems with the application on Red Hat Enterprise Linux (versions 4 and 5). The problem occurs when the application first initializes the ODBC connection, using the following sequence of calls (error checks snipped):

SQLHENV hEnv;
SQLHDBC hDbc;

SQLAllocEnv(&hEnv);
SQLAllocConnect(hEnv, &hDbc);
SQLConnect(hDbc,"datasource", SQL_NTS,"username", SQL_NTS,"password", SQL_NTS);

Of course, "datasource", "username", and "password" are real values in the actual code, but you get the idea.

When the application calls SQLConnect, the call fails with a return value of -1, i.e. SQL_ERROR. When I call SQLGetDiagRec, it reports the SQL state as "PT", the native error code as 2002, and no error text. Since the SQL state is supposed to consist of 5 alphanumeric characters, I assume it's bogus.

Other key information: I'm running CentOS 5.1, a Linux distribution that's generated by recompiling the SRPMs from Red Hat Enterprise Linux. As such, it's analogous to RHEL 5.1. I'm also using the 64-bit version of the distro, although I don't think that's a factor: my customers have reported the issue on 32-bit Linux. I believe I've been able to run this successfully on 32-bit Ubuntu, but I ran those tests a while ago, so I don't remember exactly how my environment was set up.

The application is statically linked against iODBC. Here's my .odbc.ini file (with identifying information replaced):

;
; odbc.ini
;
[ODBC Data Sources]
db = ODBC Driver for MySQL

[db]
Driver = /usr/lib64/libmyodbc5.so
Server = not.the.real.server.com
Password = fake_password
Database = test_db

I know that MySQL ODBC 5.1 needs unixODBC 2.2.12 or higher to work: I installed a Fedora Core 8 RPM (unixODBC 2.2.12-4) to upgrade from the version that comes with CentOS 5.1, which is 2.2.11. It doesn't help with this issue, although it did resolve an earlier issue I was having where the iODBC driver manager would always report "Specified driver could not be loaded" when I ran the application.

If you need more information or more testing on my part, please let me know: I'd be happy to help out.

How to repeat:
This problem is consistently reproducible.

I forgot to note that I don't see the problem with MySQL ODBC version 3.51-27.

Hi Jim and thanks for your report.

The only thing that comes to my mind, being that 3.51.x works and that error is from DM and messed up, is unicode. Please enable tracing in DM you use (I am puzzled now if that's UnixODBC or iODBC  but I'd say UnixODBC) and see if SQLConnect you pass is converted to SQLConnectW (as it should in c/ODBC 5.1) and if driver manager actually understands it.
Then, we can check further.

I'm actually using iODBC as the driver manager: sorry for the confusion.  I wouldn't need unixODBC at all if it weren't for the fact that the MySQL ODBC driver has a dependency on unixODBC.

Anyway, I enabled tracing at the driver manager level, but I don't see any calls to SqlConnectW in there.  Here's the entirety of the trace log:

** iODBC Trace file
** Trace started on Thu Feb 19 05:30:54 2009
** Driver Manager: 03.52.0507.0105

[000000.004002]
_tk240.local    2AAAAAABEF90 ENTER SQLAllocEnv
		SQLHENV         * 0x7fff779d2c20

[000000.004128]
_tk240.local    2AAAAAABEF90 EXIT  SQLAllocEnv with return code 0 (SQL_SUCCESS)
		SQLHENV         * 0x7fff779d2c20 (0x1bc0d90)

[000000.004211]
_tk240.local    2AAAAAABEF90 ENTER SQLAllocConnect
		SQLHENV           0x1bc0d90
		SQLHDBC         * 0x7fff779d2c18

[000000.004300]
_tk240.local    2AAAAAABEF90 EXIT  SQLAllocConnect with return code 0 (SQL_SUCCESS)
		SQLHENV           0x1bc0d90
		SQLHDBC         * 0x7fff779d2c18 (0x1bc1230)

[000000.004329]
_tk240.local    2AAAAAABEF90 ENTER SQLConnect
		SQLHDBC           0x1bc1230
		SQLCHAR         * 0x1bb6450
				  | tk240db                                  |
		SQLSMALLINT       -3 (SQL_NTS)
		SQLCHAR         * 0x1bb6500
				  | newuser                                  |
		SQLSMALLINT       -3 (SQL_NTS)
		SQLCHAR         * 0x84ccd1
				  | ****                                     |
		SQLSMALLINT       -3 (SQL_NTS)

[000000.545928]
_tk240.local    2AAAAAABEF90 EXIT  SQLConnect with return code -1 (SQL_ERROR)
		SQLHDBC           0x1bc1230
		SQLCHAR         * 0x1bb6450
		SQLSMALLINT       -3 (SQL_NTS)
		SQLCHAR         * 0x1bb6500
		SQLSMALLINT       -3 (SQL_NTS)
		SQLCHAR         * 0x84ccd1
		SQLSMALLINT       -3 (SQL_NTS)

[000000.546064]
_tk240.local    2AAAAAABEF90 ENTER SQLError
		SQLHENV           0x0 (SQL_NULL_HANDLE)
		SQLHDBC           0x1bc1230
		SQLHSTMT          0x0 (SQL_NULL_HANDLE)
		SQLCHAR         * 0x7fff779d2be0
		SQLINTEGER      * 0x0
		SQLCHAR         * 0x1bce880
		SQLINTEGER        1025
		SQLSMALLINT     * 0x0

[000000.546264]
_tk240.local    2AAAAAABEF90 EXIT  SQLError with return code 0 (SQL_SUCCESS)
		SQLHENV           0x0 (SQL_NULL_HANDLE)
		SQLHDBC           0x1bc1230
		SQLHSTMT          0x0 (SQL_NULL_HANDLE)
		SQLCHAR         * 0x7fff779d2be0
				  | (empty string)                           |
		SQLINTEGER      * 0x0
		SQLCHAR         * 0x1bce880
				  | (empty string)                           |
		SQLINTEGER        1025
		SQLSMALLINT     * 0x0

[000000.547925]
_tk240.local    2AAAAAABEF90 ENTER SQLFreeConnect
		SQLHDBC           0x1bc1230

[000000.548007]
_tk240.local    2AAAAAABEF90 EXIT  SQLFreeConnect with return code 0 (SQL_SUCCESS)
		SQLHDBC           0x1bc1230

[000000.548040]
_tk240.local    2AAAAAABEF90 ENTER SQLFreeEnv
		SQLHENV           0x1bc0d90

[000000.548103]
_tk240.local    2AAAAAABEF90 EXIT  SQLFreeEnv with return code 0 (SQL_SUCCESS)
		SQLHENV           0x1bc0d90

** Trace finished on Thu Feb 19 05:30:55 2009

I decided to run another test to see if switching driver managers made a difference.  I rebuilt our application so that it linked dynamically against unixODBC, rather than statically against iODBC, and reran the failing test.  This time, it worked just fine.

Therefore, the issue is specific to the interaction between MySQL ODBC 5.1 and the iODBC driver manager.  It's possible that the issue can be resolved on our side by rebuilding the iODBC library and relinking: I'll do some more tests to see if that's the case.

Jim, I apologize, of course you do not see SQLConnectW in this trace... Connect problem is likely to do with sizeof(SQLWCHAR) as we don't build for iodbc (except on mac). One of my colleagues will check if iodbc is calling sqlconncetw and having troubles there.

I found the following link that refers to an issue with iODBC expecting 4-byte Unicode characters with SQLConnectW while most ODBC drivers expect 2-byte characters:

http://community.activestate.com/forum-topic/dbd-odbc-activeperl

Tonci, is this the sizeof(SQLWCHAR) issue you were talking about?

On all non-Windows platforms except Mac, the driver is built against unixODBC and is expecting a 2-byte SQLWCHAR, not 4 bytes as iODBC is using. For this reason, the binaries are ONLY compatible with unixODBC and you will need to recompile the driver against iODBC if you wish to  use them together.

Main documentation for C/ODBC has been updated accordingly.