Problems refreshing data from MySQL database with May 2017 Release

[martinrowe]

Ive been successfully using powerbi for a number of months now, refreshing from the same datasource without any issues.

After installing the May release of powerbi desktop I have been unable to refresh the data successfully. The load takes a long time and eventually errors;

OLE or ODBC error:[datasource.error] mysql:fatal error encountered during data read

I have reverted back to April 2017 release of power bi desktop and i am able to refresh the data without this error. In terms of connection settings to the database, I have checked "Encrypt connections" and I am also using native db queries to return the data. These queries are simple SELECT * FROM tbl_xxx type queries, the database is on a remote server to me and I can sucessful connect and run tasks from a variety of other MySQL tools

The refresh pulls in about 15 tables, most are small config type tables but there are 4 main tables that are about 100k rows each so I do not believe that size of data is a problem, also my internet connection is relativey fast and reliable.

I guess my query is 2 fold.

1. Has anyone else seen this same issue when refreshing from MySql db with new version of power bi?

2. What is best practice for pulling data from MySQL databases from a refresh performance point of view?

[davipp]

I'm having the same problem, there is any way to rollback to previous version of powerbi?

[martinrowe · Accepted solution]

The only way to roll back is to locate the old installation file and to install that. Lucklly I retained an older version which I could reinstall, some others I have noted have version of the old install listed on a clould drive they have, so you would have to do some digging and see if this could work for you.

In terms of my actual problem though I think that I may have "resolved" it. Resolved being in quotes as i'm still uncertain how this will pan out over time but I have manage dto at least refresh the report several times now using the May release.

The change I made was to specifically list each column I needed in a particular table in the sql query used. Previously I just used SELECT * as I needed all but 2 text columns from across my db. I changed the query to specifically select each column and drop these 2 large text columns and it seems so far to be enough to enable a refresh to complete without this error.

Perhaps there is some limit/threshold or other wierd behaviour either with MySQL or power bi when it comes to larger text columns

[v-caliao-msft]

@martinrowe,

The issue only occurs on May release of Power BI desktop. You could open Power BI trace files (go to: File –> Options –> Diagnostics –> Enable Tracing), reproduce this issue. And then analyse Power BI Desktop diagnostic trace files. Please refer to the link below to see the details.
https://ruiromanoblog.wordpress.com/2016/01/08/power-bi-desktop-trace-logs-analyser/

Regards,

Charlie Liao