MySQL

From OpenKM Documentation
Revision as of 14:01, 12 April 2010 by Pavila (talk | contribs) (→‎Database creation)

Jump to: navigation, search

Download MySQL JDBC driver from MySQL Home Page and move it to $JBOSS_HOME/server/default/lib.


Nota clasica.png If you get an error like this: 
Packet for query is too large (1708726 > 1048576).
You can change this value on the server by setting the max_allowed_packet' variable.
You need to modify your MySQL server configuration file and increase the value of the max_allowed_packet property. Don't forget to restart the MySQL after any change in this file. In Debian based distros like Ubuntu, this configuration file is located at /etc/mysql/my.cnf.

Repository configuration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Repository PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 1.4//EN"
                            "http://jackrabbit.apache.org/dtd/repository-1.4.dtd">
<Repository>
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
    <param name="path" value="${rep.home}/repository"/>
  </FileSystem>
  <Security appName="OpenKM">
    <AccessManager class="es.git.openkm.core.OKMAccessManager"/>
  </Security>
  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
  <Workspace name="${wsp.name}">
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${wsp.home}"/>
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/okm_repository?autoReconnect=true"/>
      <param name="schema" value="mysql"/>
      <param name="user" value="openkm"/>
      <param name="password" value="*secret*"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
      <param name="externalBLOBs" value="false"/>
    </PersistenceManager>
    <SearchIndex class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
      <param name="path" value="${wsp.home}/index"/>
      <param name="useCompoundFile" value="true"/>
      <param name="minMergeDocs" value="100"/>
      <param name="volatileIdleTime" value="3"/>
      <param name="maxMergeDocs" value="100000"/>
      <param name="mergeFactor" value="10"/>
      <param name="bufferSize" value="10"/>
      <param name="cacheSize" value="1000"/>
      <param name="forceConsistencyCheck" value="false"/>
      <param name="autoRepair" value="true"/>
      <param name="analyzer" value="org.apache.lucene.analysis.standard.StandardAnalyzer"/>
      <param name="respectDocumentOrder" value="false"/>
      <param name="indexingConfiguration" value="$ {wsp.home}/../../../indexing_configuration.xml"/>
      <param name="textFilterClasses" value="
org.apache.jackrabbit.core.query.lucene.TextPlainTextFilter,
org.apache.jackrabbit.extractor.PdfTextExtractor,
org.apache.jackrabbit.extractor.HTMLTextExtractor,
org.apache.jackrabbit.extractor.XMLTextExtractor,
org.apache.jackrabbit.extractor.RTFTextExtractor,
org.apache.jackrabbit.extractor.OpenOfficeTextExtractor,
es.git.openkm.extractor.MsExcelTextExtractor,
es.git.openkm.extractor.MsPowerPointTextExtractor,
es.git.openkm.extractor.MsWordTextExtractor,
es.git.openkm.extractor.MsOffice2007TextExtractor,
es.git.openkm.extractor.ExifTextExtractor,
es.git.openkm.extractor.TiffTextExtractor,
es.git.openkm.extractor.AudioTextExtractor" />
    </SearchIndex>
  </Workspace>
  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${rep.home}/version"/>
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/okm_repository?autoReconnect=true"/>
      <param name="schema" value="mysql"/>
      <param name="user" value="openkm"/>
      <param name="password" value="*secret*"/>
      <param name="schemaObjectPrefix" value="version_"/>
      <param name="externalBLOBs" value="false"/>
    </PersistenceManager>
  </Versioning>
  <!-- Also see DatabaseDataStore-->
  <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>
</Repository>

More info about this at Jackrabbit Configuration.

Database creation

In this OpenKM 4.1 release you can create the databases automatically configuring the system.database property in OpenKM.cfg. 

system.database=mysql

Nota advertencia.png This configuration property should be set before the repository creation. Once the document repository has been initialized don't modify it because can damage your documents. If your repository 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.

OpenKM 4.0 and older

#
# Generic activity database definition
#
CREATE TABLE activity(act_date TIMESTAMP, act_user VARCHAR(32), act_token VARCHAR(48), act_action VARCHAR(48), act_item VARCHAR(256), act_params VARCHAR(256));

#
# Generic auth database definition
#
CREATE TABLE users(usr_id VARCHAR(32), usr_name VARCHAR(64), usr_pass VARCHAR(32) NOT NULL, usr_email VARCHAR(32) NOT NULL, usr_active BOOLEAN, PRIMARY KEY(usr_id));
CREATE TABLE roles(rol_id VARCHAR(32), PRIMARY KEY(rol_id));
CREATE TABLE user_role(ur_user VARCHAR(32), ur_role VARCHAR(32), PRIMARY KEY(ur_user, ur_role));
CREATE TABLE mail_accounts(ma_id INTEGER AUTO_INCREMENT, ma_user VARCHAR(32), ma_mhost VARCHAR(32), ma_muser VARCHAR(32), ma_mpass VARCHAR(32), ma_mfolder VARCHAR(32), ma_active BOOLEAN, PRIMARY KEY(ma_id));
CREATE TABLE twitter_accounts(ta_id INTEGER AUTO_INCREMENT, ta_user VARCHAR(32), ta_tuser VARCHAR(32), ta_active BOOLEAN, PRIMARY KEY(ta_id));

# INSERT DEFAULT USER / ROLES
INSERT INTO users (usr_id, usr_name, usr_pass, usr_email, usr_active) VALUES ('okmAdmin', 'Administrator', '21232f297a57a5a743894a0e4a801fc3', '', true);
INSERT INTO roles (rol_id) VALUES ('AdminRole');
INSERT INTO roles (rol_id) VALUES ('UserRole');
INSERT INTO user_role (ur_user, ur_role) VALUES ('okmAdmin', 'AdminRole');

#
# Generic dashboard stats database definition
#
CREATE TABLE dashboard_stats(ds_user VARCHAR(32), ds_source VARCHAR(64), ds_node VARCHAR(256), ds_date TIMESTAMP);

Workflow engine

You have to modify the file WEB-INF/classes/hibernate.cfg.xml located inside the OpenKM.war archive. These are the entries to change: 

<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>

to 

<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>

And in File:Jbpm.jpdl.mysql.sql are the sententeces needed to create the tables. More info about this at The jBPM Database.

JBoss datasources

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <!-- OpenKM User Activity -->
  <local-tx-datasource>
    <jndi-name>OKMActivityDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_activity?autoReconnect=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*secret*</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>0</idle-timeout-minutes>
    <track-statements/>
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <metadata>
      <type-mapping>MySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
 
  <!-- OpenKM User Auth -->
  <local-tx-datasource>
    <jndi-name>OKMAuthDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_auth?autoReconnect=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*secret*</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>0</idle-timeout-minutes>
    <track-statements/>
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <metadata>
      <type-mapping>MySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
 
  <!-- OpenKM Dashboard Stats -->
  <local-tx-datasource>
    <jndi-name>OKMDashboardStatsDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_dashboard?autoReconnect=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*secret*</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>0</idle-timeout-minutes>
    <track-statements/>
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <metadata>
      <type-mapping>MySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
 
  <!-- OpenKM Workflow -->
  <local-tx-datasource>
    <jndi-name>OKMWorkflowDS</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/okm_workflow?autoReconnect=true</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>openkm</user-name>
    <password>*secret*</password>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>0</idle-timeout-minutes>
    <track-statements/>
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
    <metadata>
      <type-mapping>MySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

More info about this at Configuring JDBC DataSources.

Retrieved from "https://www.openkm.com/wiki/index.php?title=MySQL&oldid=1690"