Thanks Jeremy for the reply.
The reason I started using jndi was to avoid the overhead of establishing
the connection to mysql again and again.
e.g.: For a test application like this
get "/test123/:pool" do
begin
if params[:pool].eql? 'sequel'
a = DB_SEQUEL[:schema_info].all[0][:version]
elsif params[:pool].eql? 'jndi'
a = DB_JNDI[:schema_info].all[0][:version]
end
DB_SEQUEL.disconnect
DB_JNDI.disconnect
return 1.to_json
rescue Exception => e
return 2.to_json
end
end
and I have the connection strings defined in my app.rb as follows:
DB_SEQUEL =
Sequel.connect($config["db_jruby"]["adapter"]+'://'+$config["db_jruby"]["host"]+'/'+$config["db_jruby"]["database"]
+ '?user=' +
$config["db_jruby"]["username"]+'&password='+$config["db_jruby"]["password"]+'&autoReconnect=true'
,:logger => $db_log)
DB_JNDI = Sequel.connect("jdbc:jndi:java:comp/env/test", :logger =>
$db_log, :max_connections => 10)
If I hit http://localhost:8080/testapp/test123/sequel twice, my mysql log
shows:
120727 10:11:28 102 Connect test_user@localhost on testdb
102 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR
Variable_name = 'net_write_timeout' OR Variable_name =
'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name =
'character_set_client' OR Variable_name = 'character_set_connection' OR
Variable_name = 'character_set' OR Variable_name = 'character_set_server'
OR Variable_name = 'tx_isolation' OR Variable_name =
'transaction_isolation' OR Variable_name = 'character_set_results' OR
Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name
= 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR
Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length'
OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR
Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
102 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
102 Query SHOW COLLATION
102 Query SET NAMES latin1
102 Query SET character_set_results = NULL
102 Query SET autocommit=1
102 Query SET sql_mode='STRICT_TRANS_TABLES'
102 Query SET SQL_AUTO_IS_NULL=0
102 Query SELECT * FROM `schema_info`
102 Quit
120727 10:11:29 103 Connect test_user@localhost on testdb
103 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR
Variable_name = 'net_write_timeout' OR Variable_name =
'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name =
'character_set_client' OR Variable_name = 'character_set_connection' OR
Variable_name = 'character_set' OR Variable_name = 'character_set_server'
OR Variable_name = 'tx_isolation' OR Variable_name =
'transaction_isolation' OR Variable_name = 'character_set_results' OR
Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name
= 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR
Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length'
OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR
Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
103 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
103 Query SHOW COLLATION
103 Query SET NAMES latin1
103 Query SET character_set_results = NULL
103 Query SET autocommit=1
103 Query SET sql_mode='STRICT_TRANS_TABLES'
103 Query SET SQL_AUTO_IS_NULL=0
103 Query SELECT * FROM `schema_info`
103 Quit
While, if I hit http://localhost:8080/gvservice/test123/jndi twice, my
mysql log shows:
120727 10:16:13 105 Connect test_user@localhost on testdb
105 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR
Variable_name = 'net_write_timeout' OR Variable_name =
'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name =
'character_set_client' OR Variable_name = 'character_set_connection' OR
Variable_name = 'character_set' OR Variable_name = 'character_set_server'
OR Variable_name = 'tx_isolation' OR Variable_name =
'transaction_isolation' OR Variable_name = 'character_set_results' OR
Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name
= 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR
Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length'
OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR
Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
105 Query /* mysql-connector-java-5.1.13 ( Revision:
${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
105 Query SHOW COLLATION
105 Query SET NAMES latin1
105 Query SET character_set_results = NULL
105 Query SET autocommit=1
105 Query SET sql_mode='STRICT_TRANS_TABLES'
105 Query SELECT 1
105 Query SET SQL_AUTO_IS_NULL=0
105 Query SELECT * FROM `schema_info`
105 Query SELECT 1
105 Query SELECT 1
105 Query SET SQL_AUTO_IS_NULL=0
105 Query SELECT * FROM `schema_info`
105 Query SELECT 1
I understand this is happening because I am using DB_SEQUEL.disconnect in
my code. But I can't avoid doing that as sequel throws en error if an
attempt to query the database is made for the first time after an interval
greater than the wait_timeout of mysql server and I don't have much control
after that. Using "autoReconnect=true" doesn't seem to solve this problem.
e.g.: On hitting the url "http://localhost:8080/gvservice/test123/sequel";
thrice with DB_SEQUEL.disconnect removed from the code, my db.log shows:
I, [2012-07-27T10:39:20.738000 #12596] INFO -- : (0.000000s) SET
SQL_AUTO_IS_NULL=0
I, [2012-07-27T10:39:20.743000 #12596] INFO -- : (0.001000s) SELECT * FROM
`schema_info`
E, [2012-07-27T10:39:35.213000 #12596] ERROR -- : NativeException:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
link failure
The last packet successfully received from the server was 14,462
milliseconds ago. The last packet sent successfully to the server was 0
milliseconds ago.: SELECT * FROM `schema_info`
I, [2012-07-27T10:39:36.124000 #12596] INFO -- : (0.000000s) SET
SQL_AUTO_IS_NULL=0
I, [2012-07-27T10:39:36.126000 #12596] INFO -- : (0.001000s) SELECT * FROM
`schema_info`
I surely can bear this overhead to get the stability, but the thing that
still is worrying me is that why my sequel-jndi setup was working so
beautifully until a few days back.
On Thursday, 26 July 2012 20:50:25 UTC+5:30, Jeremy Evans wrote:
>
> On Thursday, July 26, 2012 2:42:59 AM UTC-7, azi wrote:
>>
>>
>> http://stackoverflow.com/questions/11666056/facing-nativeexception-java-sql-sqlexception-connection-com-mysql-jdbc-jdbc4co
>
>
> First, please don't post questions on both StackOverflow and here,
> especially when you are just posting a link to StackOverflow here. I
> prefer Sequel questions get asked on sequel-talk, but I also check
> StackOverflow daily.
>
> Personally, I have no experience with JNDI. I do know that using Sequel
> on top of another connection pool often doesn't work out too well, since
> Sequel does its own connection pooling. Can you try connecting with plain
> JDBC and see if you have different results?
>
> Also, note that calling DB.disconnect in a threaded application does not
> disconnect connections currently in use by other threads. If you are using
> the standard connection pool, connections currently in use by other threads
> do not get disconnected (at all). If you are using the sharded connection
> pool, then connections currently in use by other threads do not get
> disconnected until after they are returned to the pool.
>
> You can try the following additional options and see if they help:
>
> :servers=>{}, :connection_handling=>:queue
>
> :servers turns on the sharded pool, and :connection_handling makes stale
> connections less likely.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/cF0nHmQlq_MJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
