Configuring MySQL and MariaDB
MariaDB is a community-developed split of MySQL 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. They separated it due to concerns over its acquisition by Oracle.
Preliminaries
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;
It should show something like this:
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
In the case of another default engine, there are two options:
- Use the com.openkm.db.dialect.MySQL5InnoDBDialect dialect and avoid changing the default MySQL Storage Engine.
- 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. 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 the following:
$ MySQL show -h localhost -u root -p --status okmdb;
More information at MySQL: Case Sensitivity in String Searches
Configure your openkm.properties
This configuration property should be set before the database creation. Once the database has been initialized, don't modify it because it 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.
Edit the file $. TOMCAT_HOME/openkm.properties
spring.jpa.hibernate.ddl-auto=create-only
spring.jpa.properties.hibernate.dialect=com.openkm.db.dialect.MySQL5InnoDBDialectMySQL 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:
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.data source.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*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.
Configures the values of the attributes named:
- Username.
- Password.
- URL ( change to your hosts and port ).
It can only use a resource called JDBC/OpenKMDS.
Configure application login
Edit the file $. TOMCAT_HOME/openkm.properties
authentication.openkm.database=trueRun application
As a process into OpenKM startup, the application will automatically create an empty database structure.
Once the tables are created, OpenKM will automatically change the hibernate.hbm2ddl property from create-only 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 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"
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 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';
