Power BI Service Data Refresh Error (An error happened while reading data from the provider)

[vsam_automation]

Hi, I'm Victor,
I built a report using MySQL database. The connection works fine on PBI Desktop, but when I publish and try to refresh the data in PBI Service, it takes a long time (like 2-5min) and then says this, 

Data source errorDataSource.Error: An error happened while reading data from the provider: 'One or more errors occurred.'. Microsoft.Data.Mashup.ErrorCode = 10434. The exception was raised by the IDbCommand interface. Table: Sales. 

Note: The Table referenced in this error message is Sales, but it changes on every failed refresh. Sometimes Customers, Orders, etc. So I don't think it's related to any specific table but the entire db.
Any help will be greatly appreciated. Thanks. 

[v-lgarikapat]

Hi @vsam_automation ,

Thanks for reaching out to the Microsoft fabric community forum.

@ibarrau , @GilbertQ 

Thanks for your prompt response

@vsam_automation  , could you please try the troubleshooting steps provided by  @ibarrau  and let us know how it goes, so we can assist you further.

We truly appreciate your continued engagement and thank you for being an active and valued member of the community.

Best Regards,

Lakshmi

[v-lgarikapat]

Hi @vsam_automation ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Best Regards,
Lakshmi.

[v-lgarikapat]

Hi @vsam_automation ,

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Best Regards,
Lakshmi.

[vsam_automation]

Hi @v-lgarikapat , the issue has not yet been resolved. Power BI desktop does not show any errors. Only service fails to refresh. Microsoft Support suggested U use SslMode=none but  the connection string I'm using does not support it
This is failing

= MySQL.Database({MY SERVER}, {MY_PASSWORD}, [ReturnSingleDatabase=true, CreateNavigationProperties=false,SslMode=none])

Expression.Error: The name 'none' wasn't recognized.  Make sure it's spelled correctly.

[v-lgarikapat]

Hi @vsam_automation ,

Apologies for the inconvenience. Could you please try the workaround mentioned below and let us know if it resolves the issue?

Workaround: Use ODBC Instead

To bypass this limitation, switch to an ODBC connection where you can explicitly define SSL behavior.

Example M Code Using ODBC:

powerquery

let

    connectionString =

        "Driver={MySQL ODBC 8.0 Unicode Driver};Server=your_server;Database=your_db;Uid=your_user;Pwd=your_password;SslMode=DISABLED;",

    Source = Odbc.Query(connectionString, "SELECT * FROM your_table")

in

    Source

Steps:

1. Install the MySQL ODBC 8.0 Unicode Driver.
2. Use Odbc.Query() instead of MySQL.Database().
3. Set SslMode=DISABLED in the connection string.

 Gateway Considerations

If you're publishing to Power BI Service:

• Make sure the ODBC driver is installed on the machine running the On-Premises Data Gateway.
• Configure the gateway to use the same DSN or connection string.

Best Regards,

Lakshmi.

[v-lgarikapat]

Hi @vsam_automation 

We’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Best Regards,
Lakshmi.

[GilbertQ]

Hi @vsam_automation 

Can you please let us know how you are connecting your semantic model to your database? Are you using gateway or are you connecting directly? This is for us assist you with your error.

[vsam_automation]

I'm using a cloud connection

Connection says it's onlineCloud connection

[ibarrau]

Hi. Just to make sure there is no error in the transformations, let's do a quick check. Open power bi desktop. Go to queryeditor/transformdata. Open the tables one by one selecting all the columns and filtering by "Keep Errors". Make sure there is no row for each one. If there are no error rows at any table, then it's something with mysql and power bi service. Otherwise, you can have an error in the ETL. Sometimes power bi desktop shows warnings after apply and refresh that will be errors in Service.

I hope that helps,

[vsam_automation]

Hi @ibarrau , thanks for your response.
Everything checked out on desktop. No errors or warnings