Avoiding Connection Lost (during query) exceptions

[John Smith22]

Hi,

We have a scientific program that calculates data in a very large matrix. 50 instances of the program run on 50 processors on 10 PCs, so 50 matrices are in use at any moment by all program instances. The average size of a matrix is about 500 MB but can go up to 2 GB. The server runs on Win 7 64. The client PCs run on Win XP 32.

The calculation of a single matrix by a program instance can take days or even weeks, then when it’s finished, the program starts to write all matrix data (about 1 GB) to the database.

Then I get this error:
"Lost connection to MySQL server during queryError on data writing to the connection:An established connection was aborted by the software in your host machine.Socket Error Code: 10053($2745)"

This happens on some of the write operations. Other do not have problems. I presume the largest matrices show this problems.

There seem to be two issues:

(1). The connection will be aborted by the server after a certain timeout, certainly the time to calculate a matrix is much larger than the server timeout;
(2). When writing tens of millions of records to a database, the connection might be reset or lost during query to store the data;

What’s the correct procedure to write very large amounts after a long time of inactivity to a server? In other words: How do I avoid any errors?

The structure (in pseudocode) I use now is:

(… do some very time consuming data)
MyConnection.connect ;
MyQuery.Connection := MyConnection ;
for i := 1 to 10000 do
begin
Query.SQL.Text := ALongSQLInsertStatement ;
Query.Execute ;
end ;

I tried the START TRANSACTION / COMMIT method before, but that caused even more connection lost errors as it probably tries to write the whole set of data to the database in one chunk. In the above example the data is split in 10,000 pieces which solved many errors, but not all. I still get the above error in about 10% of the (largest?) matrix write to database operations.

[ViktorV]

The MySQL server connection loss may be due to a little value of the wait_timeout MySQL system variable. Try to set the value of the wait_timeout MySQL system variable to 31536000, and check if the problem still persists.
Also, MySQL server connection loss when inserting large data to MySQL server can be due to the server settings. To load big data, you should increase the value of the max_allowed_packet server variable in the my.ini file and restart your server.
For example:
max_allowed_packet = 16M
Try to increase the value of the max_allowed_packet MySQL system variable, and check if the problem still persists.
You can find more information here:
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

[John Smith22]

OK. I will try this solution.
By the way, for a windows environment, the wait_timeout value is 2147483, not 31536000.

Since I might have to wait for a week to see whether it solves the problem, I will cannot give feedback on short term.

I'll also try whether I can mimic (part of this) this error by manually killing the connection from inside MySQL workbench before the client starts writing. This will save time.

[John Smith22]

By the way, I found an interesting article about this problem. See link below:
http://www.percona.com/blog/2010/05/05/ ... d-harmful/

This matches other solutions I've seen on this forum that reconnect on an 'not connected' exception.

I temporary solved the problem in a quick and dirty way by disconnecting and reconnect to the server before writing the matrix data. Since I'm the only user of the server and hours lie between the write sessions, this does no harm.

MyConnection.Close ;
MyConnection.Open ;
//Start writing data

I will implement a proper solution later based on the 'not connected' exception.

[ViktorV]

To resume lost connection, you should use the TMyConnection.OnConnectionLost event handler. The OnConnectionLost event handler is used to process fatal errors and perform failover. To make the OnConnectionLost event handler work, you should set the TMyConnection.Options.LocalFailover property to True. Note that to use the OnConnectionLost event handler, you should add the MemData unit to the USES clause of your unit. Here is an example of using the OnConnectionLost event handler:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MyConnection1.Options.LocalFailover := True;
  MyConnection1.Open;
end;

procedure TForm1.MyConnection1ConnectionLost(Sender: TObject;
  Component: TComponent; ConnLostCause: TConnLostCause;
  var RetryMode: TRetryMode);
begin
  RetryMode := rmReconnectExecute;
end;

In this case, if connection was lost, MyDAC will try to reconnect and reexecute the abortive operation. For more information, please read the MyDAC documentation.

[dataflow]

Hi, i have version 8.7.27 and I keep getting the timeout even after trying all this suggestions, can someone post a different solution?.

Thanks

[ViktorV]

The OnConnectionLost event occurs only when the following conditions are fulfilled:
- a fatal error occurs;
- there are no opened transactions in a connection that are not ReadOnlyReadCommitted (if connection has at least one opened transaction, which is not ReadCommitedReadOnly, FailOver does not execute. All ReadCommitedReadOnly transaction are restored with FailOver operation);
- there are no opened and non-fetched datasets;
- there are no explicitly prepared datasets or SQLs.
Please make sure that none of the conditions above is violated.

Note that MyDAC does not automatically initiate checking connection to the server. Therefore, after successfully connecting to the server if the connection to the server disconnects the TUniConnection.Connected property will be set to True and the OnConnectionLost event will not be initiated until there is an attempt to connect to the server.
Also to solve your problem you can use the TMyConnection.Ping method. This method is used to test the connection to MySQL server if the connection was disconnected, there will be an error and an attempt to reconnect to the server.
As well this method can be used in order for the server not to close a connection due to timeout connection activity. In this case, you can use TMyConnection.Ping as follows - call it from the timer and set the interval timer to a value less than wait_timeout of the server (so that the intervals between commands receiving by the server do not exceed wait_timeout).