Hard limit for attempts or timeout of native mySQL query in dataflow

[ChristophR]

Hi, 

we currently have an issue with a dataflow that contains a native mysql query that has some performance issues. Due to that the query runs into a timeout. However, we figured that the query is still continuing to fire against the database, generating hundreds of requests which recently lead to the server crashing.
Is there any means to ensure that the query terminates after a couple of attempts?

The command timeout that can be set in Power query does not seem to help. 

Thanks and best regards

Christoph   

[ChristophR]

As a follow up: our current workaround is to have a dedicated instance of the DB only for Power BI purposes (which we perhaps should have had from the start) to separate systems as much as possible, so that at least impact is reduced. But so far, I still do not see any means to really prevent this from happening.   

[Anonymous]

Cancelling the SQL query is an AWFUL solution to this. We've had load processes hung and timeout because of a power bi refresh or load that has sat idle from a user that no one even knows about. Would literally need a full time DBA to sit there and watch our warehouse 24/7 to make sure nothing hangs it up.

[ChristophR]

@Anonymous, so how do you deal with this issue then? 

[v-luwang-msft]

Hi @ChristophR ,

There is a method mentioned in the following post that you can refer to.

"Created the desired query in PowerBI Desktop.  Then I captured the M code from the advanced editor and pasted it into the editor in Dataflows.  Then I just clicked 'Close and Save' and navigated away without waiting for the preview to finish.

The dataflow loaded without problem, but managing the query has to be done out of Power Query Editor, and then copied into the editor in the Dataflows interface.  

Not a very satisfying approach, but it is functional."

refer:https://community.powerbi.com/t5/Service/Dataflow-Timeout/td-p/614925 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[ChristophR]

Hi @v-luwang-msft ,
thanks for your effort in searching for similar problems, but I guess, what you found does not really fit to my case, since I do want to have the query killed from Power BI side if it runs into issues but I cannot (the post you are reffering to rather seeks for something to prevent time out while loading the data preview if I understand correctly).  

[lbendlin]

you have to kill the query session on the MySQL side. There's nothing you can do on the Power BI side.

[ChristophR]

Hoping to here something different. But, currently, that is also my impression.