MySQL Connection timeouts - Seek recommended coding tricks

[nigel]

I am using a remote MySQL database, management of which is not under my control. The MySQL server installation currently has a connect_timeout setting of 6 seconds which is quite irritating. Although I cannot change the timeout setting, this doesn't concern me too much as in most future cases the server installation is likely to be under my own control. However, it occurred to me that any applications I develop should handle these events (even if they are far less frequent) without disruption to the application concerned.

Anyway, I am curious if any of the more experienced Purebasic database programmers could offer any recommendations on more efficient ways to code for detection and reconnection to the server from within the Purebasic client application. I can think of many ways to handle this, none of which are anything other than messy (such as coding to identify a server disconnect error at every place in the application where a Database request is made, then using a subroutine to perform the reconnection and routing the subroutine Return point to a Label where the Database request can be repeated).

I'm just recognising that others may have developed a better way of dealing with this than I can think of. If there is no better way, then I would appreciate any comments along the lines of -

NOPE - No magic answers

Thanks in advance for any advice.

Nigel

[Num3]

mysql_ping(hDB)

Place it in a thread... it will keep the connection alive...

[nigel]

Num3

Thanks for the suggestion. However, pinging the database in a thread is not the kind of solution I was looking for.

I decided to approach the problem of identifying a server disconnect by examining the DatabaseError() contents after every database request. Unfortunately, once the first disconnect takes place, this error never gets cleared from DatabaseError() making it impossible to detect any subsequent disconnections in the session.

I was pulling my hair out on this one because all database requests return either 0 or 1 and neither of these results is considered to be an error, meaning the contents of DatabaseError() perpetually show the original disconnection error. Adding insult to injury, I can find no way of resetting the text of DatabaseError(), making it impossible to handle disconnects from within my Purebasic applications.

Any ideas on this would be appreciated.

[srod]

You can always disconnect on receiving an error and then reconnect etc.

[nigel]

srod

When the remote MySQL server disconnects me for the first time, I am able to detect this has happened because DatabaseError() contains a description of the error which includes the text "server has gone away". When my application identifies this case (by finding this string in DatabaseError() text), it performs a reconnect operation automatically. The problem after this is that the text of DatabaseError() continues to return the original error text "server has gone away" even though this is no longer true and this never gets cleared, even though a reconnection has taken place, and innumerable database calls have since been made. The fact that I am unable to clear this DatabaseError text, means that I have no straightforward way of detecting any more server disconnects beyond the first one.

Unfortunately, database calls return a result which is either 0 or 1 (no error codes, just true or false). Most of these database calls do not place anything in the DatabaseError() text at all because they are simple "Select" queries. Even when no results are returned from a "Select" query, then this is not considered to be an error in PureBasic because no message gets placed in DatabaseError() text. Unfortunately it appears that the only way to ever clear the "server has gone away" text is to perhaps artificially invoke another major database error condition of another kind.

The correct way to handle this would be for something like a PureBasic "ClearDatabaseError()" statement to appear out of thin air, in which case the error can be cleared after it has been dealt with. Unfortunately such a change is unlikely to happen.

Nigel

[nigel]

I have used a kludge to circumvent this limitation as it is the best solution I can think of at the moment.

When the first disconnect occurs, I will reconnect and reset the DatabaseError() text by issuing a bogus SQL Select call to a fictional table "ZZ" that does not exist. This causes an error condition which overwrites the "server has gone away" legacy text in DatabaseError(), thereby permitting me to detect future disconnections and identify them as real events.

Ugly - Yes
Elegant - No
Effective - Yup

Nigel

[srod]

I nearly suggested doing that, but thought it was too, well, in your own words... ugly!

I could suggest switching to OLE-DB instead via ADOmate. You should be able to refine the error returns somewhat more effectively.

I am a little puzzled because I don't quite understand why you cannot use the return value from something like DatabaseQuery()? That is, only use DatabaseError() in the case when functions like DatabaseQuery() return zero to signify an error. In that way you can be sure, at least with MySQL, that the error message should be current and up to date when it matters. Or am I missing something?

[nigel]

srod

Thanks for your comments which are always appreciated.

To answer your questions - I do indeed only use this mechanism in cases where the Database function returns zero, as these are the only cases where a remote server disconnect is a possible cause. However, a zero returned result for a simple "No rows found condition" will not place any kind of error message in DatabaseError(), nor will it clear any prior error text that may have been placed there half an hour ago. Unfortunately, DatabaseError() is not something which can be used reliably to identify what has happened when a zero result is returned. This is a weakness in PureBasic. What needs to happen is either one of two things :-

1. Every database related zero result should place a text response in DatabaseError(). Currently that is not the case

or failing this,

2. There should be a mechanism for the programmer to clear (initialise) an existing text responses from DatabaseError().

In my particular case, the first instance of a server disconnect in a given session, places informative error text in DatabaseError() and I am able to identify the disconnect by examining this text when it is present following a zero database operation result. The problem is that any subsequent zero database operation results for things such as "rows not found" do not change the text contents of DatabaseError(), making every subsequent zero result look like a server disconnect. As you can see, it becomes impossible in these circumstances to identify the second or subsequent server disconnect when it arises. To paraphrase your own words - "the error message is never current and up to date when it matters" it is always very old and very stale........ because it only records catastrophic database errors and hangs onto their legacy like grim death.

The reason I intend to continue using ODBC is related to my preference for using "relatively bulletproof" OpenVMS based server platforms. Much as I like Windows as a client environment, I have no desire to use it for mission critical applications on the server end.

Thanks for everything you continue to do to help PureBasic neophites like myself (You have unblocked problems for me on more than one occasion already). Your expertise and willingness to help does not go underappreciated (I'm sure that I could easily be speaking for many other people here). I hope that one day some of us can return your generosity.

Nigel

[srod]

Hi,

I understand what you are saying. Must admit I have encountered this myself, but just switch to OLE-DB.

Seems to me that you might be able to make use of mysql_thread_id() and mysql_ping(), assuming auto-connect is enabled.

Before issuing a query, use mysql_thread_id() to retrieve the connect's thread ID. In the case of a zero return from your query, issue a mysql_ping() call followed by another mysql_thread_id(). If the two threadID's retrieved differ then you know that the connection went down etc.

Must admit that I do not have enough experience with MySQL (I have only just started using it) to know if this will work in a satisfactory manner?

[Raybarg]

In case you want to really make sure if the connection is still alive and MySQL is present, you can also make a procedure to issue simple query "SELECT 1 as IAMHere" which will return 1 row with 1 column named IAMHere and value 1 in it.

Its never good idea to rely on a server side error as suggested before. Application should never use broken queries... (it's not like I have not seen solutions where an update is attempted and if server returns error, then insert query is issued... geez).

That's why my design rule is to check row count by query in some cases before actual record query. (and use single solid index field for COUNT() instead of COUNT(*) if its even supported these days). This design rule also opens some more options to reliability and user friendliness... if COUNT() query results no records then there is an error, also if user manages to attempt huge query then application can check the count and verify user (with proper warning) that it might take some time. But it's all design options in the end.