Data Gateway Timeout on data refresh MySQL

[dpombal]

I have a local MySQL instance and a report with 5 ,1 of them 7 million rows.

I had errors based on MySQL timeouts, since data refresh takes 25 and 40 minutes.

However now I am getting a timeout , I think from Gateway side

On  Data gateway Service logs (file Gateway20170907.000000000.log)

DM.EnterpriseGateway Error: 0 : 2017-09-07T08:28:48.0289195Z DM.EnterpriseGateway    21794bd4-06e5-471d-9781-16d766846ca4    20cc4b21-5fd0-6473-0eb1-c9bc1c7ed235    MDGR    f0bb229d-432d-4eac-bbe9-01f3324b7d4c    5AB879D5 [DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.CancellationException: Task is explicitly canceled with System.Threading.Tasks.TaskCanceledException: Task cancelled
   en System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   en System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   en Microsoft.PowerBI.DataMovement.Pipeline.Common.TDFHelpers.<>c__DisplayClass9_0`1.<<GetNextResponse>b__0>d.MoveNext()
--- Fin del seguimiento de la pila de la ubicación anterior donde se produjo la excepción ---
   en System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

Which is the timeout I should change in this file to change timeout to 1 hour:

Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

I read something similar on this post, but files changed

Forums says something like this (http://community.powerbi.com/t5/Service/Increase-timeout-of-gateway-Scheduled-refresh-fails-due-to/m...)

I'm not sure if any of the gateway config files have an entry for this. All I found is this in
Microsoft.PowerBI.DataMovement.GatewayCommon.dll.config:
<setting name="HttpClientDefaultTimeout" serializeAs="String">
   <value>20</value>
</setting>

[Conarl_on_BI]

@dpombal,

Desktop would refresh successfully because the client has more resource to run the dataset and does not depend on the DataMovement.Pipeline.Gateway to render results.

With that said in the DataMovement.Pipeline.Gateway config file you can edit this settings to your desire value to see if this resolves you problem. 

</setting>

<setting name="DbConnectionPoolMaxSize" serializeAs="String">
<value>256</value>
</setting>

This are the options available for increasing or decreasing calls to the DB, so you can find the corresponding settings in the same file and play with them to see which one help you resolve the timeout.
* DbConnectionPoolMinSize: minimum size of the DB connection pool.
* DbConnectionPoolMaxSize: maximum size of the DB connection pool.
* DbConnectionPoolProactiveCreationCount: number of pro-actively created connections for a DB connection string.
* DbConnectionPoolExpirationTimeout: maximum time a connection can stay in the pool without being used.

Thanks,

Conarl_On_BI

[luizgoka]

Did you solve your problem? I am still with the same problem and I don't know what to do.

[Anonymous]

If you have a mySQL timeout on app.powerbi.com server and not Power BI Desktop, you may be interested in

http://community.powerbi.com/t5/Issues/Aha-mySQL-timeout-due-to-information-schema-on-app-powerbi-co...

[GilbertQ]

Hi @dpombal

When you refresh the PBIX file in Power BI Desktop does it refresh successfully?

If it does then at least you know that the issue is with the Gateway and possibly changing the setting below to 60 might solve your issue. 

I would suggest making a backup of the config file before making any changes. As well as restarting the service once you have made your changes.

[dpombal]

When I refresh in Desktop, it refreshes successfully.

I changed value 20 to 80 and restart service but it is still failing, not sure if it is caching.... or what is happening

[GilbertQ]

I'm not sure why it is happening. I have personally never tried it myself. I might try it early next week on a test VM

[dpombal]

thanks I am also waiting for tech support reply, thanks for your help

[v-chuncz-msft]

@dpombal,

Try to specify Timeout parameter in Advanced Editor as well.

https://msdn.microsoft.com/en-us/library/mt742860.aspx