Difference between revisions of "MySQL"

From OpenKM Documentation
Jump to: navigation, search
(→‎Change MySQL default engine to InnoDB)
 
(54 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
Download MySQL JDBC driver from [http://www.mysql.com/products/connector/ MySQL Home Page] and move it to ''$JBOSS_HOME/server/default/lib''.
+
{{TOCright}} __TOC__
  
−
== Repository configuration ==
+
Download MySQL JDBC driver from [http://www.mysql.com/products/connector/ MySQL Home Page] and move it to ''$TOMCAT_HOME/lib'' or ''$JBOSS_HOME/server/default/lib'' depending on your OpenKM version..
  
−
<source lang="xml">
+
{{Note|If you get an error like this:
−
<?xml version="1.0" encoding="UTF-8"?>
+
 
−
<Repository>
+
Packet for query is too large (1708726 > 1048576).
−
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
+
You can change this value on the server by setting the 'max_allowed_packet' variable.
−
    <param name="path" value="${rep.home}/repository"/>
+
 
−
  </FileSystem>
+
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''.}}
−
  <Security appName="OpenKM">
+
 
−
    <AccessManager class="com.openkm.core.OKMAccessManager"/>
+
* [[MySQL-OpenKM 6.0 | OpenKM 6.0]] (also valid for OpenKM 6.X+)
−
  </Security>
+
* [[MySQL-OpenKM 5.0 | OpenKM 5.0]] (also valid for OpenKM 5.X+)
−
  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>
+
* [[MySQL-OpenKM 4.1 | OpenKM 4.1]]
−
  <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/openkm?autoReconnect=true"/>
 
−
      <param name="schema" value="mysql"/>
 
−
      <param name="user" value="root"/>
 
−
      <param name="password" value="xxx"/>
 
−
      <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,
 
−
com.openkm.extractor.MsExcelTextExtractor,
 
−
com.openkm.extractor.MsPowerPointTextExtractor,
 
−
com.openkm.extractor.MsWordTextExtractor,
 
−
com.openkm.extractor.MsOffice2007TextExtractor,
 
−
com.openkm.extractor.ExifTextExtractor,
 
−
com.openkm.extractor.TiffTextExtractor,
 
−
com.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/openkm?autoReconnect=true"/>
 
−
      <param name="schema" value="mysql"/>
 
−
      <param name="user" value="root"/>
 
−
      <param name="password" value="xxx"/>
 
−
      <param name="schemaObjectPrefix" value="version_"/>
 
−
      <param name="externalBLOBs" value="false"/>
 
−
    </PersistenceManager>
 
−
  </Versioning>
 
−
</Repository>
 
−
</source>
 
  
−
== Database creation ==
+
== Change MySQL default engine to InnoDB ==
−
<source lang="sql">
+
Check if your MySQL installation has InnoDB engin enabled:
−
#
 
−
# 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));
 
−
</source>
 
  
−
<source lang="sql">
+
  $ mysql -h localhost -u root -p
−
#
+
  mysql> show engines;
−
# 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
+
In order to change the default storage engine edit '''/etc/mysql/my.cnf''' and under '''[mysqld]''' section in your ini file, and add:
−
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');
 
−
</source>
 
  
−
<source lang="sql">
+
  default-storage-engine = innodb
−
#
 
−
# Generic dashboard stats database definition
 
−
#
 
−
CREATE TABLE dashboard_stats(ds_user VARCHAR(32), ds_source VARCHAR(64), ds_node VARCHAR(256), ds_date TIMESTAMP);
 
−
</source>
 
  
−
== Workflow engine ==
+
{{Advice|[http://en.wikipedia.org/wiki/InnoDB InnoDB] is the preferred engine because it support transactions. [http://en.wikipedia.org/wiki/MyISAM MyISAM] is more responsive but does not support transactions.}}
−
You have to modify the file ''WEB-INF/classes/hibernate.cfg.xml'' located inside the OpenKM.war archive. These are the entries to change:
 
  
−
<source lang="xml">
+
If you want to show which engine is using every table, do:
−
<!-- hibernate dialect -->
 
−
<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property>
 
  
−
<!-- JDBC connection properties (begin) ===
+
<source lang="sql">
−
<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property>
+
SHOW TABLE STATUS WHERE name like 'OKM_%';
−
<property name="hibernate.connection.url">jdbc:hsqldb:mem:jbpm</property>
 
−
<property name="hibernate.connection.username">sa</property>
 
−
<property name="hibernate.connection.password"></property>
 
−
==== JDBC connection properties (end) -->
 
 
</source>
 
</source>
  
−
to
+
To see all the tables included in a database:
  
−
<source lang="xml">
+
<source lang="sql">
−
<!-- hibernate dialect -->
+
SHOW FULL TABLES FROM okmdb;
−
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 
−
 
 
−
<!-- JDBC connection properties (begin) -->
 
−
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
 
−
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/openkm</property>
 
−
<property name="hibernate.connection.username">root</property>
 
−
<property name="hibernate.connection.password">xxx</property>
 
−
<!-- JDBC connection properties (end) -->
 
 
</source>
 
</source>
  
−
== JBoss datasources ==
+
You can also filter by table name:
−
<source lang="xml">
 
−
<?xml version="1.0" encoding="UTF-8"?>
 
  
−
<datasources>
+
<source lang="sql">
−
  <!-- OpenKM User Activity -->
+
SHOW FULL TABLES FROM okmdb LIKE 'OKM_%'
−
  <local-tx-datasource>
+
</source>
−
    <jndi-name>OKMActivityDS</jndi-name>
 
−
    <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMActivity</connection-url>
 
−
    <driver-class>org.hsqldb.jdbcDriver</driver-class>
 
−
    <user-name>sa</user-name>
 
−
    <password></password>
 
−
    <min-pool-size>5</min-pool-size>
 
−
    <max-pool-size>20</max-pool-size>
 
−
    <idle-timeout-minutes>0</idle-timeout-minutes>
 
−
    <track-statements/>
 
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
−
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
−
    <metadata>
 
−
      <type-mapping>Hypersonic SQL</type-mapping>
 
−
    </metadata>
 
−
    <depends>jboss:service=Hypersonic,database=OKMActivity</depends>
 
−
  </local-tx-datasource>
 
  
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
+
== Database repair ==
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
+
If you have problems with a database and need to be repaired, you can use this command:
−
        name="jboss:service=Hypersonic,database=OKMActivity">
 
−
    <attribute name="Database">OKMActivity</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
−
 
−
  <!-- OpenKM User Auth -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMAuthDS</jndi-name>
 
−
    <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMAuth</connection-url>
 
−
    <driver-class>org.hsqldb.jdbcDriver</driver-class>
 
−
    <user-name>sa</user-name>
 
−
    <password></password>
 
−
    <min-pool-size>5</min-pool-size>
 
−
    <max-pool-size>20</max-pool-size>
 
−
    <idle-timeout-minutes>0</idle-timeout-minutes>
 
−
    <track-statements/>
 
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
−
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
−
    <metadata>
 
−
      <type-mapping>Hypersonic SQL</type-mapping>
 
−
    </metadata>
 
−
    <depends>jboss:service=Hypersonic,database=OKMAuth</depends>
 
−
  </local-tx-datasource>
 
  
−
   <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
+
   $ mysqlcheck -u root -p --auto-repair --optimize --all-databases
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMAuth">
 
−
    <attribute name="Database">OKMAuth</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
−
 
 
−
  <!-- OpenKM Dashboard Stats -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMDashboardStatsDS</jndi-name>
 
−
    <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMDashboardStats</connection-url>
 
−
    <driver-class>org.hsqldb.jdbcDriver</driver-class>
 
−
    <user-name>sa</user-name>
 
−
    <password></password>
 
−
    <min-pool-size>5</min-pool-size>
 
−
    <max-pool-size>20</max-pool-size>
 
−
    <idle-timeout-minutes>0</idle-timeout-minutes>
 
−
    <track-statements/>
 
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
−
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
−
    <metadata>
 
−
      <type-mapping>Hypersonic SQL</type-mapping>
 
−
    </metadata>
 
−
    <depends>jboss:service=Hypersonic,database=OKMDashboardStats</depends>
 
−
  </local-tx-datasource>
 
  
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
+
See also:
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
+
* [http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html Converting Tables from Other Storage Engines to InnoDB]
−
        name="jboss:service=Hypersonic,database=OKMDashboardStats">
+
* [http://highervisibilitywebsites.com/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time Convert your MySQL database from MyISAM to InnoDB]
−
    <attribute name="Database">OKMDashboardStats</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
−
 
−
  <!-- OpenKM Workflow -->
 
−
  <local-tx-datasource>
 
−
    <jndi-name>OKMWorkflowDS</jndi-name>
 
−
    <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}OKMWorkflow</connection-url>
 
−
    <driver-class>org.hsqldb.jdbcDriver</driver-class>
 
−
    <user-name>sa</user-name>
 
−
    <password></password>
 
−
    <min-pool-size>5</min-pool-size>
 
−
    <max-pool-size>20</max-pool-size>
 
−
    <idle-timeout-minutes>0</idle-timeout-minutes>
 
−
    <track-statements/>
 
−
    <!--<security-domain>HsqlDbRealm</security-domain>-->
 
−
    <prepared-statement-cache-size>32</prepared-statement-cache-size>
 
−
    <metadata>
 
−
      <type-mapping>Hypersonic SQL</type-mapping>
 
−
    </metadata>
 
−
    <depends>jboss:service=Hypersonic,database=OKMWorkflow</depends>
 
−
  </local-tx-datasource>
 
  
−
  <!-- For hsqldb accessed from jboss only, in-process (standalone) mode -->
+
[[Category: Installation Guide]]
−
  <mbean code="org.jboss.jdbc.HypersonicDatabase"
 
−
        name="jboss:service=Hypersonic,database=OKMWorkflow">
 
−
    <attribute name="Database">OKMWorkflow</attribute>
 
−
    <attribute name="InProcessMode">true</attribute>
 
−
  </mbean>
 
−
</datasources>
 
−
</source>
 

Latest revision as of 10:55, 25 November 2013

Download MySQL JDBC driver from MySQL Home Page and move it to $TOMCAT_HOME/lib or $JBOSS_HOME/server/default/lib depending on your OpenKM version..


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.

Change MySQL default engine to InnoDB

Check if your MySQL installation has InnoDB engin enabled: 

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

In order to change the default storage engine edit /etc/mysql/my.cnf and under [mysqld] section in your ini file, and add: 

 default-storage-engine = innodb

Nota idea.png InnoDB is the preferred engine because it support transactions. MyISAM is more responsive but does not support transactions.

If you want to show which engine is using every table, do: 

SHOW TABLE STATUS WHERE name like 'OKM_%';

To see all the tables included in a database: 

SHOW FULL TABLES FROM okmdb;

You can also filter by table name: 

SHOW FULL TABLES FROM okmdb LIKE 'OKM_%'

Database repair

If you have problems with a database and need to be repaired, you can use this command: 

 $ mysqlcheck -u root -p --auto-repair --optimize --all-databases

See also:

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