Lost Connection Error using MS Analysis Services 2005

[dipin]

Hi,

We have dotConnect for MySQL working with MS Analysis Services 2005. Up until about a week ago there were no issues at all. Since then we've been running into an issue where the process to update the Cubes will fail because it claims to have lost connection to the MySQL server. When we investigate we notice that in MySQL, the query is still running (4000 seconds processing and still going) so it doesn't make sense that the MS Analysis Services server would lose connection to the MySQL server.

The following was the error produced by the server:

Code: Select all

Microsoft (R) Analysis Services 2005 Command Line Tool
Version 9.0.83.1 MSIL
Copyright (C) 2006 Microsoft Corporation.  All Rights Reserved.

The relevant part of the error is

Code: Select all

Description="Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: Lost connection to MySQL server during query."

This seems to only occur with long running queries due to data volume growth as it didn't occur previously with smaller data volumes with the same schema and cubes.

Any help on resolving this issue would be greatly appreciated.

Thanks,

-Dipin

[Shalex]

Please check the wait_timeout variable of your MySQL server. What value does it have? Try increasing it. E.g.:

Code: Select all

SET GLOBAL wait_timeout = 30000 -- value is in seconds

For more information, please refer to http://dev.mysql.com/doc/refman/5.0/en/gone-away.html .

It can be also the Devart.Data.MySql.MySqlCommand.CommandTimeout default value issue (by default, 30 seconds). We will investigate this side of interoperation of dotConnect for MySQL with MS Analysis Services.

Please notify us about the results of increasing the wait_timeout variable as a global setting.

[dipin]

I apologize for not including that information before but I had already configured my wait_timeout to a ridiculously large value (86400) just in case that was the issue and was still having the same issue. I also tried increasing the net_write_timeout to 86400 just in case it was MSAS stalling in the middle of reading data from MySQL. I've also read extensively on MySQL related config parameters in relation to the lost connection to server issue except that in all my reading nothing I have encountered explains why the client (MSAS) would claim to lost the connection to the server while the server (MySQL) would still claim to be processing the query/connection as if no issues had occurred as this is the specific scenario I am seeing. MySQL shows the query as still being processed while MSAS claims that it has lost a connection to the server.

I doubt it is the Devart.Data.MySql.MySqlCommand.CommandTimeout as the query is running for over one hour prior to MSAS claiming to have been disconnected but I do not know enough about the internals of either MSAS or Devart so I could be wrong. Is there any way to configure the Devart.Data.MySql.MySqlCommand.CommandTimeout on a global level via a config file or registry entry as I don't see it in the dialog within MSAS? Is there a list of that and any other Devart related variables that I can look through in order to try and troubleshoot this issue? Also, so far I am using the Direct = true option in dotConnect and I was wondering if possibly switching to using the Direct = false option might help? Any advice on how the settings for dotConnect could affect things would be appreciated.

If you have any other ideas it would be great also.

Thanks,

-Dipin

[Shalex]

The default value of Devart.Data.MySql.MySqlCommand.CommandTimeout is 30 seconds. It cannot be changed within MSAS that uses the default value. We will investigate this issue and notify you about the results as soon as possible.

[dipin]

Hi,

Any progress with the investigation? We are still experiencing this issue and need to find a resolution soon. Please let me know if there is any other information I can provide.

Also, how does one go about opening an official support incident?

Thanks,

-Dipin

[Shalex]

Please try using the latest build of dotConnect for MySQL (5.20.29). We have added the Default Command Timeout property to the MySqlConnection class. Increase its value when you set the ConnectionString property of the MySqlConnection object. Does it resolve the problem with MS Analysis Services 2005?