3.0 SQL pooling question
In 3.0, is there a way to consider a SQL connection dead if it returns a certain class of errors, then close and re-open it? We had a problem overnight with postgres and query-planner caching for long-lived connections. Since we "do ITIL" (sigh) I have to write the problem up and submit a "possible solutions". If 3.0 can handle that for us, I can put that down as a "planned" item.
On 14 Jun 2013, at 11:55, Phil Mayers <p.mayers@IMPERIAL.AC.UK> wrote:
In 3.0, is there a way to consider a SQL connection dead if it returns a certain class of errors, then close and re-open it?
The drivers can return SQL_DOWN, which should result in a new connection being established or an existing connection being reserved in the pool and the query being retried. The current behaviour is to cycle through all connections before giving up. So if the database is down, all the connections are closed, and you don't have queries randomly failing once the database comes back up because of stale connection handles. I'm not sure this is the right behaviour, but it seems to work best for simulated failures. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
On 14/06/13 12:12, Arran Cudbard-Bell wrote:
On 14 Jun 2013, at 11:55, Phil Mayers <p.mayers@IMPERIAL.AC.UK> wrote:
In 3.0, is there a way to consider a SQL connection dead if it returns a certain class of errors, then close and re-open it?
The drivers can return SQL_DOWN, which should result in a new connection being established or an existing connection being reserved in the pool and the query being retried.
I'm actually thinking of errors where the SQL server is up, but responding incorrectly (e.g. cached query plans are invalid due to schema changes). I guess there would need to be a per-driver set of options as to which error codes count as "down"?
Phil Mayers wrote:
I'm actually thinking of errors where the SQL server is up, but responding incorrectly (e.g. cached query plans are invalid due to schema changes).
I guess there would need to be a per-driver set of options as to which error codes count as "down"?
I'd hard-code those, quite frankly. They're database-specific, and there's no reason to make them configurable. Alan DeKok.
On 14 Jun 2013, at 14:04, Alan DeKok <aland@deployingradius.com> wrote:
Phil Mayers wrote:
I'm actually thinking of errors where the SQL server is up, but responding incorrectly (e.g. cached query plans are invalid due to schema changes).
I guess there would need to be a per-driver set of options as to which error codes count as "down"?
I'd hard-code those, quite frankly. They're database-specific, and there's no reason to make them configurable.
Agreed. There's not usually any way to resolve an error string to an error code, so you'd either have to specify individual error numbers (which could be messed up by the errors being enumerated differently between library versions), or write translation tables in the drivers to convert strings to the macro values. FYI: SQL_DOWN was renamed to RLM_SQL_RECONNECT (i think SQL_DOWN conflicted with one of the drivers). Forgotten that had happened. Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team
participants (3)
-
Alan DeKok -
Arran Cudbard-Bell -
Phil Mayers