FetchAll = false causes connection lost

[joannelai]

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

Joanne

[Alexey]

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?

[joannelai]

Use e-mail address provided in the Readme file.

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

Thanks

[Alexey]

You project is received and is being investigated.

[Alexey]

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.

[joannelai]

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!

[Alexey]

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.