Documentation updated and reviewed for Confluence 3.5.
Created a separate issue for tackling in Confluence 4.0: CONF-22138

Thanks Sarah, the change looks good.

Thanks Sarah!

Yet another great update from you

I have updated the following page:
http://confluence.atlassian.com/display/DOC/Configuring+a+MySQL+Datasource+in+Apache+Tomcat
See diff: http://confluence.atlassian.com/pages/diffpages.action?pageId=1867&originalId=235670132

I have also searched the DOC and CONFKB spaces for other pages that may need updating, and not found any.

Stefan, please would you review the change?

DataSource

validationQuery

Setting the validationQuery attribute to valid query for MySQL (e.g. "SELECT 1") prevents connection problems when using a data source in Tomcat (version tested is Tomcat 6.0.26 as used in Confluence 3.4 standalone) which is using DBCP.

autoReconnect is not required for this configuration.

                    <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
                        username="USER"
                        password="PASSWORD"
                        driverClassName="com.mysql.jdbc.Driver"
                        url="jdbc:mysql://localhost:3306/confluence?useUnicode=true&amp;characterEncoding=utf8"
                        maxActive="15"
                        maxIdle="7"
                        validationQuery="Select 1" />

Note: Setting the validationQuery option on the database connection pool will have a performance impact as DBCP will use this query to validate connections before returning them to the caller.

Using autoReconnect without using the validationQuery does not prevent {{com.mysql.jdbc.exceptions.jdbc4.CommunicationsException}}s!

We should remove autoReconnect from our docs and setup instructions.

I've checked the linked JRA-15731 issue and the MySQL and DBCP/C3P0 docs. I've done local testing and for the direct JDBC connection (and most likely the DataSource as well) I think we can get rid of the autoReconnect param.

Environment

Baseline (Direct JDBC connection)

In order to reproduce the Communications link failure exceptions I changed the C3P0 pool settings, MySQL server settings and removed the autoReconnect parameter from the JDBC connection URL.

MySQL server settings

(/etc/my.cnf), set the wait_timeout (i.e. the number of seconds the server waits for activity on a noninteractive connection before closing it) to 30 seconds:

• wait_timeout from 28800 -> 30 (seconds)

C3P0 changes

• Removed the hibernate.c3p0.idle_test_period setting
• Changed hibernate.c3p0.timeout -> 60

JDBC Connection URL

Removed the autoReconnect=true param.

jdbc:mysql://localhost/confluence?sessionVariables=storage_engine%3DInnoDB<

*Confluence will log lots of Communications link failure errors.

Does autoReconnect actually help?

It doesn't! Running Confluence for a short while still logs lots of errors.

Does removing autoReconnect cause any problems?

With the default hibernate.c3p0.timeout settings (hibernate.c3p0.timeout < wait_timeout) and hibernate.c3p0.idle_test_period set autoReconnect doesn't seem to be necessary.

I haven't found any (which doesn't necessarily mean there aren't any) problems so far.

I'll check the DataSource approach and check this on our CI builds as well.