[SOLVED] MySQL via ODBC: MySQL server has gone away error

[michaelslamet]

Hi fellow members,

I'm using a MySQL UDF by cdkid which is a great UDF, IMHO. I'm using it on some of my little application sucessfully.

Currently I'm facing this problem: after a very short time (maybe around 60 secs) after connection is made, this error occured: "MySQL server has gone away".

I want it to connect when the application run at the first time and only disconnect when the user quit.

Is there anyway to initiated somekind of auto-reconnect or extend the timeout period to the maximum?

I dont see these parameter can be passed through the UDF

ODBC version is 3.5.1 running on Windows XP SP2

Thanks in advance

Edited January 4, 2013 by michaelslamet

[FireFox]

Hi,

For a database, 60sec is a long idle time. If you want the connection to be kept alive, you need to make a simple request like a ping.

SELECT 1;

Br, FireFox.

[michaelslamet]

Hi,

For a database, 60sec is a long idle time. If you want the connection to be kept alive, you need to make a simple request like a ping.

SELECT 1;

Br, FireFox.

Thanks, Firefox

This is the scenario:

The form is opened for user to input into the value to GUICtrlCreateInput. Before the form opened, it will initiated a connection to MySQL server through ODBC because on the form it will display some values taken from the MySQL database. While the form openned, the user can takes unlimited time to input the fields. When he click the "save" button, the ODBC complain that the connection has host ("mysql server has gone away") so it cant save.

Is it a good solution that before it try to save the information to the mysql database, we initiate the connection again without closing the previous connection?

So in the application we will initiate for many many times (using _MySQLConnect) but only close the connection one time (on exit).

I think it will much slower compare to we only connect one time when the application run at the first time, keep it alive all the time, and close the connection when it exit?

[FireFox]

Is it a good solution that before it try to save the information to the mysql database, we initiate the connection again without closing the previous connection?

This is not a problem, but if the connection takes a noticeable time then it might not fit your needs.

This is what I meant from my previous post :

Global $iPingTimer = TimerInit()
...

While 1 ;main loop
...

If TimerDiff($iPingTimer) >= 10000 Then ;10sec
query SELECT 1;

$iPingTimer = TimerInit()
EndIf

Sleep(1000)
WEnd

Br, FireFox.

[michaelslamet]

Firefox,

Many thanks for providing me a sample code!

Honestly without a sample code, i dont think I really know what you mean :-p

I think thats what make a different between a pro and a noob like me

So I think I implement the SQL "ping" successfully.

Instead of 10sec, I use 30sec.

Instead of query "select 1", I use "select 1 limit 1" (will it faster?)

Any suggestions is really appriciated

[FireFox]

Instead of query "select 1", I use "select 1 limit 1" (will it faster?)

I don't think this will be efficient since db algorithms are very powerful.

I'm glad to have helped you

[michaelslamet]

Ok then, I'm going to use "SELECT 1" instead of "SELECT 1 LIMIT 1"

Yes, you've helped me solving this problem, thanks a lot for that, Firefox!

[jchd]

There's a caveat I suspect: you seem to first SELECT a number of rows, display them, let the user update the form then launch UPDATEs as required when (s)he submits. The problem here is when/if someone else UPDATEs or DELETEs rows that are on this form.

The solution for that situation is what SQL calls a transaction which makes the whole lot of operations atomic: either they succeed as a whole or fail altogether. The problem is that a transaction is meant to be something quick. If one users goes for lunch (or week-end or the PC gets rebooted wild, or ...) while leaving the form open, noone can update the rows under transaction.

This is an DB design issue that needs consideration. Generally instead of leaving a write transaction pending for a long time, it is practical to set a "busy" indicator in the selected rows. Making this indicator an ID of the user "owning" the rows help tracing who opened a form without submitting it back within reasonable time.