On Thursday, September 19, 2013 5:37:59 AM UTC-7, Mikael Riska wrote:
>
> Hi!
>
> I am having a problem with connection pooling and old dead connections not
> being removed as expected.
>
> I am setting up and caching my connections like this:
>
>> module Test
>> class M3DB
>> def self.connection(connection_string)
>> $db_connection_cache ||= {}
>> return $db_connection_cache[connection_string] if
>> $db_connection_cache.has_key?(connection_string)
>> db = Sequel.connect(connection_string, :max_connections => 6)
>> db.identifier_output_method = nil
>> db.identifier_input_method = nil
>> $db_connection_cache[connection_string] = db
>> end
>> end
>> end
>
>
> Then using it for example like this:
>
>> Test::M3DB.connection(connectionstring)[:TABLE]
>> .where(:FIELD => value)
>> .select(:PROP1, :PROP2)
>
>
> What I am seeing then is that if the connection gets broken the old
> connections are not being removed from the pool and every time I try to
> issue a new query I get the following exception:
>
>> --------
>> ## Error:
>> java.sql.SQLException: The connection does not exist.
>> ## Backtrace:
>> com.ibm.as400.access.JDError.throwSQLException(JDError.java:415)
>>
>> com.ibm.as400.access.AS400JDBCConnection.checkOpen(AS400JDBCConnection.java:460)
>>
>> com.ibm.as400.access.AS400JDBCConnection.sendAndReceive(AS400JDBCConnection.java:2941)
>> com.ibm.as400.access.AS400JDBCStatement.close(AS400JDBCStatement.java:474)
>> sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>> --------
>
>
>
> I have also tried using connection_validation, but I did not see any
> difference to how the dead connections where handled. I was getting the
> same Exception then as well.
>
>> module testingTest
>> class M3DB
>> def self.connection(connection_string)
>> $db_connection_cache ||= {}
>> return $db_connection_cache[connection_string] if
>> $db_connection_cache.has_key?(connection_string)
>> db = Sequel.connect(connection_string, :max_connections => 6)
>> db.extension(:connection_validator)
>> db.identifier_output_method = nil
>> db.identifier_input_method = nil
>> $db_connection_cache[connection_string] = db
>> end
>> end
>> end
>
>
> If I use db.pool.connection_validation_timeout = -1 I can see the dead
> connections being cleared, but the performance penalty is quite high so I
> would like to avoid that if possible.
>
>
> How do you suggest we handle this scenario?
>
>
> The database in use is a DB2, so we are using jdbc:as400
>
Well, Sequel would have to start recognizing that specific error in
jdbc:as400 as a disconnect error. Looks like the JDBC as400 driver doesn't
act like other JDBC drivers and set an SQL State starting with 08 for the
disconnect case. Can you try the following patch:
diff --git a/lib/sequel/adapters/jdbc/as400.rb
b/lib/sequel/adapters/jdbc/as400.rb
index 2d4ec62..84bfe54 100644
--- a/lib/sequel/adapters/jdbc/as400.rb
+++ b/lib/sequel/adapters/jdbc/as400.rb
@@ -31,6 +31,10 @@ module Sequel
private
+ def disconnect_error?(exception, opts)
+ super || exception.message =~ /\AThe connection does not exist\./
+ end
+
# Use JDBC connection's setAutoCommit to false to start
transactions
def begin_transaction(conn, opts=OPTS)
set_transaction_isolation(conn, opts)
With this patch, if the query causes that exception, Sequel should
translate it into a DatabaseDisconnectError and remove the connection from
the pool, so the next query should get a new query (assuming it works, I
didn't test it).
Assuming these connections are being dropped due to idling, the better
solution is to make sure the connections are used more often than the idle
timeout. If there is some other reason the connections are being dropped,
you should probably address the root cause (connection dropping) rather
than adding workarounds at the Sequel level.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
