Bug #16653 MyODBC 3 / truncated UID when performing Data Import in MS Excel
Submitted: 19 Jan 2006 21:21 Modified: 26 Feb 2008 10:00
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Microsoft Windows (Win XP)
Assigned to: Jess Balint
Triage: D3 (Medium)

[19 Jan 2006 21:21] [ name withheld ]
Description:
This problem is associated with the use of MS Excel on Win XP using Connector/ODBC.  Connector/ODBC was installed and tested, without error, from the Data Sources (ODBC) access from the Administrative Tools in XP.  However, when I try to import data to Execl from the MySQL database the "OK" button in the Connector/ODBC dialog returns the message "[MySQL][ODBC 3.51 Driver]User cancelled."  See steps to repeat for full description.

How to repeat:
Connector/ODBC was installed and tested correctly from the Data Sources (ODBC) dialog of the Administration Tools.  1) Open Excel.  2) Select "Data/Import External Data/Import Data" menu item.  3) Select "New Source" from Select Data Source dialog.  4) Select ODBC DSN and installed myodbc1.  5) Databases and tables appear in the dialog consistent with the MySQL database.  6) Select a database and table.  7) Select Finish, saving the .odc file.  8)  Select and Open newly create .odc file.  9) Select Ok to import data.  10) Connector/ODBC dialog appears with a username different from that used to configure myodbc1 (the last character has been dropped).  11) Correct user name and select Ok from Connector/ODBC dialog.  12) Excel dialog appears with message "[MySQL][ODBC 3.51 Driver]User cancelled." 13) Data is not imported.
[20 Jan 2006 10:24] Valeriy Kravchuk
Thank you for a problem report. Can you turn on tracing of the ODBC calls for that DSN and upload a trace file for the actions you described?
[20 Jan 2006 15:01] [ name withheld ]
Debug file

Attachment: myodbc.log (application/octet-stream, text), 16.41 KiB.

[21 Feb 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[21 Feb 2006 14:24] [ name withheld ]
No feedback was provided for this bug for over a month, so it is
being suspended automatically....

I provided the requested debug log file on January 20 2006...
[22 Feb 2006 8:50] Valeriy Kravchuk
You had uploaded the file, but had not changed status back to "Open". It is not done automatically. That's why this bug report had not got proper attention. Sorry.

Anyway, according to the trace, you have no "myodbc1" database. Please, connect using the mysql command line client and send the results of

SHOW DATABASES

statement.
[23 Mar 2006 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 Jun 2006 13:44] Tonci Grgin
Although problem is repeatable I think this is not a bug.
How to repeat:
  Set up MySQL server (Win/Linux). Define user account with password
  Set up System DSN for MyODBC with user account that has password
  Test connection
  Open MS EXCELL
  Data / Import external data / Import data > point to New source > ODBC DSN > choose MyODBC ...
  After clicking "Open", MyODBC setup screen shows with user-name truncated
It is essential that user has password. With passwordless account everything works. It is also essential not to save password to excel connection file.
Snip from EXCEL connection file:
  <odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=&quot;DATABASE=test;DESCRIPTION=test;DSN=myodbc1;OPTION=524604;PORT=3307;SERVER=munja;SOCKET=/tmp/mysql.sock;UID=root&quot;;Initial Catalog=test</odc:ConnectionString>
   <odc:CommandType>Table</odc:CommandType>
   <odc:CommandText>`test`.`tstidx`</odc:CommandText>
  </odc:Connection>
  <odc:Connection odc:Type="ODBC">
   <odc:ConnectionString>DATABASE=test;DESCRIPTION=test;DSN=myodbc1;OPTION=524604;;PORT=3307;SERVER=munja;SOCKET=/tmp/mysql.sock;UID=roo</odc:ConnectionString>
   <odc:CommandText>SELECT * FROM `test`.`tstidx`</odc:CommandText>
  </odc:Connection>

It is visible that first connection string is OK but second one is truncated. However odd, connection sometimes succedes, usually on first attempt... I can't imagine why Excel can not read the same info correctly twice ...
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\myodbc1]
"Driver"="C:\\WINDOWS\\system32\\myodbc3.dll"
"DATABASE"="test"
"DESCRIPTION"="test"
"OPTION"="524604"
"PWD"="xxxxxxx"
"PORT"="3307"
"SERVER"="munja"
"SOCKET"="/tmp/mysql.sock"
"UID"="root"

