2 small or 1 big server? – SQL reconnect
This is similar to what we do, although our error detection and recovery code is is a subclass of the PostgreSQL DAM to handle direct executes against the session as well as using table classes.
There are a few extra caveats:
1) If we’re in an open transaction when the client is disconnected we re-start the transaction after the reconnect and throw it into a failed state using SELECT 1/0. This avoids committing work outside a transaction.
2) Your application needs to handle failed executions. This has been an ongoing enhancement for us over the last 10+ years as we’ve migrated from local servers to remote servers. As you add integrity checks to your database this also becomes necessary to handle an insert/update/delete that is rejected due to a foreign key constraint violation or a trigger refusing the action for business logic reasons.
3) When starting a transaction we’re throw an exploratory “SELECT 1;” just to ensure our connection is alive
4) With a set timeout on the server we can also track the last execute to the database and, if we exceed that timeout, proactively reconnect before running our next execute.
5) Make sure to re-establish your server-side environment when reconnecting. This might include things like setting the time zone for your client or re-setting server-side variables.
> On Aug 30, 2017, at 05:44, Phil (OmnisList) <firstname.lastname@example.org> wrote:
> Hi Mats,
> For me, its a case of catching the SQL error, checking what the error is, and if its a disconnect in some way, then logout, and login again.
> Keep alive doesn’t always work!
> So, I use table objects for SQL, I use a superclass, within which I have a $sqlerror method which is called whenever there is a SQL error…
> Set reference MyRef to $cinst.$statementobject
> If (MyRef.$nativeerrorcode=-2147467259)|(MyRef.$nativeerrorcode=2013)|(MyRef.$nativeerrorcode=2006)|(MyRef.$nativeerrorcode=-1) ;; Communication link failure
> Do method ReLogin
> Quit method kFalse
> End If
> Pools setup during login, and kept alive, are for sure a quicker way to connect to the data, if you don’t lose the SQL connection, if you lose the SQL connection then the pool needs reconnecting anyway, and by definition you will make a new connection for each pool entry…
> You can start with a pool of 1, and just resize as required…
> Again in my superclass, when I want to use a pool entry, I check to see if one is free:
> If $root.$sessionpools.[lcPoolName].$poolsize=$root.$sessionpools.[lcPoolName].$inuse
> Calculate $root.$sessionpools.[lcPoolName].$poolsize as $root.$sessionpools.[lcPoolName].$poolsize+1
> End If
> Just as long as your pool sql object is capable of doing its own $init to connect properly.
> Hope something helps here…
> Phil Potter
> Based in Chester in the UK.
> On 30/08/2017 06:41, ADJob wrote:
>> But we do not have an API yet, so how do you handle reconnection then? Our attempt to do this results in hanging for a period while negotiating for connection.
>> Our simple and dirty solution is to activate keepalive to avoid reconnection delays, but this means also a bunch of idle connections.
>> I have read that pooling skips the ”negotiation” part of the connection. Just picking a free session.
>> Any reconnection code that you are willing to share?
> Manage your list subscriptions at lists.omnis-dev.com
Manage your list subscriptions at lists.omnis-dev.com