Forum Discussion
Power BI Report Server cannot connect to MySQL despite the client drivers being installed
MarkJEverest wrote:
When looking in ODBC (64-bit) in control panel the latest MySQL drivers are showing as installed, so I know the installation has gone fine.
The ODBC drivers are registered differently to the .net drivers so you cannot assume that the .net drivers are OK just because the ODBC ones appear to be there.
MarkJEverest wrote:
However, when I try and do the same thing on the production server I get the following error after clicking the "Test connection" button:
MySQL: We were unable to find a database provider with invariant name 'MySql.Data.MySqlClient'.
I would suggest trying to uninstall and the re-install checking the options that are chosen as this suggests that there is an issue registration of the .net drivers.
I will share my findings because I have struggled with this error for a few weeks. Although I'm a noob in tech, I managed to sort out the issue.
I started having the following error when selecting the MySQL data source connection in Power BI:
"This connector requires one or more additional components to be installed before it can be used."
With a 'Learn more' option to redirect to downloading the latest connector version from MySQL.
I did uninstall and reinstall that SEVERAL times, and still I could not do anything about that error, so tried finding different ways to access.
Adding the MySQL query in the "Blank Query" format helped me a lot finding the new error:
"DataSource.MissingClientLibrary: We were unable to find a database provider with invariant name 'MySql.Data.MySqlClient'."
Looking it in the internet helped me finding part of the info. I did install the WebCommunity MySQL installer. After that, the connector got upgraded and the error changed to the following:
"An error ocurred creating the configuration section handle for system.data: Column 'InvariantName' is constrained to be unique. Value 'MySql.Data.MySqlClient' is already present. (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config line 152). This is a configuration issue with the file 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config'."
I accessed the file and noticed that it is an XML file which includes the following:
<system.data>
<DbProviderFactories>
<add name="MySQL Data Provider"....
<add name="MySQL Data Provider"....
<add name="MySQL Data Provider"....
<add name="MySQL Data Provider"....
</DbProviderFactories>
</system.data>
And the only thing I needed to do, is:
- Uninstall all the connectors I did install previously
- Remove all rows from that section
- Reinstall the latest version of the connector
- Test it again (NOT EVEN NEEDED TO CLOSE POWER BI)
I REALLY hope this helps everyone with the same issue.
Thank you this was really helpful after hours trying to figure out this issue
