Difference between revisions of "MySQL"
From OpenKM Documentation
(→Change MySQL default engine to InnoDB) |
|||
| (17 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
{{TOCright}} __TOC__ | {{TOCright}} __TOC__ | ||
| − | Download MySQL JDBC driver from [http://www.mysql.com/products/connector/ MySQL Home Page] and move it to ''$JBOSS_HOME/server/default/lib''. | + | 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.. |
{{Note|If you get an error like this: | {{Note|If you get an error like this: | ||
Packet for query is too large (1708726 > 1048576). | Packet for query is too large (1708726 > 1048576). | ||
| − | You can change this value on the server by setting the max_allowed_packet' variable. | + | 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''.}} | 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''.}} | ||
| − | * [[MySQL-OpenKM 5.0 | OpenKM 5.0]] | + | * [[MySQL-OpenKM 6.0 | OpenKM 6.0]] (also valid for OpenKM 6.X+) |
| − | * [[MySQL-OpenKM 4.1 | OpenKM 4.1]] | + | * [[MySQL-OpenKM 5.0 | OpenKM 5.0]] (also valid for OpenKM 5.X+) |
| + | * [[MySQL-OpenKM 4.1 | OpenKM 4.1]] | ||
| + | |||
| + | == 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 | ||
| + | |||
| + | {{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.}} | ||
| + | |||
| + | If you want to show which engine is using every table, do: | ||
| + | |||
| + | <source lang="sql"> | ||
| + | SHOW TABLE STATUS WHERE name like 'OKM_%'; | ||
| + | </source> | ||
| + | |||
| + | To see all the tables included in a database: | ||
| + | |||
| + | <source lang="sql"> | ||
| + | SHOW FULL TABLES FROM okmdb; | ||
| + | </source> | ||
| + | |||
| + | You can also filter by table name: | ||
| + | |||
| + | <source lang="sql"> | ||
| + | SHOW FULL TABLES FROM okmdb LIKE 'OKM_%' | ||
| + | </source> | ||
| + | |||
| + | == 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: | ||
| + | * [http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html Converting Tables from Other Storage Engines to InnoDB] | ||
| + | * [http://highervisibilitywebsites.com/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time Convert your MySQL database from MyISAM to InnoDB] | ||
[[Category: Installation Guide]] | [[Category: Installation Guide]] | ||
| − | |||
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..
 |
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. |
- OpenKM 6.0 (also valid for OpenKM 6.X+)
- OpenKM 5.0 (also valid for OpenKM 5.X+)
- OpenKM 4.1
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
 |
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:
