Thanks Jeremy for the suggestions.
I am no longer using jndi pooling and things are working pretty much fine
for me except for in some special conditions. During the off hours, I am
getting
W, [2012-07-27T23:09:42.424000 #9252] WARN -- : 134341078229443 ==
NativeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure
The last packet successfully received from the server was 106,383
milliseconds ago. The last packet sent successfully to the server was 26
milliseconds ago.
This I understand is occurring due to the wait_timeout that's set to 10
seconds.
I am using DB.disconnect at the end of every thread(in after "/*" do block)
and I thought this would close the connection in use by that thread. This
claerly is not happening.
One solution the I can think of is putting a dummy query at the start of
every thread(in the before "/*" block). But is there a way to get this
handled by Sequel itself.
Thanks
-Azitabh
On Friday, 27 July 2012 23:02:10 UTC+5:30, Jeremy Evans wrote:
>
> On Thursday, July 26, 2012 10:03:55 PM UTC-7, azi wrote:
>>
>> 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.
>>
>
> Sequel's connection pool should already take care of that.
>
>
>>
>> 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.
>>
>
> The usual solution to this is to have a periodic cron job that uses the
> connection more often than the wait_timeout (or increasing the
> wait_timeout).
>
>
>>
>> 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`
>>
>
> That's probably because you have an insane wait_timeout of 10. The Sequel
> default is 2147483, BTW. I recommend increasing the wait_timeout to a
> reasonable number.
>
>
>> 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.
>>
>
> Pure luck would be my guess.
>
> 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/-/ykfhHYudWpMJ.
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.
