Sign in to follow this  
Followers 0
michaelslamet

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

8 posts in this topic

#1 ·  Posted (edited)

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 by michaelslamet

Share this post


Link to post
Share on other sites



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.


 

OS : Win XP SP2 (32 bits) / Win 7 SP1 (64 bits) / Win 8 (64 bits) | Autoit version: latest stable / beta.
Hardware : Intel(R) Core(TM) i5-2400 CPU @ 3.10Ghz / 8 GiB RAM DDR3.

My UDFs : Skype UDF | TrayIconEx UDF | GUI Panel UDF | Excel XML UDF | Is_Pressed_UDF

My Projects : YouTube Multi-downloader | FTP Easy-UP | Lock'n | WinKill | AVICapture | Skype TM | Tap Maker | ShellNew | Scriptner | Const Replacer | FT_Pocket | Chrome theme maker

My Examples : Capture toolIP Camera | Crosshair | Draw Captured Region | Picture Screensaver | Jscreenfix | Drivetemp | Picture viewer

My Snippets : Basic TCP | Systray_GetIconIndex | Intercept End task | Winpcap various | Advanced HotKeySet | Transparent Edit control

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.


 

OS : Win XP SP2 (32 bits) / Win 7 SP1 (64 bits) / Win 8 (64 bits) | Autoit version: latest stable / beta.
Hardware : Intel(R) Core(TM) i5-2400 CPU @ 3.10Ghz / 8 GiB RAM DDR3.

My UDFs : Skype UDF | TrayIconEx UDF | GUI Panel UDF | Excel XML UDF | Is_Pressed_UDF

My Projects : YouTube Multi-downloader | FTP Easy-UP | Lock'n | WinKill | AVICapture | Skype TM | Tap Maker | ShellNew | Scriptner | Const Replacer | FT_Pocket | Chrome theme maker

My Examples : Capture toolIP Camera | Crosshair | Draw Captured Region | Picture Screensaver | Jscreenfix | Drivetemp | Picture viewer

My Snippets : Basic TCP | Systray_GetIconIndex | Intercept End task | Winpcap various | Advanced HotKeySet | Transparent Edit control

 

Share this post


Link to post
Share on other sites

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 :D

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 :)

Share this post


Link to post
Share on other sites

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 :)


 

OS : Win XP SP2 (32 bits) / Win 7 SP1 (64 bits) / Win 8 (64 bits) | Autoit version: latest stable / beta.
Hardware : Intel(R) Core(TM) i5-2400 CPU @ 3.10Ghz / 8 GiB RAM DDR3.

My UDFs : Skype UDF | TrayIconEx UDF | GUI Panel UDF | Excel XML UDF | Is_Pressed_UDF

My Projects : YouTube Multi-downloader | FTP Easy-UP | Lock'n | WinKill | AVICapture | Skype TM | Tap Maker | ShellNew | Scriptner | Const Replacer | FT_Pocket | Chrome theme maker

My Examples : Capture toolIP Camera | Crosshair | Draw Captured Region | Picture Screensaver | Jscreenfix | Drivetemp | Picture viewer

My Snippets : Basic TCP | Systray_GetIconIndex | Intercept End task | Winpcap various | Advanced HotKeySet | Transparent Edit control

 

Share this post


Link to post
Share on other sites

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! :)

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0