Configuring MySQL and MariaDB

Preliminaries

Check if your MySQL installation has the InnoDB engine enabled:

In the case of another default engine, there are two options:

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

Change default engine

Modify the MySQL configuration file named my. cnf

and under [mysqld], add

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

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

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

Once modified, a database server restart is needed.

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:

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 to restart 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 will be set the default values from the my.ini ): 

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

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 according to the timezone configured in your system, first, you can see the mappings both for MySQL and MariaDB in this way:

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

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*

Configure application login

Edit the file $. TOMCAT_HOME/openkm.properties

authentication.openkm.database=true

Run application

As a process into OpenKM startup, the application will automatically create an empty database structure. 

Additional information

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

Remove and create MySQL service in Windows.

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

or

Create the service:

Migrate from Windows to Linux

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';

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:

 In the case of a file in UTF-16 format, you should convert it to UTF-8. For example, with the command: