Forum Discussion
Advocate IIDM_GWPipeline_Gateway_MashupDataAccessError (Not like other post)
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?
I fixed it, it was actualy the query itself causeing it to time out. I re-ajusted the query and no more issues.
19 Replies
- eTribble
I fixed it, it was actualy the query itself causeing it to time out. I re-ajusted the query and no more issues.
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.
- lbendlin
Super User
Do you use the same version of the MySQL driver on desktop and gateway? OLE or ODBC?
- 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 SourceIt 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
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
KossXLHelper I
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.Hi all,
Here we had the same issue and solved with this "little guy"
