Odbc Error when refreshing

[S_M]

Hi everyone,

I have started getting the following error since last night, as a result of which none of my odbc tables will refresh :

DataSource.Error: ODBC: ERROR [08S01] [MYSQL][ODBC 5.3(w) Driver]

Lost conection to MySQL server at 'waiting for initial communication packet', system error: 10060

Would anyone know what this is related to or how i can fix it ?

Thanks!

[S_M · Accepted solution]

Hi @v-qiuyu-msft,

After a few days of testing, we have found that these have been network issues for within the office - we have some serious IP issues ongoing and as a result, switching the datasource did not help. I even created a new pbix file to use MYSQL database but refresh did not go through, whereas at home, i was able to run it without a hitch. 

I have now resolved this by actually switching out from a desktop to a windows server on aws, and a couple of days in and the refresh runs well and takes less one third the time is used to - barring some import errors while publishing which persisted last night, i think this solution is working well 🙂   

[arify]

Hi,

1. Do you get this error consistently? Have you tried refreshing, and made sure the MySQL server is running?
2. Have you tried using the built-in MySQL connector instead of ODBC?

Thanks

[S_M]

Hi!

Thanks for your response

I have had no troubles at all until last night when something went amiss - since then this error shoots consistently, usually when running a refresh all. Sometimes i am able to refresh individual tables, most times they fail and give the above result. It has been incredibly frustrating as i dont quite understand what the problem may be : MySQL is running, i am able to connect to the ODBC using tools like HeidiSQL, i am able to even import tables from the ODBC, but on refreshing it, it gives an error.

I am not sure if i can migrate my existing data model to the MySQL connectors without breaking my model. I am fairly certain there is no problem with the firewall and i am completely lost on how to resolve this issue, having spent the past day eliminating the possible reasons

[arify]

Did some search, it looks like a problem with the server. Can you try this? http://dba.stackexchange.com/questions/30725/waiting-for-initial-communication-packet-error-0-in-mys...

Or this http://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-commun...

[S_M]

Thank you! 

Since our servers are hosted in AWS, i am going to ask the developers to run a check against these and see if that resolves it - will get back to you if one of these work 🙂 

[arify]

Can you list the tables in the database? Or you can't do anything with the db?

If you want to use the built in MySQL connector, I think it would be very easy if you try connecting with MySQL.Database function, and once you reached your table, look at the M code generated in the Advanced Editor, then open the Advanced Editor of your original query & replace the ODBC call with MySQL.Database function. Rest of the operations should probably be the same so no need to change the rest of the M code.

[S_M]

It is odd, because i am able to connect, list and pull updated tables from the db. It is all fine until i try refreshing the table, which is when this error pops up. But then again, sometimes the table refreshes, most times it shoots this error. Particularly when i do a refresh against all tables, it always gives this error. This adds to the dilemma, because while MySQL connectivity and ODBC connectivity is fine, it is only when it is refreshing data in my pbix file, does the error occur

As you suggested, i am also going to give migrating to the MySQL connector and see if that resolves / solves it. Also, since i am using the June version of pbix, i will also update, see if that makes a difference 

[v-qiuyu-msft]

Hi @S_M,

Was the issue solved after updating Power BI Desktop version and change to use MySQL database type data source?

Based on my research, the issue may be caused by that the Firewall settings. See: Connect to a remote MySql database with obdc. Also please enable Power BI Desktop trace log files to see if you can find any useful information when the refresh is failed.

Best Regards,
Qiuyun Yu

[S_M · Accepted solution]

Hi @v-qiuyu-msft,

After a few days of testing, we have found that these have been network issues for within the office - we have some serious IP issues ongoing and as a result, switching the datasource did not help. I even created a new pbix file to use MYSQL database but refresh did not go through, whereas at home, i was able to run it without a hitch. 

I have now resolved this by actually switching out from a desktop to a windows server on aws, and a couple of days in and the refresh runs well and takes less one third the time is used to - barring some import errors while publishing which persisted last night, i think this solution is working well 🙂   

[duzcc]

Hi,

I have the same problem, but on my case don't exist any problem of connectivity with de server.  

something else can i check?

[duzcc]

Hi, 

I have the same error, but i check connection with the server and don't exist any problem. 

[v-qiuyu-msft]

Hi @S_M,

Glad to hear you find the solution of ODBC error. By the way, to publish the report to Power BI Service, you need to configure corresponding ODBC or MySQL data source under data gateway.

To publish the report, you can use Publish feature in desktop directly, or you can also get  .pbix file in Power BI Service via Get Data->Files->Local Files.

Best Regards,
Qiuyun Yu

[S_M]

Thanks @v-qiuyu-msft! 

Do i need to do the above configuration even if i am a free user ? Because i currently dont use a personal data gateway, as i am not a pro user, but i am facing a '(FailedToRefreshpackageAfterimport)' atleast once a day (i have data refreshing on an hourly basis and the data size is not large)

[v-qiuyu-msft · Accepted solution]

Hi @S_M,

Both personal gateway and data gateway are available for pro user. In your scenario, if you publish the report to Power BI Service, you need to configure gateway like mentioned above. As both ODBC and MySQL are not in cloud, while the Power BI Service is in the cloud, it requires gateway to communicate each other. If you only refresh report in desktop, gateway is not required.

Best Regards,

Qiuyun Yu