MySQL server has gone away. Error Code 2006

I’ve been getting this error message in my application.
I think what has happened is that mysql has terminated a connection I made due to inactivity.
Each class in my application is responsible for a table in the database.
Each class makes it’s own connection to the database when constructed.
If that class isn’t accessed for like 24 hours then used, it will fail with this message.

Must each of my classes have a timer to check its connection?
What method might I use to determine if the connection has gone away?

Could the app just attempt to reconnect if it determines it is no longer connected to the DB?

Yes that is what I did in the past… but it’s just cludgy… Try and if you fail try again…
I wish I could just put an if statement…

if not mDatabase.Connected then
mDatabase.connect
end if

Why can’t you just add your own “ConnectionTest” method to do just that?

Why not add a method to the database class that fires an event if the connection is dropped?

When would it fire? As soon as the connection is lost? After a period of time? When you attempt to access the DB? How would it know the connection is lost if you don’t attempt to access the DB? There are lots of things to consider here.

Extending the framework with code that is specific for your situation is probably a better solution for now.

I would assume that the TCP/IP base class would detect the loss of connection and propagate that event up.

Bad assumption. Plugin wraps C client libraries. The client libraries provided by database vendors typically are absolutely horrible about notifying disconnects. Middleware writers typically have to do a lot of work to detect a disconnect and report it correctly to calling environment.

I just add a timer that performs a generic sqlselect every 15 mins to keep the connection live. “SHOW DATABASES;” works on all instances of mySQL.

And ‘hope’ that the timer doesn’t fire while a query is in progress?

[quote=34047:@Brian O’Brien]And ‘hope’ that the timer doesn’t fire while a query is in progress?
[/quote]

You could protect with a CriticalSection. Enter before you select, leave after select complete.

This Class is what I use in my projects when connecting to a mySQL database. I have similar classes for the other supported database servers. It abstracts my application from the minor differences between the servers and provides central error trapping. You’ll note I pass the sql or prepared statement along with the calling method name & a sequence number within that method which allows me to go directly to the offending line of code should an error occur.

HTH

Is there a particular reason you opted to have every class create and maintain it’s own connection to MySQL? Why not have all the classes communicate via a single central connection? This does not address your disconnect issue, but I’m curious about what led to that design decision.

The SQLSelect statement is synchronous. Only one can be active at a time, even with a timer firing periodically.

chow ahn.

It’s a good question Kimball.
I have several tables in the database and I have a class for each table.

The reason I went this way was because I was worried about multiple instances sharing the same connection and that ‘shared’ connection would become a liability in a multi threaded environment. I believe multi-threading is ok with mysql but not sure about other databases.

It also seemed like a nice way to make each table ‘feel’ like an object rather than it feeling like a table in a database.
You construct the object (make the connection) and then you have methods to use the table.

I’m not super happy because it seems very un-object oriented to construct an object and then have it dissolve if you don’t use it within a certain amount of time.

Creating a class for each table also (for me) seemed to make Model View Controller paradigm simple to implement, as each table has it’s own view and model.

I’m not too crazy about bringing up and down sql connections for every single access to the table or database as that seems like a lot of overhead.

If you look around at any other database abstraction layers such as those found in every MVC framework I’ve ever used (Ruby on Rails, Yii, Pear, etc) you’ll find that it is common to write a class for every table in the database as you have done, but each class shares a single connection to the database. This can be done by passing the database connection instance to the constructor of each class, and having each class refer to it locally, in similar fashion to how you are doing it now.

The nice part about doing it this way is that you can then abstract out things that need to be done to keep the connection healthy to just the single connection, and your application can grow to an arbitrary number of tables.

Most MySQL instances have a connection limit that is relatively conservative (N connections). If your application grows to the point that you have more tables than that, you’ll run into the inability for the N+1st table instance to connect to the database, unless you have access to change the number of connections permitted.

I’ve written several large-scale apps that connect to a database with hundreds of tables and do all their interactions over a single connection. This is nice because you either have an active connection or you don’t, and you don’t have to worry about whether all the connections for all your tables are currently up and happy.

Just my $0.02. Your mileage may vary, of course.

SELECT 1 FROM DUAL

Uses much, much, less resources and is faster.

I used to have my MySQL databases on a massively shared server. It would boot off your MySQL session after 15 seconds of inactive use. I create the new database each time and have a function that creates the connection or re-awakens an existing connection.

I always use the model of keeping the connection open a minimal amount of time. In many scenarios you may not be able to control the timeout limit on the server side. Also, for large / busy servers, it is possible it will run out of connections/resources due to too many apps holding a connection open.

My app logic usually connects at launch, downloads/caches some semi-static frequently used details, then disconnects. Upon user action/request, I re-connect to server, perform a series of queries as needed to accommodate the user request, then disconnect. If I anticipate being idle more than 10 seconds, I will disconnect. The connection/disconnection process is really quick on most modern systems & networks theses days, and very few apps would notice the slight hesitation. (All of the database communication can happen in a thread, so the UI can remain responsive).

This strategy was promoted for a VERY long time by Microsoft, Oracle, and Sybase (as best practice). This was also needed in scenarios where database server licensing was based on the number of concurrent connections permit. I worked at firm with 25 connection limit to MS SQL Server, yet we had about 200 individuals using the application using this strategy. It was rare anyone noticed more than just a slight hesitation during the busy parts of the day.

As others have suggested, keeping an idle database connection open may not be the best approach. Closing the connection upon completion of activity and reconnecting as necessary will also keep the DBAs happy.