| Table of Contents | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Warning | ||
|---|---|---|
| ||
This page has been replaced by Configure Application Server-based Data Sourcesapplication server based data sources and Data source configuration - problem determination. Some of the information is out of date with respect to new releases of the add-onapp. However, we have kept the page as it has comments and answers that may still be helpful. |
Summary
| Section | |||||||||
|---|---|---|---|---|---|---|---|---|---|
|
...
Confluence standalone installation (Tomcat)
Many users install the standard Confluence standalone distribution. This section will discuss discusses 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 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 data source resource elements.User's Guide is is an example of adding a few datasources data sources for various local and non-local databases.
The essential element is something like the following, where the datasource data source name is myDS:
| Code Block | ||||
|---|---|---|---|---|
| ||||
<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"
/>
|
...
| Tip | ||
|---|---|---|
| ||
|
...
Database examples
The sql macro markup would belooks like: {sql:datasource=myDS| ... }
| Csv | ||
|---|---|---|
| ||
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}"
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}"
|
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 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 the location is <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 confluenceConfluence.
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
The database may not be listening on the requested port - verify the 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 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 data source 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 the 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 may 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 to recover from database connection problems.
| No Format |
|---|
validationQuery="Select 1" |
Other errors
For other errors 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 admininstrator tools (pgAdmin for instance), some general SQL client, or various Eclipse database plugins.