Sakai 23 Database Support
Irrespective of whether you utilize MySQL or Oracle, be sure you have configured your database to use the UTF-8 character set. Failure to do so will result in range of issues when attempting to use Unicode characters in Sakai. Consult your database documentation or a local DBA for instructions on how to set your database up properly.If you are uncertain as to how your database is currently configured, you can check with a query. Here is a sample query for checking an Oracle instance:
SQL> select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; VALUE -------------------------------------------------------------------------------- AL32UTF8
For MySQL/MariaDB, the command to see what encoding your database is currently set to is "show create database sakai", assuming, of course, that your database is named "sakai". e.g.:
mysql> show create database sakai; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | sakai | CREATE DATABASE `sakai` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
Converting a database from one character set to another is a non-trivial operation, particularly if it is a large production database. We recommend strongly that you verify this aspect of the database creation and configuration process before deploying Sakai.
1.0 Configure the database
Whatever database you choose to use you will need to modify at a minimum the following connection settings in sakai.properties:
url@javax.sql.BaseDataSourceusername@javax.sql.BaseDataSourcepassword@javax.sql.BaseDataSource
2.0 Set the database username and password
Set 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
3.0 Set the database connection
Locate the database 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 default settings.
MySQL Sample Configuration
vendor@org.sakaiproject.db.api.SqlService=mysql driverClassName@javax.sql.BaseDataSource=com.mysql.jdbc.Driver hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect url@javax.sql.BaseDataSource=jdbc:mysql://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8 validationQuery@javax.sql.BaseDataSource= defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
MariaDB Sample Configuration
vendor@org.sakaiproject.db.api.SqlService=mysql
driverClassName@javax.sql.BaseDataSource=org.mariadb.jdbc.Driver
hibernate.dialect=org.hibernate.dialect.MariaDB103Dialect
url@javax.sql.BaseDataSource=jdbc:mariadb://127.0.0.1:3306/sakai?useUnicode=true&characterEncoding=UTF-8
defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED
Oracle Sample Configuration
vendor@org.sakaiproject.db.api.SqlService=oracle driverClassName@javax.sql.BaseDataSource=oracle.jdbc.driver.OracleDriver #hibernate.dialect=org.hibernate.dialect.Oracle9iDialect hibernate.dialect=org.hibernate.dialect.Oracle10gDialect 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 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
4.0 Database table creation
On startup, Sakai will generate all database objects (tables, keys, constraints, etc.) automatically, obviating the need to run DDL scripts manually per the sakai.properties setting auto.ddl.auto.ddl=true
Once the database schema is created you should set this property to false.
5.0 Install MySQL 8 / MariaDB 10
Sakairequires transaction support. In the case of MySQL/MariaDB you must implement theInnoDBstorage engineto ensure proper transaction handling.
Users upgrading from on a Mac have reported name comparison problems on startup (for more info seehttp://collab.sakaiproject.org/pipermail/sakai-dev/2010-June/008066.html. This issue is solved by specifying the following property in a local my.cnf configuration file:
lower_case_table_names=1
You willnotneed to create Sakai database objects (tables, indices, etc) when setting up your database. Sakai generates its own database schema automatically during the Tomcat setup process via theautoDDLsetting insakai.properties.
Case sensitivity. In most varieties of UNIX, MySQL is case sensitive since the underlying operating system determines the case sensitivity of database and table names. This is not the case for MySQL running in Windows which is case insensitive. Previous MySQL conversion scripts contained a mixture of upper case and lower case statements which occasionally caused problems for upgraders; we opted for lower case for the syntax while leaving the table and field names upper case.
Install the binaries for the database you choose to use.
MySQL (8 is the recommended version)
MariaDB (10 is the recommended version)
Assign a password for the root account:
mysql -u root -pmysqlpassword
5.1 Configure my.cnf/my.ini configuration file
my.cnf. Settings are scoped and can be overridden by file location:Scope | Location | Override |
|---|---|---|
global |
| |
server-specific | binary install: | global |
user |
| global, server-specific |
Below is a minimalist MySQL/MariaDB configuration file for a development laptop /etc/my.cnf:
[mysqld] default-storage-engine=InnoDB lower_case_table_names=1
Restart in order for the new settings to take effect.
5.3 Create a "sakai" database and user account
Create the db by connecting to your db and running one of the following, depending on your db the default character set may be utf8mb3 or utf8mb4.
create database <db-name> default character set 'utf8';
This becomes important if you want your database to store high order range of utf8 which is usually emoji. If using utf8mb4 you will want to set the following property to false.
content.cleaner.filter.utf8=false
Next you will want to create a user that can be used to access the db
create user 'sakai'@'localhost' identified by 'password';
Give the user full access to the db
grant all on <db-name>.* to 'sakai'@'localhost';