MySQL Bugs: #19159: A call to SQLFreeEnv to clear an ODBC environment handle hangs the calling app

Bug #19159	A call to SQLFreeEnv to clear an ODBC environment handle hangs the calling app
Submitted:	18 Apr 2006 6:22	Modified:	6 Jun 2006 15:16
Reporter:	Christopher Colby	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12.00	OS:	Windows (Windows XP Pro, SP2)
Assigned to:		CPU Architecture:	Any

Description:
The Problem

1. The "Test" button in ODBC Data Source Administrator causes the odbcad32.exe interface App to hang when dismissing a successful connection.

2. I have a VB6 App which makes a database connection by referencing a MySQL ODBC DSN, and making direct API calls into the odbc32.dll library. This App also hangs when dismissing a connection.

To create a VB connection, I get an Environment handle, a Connection handle, activate the Connection, then get a Statement handle.

To dismiss the VB connection, I drop the Statement handle, disconnect the Connection, free the Connection handle, then free the Environment handle.

I mention this because I use VB to step through the ODBC API calls one at a time. The one that fails in VB is the final call to free the Environment handle.

I then started a Trace in the ODBC Data Source Administrator. I ran traces for both my VB app as well as the ODBC Administrator's "Test" button.

In both Trace logs you can clearly see the first call that creates the Environment handle. After all of the other calls, you can clearly see the last call at the end of the Trace. The call to SQLFreeHandle(SQL_HANDLE_ENV) or SQLFreeEnv is entered, but there is no exit.

Thus the Synopsis:

A call to SQLFreeEnv to clear an ODBC environment handle hangs the calling app

NOTE: I have used the ODBC 2.0 calls SQLFreeEnv as well as the ODBC 3.0 SQLFreeHandle. Both fail.

My OS = Windows XP Pro Version 5.1 Build 2600.xpsp_sp2_gdr.050301-1519: Service Pack 2

MySQL ODBC 3.51 Driver Version 3.51.12.00, dated 10/15/2005

The odbc32.dll file version is 3.525.1117.0 (xpsp_sp2_rtm.040803-2158)

The odbcad32.exe file version is 3.525.1117.0

I am running a Pentium 4 WITH hyperthreading

NOTE: I am running the ZoneAlarm Internet Security Suite. However, I disabled "Load at Boot", stopped the service, re-booted the machine, and the ODBC "Test" button still failed.

How to repeat:
Start ODBC Trace in the ODBC Data Source Administrator, then click the "Test" button on a DSN configured as above.

After you dismiss the "Success" button, (and the ODBC App hangs) check the last line in the Trace log. You will see the SQLFreeEnv entry, but no exit.

Suggested fix:
My work-around at the moment; don't call SQLFreeEnv when I am done with the connection. End the call stack at freeing the Connection handle.

Not sure that this would be considered a robust solution in a production situation.

Hi Christopher. Thanks for your problem report.
No matter how hard I tried I couldn't reproduce reported behavior. I checked our test suite and found that SQLFreeEnv is allways called and produced no problems.

Dear Tonci,

I receive your email indicating that you cannot duplicate the error.

Bummer.

What is surprising is that I just checked the Forums, and the problem appears to be so well known that it receives a "This is a well know problem ..." response from people that frequent the ODBC Connector forum.

I just did a simple re-test of the "Test" button in the ODBC Administrator, and once again received the Hang problem.  I had ODBC Trace on, and again received the same final lines in the Trace Log (I have edited the exact text so that it is more read-able);

-  odbcad32  c6c-c38  ENTER SQLDisconnect 
-  odbcad32  c6c-c38  EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)

-  odbcad32  c6c-c38  ENTER SQLFreeHandle <SQL_HANDLE_DBC>
-  odbcad32  c6c-c38  EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)

-  odbcad32  c6c-c38  ENTER SQLFreeHandle <SQL_HANDLE_ENV>

And that is where the Trace Log ends.  There is no associated EXIT SQLFreeHandle.  Which also means that there is no return error code.  The application just hangs.

