0

I am integrating Hibernate 4 with BoneCP and wanted to understand what these properties mean:

<property name="idleConnectionTestPeriod">30</property>
<property name="idleMaxAge">240</property>
<property name="connectionTestStatement">select 1 from db.table limit 1</property>

My application is one which needs to maintain constant connection to the mysqldb 24x7

Does it mean that every 30 minutes the test statement will get executed? and that connection will become stale after 240 minutes ? How do I make my connections permanent because with this configuration occasionally I am getting 

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
jabaldonedo
  • 25,822
  • 8
  • 77
  • 77
Ravi Nankani
  • 539
  • 1
  • 7
  • 15

1 Answers1

4

As the author of the BoneCP wrote here, there are 2 ways to manage connections state - the way you use and just setting maxConnectionAgeInSeconds.

Important note - don't forget about MySQL configuration such as wait_timeout. It given in seconds and must be greater than maxConnectionAgeInSeconds. You can change this parameter in my.ini file in MySQL's directory.

For example, my current configuration:
wait_timeout=3600 (MySQL parameter);
maxConnectionAgeInSeconds=3000 (BoneCP parameter)

Also, I set interactive_timeout variable the same with wait_timeout. I'm not sure does it make sense but I saw somewhere MySQL takes min(interactive_timeout,wait_timeout). Actually, interactive_timeout is timeout for interactive clients - when you use MySQL command line you are interactive client. Anyway, it's better to make them equal - even this parameter doesn't make sense it's something like insurance.

P.S. Don't forget turn off mysql (cmd->net stop mysql) before setting variables. After setting new values check them in command line using show variables like '%timeout%';

  • Thanks for the MySql tip for setting the variables , I set wait_timeout and interactive_timeout to 3000 , and set the maxConnectionAgeInSeconds to 3000, it seems to solve the problem but occasionally I am getting Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. – Ravi Nankani Jun 11 '13 at 14:59
  • 1) it seems BoneCP returns connections with default autocommit=false. If you perform DML operations autocommit=true saves you from doing it manually. Also, even SELECT operations requires rollback() call - I can't explain it but it seems pool return "bad" connections with "remainders" of previous DML transactions. That's why I simply set autocommit=true for all connections given by pool. 2) Your conf is not safe. It's better to set maxConnectionAgeInSeconds considerably smaller than wait_timeout. Look my example configuration above. –  Jun 12 '13 at 03:13
  • Thanks buddy, I have set autocommit = true and not seeing the issue anymore, also the wait_timeout was set to 3600 , somehow I typed 3000 here – Ravi Nankani Jun 13 '13 at 02:39
  • 2
    Just would like to add that the autocommit is now defaulting to true as per spec as of 0.8.0-rc3 – wwadge Sep 12 '13 at 13:42
  • Hello Wallace! Is rc3 stable enough for using it in production? I'm currently using rc2. –  Sep 13 '13 at 05:06