FetchAll=False & Lost connection again

GuzunNicolae · Post by **GuzunNicolae** » Fri 02 Nov 2007 09:50

Hello

I am experiencing a problem that I thought was solved a long time ago. And to be honest till now everything was perfect.
The situation is as follows:
- I have to parse a lot of records (about 1 mln) and based on some conditions to change the data in the DB. This is a very lengthy process. It may take 10h to finish.
- So I have created a query with:
FetchAll=False;
FetchedRows=2000;
Unidirectional=True;
- Everything goes perfect until the parsed record reach 2000 and the new records have to be fetched. Then I get "Lost connection to MySQL server"

It cannot be the wait_timeout cause the connection does not stay iddle for more than 30 secs.
It cannot be the link quality: I am using LAN and even on local host I get the same problem.
I have decreased the FetchedRows to 200, and on 400th record (on localhost on 200th) I get the connection loss.
I have changed UniDirectional to False but this did not help either.

I have run MySQLAdmin to look at the queries. I have found out the following:
- When I run the program one connection is created
- When I run the parsing process a new connection is created that runs for about 1 min saying "Writing to net" and the query that opens all my records.
- While the second query is running my application is parsing records already. (I guess this is how it should be)
- After the second connection finishes it closes.
- All the data updates is done in the main connection which never gets iddle for more than 30 s
- Before the new records have to be fetched I get connection loss and even the main connection is closed

I am using Deplhi 7, MyDAC 5.20.0.11 and
MySQL server version: 5.0.18-Debian_3.dotdeb.1-log
MySQL client version: Direct

Can you please point me out what the problem might be. I will try to decrease the Fetched Rows to a smaller ammount. Yet I guess if I set it higher this will increase performance as less queries will be sent to the server.

Thanks

GuzunNicolae · Post by **GuzunNicolae** » Fri 02 Nov 2007 10:07

I have set the FetchRows=25 and it still does not work

eduardosic · Post by **eduardosic** » Sat 03 Nov 2007 00:45

GuzunNicolae wrote:I have set the FetchRows=25 and it still does not work

Hi GuzunNicolae, see MySQL variables:

connect_timeout
interactive_timeout
wait_timeout
net_read_timeout
net_write_timeout
slave_net_timeout

and

Try to Set in TMyConnection.Options.LocalFailOver := True;
in the Event OnConnectionLost of TMyConnection write a code:

Code: Select all

procedure TFrmMain.MyConnConnectionLost(Sender: TObject;Component: TComponent; ConnLostCause: TConnLostCause; var RetryMode: TRetryMode);
begin
   RetryMode := rmReconnectExecute;

end;

it's solve the Lost Connection Problem.

see em MyDAC Help about LocalFailOver and DisconnectedModel...

good look.

Antaeus · Post by **Antaeus** » Mon 05 Nov 2007 10:39

To GuzunNicolae.
Do eduardosic's hints helped you to solve the problem?

GuzunNicolae · Post by **GuzunNicolae** » Mon 05 Nov 2007 17:54

These are the values for all my variables that contains timeout:

Code: Select all

| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| sync_replication_timeout | 10    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800

I have created an event for connection loss, but it does not even go there when the connection is lost. I have put break point, ShowMessages, even RetryMode := rmReconnectExecute; and nothing

Antaeus · Post by **Antaeus** » Tue 06 Nov 2007 10:35

Does the problem occurs when you set both FetchAll and Unidirectional to False?
Does the error message appear when you run your application out of IDE control?

The OnConnectionLost event occurs if you set the TMyConnection.Options.LocalFailover to True.

GuzunNicolae · Post by **GuzunNicolae** » Tue 06 Nov 2007 12:41

Antaeus wrote:Does the problem occurs when you set both FetchAll and Unidirectional to False?

No. If I set at least FetchAll to false the problem does not occur.

Antaeus wrote: Does the error message appear when you run your application out of IDE control?

Yes. It appears if I run it out of IDE. But this way I get several Lost connection messages. When I run it outside of the IDE I get only one message of Connection loss

Antaeus wrote: The OnConnectionLost event occurs if you set the TMyConnection.Options.LocalFailover to True.

It is true.

eduardosic · Post by **eduardosic** » Wed 07 Nov 2007 00:19