Workaround: Save password into Excel connection file and everything works (both connection strings are equal):
  <odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=&quot;DATABASE=test;DESCRIPTION=test;DSN=myodbc1;OPTION=524604;PWD=xxxxxxxx;PORT=3307;SERVER=munja;SOCKET=/tmp/mysql.sock;UID=root&quot;;Initial Catalog=test</odc:ConnectionString>
   <odc:CommandType>Table</odc:CommandType>
   <odc:CommandText>`test`.`test1`</odc:CommandText>
  </odc:Connection>
  <odc:Connection odc:Type="ODBC">
   <odc:ConnectionString>DATABASE=test;DESCRIPTION=test;DSN=myodbc1;OPTION=524604;PWD=xxxxxxxx;PORT=3307;SERVER=munja;SOCKET=/tmp/mysql.sock;UID=root</odc:ConnectionString>
   <odc:CommandText>SELECT * FROM `test`.`test1`</odc:CommandText>
  </odc:Connection>
[10 Jul 2006 12:07] Paul Keenan
I see exactly the same problem.  The workaround does exactly that - it works around the problem, it doesn't solve it.

I didn't understand why the status is now "Not a Bug" - can you explain ?  Are you saying the bug is in Excel, not the MySQL ODBC code ?

Thanks,
Paul.
[23 Apr 2007 21:37] Andrew Tonner
Can someone clarify?  The bug here (that the OK button returns "User Cancelled") is not addressed.
[26 Jul 2007 21:10] Erica Moss
I am still running into this problem in 3.51.17

Tonci's observation is correct that in the first block of the .odc file the OLEDB block, the UID is correct, but in the second block (ODBC), it is truncated.

I have never experienced a successful connection though as he says has happened.  It always fails, and if you try to correct the UID while the import is in process, Excel crashes.

I'm not positive that this is our problem rather than MS, however I tried the same test against a SQL Server DSN and it worked fine.  Both .odc files are attached.  These can be placed in the "My Data Sources" folder and run.

This problem has come up a few times now, so we should either figure out what's causing it or at least document the workaround in our User Manual.

The User Cancelled error, means just what it says, you clicked cancel on the MyODBC config app, because that's the only thing that you can click, assuming that the UID and password are incorrect.  Therefore that is simply a symptom not a problem.  The real problem is that the connect strings in the .odc files aren't correct.  The password needs to be added to both, and the UID needs to be corrected in the second.
[5 Jan 2008 0:05] Jess Balint
fix + test

Attachment: bug16653.diff (application/octet-stream, text), 10.55 KiB.

[5 Jan 2008 0:06] Jess Balint
Changed writing DSN strings to include the semi-colon at the end. Excel (or whatever sub-component for ODBC) was removing the last character and this works around it.
[18 Feb 2008 2:19] Jess Balint
Patch committed as rev 1035, returning "null" as opposed to blank which was in the patch. Will be released in 3.51.24.
[23 Feb 2008 22:46] Jess Balint
Patch was committed incorrectly, fixed in rev 1047.
[26 Feb 2008 10:00] MC Brown
A note has been added to the 3.51.24 changelog: 

With some connections to MySQL databases using Connector/ODBC, the connection would mistakenly report 'user cancelled' for accesses to the database information.
[25 Jun 2008 19:27] Niklas Lindquist
The immediate result of the connect call

Attachment: mysqlodbcdbg.png (image/png, text), 48.16 KiB.

[25 Jun 2008 19:31] Niklas Lindquist
Sorry. Previous attachment ended up in the wrong thread.