MySQL Bugs: #69156: can't retrieve data using MySQL ODBC driver from Microsoft Excel 2010 PowerPivot

Bug #69156	can't retrieve data using MySQL ODBC driver from Microsoft Excel 2010 PowerPivot
Submitted:	6 May 2013 15:43	Modified:	31 Oct 2013 7:48
Reporter:	An R	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.2w	OS:	Windows (vista 64bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	excel 2010, ODBC, powerpivot

Description:
Related to Bug #69154, after installing both 32 and 64 bit drivers I am able to connect to the MySql database.

However when I try to fetch data, I get the error:

ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.27-log]

How to repeat:
0) Windows Vista 64bit
0) Install MySql ODBC 32 and 64 bit drivers
1) Open Excel 2010 32bit's PowerPivot add-in (error happens also with 64 bit Excel)
2) Click on 'data from external datasources'
3) Choose ODBC 
4) Click on "build ..."
5) On the first tab, choose provider "Microsoft OLE DB Provider for ODBC Drivers". Click next.
6) Chose user DSN
9) Choose catalog. Click OK.
10) Click next
11) Choose write custom query and write a valid query (I wrote 'select * from users;')
12) Click FINISH

I get the error: ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.27-log]

* No logged errors found in %temp%/myodbc.sql

I wish to comment, I am indeed able to 'connect' to the DB (since I can read the list of tables), but I can't fetch data...

When fetching data, I get the error: ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.27-log]

Strangely, it DOES work if one does NOT select a 'catalog' in the data link properties... I was lucky to find this out !

sorry, i was mislead by doing a connection in win7 32 bit...

in vista 64 bits I cant get it to work...

Where can I find this PowerPivot addition?
I see INSERT/PivotTable, but it does not fit the steps you provided.

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

I'm having the same issue.  However, I cannot get it to work when I don't select a catalog either.

Unfortunately, I was not able to repeat the bug.
Also, I need my question answered:

Where can I find this PowerPivot addition?
I see INSERT/PivotTable, but it does not fit the steps you provided.

Thanks.

Hi,

I am having the same problem. Below are my system details:
Windows 7 Professional (64-bit)
Service Pack 1
Using MS Office Professional Plus 2013 (64 - bit)
Using PowerPivot Add-on with Excel 2013
Using MySQL ODBC 5.2 ANSI Driver as the ODBC Connector

I did the same steps as An R and am getting the same error HY010.

Please assist.

To answer your question about accessing PowerPivot - you need to have Excel 2013 Professional Plus version and enable the PowerPivot add-in. Once you do that - you should be able to see PowerPivot tab on the top ribbon in the Excel Window. Please follow the instruction here to import data from MySql DB. http://office.microsoft.com/en-us/excel-help/get-data-using-the-power-pivot-add-in-HA10283... (Use the Other(OLEDB/ODBC) option)

Hope this helps.

Thank you for the instructions.
I was able to repeat the problem. Setting the bug status Verified.

Screenshot with the error

Attachment: powerpivot_error.png (image/png, text), 41.45 KiB.

I have the same problem on Windows 7 Home Premium, except the last part of the error message is a bit different:

ERROR [HY010] [MySQL][ODBC 5.2(a) Driver][mysqld-5.0.96-community].

When I "validate" the query (in PowerPivot) the query passes, but fails to return data.

To follow up, I downgraded my ODBC Driver to 3.51.30 (64-bit) and this error disappeared, but a new error showed up intermittently:  Arithmetic operation resulted in an overflow.

Debugging my query resulted in finding one field that was generating this error. The field was a simple integer field, each record holding one of 4 values - 0, 5, 10 or 20.  I have no idea why this field resulted in this error.  

I was able to work around the error by CASTing the field as a CHAR.

I'm also experiencing this error with 64bit Excel 2013 + PowerPovit and MySQL 5.5 - same version of the connector as the reporter.

is there any status on a fix for this?

I could resolve the issue of "Log in Failed. Cannot retrieve catalog information" by checking by ODBC settings and ensuring it is Unicode and not ANSI. You can go to Control Panel --System & Security--Admin tools---select your ODBC connection.

Hi All,

I'm having the same problem in using powerpivot for MySQL data source.

Repro steps:

1. Install mysql-connector-odbc-5.1.13-winx64.msi.
2. open "C:\Windows\SysWOW64\odbcad32.exe"
3. Click Add under user DSN
4. select MySQL ODBC 5.3 Unicode Driver
5. click Finish
6. in Popup window fill the data and click ok. 
7. open excel 2010 version
8. click on PowerPivot tab ( this is an add-in, download this from Microsoft site for office 2010 and install. It will show you in a tab in excel)
9. click on PowerPivot window option given in very left side below the file tab.
10. Now click on "From other source" option
11. from list select "Others (OLEDB/ODBC) and next
12. click on build
13. fill the data that you used while creating DSN name
14. click Ok button than finish.
15. Now window will show you list of tables.
16. Select and table and click on Preview and filter option. 
17. It will show you error like "Failed to retrieve data from kimai_evt. Reason: ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.27]You have an error in your SQL syntax"

Expected: data should be visible there to filter.
Actual: Error coming there as I mentioned in step 17.

Hope this is simplest form to make you it verify and resolve it so we can further proceed with  the same.

Hi, has this issue been resolved, same challenges and still researching solutions.

Posted by developer:
 
This bug is no longer reproducible with Connector/ODBC version 8.0.32.
It was tested with Excel from Microsoft Office 365 in Windows 10 64-bit.
The data was successfully retrieved and displayed via the Power Pivot tool.