Configuring MySQL and MariaDB

Note

MariaDB is a community-developed split of MySQL intended to remain free under the GNU GPL. As a split of a leading open-source software system, it is notable for being led by the original MySQL developers. It was forked due to concerns about Oracle’s acquisition.

MariaDB has supported database sequences since v10.3, but we recommend using v10.6 or newer right now. See MariaDB Server Releases.

Preliminaries

Caution

Perform all changes with the application stopped.

Check if your MySQL installation has the InnoDB engine enabled:

$ MySQL -h localhost -u root -p
> show engines;

Tip

It should show something like this:

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

If another default engine is in use, there are two options:

1. Use the com.openkm.db.dialect.MySQL5InnoDBDialect dialect and avoid changing the default MySQL Storage Engine.
2. Change the default engine.

Change default engine

Modify the MySQL configuration file named my.cnf

Then, under [mysqld], add

default-storage-engine = innodb

Caution

After changing the default in MySQL, the service must be restarted to take effect.

Change default charset

In Ubuntu 16.04, the default charset configured in MariaDB and MySQL is utf8mb4, but we recommend switching to utf8. To get the recommended charset, you have to modify these files:

/etc/mysql/mariadb.conf.d/50-server.cnf

character-set-server = utf8
collation-server = utf8_general_ci

/etc/mysql/mariadb.conf.d/50-client.cnf

default-character-set = utf8

/etc/mysql/mariadb.conf.d/50-MySQL-clients.cnf

default-character-set = utf8

Once modified, you need to restart the database server.

Database creation

Create a database and user

DROP DATABASE IF EXISTS okmdb;
CREATE DATABASE okmdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
GRANT ALL ON okmdb.* TO openkm@localhost WITH GRANT OPTION;

You can check the database engine with the following:

$ MySQL show -h localhost -u root -p --status okmdb;

More information at MySQL: Case Sensitivity in String Searches

Create a database in Persian

Set Persian collation and character set in the my.ini configuration file (requires restarting the service):

collation_server = utf8mb4_persian_ci
character_set_server = utf8mb4

Set Persian collation and character set in the current MySQL session (after restarting the service, the default values from my.ini will be used):

SET collation_server = 'utf8mb4_persian_ci';
SET character_set_server = 'utf8mb4';

Database:

CREATE DATABASE okmdb CHARACTER SET utf8mb4 COLLATE utf8mb4_persian_ci;

CREATE USER openkm@localhost IDENTIFIED BY 'k4M0j4lBi#ed';

GRANT ALL ON okmdb.* TO openkm@localhost WITH GRANT OPTION;

Configure your openkm.properties

Caution

This configuration property should be set before creating the database. Once the database has been initialized, do not modify it, because doing so can damage your installation.

If your OpenKM installation has already been configured with another database, you can’t switch to another database by simply changing this property.

Choose the right dialect based on your database engine version.

MySQL dialects:

• com.openkm.db.dialect.MySQL55Dialect
• com.openkm.db.dialect.MySQL57Dialect
• com.openkm.db.dialect.MySQL8Dialect

MariaDB dialects:

• com.openkm.db.dialect.MariaDBDialect
• com.openkm.db.dialect.MariaDB53Dialect
• com.openkm.db.dialect.MariaDB10Dialect
• com.openkm.db.dialect.MariaDB102Dialect
• com.openkm.db.dialect.MariaDB103Dialect
• com.openkm.db.dialect.MariaDB106Dialect

Deprecated dialects:

• com.openkm.db.dialect.MySQL5InnoDBDialect
• com.openkm.db.dialect.MySQL57InnoDBDialect

Edit the file $TOMCAT_HOME/openkm.properties

spring.jpa.hibernate.ddl-auto=create-only
spring.jpa.properties.hibernate.dialect=com.openkm.db.dialect.MySQL5InnoDBDialect

MySQL Timezone

If you want to set your timezone in your JDBC connection to match the timezone configured in your system, you can first see the mappings for both MySQL and MariaDB like this:

SELECT * FROM MySQL.time_zone_name

