1. Home
  2. OpenKM 6.3 - CE
  3. Installation
  4. Database
  5. Configuring MySQL and MariaDB
  1.  

Configuring MySQL and MariaDB

MariaDB is a community-developed split of the MySQSL intended to remain free under the GNU GPL. Being a split of a leading Open Source software system, it is notable for being led by the original developers of MySQL, who separated it due to concerns over its acquisition by Oracle.

Preliminaries

Perform all changes with the application stopped.

Check if your MySQL installation has InnoDB engine enabled:

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

It should show something like this:

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

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

  1. Use the org.hibernate.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

default-storage-engine = innodb

After changing the default to 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. In order 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, 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:

$ mysqlshow -h localhost -u root -p --status okmdb;

More information at MySQL: Case Sensitivity in String Searches

Configure your OpenKM.cfg

This configuration property should be set before the database creation. Once the database has been initialized don't modify it because can damage your installation.

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

Edit the file $TOMCAT_HOME/OpenKM.cfg

hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.hbm2ddl=create

or ( recommended )

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.hbm2ddl=create

MySQL timezone

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

SELECT * FROM mysql.time_zone_name

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

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

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

Configure Tomcat datasources

Edit the file $TOMCAT_HOME/conf/server.xml and enable the resource named jdbc/OpenKMDS

MySQL:

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
          username="openkm" password="*secret*" driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>

MariaDB:

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
          username="openkm" password="*secret*" driverClassName="org.mariadb.jdbc.Driver"
          url="jdbc:mariadb://localhost:3306/okmdb?useSSL=false&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>

MariaDB can use these driverClassName values:

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

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

Configures the attributes values named:

  • userName.
  • password.
  • url ( change to your hosts and port ).

It can only use a resource named jdbc/OpenKMDS.

Configure application login

Edity the file $TOMCAT_HOME/OpenKM.xml

<security:authentication-manager alias="authenticationManager">
  <security:authentication-provider>
    <security:password-encoder hash="md5"/>
    <security:jdbc-user-service 
        data-source-ref="dataSource"
        users-by-username-query="select usr_id, usr_password, 1 from OKM_USER where usr_id=? and usr_active='T'"
        authorities-by-username-query="select ur_user, ur_role from OKM_USER_ROLE where ur_user=?"/>
  </security:authentication-provider>
</security:authentication-manager>

Check for JDBC Driver

Go to $TOMCAT_HOME/lib and check for JDBC mysql-connector-java-5.1.12-bin.jar Driver.

If it is not present, download MySQL JDBC driver from MySQL Home Page and move it.

The driver numeration version could be distinct due some version upgrade.

MariaDB can use its own JDBC mariadb-java-client-1.1.7.jar Driver.

If is not present, download mariaDB JDBC driver from MariaDB Connector/J and move it.

Run application

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

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

Additional information

Remove and create MySQL service in Windows

Ensure your mysql/bin folder is in your PATH at environment variables.

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

If you have MySQL Window service you can remove with one of these command line:

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"

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

Migrate from Windows to Linux

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

When you migrate from Windows to Linux you must convert lowercase table name to uppercase. The next script will helps on it:

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