MySQL Bugs: #36203: connector prompting for login infomation although stored in odbc-profile

Bug #36203	connector prompting for login infomation although stored in odbc-profile
Submitted:	18 Apr 2008 9:37	Modified:	7 Jul 2008 16:21
Reporter:	moritz nölting	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.4	OS:	Windows (xp sp2)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
the odbc connector randomly uses logon information stored in odbc-profile or prompts the user for connection information and ignores any settings stored in the odbc-profile. this has been experienced on multiple windows xp machines using microsoft access 2007, microsoft access 2003, sybase powerbuilder, seagate cystal reports. this problem does not exist with connector 3.51.

How to repeat:
use a blank windows machine which does not have mysql connector other than 5.01.04.00. create system-dsn odbc-profile with all connection information aka server, user, password, database. open ms access, try to import a few mysql tables. sooner or later odbc-driver will prompt for connection information.

Suggested fix:
let the driver always load stored odbc profile.

Hi Moritz and thanks for your report.

I have been working on reproducing this problem apart from your report but without success.

I have two quick questions:
 1) Has the DSN you're using been created by previous version of c/ODBC 5.1? If so, did you uninstall previous version of driver with "Add/Remove programs" before installing c/ODBC 5.1.4?
 2) Do you use "Enable auto reconnect" option?

I tested with Access 2003, c/ODBC 5.1.4GA and MySQL 5.0.58PB on my WinXP Pro localhost. I used old DSNs (System & User) without auto-reconnect. No problems detected.

Seemingly, I will need much more info from you, connect options, DM trace etc. if I'm to repeat this.

Hi Tonci,

>I have two quick questions:
> 1) Has the DSN you're using been created by previous version of c/ODBC
>5.1? If so, did you uninstall previous version of driver with
>"Add/Remove programs" before installing c/ODBC 5.1.4?

dsn has only been created by c/odbc 5.1.4 on one test machine. on other machine dsn with 3.51 worked fine. deleted dsn and uninstalled 3.51. created new dsn 5.1.4. We would fallback to 3.51 but there we have problems with unicode which are not a problem with 5.1.4.
 
> 2) Do you use "Enable auto reconnect" option?
no, this option hasn't been enabled. #36203 [Com,Anl->Fbk]: connector prompting for login infomation although stored in odbc-profile

To be sure the problem does not reside within powerbuilder we did some tests with ms-access as described in our bugreport. Also we have the same problem with Crystal Reports XI. The Bug is not always reproduceable right away. This morning we created new access database. linked 3 mysql tables into access, created one query on these tables. closed access, opened again, opened tables and querie. we Repeated this a few times and suddenly the connector prompts for connection information, not knowing any information (server, database, credentails, options) stored in the dsn.

Options used for our Powerbuilder main application: "Enable safe options", "Ig
nore Space after Function names". These Options also used for below log with ms-access 2007.

Database(s) resides on another XP SP2 Pro machine with all current updates installed. TCP/IP connectivity 100MBit Link.

ODBC Trace Log of Ms-Access up to point where Connector prompts for connection options:
------------------------------------------------------------------------------
Datenbank1      e48-b9c	ENTER SQLAllocEnv 
		HENV *              00139784

Datenbank1      e48-b9c	EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
		HENV *              0x00139784 ( 0x08a81788)

Datenbank1      e48-b9c	ENTER SQLAllocConnect 
		HENV                08A81788
		HDBC *              001397D0

Datenbank1      e48-b9c	EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)
		HENV                08A81788
		HDBC *              0x001397D0 ( 0x08a81830)

Datenbank1      e48-b9c	ENTER SQLSetConnectOption 
		HDBC                08A81830
		SQLINTEGER                 103 <SQL_LOGIN_TIMEOUT>
		SQLPOINTER          0x00000014

Datenbank1      e48-b9c	EXIT  SQLSetConnectOption  with return code 0 (SQL_SUCCESS)
		HDBC                08A81830
		SQLINTEGER                 103 <SQL_LOGIN_TIMEOUT>
		SQLPOINTER          0x00000014 (BADMEM)

Datenbank1      e48-b9c	ENTER SQLSetConnectAttrW 
		SQLHDBC             08A81830
		SQLINTEGER               30002 <unknown>
		SQLPOINTER          [Unknown attribute 30002]
		SQLINTEGER                  -3 

Datenbank1      e48-b9c	EXIT  SQLSetConnectAttrW  with return code 0 (SQL_SUCCESS)
		SQLHDBC             08A81830
		SQLINTEGER               30002 <unknown>
		SQLPOINTER          [Unknown attribute 30002]
		SQLINTEGER                  -3 

Datenbank1      e48-b9c	ENTER SQLDriverConnectW 
		HDBC                08A81830
		HWND                000D0544
		WCHAR *             0x745D9A38 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x745D9A38 
		SWORD                        2 
		SWORD *             0x00000000
		UWORD                        1 <SQL_DRIVER_COMPLETE>

Thank you for looking into this, we are very desperate into going on production machines.

regards 

moitz

I have quite the same problem like Moritz has. Initial situation:

Win XP
Acces 2003 and Access 2000
Connector version: 5.1.4
Remote Server : MySQL Server 5.0.24

An DB with new tables linked by 5.1.4 ODBC version.

The fist time that has been created link to Access 2003 work fine. After Access program is closed, and open later, if I try to open the new tables, Access ask about Server and/or password. Every day occur the same, after restarting the computer. I have tried to flag:

Return matched rows (is not active by default)
Allow big results set (is not active by default)
Enable automatic results (is not active by default)