GuzunNicolae wrote:These are the values for all my variables that contains timeout:
Code: Select all
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| sync_replication_timeout | 10    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800
I have created an event for connection loss, but it does not even go there when the connection is lost. I have put break point, ShowMessages, even RetryMode := rmReconnectExecute; and nothing

Hi,

the value for you connect_timeout variable is very small, try do increase this..

i use a max Value for all variables...

connect_timeout = 31536000
interactive_timeout = 31536000
wait_timeout = 31536000
net_read_timeout = 31536000
net_write_timeout = 31536000
slave_net_timeout = 31536000
max_connections = 31536000
max_user_connections = 31536000

the OnConnectionLost work's perfect for me.

Wait.. Antaeus go help you...

GuzunNicolae · Post by **GuzunNicolae** » Wed 07 Nov 2007 09:35

the OnConnectionLost work's perfect for me.

Does it work with FetchAll = False too?

GuzunNicolae · Post by **GuzunNicolae** » Wed 07 Nov 2007 09:57

GuzunNicolae wrote:
Antaeus wrote:Does the problem occurs when you set both FetchAll and Unidirectional to False?
No. If I set at least FetchAll to false the problem does not occur.

Sorry I messed up FetchAll = True and False.

If I set FetchAll=False no matter what value Unidirectional has (it might be either True or False) I get the error.
If I set FetchAll = True everything works well. But this way I cannot load 2mln records. And I may have 10mln in the future.

eduardosic · Post by **eduardosic** » Wed 07 Nov 2007 10:36

GuzunNicolae wrote:
GuzunNicolae wrote:
Antaeus wrote:Does the problem occurs when you set both FetchAll and Unidirectional to False?
No. If I set at least FetchAll to false the problem does not occur.
Sorry I messed up FetchAll = True and False.

If I set FetchAll=False no matter what value Unidirectional has (it might be either True or False) I get the error.
If I set FetchAll = True everything works well. But this way I cannot load 2mln records. And I may have 10mln in the future.

Hi Antaeus.

GuzunNicolae are rigth.

the OnConnectionLost don't is called while browsing on DataSet.

I go to make a small project to demonstrate it.

Antaeus · Post by **Antaeus** » Wed 07 Nov 2007 16:02

GuzunNicolae wrote:If I set FetchAll=False no matter what value Unidirectional has (it might be either True or False) I get the error.
If I set FetchAll = True everything works well. But this way I cannot load 2mln records. And I may have 10mln in the future.

It will be difficult to continue investigation of this problem without an example. Could you prepare one and send it to us? Try to make it as simple as it is possible. Also try decrease amount of test data. You can send your test sample to mydac*crlab*com.

GuzunNicolae · Post by **GuzunNicolae** » Wed 07 Nov 2007 16:08

If I decrease the amount of test data the problem is no longer valable

Anyway I will try

eduardosic · Post by **eduardosic** » Wed 07 Nov 2007 19:30

Antaeus wrote:
GuzunNicolae wrote:If I set FetchAll=False no matter what value Unidirectional has (it might be either True or False) I get the error.
If I set FetchAll = True everything works well. But this way I cannot load 2mln records. And I may have 10mln in the future.
It will be difficult to continue investigation of this problem without an example. Could you prepare one and send it to us? Try to make it as simple as it is possible. Also try decrease amount of test data. You can send your test sample to mydac*crlab*com.

Hi Antaeus, a made a small project, I send to Mydac*crlab.com

you can download in http://www.drdsistemas.com/util/mydac/mydac.zip

eduardosic · Post by **eduardosic** » Fri 09 Nov 2007 10:15

eduardosic wrote:
Antaeus wrote:
GuzunNicolae wrote:If I set FetchAll=False no matter what value Unidirectional has (it might be either True or False) I get the error.
If I set FetchAll = True everything works well. But this way I cannot load 2mln records. And I may have 10mln in the future.
It will be difficult to continue investigation of this problem without an example. Could you prepare one and send it to us? Try to make it as simple as it is possible. Also try decrease amount of test data. You can send your test sample to mydac*crlab*com.
Hi Antaeus, a made a small project, I send to Mydac*crlab.com

you can download in http://www.drdsistemas.com/util/mydac/mydac.zip

Antaeus, you can reproduce the problem with my project?

Devart Forums

FetchAll=False & Lost connection again

FetchAll=False & Lost connection again

solution..

increase variables..

i reproduce the problem..

I made a example..

Re: I made a example..