DM_GWPipeline_Gateway_MashupDataAccessError (Not like other post)

[eTribble]

So I have multiple reports that pull from 1 or 2 MySQL databases, Most pull from the same database in which I get this error. The "table" at the end of the error varies. And this error is not constint, some days out of the 6 reports I get 1 or 2 of the reports that get this error message, some days I dont get any, and about 10 mins before this post I got all 6 (same error message minus the table at the end).

Now If I go to power bi > data sources > and manually click the refresh I never get any errors, this only occurs randomly during the auto refreshes.

I made sure my On-Premises Data Gateway is up-to-date and refreshed/restarted it. The MySQL connections are good with encription not enabled and under Orginization not public.

This has been happening off and on for about a month now.

I would normaly think it would be something on my end, but if it was I wouldnt be able to refresh it without errors via the power bi site or on the desktop editor as well. So Im sure its a odd setting somewhere, anyone have any ideas?

[KossXL]

I have the same problem 
{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"MySQL: Fatal error encountered during data read."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"MySql"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"**********</ip>:3306;admin_ibs"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Fatal error encountered during data read."}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: students_ibs.

[eTribble · Accepted solution]

I fixed it, it was actualy the query itself causeing it to time out. I re-ajusted the query and no more issues.

[IsmaelQR]

Hi @eTribble, 

Excuse me, could you please explain a bit more about the error I'm seeing in Power BI? I would like to better understand the queries you're using in order to solve the problem more effectively. Could you please provide me with more details or explanations about the specific queries you're using?

I've been having the same problem since January and I've been doing a manual refresh every day to avoid this problem. Do you change only the query or also the datasets and DAX formulas?

Regards.

[eTribble]

Its hard to explain without giving away to much buisness info:

But the simple version is the MYSQL query I was useing was causing the MySQL connection to time-out and close becuause it was going over the 60sec limit.

Like if you are using MySQL workbench and run a complex query and it never loads because it fails, this is that.

So my query originally worked because the date range was small, it was only a span of a few months, but as time went on it was growing and growing and when this error occured it was trying to do an complex query with a large date range (multiple years).

So I had to break it up into 2 queries, 1 for the first year, and ran it once, and then set it to not auto refreash ( the data in it shouldnt change)

Then the newest data is on a new query and runs fine. And when it gets near the end of the year ill probably archieve this years data and start the query from 2023-12-01 and go from there.

[IsmaelQR]

Thank you very much, this is really great.

[lbendlin]

which version of the MySQL driver are you using?

[eTribble]

Im not, I use Direct Query to a MySQL Database, The MySQL version of the database is 5.7 but thats what the company wants.

I direct query to the database through the On-Premises Data Gateway (which is up-to-date).

So I am not using ODBC. ODBC is slower than Direct Query. And with the queries I run I need speed.

Unless Im mistaken, im not using a driver. And I have have been doing it this way for months without issues.
This is all just new and only in the last month, and its random.

[lbendlin]

Check your installed apps list.  You will find something like 

[eTribble]

Oh mybad, sorry its an early morning. Here it is, its 8.0.30 and I check for updates on it as well, and it says its up-to date.

[lbendlin]

If you can, go back to 8.0.28  .  There was a bug in 8.0.29 and I don't know if 8.0.30 fixed it.

[eTribble]

How do you downgrade? Do I uninstall then choose the  8.0.28 version, do I need to do it on all MySQL apps? Like the workbench and all of that or just the Net connecter?

[lbendlin]

just the .Net connector

[eTribble]

An error occurred while processing the data in the dataset.
Refresh failed:

LeadTracker has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"<ccon>MySQL: Fatal error encountered during command execution.</ccon>"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"MySql"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"<ccon>[Removed for Security].database.azure.com;crm</ccon>"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"Fatal error encountered during command execution."}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Payment.

Refresh Start Time:
January 11, 2023 20:04 UTC

Refresh End Time:
January 11, 2023 20:06 UTC

Got the Error again after the downgrade and reset. the Error is above but I put [Removed for Security] in the host name spot, the actual full host is there in the original message.

[eTribble]

Ok downgraded the net connector, and restarted my gateway. I manually refreshed one of the reports to make sure it still manually refreshed and its good.

So I will see if I get any more errors during the auto refreshes, hope this fixed it. Thank you for your help.

[eTribble]

I actually use direct connections to the database through the gateway, 

let 

Source = 

MySQL.Database("[HOST]", "[Database]", 

[CommandTimeout = #duration(0, 0, 2, 0), Query = "  

            SELECT  * FROM [Database];

 ", CreateNavigationProperties = false]) 

in 

Source

It was just easier that way, would it make a difference to change them to OLE or ODBC?

Again this is only recent, been doing direct query with this reports for almost a year with no issues.

[lbendlin]

SELECT  * FROM [Database]

is not a valid query. 

[eTribble]

??? lol I know it isnt, I put a [Default] fake query just for the example.

[lbendlin]

Do you use the same version of the MySQL driver on desktop and gateway?  OLE or ODBC?