REST API Cancel Refresh ViaApi - Operation failed - Canceling refresh operation is disabled

[jesus21282]

Hi folks!- I have a dataset refresh stuck for 4+ hours so far, where it usually takes ~10 mins to refresh.

I would like to cancel this refresh, but there is no option in the dataset options to cancel it, so it keeps going without finishing.

Scenario:

- Dataset sourced from MySQL multi-server round-robin proxy behind VPN / On-Premises Data Gateway in CLUSTER mode.

- Refreshes trigger via POWER AUTOMATE's Refresh a Power BI dataset action, every 20 mins. (`ViaApi` RefreshType).

I tried leveraging the Power BI REST API Cancel dataset refresh method: Datasets - Cancel Refresh - REST API (Power BI Power BI REST APIs) | Microsoft Docs, but it gives me this error:

{
    "error": {
        "code": "MethodNotAllowed",
        "message": "Operation failed - Canceling refresh operation is disabled. Dataset ID 7db...xxx Refresh ID 410...xxx RefreshType is ViaApi."
    }
}

Any other methods you'd suggest me to try??

So far, I have tried:

- Doing local refresh, and then trying uploading to service, but the Publish won't finish.

- Tried the same, BUT bypassing the MySQL proxy by pointing the PBIX file against a specific MySQL server. Tried already with all servers individually. Still the Publish won't finish.

- Inspecting any stuck queries in the MySQL servers behind the proxy, no queries stuck there either.

Last, I am under the impression that this API method is disabled for RefreshType ViaApi, which calls my attention a lot. I think they should absolutely enable it!

Let me know your thoughts, thanks

EDIT:
Sorry, I meant we used Cancel Refresh in Group action instead. Datasets - Cancel Refresh In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs.

Also, I managed to finally fix the issue, by stopping the Flow calls to refresh the dataset. This means, I turned off the Power Automate flow triggering the dataset refresh temporarily, and then,I was now able to upload my dataset from local again, effectively overriding the version online. Finally, I turned on my flow again and it's all running well back again.

[nkuppili]

we are facing exact same issue (used "cancel in group api") to cancel our enhanced dataset refresh which is taking more than 6 hours now. Were you able to figure out the issue on API errors? @m-colbert @jesus21282 

[m-colbert]

Is this dataset in your My Workspace or a shared workspace. If in a shared workspace, you need to use the Cancel a Dataset in a Group.

Other possible ways to kill are to kill the session/thread  on your backend, or restart/take offline the gateway its going through. 

[jesus21282]

Good point. The dataset is in a shared workspace, and I used Cancel Refresh in Group instead ( I will edit this in my first message ), but it failed with the error shown.

I also managed to finally fix the issue, by stopping the Flow calls to refresh the dataset. This means, I turned off the Power Automate flow triggering the dataset refresh temporarily, and then,I was now able to upload my dataset from local again, effectively overriding the version online. Finally, I turned on my flow again and it's all running well back again.

I further dig into your comment, and:

- I am not sure how to tell session/thread in my backend;

- Restarting the gateway is not easy for us, because we use a Gateway cluster of 5 On-Premises data gateways, and we don't know which one server in particular is taking this load. Any thoughts on this?