Forum Discussion

ahmadov_10's avatar
ahmadov_10
Icon for Helper II rankHelper II
3 years ago

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

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 ?

Need Help

5 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      ahmadov_10
      Icon for Helper II rankHelper II
      3 years ago

      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

  • ahmadov_10's avatar
    ahmadov_10
    Icon for Helper II rankHelper II
    3 years ago

    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's avatar
      Akarena
      New Member
      2 years ago

      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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.