search

mariadb-corporation / mariadb-powerbi

Power BI DirectQuery Connector
https://mariadb.com
3 stars 1 forks source link

Not able to connect with Maria Db #10

Closed tpervaiz closed 3 years ago

tpervaiz commented 3 years ago

I want to use MariaDB database in Power BI however whenever i am entering server details in format "serverIP:port;databasename" i am getting the following error. [ma-3.1.11]Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 0

Can someone help me to solve the above issue

dversoza commented 3 years ago

I'm getting the same issue. After some new attempts to refresh my reports, it worked. But I didn't change anything.

ilyagithub1 commented 3 years ago

@tpervaiz The issue seems to be related to your MariaDB setup. The reasons for the error you are experiencing is most likely either MariaDB Server network configuration or security settings.

Could you please provide more info about your setup - where's MariaDB running (same network as the Power BI Desktop PC, cloud, Docker, elsewhere)?

tpervaiz commented 3 years ago

@tpervaiz The issue seems to be related to your MariaDB setup. The reasons for the error you are experiencing is most likely either MariaDB Server network configuration or security settings.

Could you please provide more info about your setup - where's MariaDB running (same network as the Power BI Desktop PC, cloud, Docker, elsewhere)?

Hi @ilyagithub1 we are using the network however different machine as MariaDB is on Windows Server 2019 and Power BI Desktop is on my PC windows 10.

tpervaiz commented 3 years ago

I'm getting the same issue. After some new attempts to refresh my reports, it worked. But I didn't change anything.

@dversoza i have tried several time and trying since 1 month but unable to connect

ilyagithub1 commented 3 years ago

Hi @ilyagithub1 we are using the network however different machine as MariaDB is on Windows Server 2019 and Power BI Desktop is on my PC windows 10.

@tpervaiz it is a network configuration-related issue.

How To Test

A simple test to check whether it's a network issue indeed is to run telnet on your Windows 10 PC (you might need to add Telnet Client in Windows Features for this command to become available):

C:\>telnet <servername-or-ip> 3306

When running the telnet command, make sure to use the same servername-or-ip and port values as in Power BI Desktop.

If you cannot connect to your MariaDB instance with telnet, then you need to check your network configuration.

Possible Causes

  1. Firewall. Check your Windows Server 2019 and make sure Windows Firewall and any 3rd party firewall is disabled. It's probably a good idea to disable Windows Defender temporarily, too.

  2. MariaDB Server binding address config in my.cnf:

bind-address = 127.0.0.1

Loopback address binding does not support remote access to the server over the network. So, the above is incorrect. A correct bind-address should be either disabled or a valid IP used, e.g.:

# bind-address = 127.0.0.1

  1. root is used to access MariaDB Server remotely. It's best to create a dedicated user account for remote access.

For detailed steps check this discussion thread: https://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste

Try starting with the telnet check and then working through the possible causes.

tpervaiz commented 3 years ago

Hi @ilyagithub1 we are using the network however different machine as MariaDB is on Windows Server 2019 and Power BI Desktop is on my PC windows 10.

@tpervaiz it is a network configuration-related issue.

How To Test

A simple test to check whether it's a network issue indeed is to run telnet on your Windows 10 PC (you might need to add Telnet Client in Windows Features for this command to become available):

C:\>telnet <servername-or-ip> 3306

When running the telnet command, make sure to use the same servername-or-ip and port values as in Power BI Desktop.

If you cannot connect to your MariaDB instance with telnet, then you need to check your network configuration.

Possible Causes

  1. Firewall. Check your Windows Server 2019 and make sure Windows Firewall and any 3rd party firewall is disabled. It's probably a good idea to disable Windows Defender temporarily, too.
  2. MariaDB Server binding address config in my.cnf:

bind-address = 127.0.0.1

Loopback address binding does not support remote access to the server over the network. So, the above is incorrect. A correct bind-address should be either disabled or a valid IP used, e.g.:

bind-address = 127.0.0.1

  1. root is used to access MariaDB Server remotely. It's best to create a dedicated user account for remote access.

For detailed steps check this discussion thread: https://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste

Try starting with the telnet check and then working through the possible causes.

Thanks @ilyagithub1 through telnet connection I am getting below

Y 5.5.5-10.1.34-MariaDB▄<SkDOB;☻?á§}k#iiR?%9/|Jmysql_native_password

and then connection gets lost

ilyagithub1 commented 3 years ago

Thanks @ilyagithub1 through telnet connection I am getting below

Y 5.5.5-10.1.34-MariaDB▄<SkDOB;☻?á§}k#iiR?%9/|Jmysql_native_password

and then connection gets lost

@tpervaiz IP socket looks fine.

Ok, let's find the error code from Power BI logs.

Please do the following:

  1. Enable tracing in Power BI Desktop: 1.1. In Power BI Desktop, open File > Options and settings > Options > Diagnostics. 1.2. Click Clear Traces folder. 1.3. Click Open crash dump/traces folder. This will the traces folder in Windows Explorer. Keep this window opened. 1.4. Check the Enable tracing box. 1.5. Click OK. This will save the changes and close the Options dialog. PowerBIDesktop-Options

  2. In Power BI Desktop, try connecting to your database on Windows Server 2019 again.

  3. After you see the Lost connection... error message in Power BI Desktop, close Power BI Desktop app. It is important to close Power BI Desktop before looking into its logs because it flushes all memory-cached logs on exit.

  4. Go back to the crash dump/traces folder Windows Explorer window that was opened in step 1.3. It should have a number of text files like in the screenshot below: PowerBIDesktop-crash-dump-traces-folder-example

  5. Open all files named Microsoft.Mashup.Container.NetFX45.log in a text editor. 5.1. In the opened Mashup Container logs, search for an occurrence of the error string you saw in Power BI Desktop UI: Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 0. Note the misspelled word inital in the driver's message and make sure to check for typos in the search string, or search for a shorter substring like handshake. Most likely, you'll find several occurrences of the string in a single log file. 5.2. Examine the matching string to the left - there should be the driver error code in the form of ERROR [XXXXX], e.g.:

Message: ERROR [08S01] [ma-3.1.11]Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 0

  1. Please post here the ERROR code from the square brackets (highlighted in bold in the sample above). This error code should give a clue to the root cause of the error.

In the example above, the error code 08S01 would indicate an issue with MariaDB Server configuration. Specifically, the bind-address did not allow remote connections. Yet, the error code for the same error text can vary. That's why it is important to find out what error code ERROR [XXXXX] you see in your logs.