MySQL / PostgreSQL error on gateway

[brendan_w]

Hi, so the Power BI reporting was working perfectly before this weekend. The Power BI solution uses connections to both a PostgreSQL and a MySQL database and a variety of Excel/CSV local sources.  Over the weekend there was a change to the PostgreSQL server and the credentials had to be updated. Since then there's been the following error for connecting to this DB:

Unable to connect: This data source cannot connect to any gateway instances of the cluster. Please find more details below about specific errors for each gateway instance.

The MySQL DB initially gave a many connections error but then gave the above error after a flush-hosts was performed on the server. 

Unable to connect: This data source cannot connect to any gateway instances of the cluster. Please find more details below about specific errors for each gateway instance

Activity ID:	5ce3dac1-7e97-4ce8-a83f-5c7e510a5206
Request ID:	026930e6-8ada-352c-d0e5-413809758602
Cluster URI:	https://wabi-north-europe-redirect.analysis.windows.net
Status code:
Time:	Mon Oct 01 2018 16:23:28 GMT+0100 (GMT Summer Time)
Version:	13.0.6781.178

Main-Enterprise-2:

Received error payload from gateway service with ID 1630208: Mashup expression evaluation error. Reason:

I then tried the next steps as per advice on this thread https://community.powerbi.com/t5/Service/Enterprise-gateway-quot-cannot-connect-to-the-mashup-data-s...

• uninstalled the enterprise gateway and the legacy personal gateway on our virtual machine (AWS)
• installed latest version of the enterprise gateway
• created a new gateway cluster
• stopped and started gateway service on VM

I'm still getting the same error when connecting to the DBs. This issue is preventing refreshes of all internal and external reporting, so any help would be very much appreciated!

Many thanks,

Brendan

[brendan_w]

Apologies, forgot to mention that the PostgreSQL DB is accessed through an ODBC connection string to an OODBC. It still reports the same error

Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .; ErrorShortName: MashupDataAccessValueException[ErrorCode=-2147467259,HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259,HResult=-2147467259]

[v-qiuyu-msft]

Hi @brendan_w,

From your description "Over the weekend there was a change to the PostgreSQL server and the credentials had to be updated. ", it looks like the PostgreSQL server name or IP address and credential are changed, right? 

As you are creating a ODBC DSN to connect to PostgreSQL server, please check this ODBC DSN firstly, modify the server name/IP address and credential and test connection ensure everything is fine. 

On Power BI service, please go to dataset settings of this dataset, check which gateway is configured and what's the status, ensure the used gateway is online. 

Best Regards,
Qiuyun Yu 

[brendan_w]

Hi @v-qiuyu-msft,

Thanks for the reply. The issue was with the ODBC DSN for the PostgreSQL DB.

Unfortunately I still get the same issue for the MySQL DB. It works after an initial flush-hosts but then reverts to the error given above after 1 or 2 reports attempt a refresh. Is there any known fixes to this MySQL many-connections issue?

Regards,

Brendan

[v-qiuyu-msft]

Hi @brendan_w,

Does the same error occurs when the refresh is failed? 

Please share detail refresh information (RequestID, time) and on-premises data gateway log files. You can upload them to OneDrive and send to me via private message. 

Best Regards,
Qiuyun Yu 

[brendan_w]

@v-qiuyu-msft

Please check your inbox, I've sent on the log files as requested. 

Many thanks,

Brendan

[v-qiuyu-msft]

Hi @brendan_w,

From internal trace, I found error:

1. The measure Projects # Stock Turns in table KPI Measures has error "Column 'Predicted Stock Turns' in table 'Monthly Data' cannot be found or may not be used in this expression." 

2. The measure Latest Transaction (SKUs) in table KPI Measures has error "Argument '2' in SUMX function is required."

3. The measure Sage Revenue in table SAGE OBATICS Verification has error "Column 'Sage Invoice Value' in table 'Monthly Data' cannot be found or may not be used in this expression." 

4. The measure Num Weeks in table Projected KPI Measures has error "Column 'WeekOfYear' in table 'Monthly Data' cannot be found or may not be used in this expression."

Please download the report from Power BI service then open it in Power BI desktop, check these measures. 

Best Regards,
Qiuyun Yu 

[brendan_w]

Hi @v-qiuyu-msft,

Thanks very much for the response, I'll investigate and let you know the update. 

As an aside, how can one perform an internal trace? Or is it only available to Microsoft employees with access to the back end. It look like it would be an excellent technique to have when diagnosing issues as a developer of PBIX files. 

Regards,

Brendan

[v-qiuyu-msft]

Hi @brendan_w,

Sorry it's internal only in Microsoft. Please let me know if it works follow my previous post. 

Best Regards,
Qiuyun Yu 

[brendan_w]

Hi @v-qiuyu-msft,

Thanks for the reply. I tried the above but I still get issues. 

I'm able to refresh all the reports with no many connection errors from the desktop after a flush-hosts. 

I then published to the service, configured the MYSQL DB on the Gateway and tried to refresh one of the reports. 

The report refreshes correctly but then the Gateway stops working with the following error on connecting to the MYSQL DB.

Then when I try to connect to the Gateway by re-entering the password, I get the same many connections error. 

I've sent the logs by private message to you again.

Kind regards,

Brendan

[v-qiuyu-msft]

Hi @brendan_w,

I would suggest you create a support ticket to Microsoft and let engineers look into your environment to see what's going on. 

Best Regards,
Qiuyun Yu