Sadly, I have no further suggestions.  I have provided all of the version information regarding all of the components which I believe are involved here.  And the above is just a continuing example of the Trace Log output.

I don't know what else to do.

Regards,

Chris Colby

I have tested MyODBC from version 3.51.12 down to 3.51.06 in clean versions of Windows XPSP2 at still the probelem persist. Then I tested with WindowsXP SP1, miraculously no problem at all. I think the root of this problem is most likely in WinXP SP2. Hope this might help.

Thanks for your findings Russ. These kind of errors are very hard to track / repeat. I use WinXP Pro SP2 32bit and XP Pro 64bit and haven't seen this error ever.

I also have this problem. Im my case it manifests itself as a "frozen" or "non-responding" MyODBC setup dialog (and ODBC Manager) when I press the "Test connection" button whilst creating a DSN. I have also tried the option of simply using the DSn via Access 2003 and SQL Server's Data Transformation wizard. In both cases the programs simply freezed too. There is minimal CPU usage so it isn't a CPU hogging scenario. 

So far I have used the following combinations of MySql and MyODBC:

1. mysql-essential-4.1.9-win32.msi
   mysql-connector-odbc-3.51.10-2-win32.msi

2. mysql-essential-4.1.9-win32.msi
   mysql-connector-odbc-3.51.12-win32.msi

3. mysql-4.1.21-win32.zip
   mysql-connector-odbc-3.51.12-win32.msi

My testing environment uses P4 HyperThreading Windows Server 2003 SP1 systems with MySql Server and MyODBC driver on the same system.

I have on occasions seen the connection test randomly succeed. But only once. If I immediately re-test the connection, it freezes again. I am yet to find a solution that lets me connect successfully and consistently via MyODBC. From my searches of the MySql forums (and others), I can see that this issue has been surfacing regularly since 2004.

The following trace is typical of what the ODBC Manager's tracing option produces:

---------------------BEGIN TRACE------------------------------------

odbcad32        8a0-418	ENTER SQLAllocHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00000000
		SQLHANDLE *         0007D574

odbcad32        8a0-418	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           00000000
		SQLHANDLE *         0x0007D574 ( 0x007e1540)

odbcad32        8a0-418	ENTER SQLSetEnvAttr 
		SQLHENV             007E1540
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER          0x00000003
		SQLINTEGER                   0 

odbcad32        8a0-418	EXIT  SQLSetEnvAttr  with return code 0 (SQL_SUCCESS)
		SQLHENV             007E1540
		SQLINTEGER                 200 <SQL_ATTR_ODBC_VERSION>
		SQLPOINTER          0x00000003 (BADMEM)
		SQLINTEGER                   0 

odbcad32        8a0-418	ENTER SQLAllocHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           007E1540
		SQLHANDLE *         0007D57C

odbcad32        8a0-418	EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           007E1540
		SQLHANDLE *         0x0007D57C ( 0x007e15e8)

odbcad32        8a0-418	ENTER SQLDriverConnectW 
		HDBC                007E15E8
		HWND                00000000
		WCHAR *             0x4BF78088 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x4BF78088 
		SWORD                        2 
		SWORD *             0x00000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

odbcad32        8a0-418	EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
		HDBC                007E15E8
		HWND                00000000
		WCHAR *             0x4BF78088 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x4BF78088 
		SWORD                        2 
		SWORD *             0x00000000
		UWORD                        0 <SQL_DRIVER_NOPROMPT>

		DIAG [HY000] [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10060) (2003) 

odbcad32        8a0-418	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  2 
		SQLHANDLE           007E15E8
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0007D25C (NYI) 
 		SQLINTEGER *        0x0007D4E4
		SQLWCHAR *          0x007E2738 (NYI) 
 		SQLSMALLINT                512 
		SQLSMALLINT *       0x0007D4D8

