MySQL Bugs: #16653: MyODBC 3 / truncated UID when performing Data Import in MS Excel

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:	Windows (Win XP)
Assigned to:	Jess Balint	CPU Architecture:	Any

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.

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?

Debug file

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

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".

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...

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.

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".

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>

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.

Can someone clarify?  The bug here (that the OK button returns "User Cancelled") is not addressed.

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.

fix + test

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

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.

Patch committed as rev 1035, returning "null" as opposed to blank which was in the patch. Will be released in 3.51.24.

Patch was committed incorrectly, fixed in rev 1047.

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.

The immediate result of the connect call

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

Sorry. Previous attachment ended up in the wrong thread.