Page 1 of 1

Lost connection to MySQL server during query

Posted: Wed 26 Mar 2008 13:25
by jammerjam
Production verisions:
CoreLab.Data: 4.30.20.0
Corelab.MySql: 4.30.24.0
Using MS VS 2008.

I'm getting an error: Lost connection to MySQL server during query part way through my process, which imports data to a mysql database, then adds indexes. The error occurs when executing the sql to add the index. I've manually set the timeout on the connection to 108000 (which I can verify when debugging).

However, if I actually time the execution process, the connection is stopping after 30 seconds. I can run a smaller index before this one, and I can run this index creation in a query browser no problem, so the issue isn't the sql.

How can I correctly set the timeout so that I don't get this error?

Thanks in advance.

Posted: Thu 27 Mar 2008 15:59
by Alexey.mdr
Please check server timeout property and command timeout property.
Was the index (where the connection was lost) created successfully in the database?
If yes is it valid?

Posted: Thu 27 Mar 2008 21:21
by jammerjam
Yes, the index was created, but it looks like it didn't finish. (I'm not totally sure how to check, aside from comparing the cardinality to the total distinct values for that field - which was off by 3).

Posted: Fri 28 Mar 2008 08:38
by Alexey.mdr
What component do you use for data loading?
Did you check command timeout property and
server timeout property (connect_timeout)?

Posted: Fri 28 Mar 2008 12:35
by jammerjam
I use a 3rd party app to create the database and import records from other file sources - so adding the index before the data isn't an option.

I'm not exactly sure what other timeout property you're referring to. Out mysql server doesn't have the wait_timeout property set, so it's using the default 8 hours. My MySql connection ConnectionTimeout is set to 108000.

Posted: Fri 28 Mar 2008 12:56
by Alexey.mdr
Perhaps the application that you are using for loading data is closing the connection?

Posted: Fri 28 Mar 2008 13:02
by jammerjam
The process that loads the data is started by the custom app. It's also closed by the custom app. I can see it start the 3rd party app, load the data, and close the app. At that point, I can pause the custom app, open a connection to mysql, and see the database.

After unpausing, the custom app inserts three indexes (small ones), then times out adding the fourth. I can also pause before the MySqlScript command is executed and see that the connection state is open. I also tried to set a CommandTimeout on the MySqlScript object, but that didn't help.

Posted: Fri 28 Mar 2008 17:25
by Alexey.mdr
So, basically you are using a MySqlScript component for data loading, right?
Did you try to change the following property?

Code: Select all

mySqlScript.CommandTimeout = 200;

Posted: Fri 28 Mar 2008 17:33
by jammerjam
No - again, I'm using a 3rd party control that uses ODBC to load data. After that's complete, I run 20 scripts to create indexes. The process fails on the 4th script with the error previously mentioned.

Yes, I have set both the connection timeout and the script timeout. If I run my app in debug mode, I can see that the timeout is set properly.

Basically, I could create a new .net project and setup the mysqlscript object to execute this 1 index, and it'll timeout, even with the two timeout properties set correctly.

Posted: Mon 31 Mar 2008 13:31
by Alexey.mdr
Basically, I could create a new .net project and setup the mysqlscript object to execute this 1 index, and it'll timeout, even with the two timeout properties set correctly.
Could you please send me this project?
Please include needed database objects.
What 3-d party tool do you use?
Does it uses the same connection as a MySqlScript component?