odbcad32        8a0-418	EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 
		SQLHANDLE           007E15E8
		SQLSMALLINT                  1 
		SQLWCHAR *          0x0007D25C (NYI) 
 		SQLINTEGER *        0x0007D4E4 (2003)
		SQLWCHAR *          0x007E2738 (NYI) 
 		SQLSMALLINT                512 
		SQLSMALLINT *       0x0007D4D8 (77)

odbcad32        8a0-418	ENTER SQLGetDiagRecW 
		SQLSMALLINT                  2 
		SQLHANDLE           007E15E8
		SQLSMALLINT                  2 
		SQLWCHAR *          0x0007D25C (NYI) 
 		SQLINTEGER *        0x0007D4E4
		SQLWCHAR *          0x007E2738 (NYI) 
 		SQLSMALLINT                512 
		SQLSMALLINT *       0x0007D4D8

odbcad32        8a0-418	EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
		SQLSMALLINT                  2 
		SQLHANDLE           007E15E8
		SQLSMALLINT                  2 
		SQLWCHAR *          0x0007D25C (NYI) 
 		SQLINTEGER *        0x0007D4E4
		SQLWCHAR *          0x007E2738 (NYI) 
 		SQLSMALLINT                512 
		SQLSMALLINT *       0x0007D4D8

odbcad32        8a0-418	ENTER SQLFreeHandle 
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           007E15E8

odbcad32        8a0-418	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  2 <SQL_HANDLE_DBC>
		SQLHANDLE           007E15E8

odbcad32        8a0-418	ENTER SQLFreeHandle 
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           007E1540

odbcad32        8a0-418	EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
		SQLSMALLINT                  1 <SQL_HANDLE_ENV>
		SQLHANDLE           007E1540
---------------------END TRACE--------------------------------------

I seriously suggest that another person should look upon this, this is a very widespread and popular issue right now and still no one in MySQL has given any concrete explanation regarding this. It's not even a Firewall problem, just right now I tested a Windows XP HOME Edition with Zone Alarm (my current firewall for my Pro SP2) and this problem doesn't show. Though this is not a really serious matter, it is really annoying. VB hangs after a few seconds when you do a test execution of your program. I am not picking on Tonci Grgin's ability to solve this problem, nd I'm sorry if I seem like picking on him but I am not, I am just suggesting that it is always better if you have more person working on a problem rather than one. Any news on a new version? Does this issue being looked listed o the todo list of the next version? Please, please this has been going on for the longest time now, as far as 3.51.09 for me, any new updates? At least a concrete reason or cause, that's all we're asking. Thanks.

Russ you are right. Although I've never seen this, there *might* be a problem with Qt on windows (just my assumption). I will consult with colleagues again.

I was unable to repeat the bug on WinXP Pro SP2, Win2000 SP4 and Win2003 Server.
In my opinion, this problem is mostly related to the system-specific settings/updates. However, I can offer you a new preliminary build of MySQL Connector/ODBC driver version 3.51.13:

You can download .zip archive from here:
ftp://ftp.mysql.com/pub/mysql/download/connectors/odbc/mysql-connector-odbc-3.51.13r134-pr...

To apply the new version of MyODBC driver you need to replace myodbc3.dll and myodbc3S.dll files in Windows\system32 directory.

I tried your current build but unfortunately it didn't worked, I received a prompt that I need to reinstall the driver, also curiously, I don't have a myodbc3S.dll in my system folders, but rather a myodbc3d.dll. Any problems with that? Could it be that what we are downloading from the site is the cause of the problem. I uploaded a screenshot just in case a screwed up, http://i96.photobucket.com/albums/l192/rudedholf/MyODBCError.jpg, the files with underscores ("_") are my original 3.51.12 files. Again thank you guys for the quick replies. I'll also give more prompt actions to your replies so that we can at least trace the root of this problem the soonest.

Russ,
Here is the .msi installer available for downloading:
ftp://ftp.mysql.com/pub/mysql/download/connectors/odbc/mysql-connector-odbc-3.51.13-beta-w...

Can't seem to download the file, Error 550, failed to change directory.

Oh sorry I it seems the link was wrong.

