Aha! mySQL timeout due to information_schema on app.powerbi.com

[Anonymous]

1. My reports and mySQL refreshes in Power BI "Desktop" work fine.

2. I am uploading my report to app.powerbi.com, and need to schedule a refresh to my 
"On-premises data gateway (personal mode)" [let's not get into why I am using personal mode right now.  It's the latest as of June 6 2018)

3. When you go to "Data Source Credentials" under "Datasets", I have maybe 25 mySQL DB's I need to provide login and password for.  5 DB's may be on Server A, 8 DB's on server B, etc.

4. I have DB's on Server A and C that connect without issue.  However, I have DB's on server B that timeout over and over again. 

pic... https://screencast.com/t/EdVtUY3e5K7B

Why?  The big mystery:

I did some detective work and found out that when trying to connect, Power BI runs this query...

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_TIME AS CREATED_DATE, UPDATE_TIME AS MODIFIED_DATE, TABLE_COMMENT AS DESCRIPTION
from information_schema.TABLES

• On Server A, it may take 3 seconds to run, but on Server B, it takes 61 seconds and we time out.
• Google searches to speed up information_schema queries suggest making sure mySQL server variable innodb_stats_on_metadata = 0.  That is already the case for my servers, so no luck there.
• That means the ball appears to be in Microsoft's court.  They need to either
  • change the timeout to something longer
  • tell me how to change the timeout to somethign longer
  • or since they already know the DB name we are trying to connect to, add a where clause to their query.  This go much faster with...
    
    select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_TIME AS CREATED_DATE, UPDATE_TIME AS MODIFIED_DATE, TABLE_COMMENT AS DESCRIPTION
    from information_schema.TABLES where TABLE_SCHEMA IN ('information_schema','mysql','my_db_number123');

If anyone else has ideas or a solution, HELP!!!.  I'm dead in the water going to production because of a login timeout!

Here is a video with more info... 
https://screencast.com/t/SQasrsKdJFI

[Anonymous]

@Anonymous,

Could you confirm that if you can connect to Server B from MySQL client in the machine that installing personal mode gateway? 

If there is no issues with the above connection, please go to C:\Users\Yourname\AppData\Local\Microsoft\On-premises data gateway (personal mode), open Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file, and change the value of MashupDSRTestConnectionTimeout setting from  00:00:50 to 00:02:50, then check if the timeout error goes away. Back up the config file before your make any changes to it. 

There is a similar thread for your reference.
https://community.powerbi.com/t5/Service/Can-t-connect-Gateway-to-dataset-Timeout-expired-The-timeout/td-p/357378

Regards,
Lydia

[Anonymous]

Lydia! Thank you for the quick reply.

I came into the office excited to try your fix.  Unfortunately...

1) I came in and my machine was back to the login screen.  A typical sign that Microsoft has forced an update to Windows.

2)  I logged in and found the dll.config file you mentioned and changed the value to 2:50.

3)  I started up the Gateway (personal mode) and attempted to log in.  I got an error message.

4)  I uninstalled gateway, then reinstalled gateway (losing my dll.config change (ok)).  Same error.

5)  Uninstalled, shut down machine, restarted and installed, still same error.

My guess is the Windows update (rather than the dll change) mucked something up.  This has happened before.

Any idea how I am supposed to get rid of this error so I can try your fix?

https://www.screencast.com/t/xv6t6NdK

Error generating an asymmetric key.
Key not valid for use in specified state.

Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.PowerBI.DataMovement.Pipeline.InternalContracts.IGatewayConfigurationService.GenerateAsymmetricKey()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayClient.GatewayConfigurationClient.GenerateAsymmetricKey()An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Security.Cryptography.CryptographicException: Key not valid for use in specified state.

at System.Security.Cryptography.Utils.CreateProvHandle(CspParameters parameters, Boolean randomKeyContainer)
at System.Security.Cryptography.Utils.GetKeyPairHelper(CspAlgorithmType keyType, CspParameters parameters, Boolean randomKeyContainer, Int32 dwKeySize, SafeProvHandle& safeProvHandle, SafeKeyHandle& safeKeyHandle)
at System.Security.Cryptography.RSACryptoServiceProvider.GetKeyPair()
at System.Security.Cryptography.RSACryptoServiceProvider..ctor(Int32 dwKeySize, CspParameters parameters, Boolean useDefaultKeySize)
at Microsoft.PowerBI.DataMovement.CommonUtilities.AsymmetricKeyEncryptionHelper.ProduceAsymmetricKey(String containerName, Boolean cleanUpOldKeys)
at Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.ExecuteInActivity[T](PipelineActivityType pipelineActivityType, Func`1 action)
at SyncInvokeGenerateAsymmetricKey(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

[Anonymous]

@Anonymous,

Is there any possibility that you uninstall the Windows update and re-install gateway?

Regards,
Lydia

[Anonymous]

I wrote a separate ticket and got help fixing the gateway Install issue. (so we're back to this original problem of timeouts).

• I changed the MashupDSRTestConnectionTimeout setting from  00:00:50 to 00:02:50 and at first it did not seem to work at all.
• I rebooted the machine, and then tried again.  I got 1 DB on 1 of the problem servers to connect, but I think that was just luck.  Trying the other DB's over and over still fails 90% of the time (and at 60 seconds, not at 50s or 2:50.  Weird!)
• I watched what queries are being run again, and it was interesting.  It appears to have run this query for 30 seconds...
  • select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_TIME AS CREATED_DATE, UPDATE_TIME AS MODIFIED_DATE, TABLE_COMMENT AS DESCRIPTION
    from information_schema.TABLES 
• ... then at 30 seconds, the query resets and tries to run the same query again for another 30 seconds.  After that 30s (60s total, the error returns).

Super weird.  Every once in a while I get one to connect, but it's been an hour of fiddling and I still cannot get all the DB's to connect.

Is there another dll.config setting that is trumping the 2:50 one I set?

[Anonymous]

@Anonymous,

I would recommend you create a support ticket here.

Regards,
Lydia