Database Configuration

From AlfrescoWiki

Contents 1 Database Configuration 1.1 Introduction 1.2 Overriding the Database Connection Properties 1.2.1 Basic Properties 1.2.2 Advanced Properties 1.3 MySQL Notes 1.4 Oracle Example 1.5 PostgreSQL Example 1.6 DB2 1.7 MS-SQL Databases 1.8 Other Databases 1.9 Appendix

[edit] Database Configuration

[edit] Introduction

This page assumes knowledge of how to extend the repository configuration and override properties: Repository Configuration. This page has been updated for the latest Alfresco releases. For Alfresco versions prior to v3.2, please refer to Database Configuration Prior To Version 3.2.

[edit] Overriding the Database Connection Properties

Follow the instructions in Repository Configuration to obtain alfresco-global.properties.sample and copy it to <extensionRoot>/alfresco-global.properties.

Modify the properties to enable the configuration appropriate to the database installation that the server will be run on.

[edit] Basic Properties

The following properties must be set for every installation

db.driver

The fully-qualified name of the JDBC driver class

db.url

The JDBC URL to the database connection

db.username

The name used to authenticate with the database

db.password

The password used to authenticate with the database

[edit] Advanced Properties

The following properties are configured with sensible defaults, but for advanced purposes you may want to set your own values. Note that not all of these properties are available in the 3.2 Community edition. The default connection pool bean is called defaultDataSource and is declared in the core-services-context.xml file. If necessary, for advanced purposes, this bean may be overriden with a custom definition. See Overriding Spring Configuration.

db.pool.statements.enable

A Boolean that when true indicates that all precompiled prepared statements used on a connection will be kept open and cached for reuse. This is enabled by default.

db.pool.statements.max

The maximum number of prepared statements to cache for each connection. The Alfresco default is 40. Note that Oracle does not allow more that 50 by default.

db.txn.isolation

The JDBC code number for the transaction isolation level, corresponding to those in the java.sql.Connection class. The default value of -1 indicates that the database's default transaction isolation level should be used and this is the most common setting. For the Microsoft SQL Server JDBC driver, the special value of 4096 should be used to enable snapshot isolation.

db.pool.initial

The number of connections opened when the pool is initialized

db.pool.max

The maximum number of connections in the pool

db.pool.idle

The maximum number of connections that are not in use kept open

db.pool.min

The minimum number of connections in the pool

db.pool.wait.max

The maximum number of milliseconds to wait for a connection to be returned before throwing an exception (when connections are unavailable) or -1 to wait indefinitely

db.pool.validate.query

The SQL query that will be used to validate connections are still alive. Useful if your database closes long-running connections after periods of inactivity.

Oracle Example

SELECT 1 FROM DUAL

MySQL Example

SELECT 1

db.pool.validate.borrow

A Boolean that when true indicates that connections will be validated before being borrowed from the pool. The default is true.

db.pool.validate.return

A Boolean that when true indicates that connections will be validated before being returned to the pool. The default is false.

db.pool.evict.interval

The number of milliseconds to sleep between eviction runs, when greater than zero. If zero or less, idle objects will not be evicted in the background, as is the default.

db.pool.evict.idle.min

The minimum number of milliseconds that a connection may sit idle before it is eligible for eviction

db.pool.evict.validate

A Boolean that when true indicates that idle connections will be validated during eviction runs

db.pool.abandoned.detect

A Boolean that when true indicates that a connection is considered abandoned and eligible for removal if it has been idle longer than the db.pool.abandoned.time. The default is false.

db.pool.abandoned.time

The time in seconds before an abandoned connection can be removed

[edit] MySQL Notes

These instructions may be incomplete. However they do correct a problem that an older driver causes.

Be sure that in your properties file (e.g., alfresco-global.properties) that if you're using MySQL, you have a line such as:

db.driver=com.mysql.jdbc.Driver

You will need mysql-connector-java-5.1.7-bin.jar or perhaps a more modern equivalent on your classpath as well.

Older installations of Alfresco used:

db.driver=org.gjt.mm.mysql.Driver

which as of 3.2r2 does not seem to work.

[edit] Oracle Example

To use Oracle please follow the steps below:

• Create a new 'alfresco' user and schema in oracle.
• Ensure that the alfresco user has the required privileges to create and modify tables. This can be removed once the server has started, but may be required during upgrades.
• Override following properties in your alfresco-global.properties:

db.driver=oracle.jdbc.OracleDriver
db.name=alfresco
db.url=jdbc:oracle:thin:@<machinename>:1521:<database sid>
db.username=alfresco
db.password=alfresco
db.pool.validate.query=SELECT 1 FROM DUAL

• If you have multiple Alfresco instances installed on an Oracle server, you will need to force the database metadata queries to target the schema that each database user is using. Put the following in alfresco-global.properties:

hibernate.default_schema=ALFRESCO

• Copy the Oracle JDBC driver JAR (ojdbc14.jar) into \tomcat\lib (on Tomcat 6) or \jboss\server\default\lib (JBoss).

• You can now startup the Tomcat or Jboss server

• Note: if you get JDBC errors ensure the location for the Oracle JDBC drivers are on the system path, or add them to the relevant lib directory of the app server - the Oracle JDBC drivers can be found in the <orainst>/ora<ver>/jdbc/lib directory (e.g. c:\oracle\ora92\jdbc\lib)

• Note: for performance reasons it is recommended that you use the 10g JDBC drivers, even if your Oracle server is 9i.

[edit] PostgreSQL Example

To use PostgreSQL please follow the steps below.

• Copy the appropriate PostgreSQL driver JAR to /tomcat/lib (TomCat) or /jboss/server/default/lib (JBoss).

• Create a new 'alfresco' user and database. Set this user's password to 'alfresco'.
• Ensure that the alfresco user has the required privileges to create and modify tables.

• Verify/modify the Alfresco data location in the alfresco-global.properties file.

• Override following properties in alfresco-global.properties:

db.driver=org.postgresql.Driver
db.name=alfresco
db.url=jdbc:postgresql://localhost/<database name>
db.username=alfresco
db.password=alfresco
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.query.substitutions=true TRUE, false FALSE

• Ensure that the postgresql.conf file (pg_hba.conf for postgres 8.1.3) (refer to PostgreSQL documentation for more info on this file) contains:

host    all    all    127.0.0.1/32    password

... to allow password-authenticated connections via TCP/IP.

• Do not forget to change the ownership of the directory tree specified in custom-data-location.properties to be owned by the user running the alfresco server.

• Download and install the postgresql amp file (eg, postgresql-3.2r.amp for Alfresco 3.2r2). You will use the Module Management Tool to do that.

• You can now startup the Tomcat or Jboss server

[edit] DB2

There is no official support for DB2.

• Default access to DB2 from Alfresco will use the cursor stability isolation level. This will produce concurrency issues arising from read locks. The following registry variables need to be set to work round this. They do not resolve all issues. See [[1]]
  • db2set DB2_EVALUNCOMMITTED=ON (avaiable from DB2 v8.1.4)
  • db2Set DB2_SKIPDELETED=ON (avaiable from DB2 v8.1.4)
  • db2set DB2_SKIPINSERTED=ON (avaiable from DB2 v8.2.2)

• Create a DB2 database with a larger page size of 32 K and using UTF8. If you do not do this you will see error SQL0286N (sqlCode -286, sqlstate 42727) as the schema is created for tables that do not fit the page size.

• Set up the alfresco user and associated schema. Note DB2 only integrates with the OS security. You can not add a DB user with a password as you can say in oracle.

• The default hibernate DB2 driver is fine
  • hibernate.dialect=org.hibernate.dialect.DB2Dialect

• Known issues
  • DB2 is case sensitive (and a pain to do otherwise).
  • AVM support requires a reverse index for AVM_HISTORY_LINKS (descendent, ancestor)
  • Statistics must be updated after this index is added (after bootstrap is fine)
  • Requires DB2 v8.2.2 or later

• DB2 Configuration issues
• If you see DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH203 0X5359534C564C3031, DRIVER=3.50.152
  • On Windows
  • start the db2 command line window
  • cd ../bnd
  • db2
  • connect to dbname
  • bind @db2cli.lst blocking all grant public clipkg 10

• • It is possible you could get away with 4 above (the default is 3)
  • check the package is there
  • select pkgname from syscat.packages where pkgname like 'SYSLH20%'

[edit] MS-SQL Databases

• create an alfresco database and corresponding user, with sufficient rights on the database (to create tables etc).

• Issue the following command to enable snapshot transaction isolation mode.

ALTER DATABASE alfresco SET ALLOW_SNAPSHOT_ISOLATION ON;

• Ensure TCP connectivity is enabled on fixed port 1433

• Copy the Microsoft SQL Server 2005 JDBC driver to $TOMCAT_HOME/lib

• Edit alfresco-global.properties and add/update the following entries:

db.username=alfresco
db.password=alfresco
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.url=jdbc:sqlserver://localhost:1433;databaseName=alfresco
db.txn.isolation=4096
hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect

• Start the application server to verify your configuration changes.

[edit] Other Databases

Hibernate supports a range of database dialects: Hibernate Dialects

Alfresco supports Microsoft SQL Server and Sybase but requires custom dialects to be used. The configuration is the same. Make sure the database connector is on the classpath, set the hibernate dialect in the config, set the properties for the database connection in the config, make sure the appropriate user is defined on the database and that they can login, that the holder db/tablespace/... where this user can create tables exists, and that they have rights to create tables etc.

[edit] Appendix

• Sample Diagram of Core ADM Tables

---

Back to Server Configuration