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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SandipD
New Member

Unable to connect MYSQL | Details: "MySQL: Unable to connect to any of the specified MySQL hosts."

Have Workbench intalled. Able to use MYSql dataset in Excel too. but unable to connect in POwerBI.

Details: "MySQL: Unable to connect to any of the specified MySQL hosts."

 

Installed .Net connector.

 

Pls help. 

13 REPLIES 13
italojsr_99
New Member

I was able to connect following these steps : 

Inside Power BI, after Get Data > Database Mysql  on the server field (Figure 1), i used the "hostname" given from the server on the mysql (Figure 2)

italojsr_99_1-1692184787953.png

italojsr_99_2-1692184895915.png

After it, i went to this screen which asked for the name of the server and the password.

italojsr_99_3-1692185064731.png

 

Then it worked.

 

 

 

zanottiluca
Helper II
Helper II

hi guys, 

I encounter this issue when inputting username and password (second stage after the server:port database name) do you know why? 

Thansk
Luca

duvanvilla
New Member

I was able to solve it, you must send the host name at the end of the port, example if you are going to connect to the server 192.50.60.112:13287 server : port

 

ya lo pude solucionar, debes enviar en el nombre del host al final el puerto, ejemplo si te vas a conectar al servidor 192.50.60.112:13287 servidor : puerto 

Anonymous
Not applicable

A work around for this issue is to use the ODBC connection for MySQL. I recommend using the Unicode instead of the ANSI ODBC in case there is Unicode data in any column in the database.

 

To do this, in Windows 10, run ODBC Data Sources in windows and give it a name (i recommend not having any spaces in the name).
This program is located at:
64 bit: %windir%\system32\odbcad32.exe
32 bit: %windir%\syswow64\odbcad32.exe

Note: The version above that you should use must match your PowerBI version (32bit or 64 bit).

I created my connection with MySQL ODBC 5.3 Unicode Driver, but you can use a newer version as version 8 is also available:
https://dev.mysql.com/downloads/connector/odbc/

To Configure ODBC DSN (Data Source Name) Connection for MySQL:

Step 1> Click Add
Step 2> Select MySQL ODBC X.X Unicode Driver then click Finish
Step 3> In pop-up menu,
     A) specify data source name (this is a DSN name that is local to your PC) --- be consistent if sharing this model with other users, and I recommend NOT using spaces in the name --- Ex. rpt_server
     B) specify the TCP/IP server as the server name that you want to connect to
     C) specify the port if different from 3306.
     D) use your username & password to test the connection (these aren't saved)
     E) specify the database
     F) Most important step: Click Details and Check Enable Cleartext Authentication
     G) Click Test, assuming the test succeeded click OK.


To Connect to ODBC DSN Connection:

In Excel or PowerBI, use ODBC connection and select the DSN name that you used in 3A.

SandipD
New Member

Hi Charlie & Team powerbi,

 

Any help, 

Hi SandipD,

 

You said that you have installed  MySQL Connector/Net 6.6.5 for Microsoft Windows, please ensure that matches your Power BI Desktop installation (32-bit or 64-bit).

 

In addition, please create a dsn for your hostinger database, then connect to it via Get Data>ODBC entry in Power BI Desktop and check if it is successful.

 

Regards,

Charlie Liao

Hi Charlie & Team powerbi,

"MySQL: Authentication to host 'localhost' for user 'root' using method 'caching_sha2_password' failed with message: Unknown database 'gdb046'"

 

Please help me.

 

Thanks.

 

PrasanthiJINAKA_0-1668511025981.png

 

Anonymous
Not applicable

@v-caliao-msft
"You said that you have installed MySQL Connector/Net 6.6.5 for Microsoft Windows, please ensure that matches your Power BI Desktop installation (32-bit or 64-bit)."

I've had the same error and patching/reinstalling MySQL Connector have helped. I've also downloaded newest version of Power BI. One of those above helped, try this if you have this error again. 

Best,
Przemyslaw

add MySQL posrt like "ServerName:portnumber"

 

use database credentials i.e username and pass

I also get the same error but I dont understand what do you mean by :add MySQL posrt like "ServerName portnumber":

I dont see any option to give the port number for the DB server. 

I tried the odbc connector option but unfortunately 32-bit is able to connect the DB but PowerBI throws an error when connecting to it. But when I try to connect using 64-bit odbc connector , the connector itself cant connect to the db. 

I tried clear the cache and tried to connect again. Still it fails. 

The credentials is correct and I am sure about that

v-caliao-msft
Employee
Employee

Hi @SandipD,

 

Which version of desktop are you using? Please try to update your desktop to latest version and check if this issue persists or not. The latest version is available on the link below.
https://powerbi.microsoft.com/en-us/get-started/

 

Regards,

Charlie Liao

Hi Charlie,

 

I have tried but still getting same error.

It happens the same to me too

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.