Page Comparison

Table of Contents

maxLevel	3
minLevel	3
maxlevel	3
minlevel	3
type	flat
separator	pipe

Warning

title	Archive

This page has been replaced by Data source configuration - application server and Data source configuration - problem determination Configure Application Server-based Data Sources. Some of the information is out of date with respect to new releases of the pluginadd-on. However, we have kept the page as it has comments and answers that may still be helpful.

...

Summary

Section

Column

The SQL uses datasources defined to the application server to gain access to the database. The datasource is refenced using the datasource parameter. The exact configuration of a datasource is application server and database specific. The configuration must be done by a Conflunece administrator and the Confluence application server must be restarted for changes to be active. The configuration must be done very carefully as any errors will result the datasource being unusable . Each database you need to access in Confluence needs to a have a datasource configuration.

These are the major elements of the configuration

Application server datasource configuration - consult your application server documentation on how this is done and problem determination mechanisms. Note that the configuration may be specific to the application server version. For example, older Tomcat versions have some different configurations.
JDBC driver specific for the database to be accessed - consult your database documentation and other sources for the best JDBC driver to use.
Installation of JDBC driver on the application server
Database access - consult your database documentation on how to configure your database for access

Various users have provided some examples that covers many standard situations. This page can be used to share that type of information as well as problem determination information. This is the best place to start especially if you are not an expert in this area.

Column

Info

title	Just Upgraded Confluence?

A common problem is errors after upgrading Confluence. Ensure that your upgrade procedures include updating your server.xml and copying all the JDBC drivers you use into the appropriate location. Also, check out this page for isolating these or application server version differences that cause problems. If you get errors immediately after an upgrade, this is the most likely cause.

...

Confluence standalone installation (Tomcat)

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 use newer versions of the Tomcat application server where the following configuration examples apply:

JDBC driver location

Put the driver jar in <install-dir>/lib - this is the directory that has other tomcat jars. If you have an older version of Confluence that does not have this directory, look for <install-dir>/common/lib instead.

Tomcat configuration

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.

...

Tip

title	Resource must be within the confluence context tag

No Format
... <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\\ [Oracle JDBC|http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-111060-084321.html], ojdbc6.jar, "{noformat}
<Resource
    name=""jdbc/myDS""
    auth=""Container""
    type=""javax.sql.DataSource""
    driverClassName=""oracle.jdbc.OracleDriver""
    url=""jdbc:oracle:thin:@www.some_server.com:1521:mysid""
    username=""username""
    password=""password""
    connectionProperties=""SetBigStringTryClob=true""
    maxActive=""25""
    maxIdle=""5""
    maxWait=""10000""
/> {noformat}"
Oracle Advanced, Thin Driver \\ [Oracle JDBC|http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-111060-084321.html], ojdbc6.jar, See [Tomcat 6.0 using Oracle Universal Connection Pool|http://www.oracle.com/technetwork/articles/oem/ucp-jdbc-tomcat-355431.html]
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}"

...

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}"

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.

...

WebLogic 9.1 Application Server

In the WebLogic console, under Services > JDBC, create a data source (see WebLogic's documentation on how to Configure JDBC data sources for more information).
Take note of the JNDI name you used to create the data source.
Point the SQL macro to that JNDI name (datasource parameter).

...

Problem determination

Here are some common errors and examples.

Double check all the configuration values and that JDBC driver is installed - use the information above
Make sure you restarted Confluence after making configuration changes
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 outside of 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

Error - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)

Make sure your database server is accepting connections from the Confluence server TCP address and user is authorized to connect.

Error - MyDS not found

The datasource names are case sensitive, make sure you use exactly the same name as what you have configured!

Non-standard JDBC drivers

If you are using a non-standard JDBC driver for accessing less common databases, there are potential problems with unsupported or non-standard behavior. Experiment on a test instance to ensure it works as expected. Here are some workarounds for problems you might encounter. If you continue to have problems, you will need to debug the code on your installation.

Hangs - SQL-86 is an example. Use multipleResults=false to disable multiple results sets.
Avoid using horizontal rowOrientation

Broken database connections - java.sql.SQLRecoverableException

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

No Format
validationQuery="Select 1"

Other errors

For other errors replated related to connectivity, google your symptoms and application server combination

SQL exceptions

Consult your database documentation as SQL has some database specific differences . It is best to validate your SQL outside of Confluence before putting it on a page. Use a database admin tools (pgAdmin for instance), some general SQL client, or various Eclipse database plugins.

Versions Compared

Old Version 23

New Version Current

Key

Summary

Confluence standalone installation (Tomcat)

JDBC driver location

Tomcat configuration

Database examples

Other databases

WebLogic 9.1 Application Server

Problem determination

Error - no JDBC driver

Error - database server not found

Error - port

Error - user authentication

Error - validation query

Error - user authority

Error - bad server.xml configuration

Error - class not found

Error - Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)

Error - MyDS not found

Non-standard JDBC drivers

Broken database connections - java.sql.SQLRecoverableException

Other errors

SQL exceptions