Hello Friends, I am using ODBC d

[JINESH GANDHI]

Hello Friends,

I am using ODBC driver to connect MySQL but randomly I face problem of MYSQL Connection Gone.
Guide me how to handle these error.

 

[Chris Miller]

Well, reconnect. You can't force a connection handle to stay valid, so in case you get an error or no result when using a handle that's gone invalid, you reconnect.

The easiest way to handle this is using a class for the data access, which does not only detect such an error but then also maintains the connection handle and reconnects to redo whatever last query didn't work. All in all just a simple case for OOP.

All failing queries will result in a negative return value of an SQLEXEC, in which case you do AERROR to get the error details. This will have error 1526, which you find in the help topic about AERROR. Error elements 3,4, and 5 will have ODBC specific error message and numbers from MySQL Server in your case, and look up what number means your connection handle was rejected or got invalid, broken, disconnected from the server side or anything like that, then redo the SQLSTRINGCONNECT or SQLCONNECT and redo whatever you were doing, unless that fails permanently and something more serious happened, like the server itself, not just MySQL went down, in which case you can do nothing more.

Things like non-shared connections can lead to the situation in combination to only having a few connections allowed, which can lead to the MySQL server cutting off the least used connection in favor of the more frequently used ones. So it's not pointing out a programming error, a connection handle can get invalid just by force of the server, not in your hands and in your responsibility. Even more so, if the server simply shuts down the service or shuts down overall, of course. But for the usual cases like limits in CALs or other network errors your code should be prepared to reconnect and retry what failed.

If your error handling detects a query error, of course reconnecting and retrying won't help, so it's important to detect you actually have an error based on the loss of the connection and not just a query error. And all in all, as I already said initially, this is doable when you have a class handling the SQL and maintaining the connection.

Chriss