SciBit

Forums
It is currently Sun Sep 21, 2014 12:04 pm

All times are UTC




Post new topic Reply to topic  [ 14 posts ]  Go to page 1, 2  Next
Author Message
PostPosted: Wed Feb 15, 2006 6:36 am 
Site Admin

Joined: Wed May 21, 2003 7:41 am
Posts: 951
This error usually pops up at the most unexpected times and places, and usually in an already working client application/website.

Here are a couple of reasons you get this error on the client-side while communicating with MySQL:

1. BLOBs

Another primary cause for this error, is when you update or select tables containing large blobs. By default MySQL's max_allowed_packet is set to 1MB. Should your update-SQL grow larger than this MySQL parameter, MySQL will simply drop the connection. This can easily happen with even a 512MB blob value, as blob values are usually hexed and thus takes two hex values per byte of the blob.

2. Client Timeouts

Most MySQL servers have at least three primary timeout settings for a client connection, connect, read and write timeouts. These control how long your client should wait for a response from the server when it connects, how long your client will wait while reading data from MySQL (i.e. doing a query) and how to wait when doing a write (i.e. sending a query or update), respectively. Usually is it a good idea notice what exactly you where doing when MySQL's "connection was lost". So check your timeouts on both the client and server side.

3. Server's wait_timeout

Your error may simply be due to your MySQL server's wait_timeout being set too low. The default is usually 8 hours, but sometimes hosting companies set it lower, ex. 5 mins, to best service websites. Yet, if you now have a live connection client connection to MySQL and does nothing on the connection for 5mins, the connection will simply drop.

You can read more on this and related issues here:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 31, 2006 2:33 pm 
Advanced

Joined: Wed May 31, 2006 2:01 pm
Posts: 13
This does happen quite often. The connection goes down due to inactivity, or due to the Internet service being lost. The problem I have is that the application gets stuck in a loop displaying the "Lost connection" or "Gone away" message. Most often the only thing the user can do is end the task and restart it. What would be good is for MyComponents to open a new connection and continue seamlessly, rather than displaying this error and doing nothing about it. Possibly have an optional dialog prompting the user to "Make a new connection?".

I know I can change the timeout to a very high value or regularly ping the MySQL server, but I rather not do this. Does anybody have a proper solution? Will I have to modify the MyComponents source code, or can I do something in my own code to prevent this from happening?


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 31, 2006 3:36 pm 
Site Admin

Joined: Wed May 21, 2003 7:41 am
Posts: 951
Good suggestions Surinder. However, this is not the work of the MyComponents, as different developers and scenarios begs for different solutions. For ex, you would not like it if you developed a CGI using the MyComponents and everytime a connection issue occurs when a user browse your website a popup dialog appears on your webserver's screen, would you?

This is why it is up to you to handle the exception as you see fit using Delphi's try..except blocks. For your specific errors it would be best to simply close the MySQLServer, and start a connection from scratch. If you don't then the error will re-occur each time a connected dataset is refreshed, as they will try to use the now existing but invalid MySQLServer connection.


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 31, 2006 5:10 pm 
Advanced

Joined: Wed May 31, 2006 2:01 pm
Posts: 13
Thanks very much for your suggestion. I will try trapping the exception and reopen the MySQL server connection. Looks like this will solve my problem. I will report back to you if it doesn't.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 02, 2006 12:31 pm 
Advanced

Joined: Wed May 31, 2006 2:01 pm
Posts: 13
I've tried reopening the MySQL Server connection, in a try..except block. It doesn't quite work as I want it to. When the MySQL Server connection is closed, all the datasets that this connection uses are closed too. The datasets have to be reopened and the data has to be fetched again. This messes up various forms that the application may have open. Also the query that caused the exception isn't re-run, i.e. it's lost for ever.

What I would like, is for the MySQLServer component to reopen the connection without closing all the datasets and refetching the data. i.e. to behave as if the connection didn't get lost at all.

I can solve the timeout problem by adding something like the following to a timer event that runs every 5 minutes:
Code:
if MySQLServer1.Connected then MySQLServer1.ExecSQL('SELECT "KEEP ALIVE";');

But this doesn't help if the Internet connection goes down for half an hour or so. Is it possible for the MySQLServer component to make a reconnection without affecting the datasets? This would be the most ideal solution.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 02, 2006 12:44 pm 
Site Admin

