Manfred P. - 2015-05-17

Hi,

here are some hints that might be a solution to your problem. Please let me know, if this does not solve your problem.

One MySQL specific solution is to add the autoReconnect property to the JDBC connection URL, as shown in the following example: 

  jdbc:mysql://localhost:3306/docmentadb?autoReconnect=true

However, it is discouraged to use the autoReconnect option (see http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html). Furthermore, this might still give an initial error message when trying to connect to database after several hours of idle time.

Therefore the preferred solution is to use a connection pool and configure the connection pool to deal with timeout of connections. If you're using Tomcat, then you could configure a datasource that uses the connection pool implementation provided by Tomcat. See http://www.docmenta.org/onlinedocs/content/product_configuration_database.html for an example.

If, for some reason, configuring a datasource and using a java:/comp/env/ connection URL is not possible, then you can try to configure Hibernate to use a third party connection pool, for example C3P0. The connection URL is unchanged (e.g. jdbc:mysql://localhost:3306/docmentadb). Following a short description how to set up C3P0 in Docmenta.

1)
Download the C3P0 connection pool from http://sourceforge.net/projects/c3p0/
2)
Extract the zip file and place all jar libraries to the lib folder of your Tomcat installation.
3)
Configure Hibernate to use the C3P0 connection pool by setting Hibernate properties in the file WEB-INF/db_config.properties of the Docmenta web application. For example set following properties:

hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=150000
hibernate.c3p0.idle_test_period=300
hibernate.c3p0.max_statements=200
c3p0.minPoolSize=1
c3p0.maxPoolSize=20
c3p0.checkoutTimeout=150000
c3p0.idleConnectionTestPeriod=300
c3p0.maxIdleTime=1800
c3p0.maxConnectionAge=250000
c3p0.testConnectionOnCheckout=false
c3p0.testConnectionOnCheckin=false
c3p0.acquireRetryAttempts=2
c3p0.acquireRetryDelay=500
c3p0.breakAfterAcquireFailure=false

For more information on these properties see the Hibernate documentation and the C3P0 documentation on the project homepage ( http://www.mchange.com/projects/c3p0/ ).

Some notes on the properties:

The properties hibernate.c3p0.timeout and c3p0.checkoutTimeout define the maximum time a connection can be "checked-out". It should be sufficient to set only one of both properties, however, I did not find a clear description in the Hibernate documentation. Therefore I set both properties, just to be sure. In Docmenta you have to set the checkout timeout higher than the maximum time one activity could take in the worst case. For example, if you copy a large product which has several translations, a lot of versions, a lot of exported publications, and so on, then the copy activity might take several hours, or even more than a day if applicaton server and database server are on different machines and network performance is slow. In the example above the checkout timeout is set to 150000 seconds (~40 hours), which should be sufficient even for worst cases.

The idleConnectionTestPeriod is set to 300 (seconds). This means every connection is checked every 5 minutes if it's is still alive. For example, on database restart, the connections in the pool are still existing but stale. This might cause an error, in case Docmenta tries to connect to the database and gets a stale connection. However, using this setting the stale connections are detected and removed from the pool within 5 minutes. You could decrease this value to e.g. 60 seconds to remove stale connections every minute. Note however, that testing connections periodically decreases the performance of the application.

The property maxIdleTime defines how long a connection can remain unused in the pool before it is removed from the pool. In the example above the property is set to 1800 seconds (30 minutes). For example, if know that your database will timeout idle connections after e.g. 8 hours, then you could increase this value to let's say 7 hours, to be sure that connections are removed from the pool before they timeout.

The acquireRetryAttemps property defines how often the pool shall retry to get a connection, in case connecting to the database fails (e.g. if the database server is down). The property acquireRetryDelay defines the milliseconds to wait between two connection attempts. For the Docmenta application you should set the acquireRetryAttemps to a low value (between 1 and 5), to avoid blocking the application for a long time before an error message appears. Also the user interface might be blocked if you set this value to a high number (or to 0, which means retry indefinitely). For example, if you set acquireRetryAttemps to 5 and acquireRetryDelay to 1000, then, in case the database server is down, the application is blocked for 5 seconds before an error message appears.

Upgrade Note:
If you upgrade Docmenta to a newer version, then the settings in the WEB-INF/db_config.properties will be lost. Therefore you might consider creating a Docmenta plug-in as described in

http://www.docmenta.org/onlinedocs/content/create_plugin.html

For example create following plug-in structure to install the C3P0 libraries and the db_config.properties:

  plugin.properties
  locale.properties
  web/
     WEB-INF/
        db_config.properties
        lib/
           c3p0-0.9.5.jar
           mchange-commons-java-0.2.9.jar

Edit:
There was a typo in my initial post. The letter "t" was missing in acquireRetryAttemps. Please change "acquireRetryAttemps" to "acquireRetryAttempts". Otherwise the application will be blocked indefinitely if the database server is not available.

 

Last edit: Manfred P. 2015-05-19