Database Connection Settings (2.5)

The sakai.properties configuration file defines database technology and connection information. Appropriate sample settings for HSQLDB, Oracle and MySQL are listed below.

By default, all Sakai distributions are configured to use an in-memory version of HSQLDB. HSQLDB is provided for testing/demo purposes only and should not be run in production.

Whatever database you choose to use, you will need to modify at a minimum the following connection settings:

url@javax.sql.BaseDataSource
username@javax.sql.BaseDataSource
password@javax.sql.BaseDataSource

Set the Database Username and Password

Start by setting your database username and password:

# DATABASE CONFIGURATION - make sure to modify details to match your particular setup

# The username and password.  The defaults are for the out-of-the-box HSQLDB.  Change to match your setup.
username@javax.sql.BaseDataSource=yourDbUserName
password@javax.sql.BaseDataSource=yourDbPassword

Configure Database Settings

HSQLDB Sample Configuration

HSQLDB is Sakai's default database. Remember to comment out its settings should you choose to run Sakai utilizing either MySQL or Oracle.

## HSQLDB settings - on by default
vendor@org.sakaiproject.db.api.SqlService=hsqldb
driverClassName@javax.sql.BaseDataSource=org.hsqldb.jdbcDriver
hibernate.dialect=org.hibernate.dialect.HSQLDialect
validationQuery@javax.sql.BaseDataSource=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
# two hsqldb storage options: first for in-memory (no persistence between runs), second for disk based
#url@javax.sql.BaseDataSource=jdbc:hsqldb:mem:sakai
url@javax.sql.BaseDataSource=jdbc:hsqldb:file:${sakai.home}db/sakai.db

MySQL Sample Configuration

Locate the MySQL configuration block, uncomment the settings and save your changes. Make sure you modify the data source, username and password settings to match your local environment. Do not forget to comment out the HSQLDB and Oracle settings.

# MySQL settings - make sure to alter as appropriate
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
url@javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

# To get accurate mysql query throughput statistics (for example for graphing) from the mysql command
# show status like 'Com_select'
# this alternate validation query should be used so as not to increment the query counter unnecessarily
# when validating the connection:
#validationQuery@javax.sql.BaseDataSource=show variables like 'version'

Oracle Sample Configuration

Locate the Oracle configuration block, uncomment the settings and save your changes. Make sure you modify the data source, username and password settings to match your local environment. Do not forget to comment out the HSQLDB and MySQL settings.

## Oracle settings - make sure to alter as appropriate
vendor@org.sakaiproject.db.api.SqlService=oracle
driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver
hibernate.dialect=org.hibernate.dialect.Oracle9Dialect
url@javax.sql.BaseDataSource=jdbc:oracle:thin:@your.oracle.dns:1521:SID
validationQuery@javax.sql.BaseDataSource=select 1 from DUAL
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

Oracle users may experience performance issues with certain of the SQL settings that work for HSQL and MySQL. Oracle users can reduce Db load by uncommenting the following settings:

# For improved Oracle performance (from the University of Michigan)
validationQuery@javax.sql.BaseDataSource=
defaultTransactionIsolationString@javax.sql.BaseDataSource=
testOnBorrow@javax.sql.BaseDataSource=false

The auto.ddl Setting

On startup, Sakai will generate all database objects (tables, keys, constraints, etc.) automatically, obviating the need to run DDL scripts manually.

# establish auto.ddl - on by default
auto.ddl=true
#auto.ddl=false

Once the database schema is created you should set auto.ddl=false.