Refresh fails first attempt (reason : data format) and success second attempt

[ahmadov_10]

Hello Experts,

I'm working on a Power BI report that takes a MySQL database as its source and I'm updating the data via the gateway.

I'm facing a recurring problem that I can't find a clear explanation:

I planned two refreshes:

1st attempt at 9:00 utc

2nd attempt at 9:30 am utc

the 1st time always fails with the same error message every day and the 2nd attempt always ends successfully.

Error message :

{"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":"An error happened while reading data from the provider: 'Input string was not in a correct format.'"}},{"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_server.mysql.database.azure.com;my_database"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Currency.

URI du cluster:   WABI-EUROPE-NORTH-B-redirect.analysis.windows.net

ID d'activité:       ff2a174b-d3e4-49c4-832f-7281b0880766

ID de demande:               45421fac-d923-4f08-9193-27f00635bb68

Heure:   2023-08-08 08:39:30Z

Summary :

Code : DM_GWPipeline_Gateway_MashupDataAccessError

Microsoft.Data.Mashup.ValueError.Reason

Table: Currency (it depends , every day can change the table)

An error happened while reading data from the provider: Input string was not in a correct format

@amitchandak  @Ritaf1983 @Ashish_Mathur  @Sahir_Maharaj  @Greg_Deckler  @TomMartens @Jihwan_Kim 

@rubayatyasmin 

--> if the 2nd attempt refresh works fine , so we cannot talk about data format ?

[Anonymous]

I agree. I have a manual refresh in Power BI from MySQL which always fails first time with the input string in the wrong format problem. I go into Power Query editor and see the candy stripe error for the whole table. I click on another table and then click back on the problem table and it goes green (no changes made in the code). I close and apply and refresh and everything works just fine - on the third refresh(!) It seems as though Power BI thinks "this data is no good" (first time)  - "this data has a problem" (second time) - "Oh go on, I'll load it anyway" (third time). Surely this can't be the protocol?

MySQL is updated once a day by somebody else, and I'm the only one using it, so there's no conflicts or clashes. I've also ended parallel processing.

Ideas?

I also access the data from PowerQuery in Excel. Here I use VBA to load the data twice. First time each table (10) nearly always fails, so I have an error trap which runs the request again, which nearly always works.

[ahmadov_10]

Hello Team ,
sorry to answer late : we don't find a logical reason for the error (in the tables , in the model relationships , etc) :
at any time when we refresh the report :
the first attempt make error
the 2nd attempt refresh works fine
--> so as a temporary solution , we had schedule the report refresh for 2 times (9:00 and 9:30) and it works to finally refresh the data and we talk to the customer to ignor the first attempt

[Akarena]

Hello,

I am facing the same problem as this,

Is there is any new update on this bug. I am also connected to MySQL database,

My first refresh always fails, and then the second succeeds.

My regards,

[rubayatyasmin]

@ahmadov_10 

interesting that the first attempt fails and the second succeeds. Are there any processes that might be conflicting with the first refresh attempt? Maybe another process is locking a table or altering data at that time.

Also, is it possible that your MySQL DB updates/writes data at 9 UTC? Probably that is causing the issue. Try changing the schedule refresh time. See if that works. 

Also, from this doc, 

• Gateway process may have restarted when the dataset refresh was in progress.

https://community.fabric.microsoft.com/t5/Service/Gateway-Mashup-Data-Access-Error/m-p/1386953

https://community.fabric.microsoft.com/t5/Desktop/Power-BI-dataset-refresh-issue/td-p/2677275

Did I help? If yes, hit 👍 and accept this answer as solution. 

[ahmadov_10]

Hello , the problem i can't confirm if the team worked in the database source (my SQL) is updating database at 9h00 but if find a temporary solution : 

1st schedule at 9h00 : fails 

2nd schedule at 9h30 : success with any problems

so logical , it cannot be a problem of confilcts but it seems at some readed data at the first time not well interpered (compiled in the format , for this reason the error message : Input string was not in a correct format) and after it works : the unique explanation that i find .

Kind Regards