Joined: Wed May 21, 2003 7:41 am
Posts: 951
Well this is actually suppose to happen automatically, but depends on your MySQL's variables as well as the options you specify in your MySQLServer.Options. You'll need to study the optimum combination, especially as it is determined if you have control over your MySQL variables or not. You can read up more on this in the MySQL Docs, mysql_options C API.

Alternatively can you use the MySQLServer.Ping method which does what the MYSQL Docs have to say on the mysql_ping C API method.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 02, 2006 12:59 pm 
Advanced

Joined: Wed May 31, 2006 2:01 pm
Posts: 13
The http://dev.mysql.com/doc/refman/5.0/en/gone-away.html document says the following:
Quote:
You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).

Quote:
Prior to MySQL 5.0.19, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn't know if the server did get the original query or not.

We're running MySQL 5.0.21. Is this "reconnect" option configurable in the MySQLServer component? I can't see it in the Options property. How do I make sure this reconnect option is enabled?


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jun 03, 2006 12:19 pm 
Site Admin

Joined: Wed May 21, 2003 7:41 am
Posts: 951
Hi Surinder,

No it is not in the Options property, in fact, you are right, it is not available in the TMySQLServer at the moment because of it's recent addition. Prior to 5.0.21 was this option only available via the coInteractive option which correspond with the MySQL CLIENT_INTERACTIVE option as per the MySQL Docs.

However, if you are willing to change a couple of lines of source code you can easily add this to your MyComponents suite (we'll add it as well for the next release):
Code:
MySQLDrivers.pas line 525, append this to the list of options:
    MYSQL_SECURE_AUTH,
    MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_RECONNECT);

line 927:
     if (FOptions.IndexOfName('autoreconnect')>-1) then begin
        i := StrToIntDef(FOptions.Values['autoreconnect'],1);
        mysql_options(Result,MYSQL_OPT_RECONNECT,@i);
     end;

MySQLDirect.pas line 83 (this will add the option to the direct driver), like in the previous change:
    MYSQL_SECURE_AUTH,MYSQL_REPORT_DATA_TRUNCATION, MYSQL_OPT_RECONNECT);

MySQLDirect.pas line 172:
     MYSQL_OPT_RECONNECT: TMySQLDirectClient(mysql).ShouldReconnect := True;


Save and recompile.

Now you can "autoreconnect=1" to your TMySQLServer.Params or the TMySQLServer.Driver.Options


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jun 03, 2006 4:00 pm 
Advanced

Joined: Wed May 31, 2006 2:01 pm
Posts: 13
Dear SciBit Support,

Thanks very much for this. I made the changes to MySQLDrivers.pas & MySQLDirect.pas as you suggested. Then I added "autoreconnect=1" to MyServer.Driver.Options. It works perfectly! This is exactly what I wanted. When I kill the thread on the MySQL server to emulate a lost connection, everything carries on functioning. A new connection is made seamlessly. I no longer get the annoying "MySQL server has gone away" and "Lost connection to MySQL server" errors.

Developers need to be aware that a reconnection means temporary tables and user-defined variables from the previous connection are lost. Transactions are rolled back and locks are released etc. See http://dev.mysql.com/doc/refman/5.0/en/ ... nnect.html for more details.

Thanks again for your help and solving my problem.

Regards, Surinder


Top
 Profile  
 
PostPosted: Wed Jun 21, 2006 4:16 pm 

Joined: Thu Aug 11, 2005 5:02 pm
Posts: 7
Hmmm.

My users are not sitting on unused connections for long periods of time. In general terms they're hitting the MySQL server constantly. Nor are they submitting excessively large update SQL commands. My MySQL server is set to allow 20MB while the maximum average update involves at *most* 100K and doesn't involve BLOBs at all.

But my users are getting this error on a rather constant basis.

Are there any other conditions that would cause this?

It's not a timeout issue. No possible way. It's not a memory issue as the application uses anywhere from 10MB to 20MB RAM, at most. It's not a packet size issue because the maximum packets submitted to the server is around 100K while the limit is far above this.

Frankly this is really aggravating me. Particularly irritating is that the error message itself is rather unhelpful.

Any ideas? At this juncture I'm facing having to abandon the SciBit component and shifting to something else.

Any help would be vastly appreciated.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ]  Go to page 1, 2  Next

All times are UTC


Who is online

Users browsing this forum: No registered users


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group