MySQL - OpenKM 5.0

In this OpenKM release you can create the databases automatically configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg.

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

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

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 (default one is an embedded one called HSQL) you can't switch to another database simply changing this property.

More info about this at:

• Hibernate: SQL Dialects

Database creation

Starting with OpenKM 5.0, only two databases are needed:

DROP DATABASE IF EXISTS okm_repo;
DROP DATABASE IF EXISTS okm_app;

CREATE DATABASE okm_repo DEFAULT CHARACTER SET utf8;
CREATE DATABASE okm_app DEFAULT CHARACTER SET utf8;

CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
GRANT ALL ON okm_app.* TO openkm@localhost WITH GRANT OPTION;

JBoss datasources

<local-tx-datasource>
    <jndi-name>OpenKMDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_app?autoReconnect=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*****</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>28680</idle-timeout-minutes>
    <exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
    <metadata>
        <type-mapping>MySQL</type-mapping>
    </metadata>
</local-tx-datasource>

Login configuration

<!-- OpenKM -->
<application-policy name = "OpenKM">
    <authentication>
        <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
            <module-option name="dsJndiName">java:/OpenKMDS</module-option>
            <module-option name="principalsQuery">select usr_password as PASSWD from OKM_USER where usr_id=? and usr_active=true</module-option>
            <module-option name="rolesQuery">select ur_role as ROLEID, 'Roles' from OKM_USER_ROLE where ur_user=?</module-option>
            <module-option name="hashAlgorithm">md5</module-option>
            <module-option name="hashEncoding">hex</module-option>
        </login-module>
    </authentication>
</application-policy>