This flags doesn't solved this problems of Access connection.

A very interesting behavior:

The same DB with old 3.51 created link in Access, before the 5.1.4 has installed
ODBC 5.1.4 has installed.
It has deleted ODBC 3.51 connection, and been created with 5.1.4 ODBC version
This old tables with ODBC 5.1.4, work fine. But the link was created before the change.....

Another interesting behavior:

If the mouse point flies to the old link tables in Access, it appear an “yellow label” explaining the connection link of the table : Server, Port, DB, DSN, table name, by and by...
If the mouse point  flies on a new link table, the “yellow label” only shows: DSN, and table name.

Thaks to Tonci and Moritz

Best regards,

José Miguel Serrano

In my environment I have exactly the same behaviour.
I am using ODBC 5.01.04 with MySQL 5.0.51b. Previous version ODBC 3.51 works fine
Something interesting : When defining an ODBC as User-DSN instead of System-DSN, it works !!
Any explanations/solutions  yet ?

Guys, one can not fix what one can not repeat... so no, no advance on this problem.

Can we at least try to find out common factors involved? From what I see:
 - MySQL server should (?) be on remote box
 - One should use System DSN
 - One should use c/ODBC 5.1.4 as other versions (both 5.1 and 3.51) work
 - Client is irrelevant, can be repeated with Access, CR XI, PB...

Is this correct? Can we add something?

Could it be that you are getting disconnected from MySQL server due to some timeout set too short?

More information answering to Tonci:
   - My server is in a remote computer in the same building
   - I have installed both: 5.1.4 and 3.51
   - Sorry I don't know what is CR, XI, PB......
   - My server is in production about 5 years, and my colleges and me (about 7 persons) connect daily with MySQL. I only am that is trying 5.1.4 ODBC version.

Other new tries that I have made:
   - I have uninstalled 3.51 ODBC driver. Result: doesn't solve problem. After I have install 3.51 again.
   - I have tried that Roman has proposed about to create an user DSN connection. Result: This work very fine in my computer too!!!
   - I have looking Windows register with “regedit”. Result: It seem that in user DSN and system DSN keeps apparently the same information.
   - I have made a new OpenOffice database (.odb) with a link to an ODBC 5.1 connection. Result: Impossible to connect.
   - The same last try (OpenOffice database with a link) with 3.51 ODBC connection. Result: I have connected, and work fine.

Best regards,

José Miguel

Thx folks, 
nice to read we're not alone with this problem.
Our Setup:
- MySql Server on remote box
- System DSN used
- c/ODBC 5.1.4 used on Client XP Machine, User has Administrator rights
- Flags used: enable safe options, ignore space after function names
- Clients: Microsoft Access 2007, Business Objects Crystal Reports XI, Powerbuilder v. 11.2
- (Occasionally) prompts for Mysql login credentials with use of SYSTEM DSN (this is the Problem !)
- (Same here, at least after short tests doesn't seem to) prompt for login credentials with use of USER DSN 
- no timeout set (wouldn't know where)

Guys, this seems completely environment related to me...

> If the mouse point flies to the old link tables in Access, it appear an “yellow label”
explaining the connection link of the table : Server, Port, DB, DSN, table name, by and
by...
If the mouse point  flies on a new link table, the “yellow label” only shows: DSN, and
table name.

It should show DNS;TABLE...

Does MySQL server *should* reside on remote box? Anyone repeated locally? If not, could it be network error? If do, please add some netsat info, or wireshark to process.

Are there aborted_clients or errors in MySQL server error log?

XP-specific bug. Cannot reproduce on Windows 2003.

fix

Attachment: bug36203.diff (application/octet-stream, text), 2.42 KiB.

Among other small fixes, the main problem was the memory initialization in the debug build of the driver sets all uninitialized char arrays to known values to detect for use of uninitialized memory. In release mode, this is not done and the detection for the WinXP DSN lookup bug did not work correctly. This fix initializes the memory so we can properly detect if the bug is encountered.

Can this become a problem once again with XP SP3?

Patch committed as rev 1113. Will be released in 5.1.5.

additional fix

Attachment: bug36203_2.diff (application/octet-stream, text), 983 bytes.

Additional fixes committed as rev 1115.

An entry has been added to the 5.1.5 Changelog:

The ODBC connector randomly uses logon information stored in odbc-profile, or prompts the user for connection information and ignores any settings stored in odbc-profile.

Is this fix also applied in professional version 5.1.5 of the ODBC connector ( for commercial use ) ?

Had the same issue with Vista here, random drops and sometimes prompting for credentials again. Moving the DSN connection from the System defined space and redefining in the User DSN solved my problem with a remote connection to MySQL via ODBC and Crystal X

disregard my above post... it only delayed the inevitable.

I had the same problem running 64-bit Windows 7 Ultimate and using Access 2010 and Mysql odbc connector version 5.1.8 (mysql-connector-odbc-5.1.8-winx64).  I solved the problem by uninstalling the 64 bit version of Mysql Connector and then installing the 32-bit version.  I then used the program Odbcad32.exe to create a 32 bit DSN connection for Mysql in which I saved the user credentials and which I save using a name that included the 32bit designation.  When I then linked the tables to Access 2010 using the External Data linked tables option, I used the user dsn connection that I had saved using Odbcad32.exe (I found that connection, surprisingly under the Machine tab for the dsn connections).    I checked the SAVE PASSWORD box.  After logging off of access 2010 and back in, I was NOT prompted for credentials when openning the MySql linked tables.