Just installed and YES!!!!!! It works!!!! When is the production release for this version? I think this solves this nasty problem, any details on the cause of this bug?

Thank you very much! Hope everyone tests this also to clear this bug or add more data if ever it doesn't work for them.

Additional information guys, you HAVE TO UNINSTALL PREVIOUS MYODBC INSTALLATIONS first rather than upgrading or install both, if you do not, ODBC still hangs.

My current System:

Windows XP Pro SP2
MySQL 4.1.19 / 5.0.15
MyODBC 3.51.13 Beta
Zone Alarm 6.1.737

After some experiments, I think I got this right, it seems that you need to reset Winsock on XP PRO SP2 machines who have firewalls installed by opening a command prompt and typing "netsh winsock reset", then restart your computer. I tried this with MyODBC versions 3.51.12 down to 3.51.09

Thanks Russ. I'm following this report even though it's "can't repeat".

No probs, just wanna get rid of this bug, actually you can't really repeat it on machines who don't have 3rd Party Firewall softwares I guess, I just do hope someone else reads this and verify this fix, because this is not what seems to be a Bug but a problem with XP and 3rd party Firewalls and should be marked as "Not a Bug" appropriately.

Hi Russ and thanks for helping us. 

I'm afraid it's not that easy... I tested this report few weeks (a month?) ago with ZoneAlarm (trial, older version) and with BitDefender fire walls, still "Can't repeat".

Thanks for your interest in MySQL!

Russ,

What's the result you get from running the MDAC component checker?

You can download it from MS at:
http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef09...

Can you post an alternative link for the file? M$ validator cannot validate my OEM Windows XP that came with my laptop. And what is this tool suppose to tell you about MDAC?

Russ, I believe your laptop should have passed M$ checks... Find the machine that does, download SFX archive containing MDAC Utility and copy it to your laptop. As for alternatives, try googling...

Ok downloaded it from M$ but I can't seem to understand what's this tool supposed to do, after running it just gave me a confirmation that I have MDAC 2.8 SP1 on XP SP2, then a list of the MDAC files, there seems to NO indication of a problem from the summary report. Can you clarify or tell me what possible results would this too l have that may be related to this bug?

I did 2 test runs now , again for my laptop and my brother's desktop PC, and it seems everything is fine I guess, File Details, COM Details and Registry Details all have the subtree "MATCH" and doesn't seem to have the subtrees "MISMATCH" and "NO FOUND"

Russ, there should be report files in C:\WINDOWS\MPSReports\LITE (depending on settings). Try finding them.

Tried to search for "mpsreports" allover my main drive but it seems it does not exist, I can save the results in XML though, would that be of some help?

Yes Russ, thanks.

OK you can download it here http://www.geocities.com/yugicrossovers/results.zip

Thanks Russ. Are you using a firewall on the client computer? If so, what kind?

For future reference, the MDAC component checker results posted by Russ report MDAC 2.8 SP 1 (which is default for Windows XP SP 2).

I have Zone Alarm version 6.1.737 installed on both computers. If you uninstall or do not install Zone Alarm, MyODBC doesn't hang. Then I had this hunch that Zone Alarm maybe blocking the sockets that MySQL uses so I did a reset on the winsock by running "netsh winsock reset" and the problem was gone, by the way, I already went back to MyODBC version 3.51.12

I too have been suffering this issue although I don't pretend to understand much of the technical aspects noted within. However, I have also managed to solve it on my system:

Windows Xp SP2
Norton Internet Security 2005
MySQL ODBC 3.51.12
MySQL 5.1.14. beta
MySQL GUI Tools 5.0.r9a

I had to add the MyODBC3i.exe and MyODBC3m.exe files manually to the Norton firewall, which when I then tested an ODBC defintion, popped up a firewall dialog for ODBCcad32. Which is the MSFT ODBC administrator.

Without the i and m files defined as permit within the firewall, my system would just lock up.

I was put on this path as I had to do the exact same thing for the GUI tools - add them to the firewall manually else suffer a lockup when I try to connect.