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/-/kIVYZwe9gRMJ.
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.