So, for example, you could configure your JDBC connection like this if your system timezone is UTC+1:

spring.datasource.url=jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8&nullNamePatternMatchesAll=true&serverTimezone=Europe/Madrid

Where Europe/Madrid is a valid value taken from the table time_zone_name.

Configure Tomcat data sources

Edit the file $TOMCAT_HOME/openkm.properties and enable the resource named JDBC/OpenKMDS

MySQL:

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8&nullNamePatternMatchesAll=true&serverTimezone=Europe/Madrid
spring.datasource.username=openkm
spring.datasource.password=*secret*

MariaDB:

spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.url=jdbc:mariadb://localhost:3306/okmdb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8&nullNamePatternMatchesAll=true&serverTimezone=Europe/Madrid
spring.datasource.username=openkm
spring.datasource.password=*secret*

Note

MariaDB can use these driverClassName values:

• com.mysql.jdbc.Driver
• org.mariadb.jdbc.Driver

In recent MySQL versions, you need to add useSSL=false to the connection URL.

Caution

Configure the values of the attributes named:

• Username.
• Password.
• URL (change to your host and port).

It can only use the resource called JDBC/OpenKMDS.

Configure application login

Edit the file $TOMCAT_HOME/openkm.properties

authentication.openkm.database=true

Run application

At OpenKM startup, the application will automatically create an empty database structure.

Note

Once the tables are created, OpenKM will automatically change the hibernate.hbm2ddl property from create-only to none.

Additional information

• InnoDB Performance Tuning Tips
• InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Remove and create MySQL service on Windows.

Caution

Ensure your MySQL\bin folder is in your PATH environment variable.

For example, in MySQL 5.7, the bin folder location is at C:\Program Files\MySQL\MySQL Server 5.7\bin

If you have a MySQL Windows service, you can remove it with one of these command lines:

c:\> mysqld --remove MySQL57

or

c:\> sc delete MySQL57

Create the service:

c:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --install MYSQL57 --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"

Note

More information at http://dev.mysql.com/doc/refman/5.7/en/windows-start-service.html

Migrate from Windows to Linux

Caution

By default, table names in Windows are lowercase and in Linux are uppercase.

When you migrate from Windows to Linux, you must convert lowercase table names to uppercase. The following script will help with it:

select concat('rename table ', table_name, ' to ' , upper(table_name) , ';') from information_schema.tables where table_schema = 'okmdb';

Case-insensitive node names

By default, the database is case sensitive. However, by executing:

ALTER TABLE OKM_NODE_BASE MODIFY NBS_NAME varchar(256) COLLATE utf8mb3_general_ci;

the NBS_NAME column is modified so that node names are treated in a case-insensitive manner. This collation also ignores accent differences, so names with and without accents are considered equivalent.

As a consequence, it will not be possible to have two documents such as “Más temas.txt” and “mas temas.txt”, since for the table index they are interpreted as the same string and the uniqueness constraint will fail.

If this happens, the system may throw an error like:

ERROR 1062 (23000): Duplicate entry '483ab139-9c20-44a9-8534-ed124a8f37ba-NULL' for key 'IDX_NODE_BASE_PARNAM'

This means both values are considered identical and the unique index detects them as duplicates.

To identify problematic nodes, you can run the following query:

SELECT NBS_PARENT, NBS_NAME, COUNT(*) FROM OKM_NODE_BASE
GROUP BY NBS_PARENT, LOWER(NBS_NAME) HAVING COUNT(*) > 1;

Any duplicates found should be renamed so they stop being treated as equal.

Troubleshooting

Error “ASCII ‘\\0’ appeared in the statement” when importing a database dump.

It is possible that the error is caused by the encoding of the SQL file. You should check the file format. For example, with the command:

> file okmdb.sql
okmdb.sql: Little-endian UTF-16 Unicode text, with very long lines, with CRLF line terminators

If the file is in UTF-16 format, convert it to UTF-8. For example, use the command:

> iconv -f utf-16 -t utf-8 okmdb.sql > okmdb-good.sql