http://qs321.pair.com?node_id=11112334


in reply to RESOLVED - Keep a Database Connection Active and Reuse it Between Script Executions?

The failures are a greater concern aren't they? If the database fails to accept a connection, that's a visible failure. Is it also failing to take input/output on an existing connection or dropping connections in a less visible manner? What could the underlying issue be; network, server load, ...there has to be something.

For keeping a connection opened, I think your scripts would need to be running within a persistent process. That may require a complete change of how the scripts are written. You wouldn't even want them to be forked subprocesses (whatever Windows actually does for forks) because then children are sharing a connection, and that's "bad".

I'd probably try to use a resilient connector such as DBIx::Connector to manage the connection. But at least, could you retry on connection failure? I like DBIx::Connector because it handles reconnecting when a connection falls over. It should be able to handle a failure to connect in the first place too, but if not you could retry on failure after a very brief delay.


Dave

  • Comment on Re: Keep a Database Connection Active and Reuse it Between Script Executions?

Replies are listed 'Best First'.
Re^2: Keep a Database Connection Active and Reuse it Between Script Executions?
by perldigious (Priest) on Feb 03, 2020 at 21:23 UTC

    Thanks, davido. This sent me down what seems to be a working path. I didn't go so far as to try DBIx::Connector yet, but you got me poking around and it seems just adding the InactiveDestroy => 1 attribute to my connection string along with my other changes has remedied the immediate issue.

    This is something I would expect to be an issue for me during code development work, where I may be running a script repeatedly in a very short order of time for debugging purposes and testing different things, which is what I was doing and the repeated connection issues were slowly driving me insane as they slowed me down.

    I do tend to agree that the failures are possibly a greater concern, but I know very little about databases; I do this stuff on the side to make my regular job more efficient. I have put in an issue ticket with my Oracle DBA team to contact me and look at the issue with me, but they can be a bit slow to get around to minor issue tickets that only impact 1 person... also, sometimes they can end up hurting more than helping if they decide they don't like what someone is doing to their database. I try to sympathize, and I'm happy to play by their rules as long as they help me... I just want easy access to the data I need to do certain job tasks of mine more efficiently.

    Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
[reply]
[d/l]

      An advantage of DBIx::Connector is that if a handle falls over (the connection drops) the connector will re-establish a connection automatically, so you don't have to solve that problem yourself. Eventually you're going to solve this problem one way or the other. DBIx::Connector allows you to not have to solve it with code that you will then have to maintain yourself.

      But it's not right for every use case. Sometimes you really may have a good reason for solving it with your own code.


      Dave

[reply]

In Section Seekers of Perl Wisdom