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 22:21	Modified:	26 Feb 2008 11:00
Reporter:	[ name withheld ]
Status:	Closed
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Microsoft Windows (Win XP)
Assigned to:	Bugs System	Target Version:
Triage:	D3 (Medium)

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;POR
T=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=xxxxxxx
x;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.