How to Set Up Scheduled Refresh for MySQL (ODBC) in Power BI Service?

[Anonymous]

Issue Description

I have installed MySQL Connector ODBC 8.0.33 (winx64) on my machine and connected to a remote MySQL database in Power BI Desktop using the ODBC data source. I successfully imported the data and created a report.

In Power BI Desktop, when I manually refresh, the data updates as expected based on the latest data in the MySQL database.

However, after publishing the report to Power BI Service, I need to set up a scheduled refresh. Here are my questions:

Questions:

1. Do I need to set up an on-premises data gateway for scheduled refresh in Power BI Service?

2. Under the "Data Connections" section in Power BI Service, what configurations need to be set?

3. Where do I need to provide the database credentials for the refresh to work properly?

Current Setup:

• Power BI Desktop: Connected via ODBC to MySQL

• Power BI Service: Published report and dataset

• MySQL Database: Hosted remotely

• MySQL Connector: Installed MySQL Connector ODBC 8.0.33 (winx64)

Expected Outcome:

I want my Power BI report to refresh on a scheduled basis in Power BI Service without requiring manual refresh in Power BI Desktop.

Any guidance on setting up the gateway (if needed), credentials, and scheduled refresh settings would be greatly appreciated!

Thanks in advance for your help!

[v-hashadapu]

Hi , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi @Anonymous , Thank you for reaching out to the Microsoft Community Forum.

Install and configure the on-premises data gateway in standard mode on the machine where your ODBC setup is active. Next, create a matching System DSN using MySQL Connector ODBC 8.0.33, replicating the Desktop configuration with secure, read-only service account credentials instead of root or admin access, and test the connection. Then, in Power BI Service, register the ODBC data source with the gateway, map your published dataset to it using the same credentials, and verify the setup. Finally, enable scheduled refresh in the dataset settings, setting a frequency and times and monitor the refresh history to ensure smooth performance and address any issues.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[SaiTejaTalasila]

Hi @Anonymous ,

Why you want to use odbc connection?

You can install MySQL connector on that gateway and create a MySQL connection on the power bi service.You can use same connection under your semantic model and dataflow.

I hope it helps.

Thanks,

Sai Teja 

[Anonymous]

Hi @SaiTejaTalasila 

Thanks for your input.

Initially, I did set up the connection using the MySQL connector. However, when I tried to manually refresh the report in Power BI Desktop, I kept encountering two alternating errors:

• “An error happened while reading data from the provider: ‘Object cannot be cast from DBNull to other types’”

• “An error happened while reading data from the provider: ‘The given key was not present in the dictionary’”

Due to these issues, I decided to try using an ODBC connection instead. With ODBC, the connection was successful, and I was able to refresh the report in Power BI Desktop without any issues.

That said, I'm still unsure how to set up a scheduled refresh using the ODBC connection in Power BI Service.

Appreciate any guidance on this.

Thanks,
Gokul Saraboji

[lbendlin]

Downgrade your native MySQL driver to 8.0.28 and try again.

[Anonymous]

9.1.0
9.0.0
8.4.0
8.3.0
8.2.0
8.1.0
8.0.33
8.0.32
8.0.31
8.0.30
8.0.29
8.0.28
8.0.27
8.0.26
8.0.26
8.0.25
8.0.24
8.0.23
8.0.22
8.0.21
8.0.20
8.0.19
8.0.18
8.0.17
8.0.16
8.0.15
8.0.14
8.0.13
8.0.12
8.0.11

-I have tried all this versions of mysql connector but am getting the two common errors as

• “An error happened while reading data from the provider: ‘Object cannot be cast from DBNull to other types’”

• “An error happened while reading data from the provider: ‘The given key was not present in the dictionary’”

[SaiTejaTalasila]

Hi @Anonymous ,

In last I myself configured the connection for one our business user and I haven't seen any issues. Try to import any other tables and see whether the same issue occurs or not.

If you want to use odbc connection you can refer this one.

https://medium.com/@OluwakemiAdedoyin/how-to-connect-powerbi-to-mysql-database-with-odbc-connector-317a920ea425

I hope it helps.

Thanks,

Sai Teja 

[lbendlin]

Only 8.0.28 is "known good". All others are questionable.

[lbendlin]

Please describe what "Remote"  means.  Is it directly visible from the Azure service?

[Anonymous]

Hi @lbendlin 
The data is hosted on a web hosting platform called Hostinger. It uses a MySQL database to store the data, and phpMyAdmin is provided as the management tool for accessing and managing the database.

[lbendlin]

So on the open interwebs and anyone can access it ? In that case you would not need a gateway.

[Anonymous]

the MySQL database is hosted on Hostinger and is accessible over the internet using a public hostname and port. There's no VPN or firewall restriction blocking outside access.