Page Comparison

Summary

...

Many users install the standard Confluence standalone distribution. This section will discuss configuration specific to this application server environment. The most recent versions of Confluence (2.9, 2.10, 3.0) use newer versions of the Tomcat application server where the following configuration examples apply:

...

Modify <install-dir>/conf/server.xml to add one or more datasource resource elements. server.xml is an example of adding a few datasources for various local and non-local databases.

The essential element is something like the following where the datasource name is myDS:

<Resource name="jdbc/myDS"
    auth="Container" 
    type="javax.sql.DataSource" 
    username="confluence" 
    password="confluence"
    maxActive="100"
    maxIdle="10"
    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://localhost:5432/mydatabase"
    validationQuery="Select 1"
/>

...
<Context path="" docBase="../confluence" debug="0" reloadable="false">
...
<Resource ... />
...
</Context>

...

Database examples

The sql macro markup would be: {sql:datasource=myDS| ... }

{csv:output=wiki}
Database, Information, JDBC driver example, Resource configuration
PostgreSQL, [PostgreSQL JDBC|http://jdbc.postgresql.org/], postgresql-8.2-507.jdbc3.jar, "{noformat}
<Resource name=""jdbc/

...

myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    username=""confluence""
    password=""confluence""
    maxActive=""100""
    maxIdle=""10""
    driverClassName=""org.postgresql.Driver""
    url=""jdbc:postgresql://localhost:5432/confluence""
    validationQuery=""Select 1""
/>{noformat}"
MySQL, [MySql connector|http://dev.mysql.com/downloads/connector/j/5.1.html], mysql-connector-java-3.1.14-bin.jar, "{noformat}
 <Resource name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""com.mysql.jdbc.Driver""
    url=""jdbc:mysql://localhost/mydatabase?autoReconnect=true""
    username=""username""
    password=""password""
    maxActive=""25""
    maxIdle=""5""
    maxWait=""10000""
/> {noformat}\\ See SQL-146 for info on how to configure multiple result sets."
Oracle, Thin Driver\\ [Oracke JDBC|http://wiki.oracle.com/page/JDBC], , "{noformat}
<Resource
    name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""oracle.jdbc.driver.OracleDriver""
    url=""jdbc:oracle:thin:@www.some_server.com:1521:some_schema""
    username=""username""
    password=""password""
    connectionProperties=""SetBigStringTryClob=true""
    maxActive=""25""
    maxIdle=""5""
    maxWait=""10000""
/> {noformat}"
Microsoft SQL Server, [jtds driver|http://jtds.sourceforge.net/], jtds-1.2.2.jar, "{noformat}
<Resource
    name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""net.sourceforge.jtds.jdbc.Driver""
    url=""jdbc:jtds:sqlserver://mysqlserver:1433/mydatabase""
    username=""username""
    password=""password""
    maxActive=""20""
    maxIdle=""10""
    maxWait=""-1""
/> {noformat}"
DB2 for IBM i, [JTOpen|http://www-03.ibm.com/systems/i/software/toolbox/troubleshooting.html], jt400.jar, "{noformat}
<Resource
    name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""com.ibm.as400.access.AS400JDBCDriver""
    url=""jdbc:as400://ibmi;prompt=false;translate binary=true; extended metadata=true""
    username=""username""
    password=""password""
    maxActive=""20""
    maxIdle=""10""
    maxWait=""-1""
/>{noformat}"
Derby, [Derby|http://db.apache.org/derby/], derbyclient.jar, "{noformat}
<Resource
    name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""org.apache.derby.jdbc.ClientDriver""
    url=""jdbc:derby://localhost:1527/dbname""
    username=""username""
    password=""password""
    maxActive=""20""
    maxIdle=""10""
/>{noformat}"
{csv}

Other databases

Support for other databases depends on the capabilities provided by the JDBC driver. The SQL expects certain JDBC features to be available. In some cases, parameter choices can affect which capabilities are needed. User testing and community support is about the only help that is available for less popular databases/JDBC drivers. If you have had success with other databases, please add information to this page.

...

1. Double check all the configuration values and that JDBC driver is installed - use the information above
2. Make sure you restarted Confluence after making configuration changes
3. Make sure your database is configured from remote access from the Confluence server with the user and password provided. If you have problems, connect using an independent SQL client from the server to verify access. Consult your database documentation for how to configure remote access and database permissions

Error - no JDBC driver

• Check your driver location. The driver must be installed in a location that is on the classpath of your application server. For standalone installations, that would be <install-directory>/lib.

  No Format
  sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driver class 'net.sourceforge.jtds.jdbc.Driver'

Error - database server not found

• Check your application server (server.xml) configuration and ensure the DNS host name is valid on the server running confluence

  No Format
  sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown server host name 'myserver'.)

Error - port

• Database may not be listening on the requested port - verify port configuration in server.xml and database

  No Format
  sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Network error IOException: Connection refused)

Error - user authentication

• Application server configuration (server.xml) has the wrong password

  No Format
  sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user "bad")

Error - validation query

• Validation query not supported or incorrect syntax - remove the validation query or ensure it is correct for your database

  No Format
  sql: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Syntax error: Encountered "<EOF>" at line 1, column 8.)

Error - user authority

• User does not have permission to the database or table - modify your database configuration

  No Format
  sql: org.postgresql.util.PSQLException: ERROR: permission denied for relation test01

Error - bad server.xml configuration

• The resource configuration is outsideof the confluence context - change your application server configuration (server.xml) to ensure the resource is within the confluence context

  No Format
  sql: Unexpected program error: com.atlassian.renderer.v2.macro.MacroException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'

Error - class not found

• After upgrading Confluence or your application server, this error can occur if your datasource configuration is out of date. Look for something like factory="org.apache.commons.dbcp.BasicDataSourceFactory" in your server.xml. Either remove it or rename it to factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory". Reference is SQL-68.

  No Format
  java.lang.ClassNotFoundException: org.apache.commons.dbcp.BasicDataSourceFactory

...

Add a validation query to your configuration. This helps the application server connection pool recover from database connection problems.

validationQuery="Select 1"

...