MySQL Bugs: #79616: Connector doesn't enable expected option in Excel 2016 Power Query Menus

Bug #79616	Connector doesn't enable expected option in Excel 2016 Power Query Menus
Submitted:	12 Dec 2015 13:19	Modified:	21 Dec 2015 17:56
Reporter:	Justin P	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.9.8	OS:	Windows (10 Pro 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	Excel 2016, Power Query, windows

Description:
After installing the 'Connector/Net' add-on I was expecting 'MySQL' to show as a data source in the Excel 'Data' > 'New Query' > 'From Database' menu but it doesn't.

It's possible that it should also show in some of the other data menus too, like 'Get External Data' > 'From Other Sources' but doesn't.

Maybe this is a regression problem with Power Query in Excel 2016 which now has a slightly older version of PQ baked in rather than being a separate add-on as was the case in older Excel versions.

I can't be sure whether the fault lies with the Connector or with Excel.

How to repeat:
Using an up to date Windows 10 system and Office 2016 32bit via Office 365, I followed the instructions below substituting the 'Connector/NET' version with the latest 6.9.8.

https://support.office.com/en-us/article/Connect-to-a-MySQL-database-Power-Query-8760c647-...

My install of MySQL is running on the same machine which was installed using the .msi install file and is the latest 64 bit version 5.7.10.

Suggested fix:
Research whether the Connector is registering its' existence correctly to be picked up by Excel.

Hello Justin,

Thank you for the bug report.
I tried to reproduce the issue at my end using MySQL Connector/Net 6.9.8 and Ms Excel 13 & 16 but not seeing any issues in options.
Could you please provide repeatable test case  (exact screenshot, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

screenshots

Attachment: 79616.zip (application/x-zip-compressed, text), 275.74 KiB.

Hi Chiranjeevi,

Apologies, it turns out it's a subtly that MS have introduced in Office 2016.

Someone on Stackoverflow answered this issue which someone else was also having around the same time as me.

http://stackoverflow.com/questions/34230041/excel-2016-connection-to-mysql-in-data-new-que...

It turns out that the new Office 2016 introduces differences in the functionality of the installed desktop apps, even though they are referred to as the same app name across packages they need to be thought of as different editions of Excel now. I think before the differentiating factor between subscription packages was the bundled online services rather than Pro versions of the apps in some packages.

Anyway the starting packages for Office now have a clipped / gimped version of Excel that doesn't include the same range of data source connectivity as the higher end packages.

https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-...

https://support.office.com/en-us/article/What-s-new-in-Power-Query-936B2FCA-4168-41AD-BBBD...

Alternative workarounds;

Power BI desktop (free) has more external source capabilities, strangely including MySQL even though this is a free product vs Excel desktop which is paid for. Not sure if Power BI might pose an issue around sharing the final results though in the free version.

https://powerbi.microsoft.com/en/

'MySQL for Excel' add-on but then that doesn't integrate directly to Power Query but does get the data into Excel where you could then use PQ to reference it from the Workbook it was imported to.

https://www.mysql.com/why-mysql/windows/excel/

Outside of the MS world I think Eclipse Birt is the way I'm going to go with ReportServer.

Thanks for your time.
Kind regards
Justin

Changed status to closed.

I believe I have the same issue and my setup is similar, expect my Office 365 subscription includes ProPlus which (according to the links Justin posted) should include the required functionality. I will post screenshots of my error.

Connector version shown in screenshot is 6.9.8, but I also (first) tried with 6.6.5 which is the version linked in the Excel error message.

screenshots

Attachment: screenshots.zip (application/x-zip-compressed, text), 111.91 KiB.

Hi Patrick, 

You appear to have more functionality showing than I did as I didn't have the MySQL source showing in the list of DB sources as shown in one of your screenshots.

Are you using a 32bit install of Office with the 32bit Connector?

Kind regards
Justin

Yes, I just double checked to make sure.

Hi Patrick,

Something else that occurred to me is I wonder if that error is serving you a somewhat curved ball.  Are you trying to connect using root?  The reason I ask is my Windows 10 install of MySQL won't let me connect with root.  Are you successful with your connection parameters when using them with MySQL Workbench?  If you are trying to connect with root can you try setting up a new MySQL user with the permissions you require and then test it with Workbench and then Excel.

Kind regards
Justin

That was it. I tested with another user and it worked. Thanks Justin.