Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Power BI Desktop MySQL connector

Hi all,

I'm trying tot establish a connection to a MySQL database. Both PBI and MySQL are installed on the local machine.

Upon connection to the database, I get an error mentioning a missing system variable named 'lower_case_table_names'.

Research points me towards the MySQL version.

I still have to use an outdated MySQL instance due to software incompatibility.

 

Can someone please share ideas on how to connect to a MySQL version 5.0?

8 REPLIES 8
Syndicate_Admin
Administrator
Administrator

Then you must have a rather old version of MySQL ?

Syndicate_Admin
Administrator
Administrator

Thank you!

I had tried those, but they didn't even connect to my base.

I just found the one that does... It's 8.0.13 in my case.

Best regards

Gaston

Syndicate_Admin
Administrator
Administrator

Hello dears,

Has anyone had any luck fixing the 'lower_case_table_names' system variable problem?

I was connecting to the database without problems for almost 2 years, and suddenly I started having this problem.

I've tried several versions of the .NET connector but can't find the solution.

We look forward to your comments.

Thank you very much in advance.

Gaston

8.0.28 is a known good version. 8.0.30 and 8.0.31 might work as well.

lbendlin
Super User
Super User

here's an intriguing workaround

 

https://docs.microsoft.com/en-us/power-query/connectors/mysqldatabase

 

I couldn't find any point about the minimum version but I would think that the MySQL Community Installer (currently version 8.0.21) would allow you to connect to your legacy database as well?

Anonymous
Not applicable

Hi Ibendlin,


Thanks for your reply.
I'll give the SQL Native Client a try.

 

What I can confirm, is that the MySQL Community Installer 8.0.21 is not working at all.

I've tried several versions, not one is working.

I've analyzed the returned error message using WireShark, and I was able to get some more information.
Apparently, at some point, MySQL has altered their routine to authenticate against the database, which is causing this connection to fail. My guess is that the MySQL Community Installers only use the new method, and the old authentication method (as used by MySQL 5.0 and below) is ignored.

Anonymous
Not applicable

Good afternoon DIMBACKA.

I hope you are well.

 

Did you manage to connect your data in the end?

I am also facing the same error mentioning a missing system variable named 'lower_case_table_names'. (MySQL V5.0 User as well)

Anonymous
Not applicable

Hi LukeJWest24,
Unfortunately not, at least not with a direct connection.
I ended up using an ODBC connection, which eventually gets the data into Power BI, but I must say this is not the ideal situation.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.