Mysql Syntax Error

[swasistemas]

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

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.

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

[Anonymous]

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]

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]

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]

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]

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