Difference between revisions of "MySQL - OpenKM 6.2"

From OpenKM Documentation
Jump to: navigation, search
(Created page with '{{TOCright}} __TOC__ {{Note|These instructions are also valid for OpenKM 6.1.}} In this OpenKM release you can create the databases automatically configuring the '''hibernate.di…')
 
(→‎Database creation)
 
(17 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{TOCright}} __TOC__
 
{{TOCright}} __TOC__
−
{{Note|These instructions are also valid for OpenKM 6.1.}}
 
  
 
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in [[OpenKM.cfg]].
 
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in [[OpenKM.cfg]].
Line 6: Line 5:
 
<source lang="java">
 
<source lang="java">
 
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
 
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
 +
hibernate.hbm2ddl=create
 +
</source>
 +
 +
or
 +
 +
<source lang="java">
 +
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
 
hibernate.hbm2ddl=create
 
hibernate.hbm2ddl=create
 
</source>
 
</source>
Line 17: Line 23:
  
 
== Database creation ==
 
== Database creation ==
−
Starting with OpenKM 6.0, only one database is needed:
+
Check if your MySQL installation has InnoDB engine enabled:
 +
 
 +
<source lang="bash">
 +
$ mysql -h localhost -u root -p
 +
mysql> show engines;
 +
</source>
 +
 
 +
{{Note|You can use the '''org.hibernate.dialect.MySQL5InnoDBDialect''' dialect and avoid changing the default MySQL Storage Engine.}}
 +
 
 +
Edit /etc/mysql/my.cnf and under [mysqld] section in your ini file, add:
 +
 
 +
<source lang="bash">
 +
default-storage-engine = innodb
 +
</source>
 +
 
 +
{{Note|Starting with OpenKM 6.0, only one database is needed.}}
 +
 
 +
Now create databases and user:
  
 
<source lang="sql">
 
<source lang="sql">
−
DROP DATABASE IF EXISTS okm_app;
+
DROP DATABASE IF EXISTS okmdb;
  
−
CREATE DATABASE okm_app DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
+
CREATE DATABASE okmdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
  
 
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
 
CREATE USER openkm@localhost IDENTIFIED BY '*secret*';
−
GRANT ALL ON okm_app.* TO openkm@localhost WITH GRANT OPTION;
+
GRANT ALL ON okmdb.* TO openkm@localhost WITH GRANT OPTION;
 +
</source>
 +
 
 +
You can check the database engine with:
 +
 
 +
<source lang="bash">
 +
$ mysqlshow -h localhost -u root -p --status okmdb;
 
</source>
 
</source>
  
Line 31: Line 60:
  
 
== Tomcat datasources ==
 
== Tomcat datasources ==
−
 
 
<pre>$ vim $TOMCAT_HOME/conf/server.xml</pre>
 
<pre>$ vim $TOMCAT_HOME/conf/server.xml</pre>
  
Line 37: Line 65:
 
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
 
<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
 
           maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
 
           maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
−
           username="openkm" password="*****" driverClassName="com.mysql.jdbc.Driver"
+
           username="openkm" password="*secret*" driverClassName="com.mysql.jdbc.Driver"
−
           url="jdbc:mysql://localhost:3306/okm_app?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
+
           url="jdbc:mysql://localhost:3306/okmdb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"/>
 
</source>
 
</source>
  
 
== Login configuration ==
 
== Login configuration ==
 
<pre>$ vim $TOMCAT_HOME/OpenKM.xml</pre>
 
<pre>$ vim $TOMCAT_HOME/OpenKM.xml</pre>
 +
 
<source lang="xml">
 
<source lang="xml">
 
<security:authentication-manager alias="authenticationManager">
 
<security:authentication-manager alias="authenticationManager">
Line 54: Line 83:
 
</security:authentication-manager>
 
</security:authentication-manager>
 
</source>
 
</source>
 +
 +
== More information ==
 +
* [http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html InnoDB Performance Tuning Tips]
 +
* [http://dba.stackexchange.com/questions/21209/innodb-high-disk-write-i-o-on-ibdata1-file-and-ib-logfile0 InnoDB - High disk write I/O on ibdata1 file and ib_logfile0]
  
 
[[Category: Installation Guide]]
 
[[Category: Installation Guide]]
−
[[Category: OKM Network]]
 

Latest revision as of 09:54, 5 November 2013

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

or 

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

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


Nota advertencia.png 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:

Database creation

Check if your MySQL installation has InnoDB engine enabled: 

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

Nota clasica.png You can use the org.hibernate.dialect.MySQL5InnoDBDialect dialect and avoid changing the default MySQL Storage Engine.

Edit /etc/mysql/my.cnf and under [mysqld] section in your ini file, add: 

default-storage-engine = innodb

Nota clasica.png Starting with OpenKM 6.0, only one database is needed.

Now create databases 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 info at MySQL: Case Sensitivity in String Searches.

Tomcat datasources

$ vim $TOMCAT_HOME/conf/server.xml
<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"/>

Login configuration

$ vim $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>

More information

Retrieved from "https://www.openkm.com/wiki/index.php?title=MySQL_-_OpenKM_6.2&oldid=10858"