Lost connection to MySQL server during query

Lost connection to MySQL server during query

Postby jammerjam » Wed 26 Mar 2008 13:25

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.
jammerjam
 
Posts: 27
Joined: Wed 09 Jan 2008 22:56

Postby Alexey.mdr » Thu 27 Mar 2008 15:59

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?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby jammerjam » Thu 27 Mar 2008 21:21

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).
jammerjam
 
Posts: 27
Joined: Wed 09 Jan 2008 22:56

Postby Alexey.mdr » Fri 28 Mar 2008 08:38

What component do you use for data loading?
Did you check command timeout property and
server timeout property (connect_timeout)?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby jammerjam » Fri 28 Mar 2008 12:35

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.
jammerjam
 
Posts: 27
Joined: Wed 09 Jan 2008 22:56

Postby Alexey.mdr » Fri 28 Mar 2008 12:56

Perhaps the application that you are using for loading data is closing the connection?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby jammerjam » Fri 28 Mar 2008 13:02

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.
jammerjam
 
Posts: 27
Joined: Wed 09 Jan 2008 22:56

Postby Alexey.mdr » Fri 28 Mar 2008 17:25

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;
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Postby jammerjam » Fri 28 Mar 2008 17:33

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.
jammerjam
 
Posts: 27
Joined: Wed 09 Jan 2008 22:56

Postby Alexey.mdr » Mon 31 Mar 2008 13:31

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?
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL