This page last changed on Feb 28, 2010 by jlargman.

On this page:

This page provides tips on configuring MySQL as Confluence's database.

No 'toLower' Capability - Database Case Sensitivity

Some of the database indices are not available for MySQL. See Creating a Lowercase Page Title Index for more detail on a workaround.

Configuring Database Character Encoding

To prevent problems with character encoding, for consistency, we recommend to use Unicode character encoding UTF-8 among all the entities of your system. See Configuring Database Character Encoding for more details.

When specifying a character encoding as part of your mysql connection url (eg: &characterEncoding=utf8), it is important to ensure that the specified encoding is compatible with the default encoding used by your database. Note: if you do not specify a characterEncoding on the connection url, the connection will default to the server's default character set.

Full details of MySQLs character support is available here: http://dev.mysql.com/doc/mysql/en/charset.html

MySQL Storage Engine

The default storage engine for MySQL is MyISAM. Because this storage engine does not support referential integrity, foreign key constraints or transactions, using it may lead to data corruption. Some known issues caused by using MyISAM include CONF-16070 and CONF-16494. Hence, this storage engine is not recommended for use with Confluence.

For a MySQL command line session, you can set the storage engine by passing the '--default-storage-engine=INNODB' option when starting the session. You can make this the default MySQL Server setting by adding this option to the my.ini (or my.cnf) configuration file.

For more information, please refer to: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html.

Upgrading to Confluence 3.0 or later fails

Some customers running Confluence on a MySQL database may find that when they upgrade to Confluence 3.0 or later, their Confluence 3.0 upgrade fails, with the Confluence logs revealing a "Specified key was too long" error. This issue is known to occur when MySQL's MyISAM storage engine and UTF-8 character set is used with Confluence.

If this is the case, please refer to the Upgrade to Confluence 3.0 with MySQL database fails with messages like "specified key was too long" knowledge base article on how to resolve this upgrade issue.

MySQL JDBC Drivers

Ensure that you are using the latest (5.1) MySQL Java Connector. Earlier versions of the MySQL connector have a bug which is triggered by improvements in Confluence 2.2. These earlier connector versions will result in an error being recorded in your logs on upgrade (and will result in unstable operation of Confluence)

ERROR [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update

You can download the latest MySQL connector from the MySQL Java Connector 5.1 download page. Please be sure that you remove any older versions of the connector from your application server.

Don't use the debug version of these drivers (the jar file ending in '-g.jar'). This requires extra configuration, see Installing the Driver and Configuring the CLASSPATH

Access Denied

If you get a connection error: Access denied for user 'confluenceuser'@'localhost.localdomain' this may be because 127.0.0.1 resolves to 'localhost.localdomain' in your environment. Create a user 'confluenceuser@localhost%' to match any domain starting with localhost.

Max Allowed Packet Size Exceeded

A common problem with MySQL is the max packet size restriction, which can result in an "Object Error" message when installing certain plugins.

If you are using MySQL 4 and prior, you may come across a problem with max_allowed_packet size.

ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions Packet for query is too large (1259485 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

To resolve these problems, you need to either specify or increase the value for max_allowed_packet. Make sure to set the packed size when starting the server, not the client.

Prior MySQL 4.0, use this syntax instead:

shell> mysqld --set-variable=max_allowed_packet=16M
In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB.

From MySQL 4.0, use this syntax

shell> mysqld --max_allowed_packet=32M

For more information, please refer to MySQL manual:
http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html

If you use MySQL Administrator, you can set this parameter from the Startup Variables section on Windows OS (Options on MacOS) > Advanced Networking subsection. You can set max_allowed_packet option to 16 (MB), which in the screenshot below, is currently set at 1 MB.

Screenshot: Setting the max_allowed_packet option via the MySQL Administrator

Duplicate Key Exception During Import

When upgrading to MySQL from another database, such as HSQL, importing the site backup often fails with an error like this:

Duplicate key or integrity constraint violation message from server:

Such errors occur because usually MySQL evaluates unique key constraints and primary key constraints in a case insensitive way. So if you have a space with the key "sp" and another with the key "SP", MySQL will refuse to add the second one.

This problem is avoidable by setting the collation on the database to be case sensitive.

Setting the MySQL Collation to be case sensitive

MySQL uses collations for sorting data and for evaluating uniqueness.

To set the collation to case sensitive when using utf8, use this command:

CREATE DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

Note: The collation must be compatible with the character set.  The name of the database in the example is confluence.

For further information see the MySQL documentation on character sets on collations.

Database Timeout Issues when creating a Manual Backup

A problem that some customers have encountered is their Database connection timing out whilst in the middle of performing a Manual Site Backup in Confluence.

This issue is indicated in the log files with the following Error Message:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

According to the MySQL manual:

The number of seconds the server waits for activity on a non-interactive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made via named pipes, or shared memory.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

This problem is resolved by adjusting the wait_timeout parameter to a higher value.

If problems persist after making the changes, switch to the Production Backup Strategy.

After a while, database errors are generated and Confluence stops working

MySQL's JDBC drivers usually close a connection that remains idle for a certain amount of time (normally eight hours). Since Confluence uses a connection pool, this means that pooled connections will be terminated if they are not used within a certain time period.

The solution is to append ?autoReconnect=true to the end of your database's JDBC URL.

  • If you are using JNDI data-sources, you will do this in your application server's configuration files.
  • If you have configured Confluence to access the database directly, you will need to manually edit the hibernate.connection.url property in the confluence.cfg.xml file in your confluence.home directory. After you have changed the URL in this file, restart Confluence.

Troubleshooting Tips

The following are tips for Troubleshooting MySQL.

Page: ClientAbortException java.net.SocketException Broken Pipe with Jira User management on MySQL
Page: Change MySQL Database Character Encoding to utf8 During Server Mirgration
Page: Error loading plugins or saving content larger than 1Mb
Page: Invalid Use of Group Function
Page: Database errors when using MySQL and MyISAM tables
Page: Upgrade to Confluence 3.0 with MySQL database fails with messages like "specified key was too long"
Page: Setup Fails Creating MySQL Schema due to Tomcat Incompatibility
Page: MySQL Table is Marked as Crashed and Should be Repaired
Page: Cannot Create XML Backup due to Corrupt Table
Page: Cannot Restore XML Backup due to Data Truncation - MySQL Driver 3.1
Page: Login after a long inactivity on confluence fails using MySQL
Page: Characters appear as question marks using MySQL
Page: MySQL has Performance Problems when Pages Have Many Revisions

MySQLAdmin.png (image/png)
MySQLAdmin.png (image/png)
Document generated by Confluence on Jul 09, 2010 01:09