FetchAll = false causes connection lost

FetchAll = false causes connection lost

Postby joannelai » Thu 17 May 2007 04:08

Hi

I have an advanced grid in which client is able to do sorting, filtering etc. This grid is used to load massive of records (eg. >10000) so I set mysqldatatable.FetchAll = false to avoid fetching all records in once.

I have the following settings:
mysqldatatable.FetchAll = false
mysqldatatable.MaxRecords = 0
mysqldatatable.NonBlocking = false
mysqldatatable.QueryRecordCount = true
mysqldatatable.StartRecord = 0
grid.DataSoruce = mysqldatatable

During the initialization in runtime, I set mysqldatable.Active = true so the grid shows the first "page" of records. Meanwhile, I check the process in MySQL using SHOW FULL PROCESSLIST and I have the followings

+------+----------------+----------------------------------------------------+
| Time | State | Info |
+------+----------------+----------------------------------------------------+
| 10 | Writing to net | SELECT * FROM COMMUNICATE LIMIT 10000 |
+------+----------------+----------------------------------------------------+

If the client side does not do any action in which fetching all records is needed (such as sorting, filter), the 'Time' in MySQL keeps increasing until 8x or 9x (which is not a constant value) and the connection is killed suddenly. I tried to catch the change of connection state by MySqlConnection.StateChange but having no luck. Therefore, as the client try to access the non-fetched records, a MySqlException is thrown wtih error message "Lost connection to MySQL server during query"

On the other hand, if the client fetches all records (eg. by sorting) before the connection is killed, the connection is slept after fetching all.

Somehow, it is weird that the connection is killed without notify the client and I even don't understand why the connection is killed in this way.

Source code can be provided by request. Thanks :D

Joanne
joannelai
 
Posts: 5
Joined: Tue 26 Apr 2005 02:40

Re: FetchAll = false causes connection lost

Postby Alexey » Thu 17 May 2007 06:46

joannelai wrote:Source code can be provided by request.
I accept your offer. Please send me a test project to reproduce the problem.
Include definition of your own database objects.
Use e-mail address provided in the Readme file.
P.S. What versions of MySQL Server and MySQLDirect .NET are you using?
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Re: FetchAll = false causes connection lost

Postby joannelai » Thu 17 May 2007 07:59

Alexey wrote:Use e-mail address provided in the Readme file.


I have send the source code to the specified email address.

Thanks :lol:
joannelai
 
Posts: 5
Joined: Tue 26 Apr 2005 02:40

Postby Alexey » Thu 17 May 2007 09:28

You project is received and is being investigated.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Fri 18 May 2007 05:34

the 'Time' in MySQL keeps increasing until 8x or 9x
What is meant by "8x" and "9x"? We can't reproduce the connection problem. Time is increasing indeed, but this correct behaviour.
Please check your server settings, in particular net_write_timeout and
interactive_timeout parameters.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby joannelai » Mon 21 May 2007 08:52

I adjust net_write_timeout to a bigger value (e.g. from 60 to 300) and the disconnection time extends to 3xx seconds after the data fetch.

Thanks alexey.
It seems that I can solve the abrupt disconnection problem from the server side. However I have some concerns.

A user may open the grid, sit there and do not close the gird for hours. If I set the net_write_timeout to several hours, the mysql connection state will be "writing to net" for hours. If 50 or more users open grids and don't close them, I guess it will affect the mysql server operations.

On the other hand, if I set the net_write_timeout to a small value, then users may suffer from abrupt disconnection if they were idle for a certain time. (e.g. net_write_time=1 hour, user will be disconnected after 1 hour of data first fetch)


I'm wondering is the behavior intended?
What value does the MySQLDirect design team suggest about the net_write_timeout parameter?

Thanks again!
joannelai
 
Posts: 5
Joined: Tue 26 Apr 2005 02:40

Postby Alexey » Mon 21 May 2007 13:57

The value of net_write_timeout parameter parameter should be set after the deep investigation of your system, statistacal research and so on.
We can just suggest you cancelling fetch process on the client side if it takes to long to finish up.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL