Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

swasistemas

Mysql Syntax Error

Submitted by on ‎03-04-2023 12:34 PM

When I update a dataset on the web Power BI app I'm getting the following error:

 

1.png

 

The thing is, there is not syntax error on the query. The query run successfully on the MySQL console. The query with the error even changes if I try to refresh the data one more time.

 

This process was working fine until I choose a on-premises gateway to enable data auto-refresh and mapped to the report.

2.png

 

Now, every time i try to refresh the dataset I get a sql syntax error in a different query.

 

Anyone knows what might be causing this issue? Any help would be greatly appreciated

Status: Investigating
See more ideas labeled with:
5 Comments (5 New)
Comments
Anonymous
Not applicable
‎03-05-2023 10:05 PM

Hi @swasistemas 

Can you successfully refresh this report in PBI Desktop? Is this your first connection to a MySql data source on Service? Can you provide the English translation of the error message? 

 

Best Regards,
Community Support Team _ Ailsa Tao

swasistemas
Regular Visitor
‎03-06-2023 03:04 AM

Hello, @Anonymous .

 

I'm able to refresh the report on PBI Desktop, no problem and this is not the first time connecting to a Mysql data source on the service. The error says: 'Unable to connect to data source undefined'

Anonymous
Not applicable
‎03-06-2023 10:21 PM

Hi @swasistemas 

Please update your gateway to the latest version and then recreate a data source in Manage gateways . Map the dataset with new datasource to check the refresh .

 

Best Regards,
Community Support Team _ Ailsa Tao

swasistemas
Regular Visitor
‎03-09-2023 10:55 AM

Hello again, @Anonymous .

 

I updated the gateway to the latest version. Recreated and mapped the datasource and the error still persists. I also discovered that this error only occurs when the 'Scheduled Update' option is active. If is inactive I can publish a new version of my report and manually update the data with no errors.

 

In a different workspace the same error occurs but appears in a different way, maybe it helps:

 

{"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: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'my query' at line 1"}},{"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":"my database"}},{"code":"Microsoft.Data.Mashup.ValueError.ErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Message","detail":{"type":1,"value":"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'my query' at line 1"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: myTable.

swasistemas
Regular Visitor
‎03-10-2023 08:17 AM

Hello, @Anonymous . I found the solution to my problem. There was a mysql syntax error after all. For whatever reason, the gateway was not "processing" formatting characters like tab and line-feed. That's why the error only ocurred when the updated schedule was active. I edited my queries and now I can update my data source without problems.

 

Thanks for all the help

Idea Statuses