SciBit

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

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?

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.

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.

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:

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.

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.

The http://dev.mysql.com/doc/refman/5.0/en/gone-away.html document says the following:


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?

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


Save and recompile.

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

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

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.