Configuration Tips for Installation

The following are tips for Configuring MySQL when Installing

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.

データベース文字エンコード設定

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. This storage engine does not support foreign key constraints or transactions. This may cause data corruption and is not recommended for use.

You can set the default Storage Engine for MySQL by passing the '--default-storage-engine=InnoDB' option when starting mysql.

For more information see: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html.

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

トラブルシューティングのヒント

The following are tips for Troubleshooting MySQL.

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

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.

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.

To resolve this problem, you need to 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
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 Options > Advanced Networking. You can set Max Packet Size to 16. Here's a screenshot:

 

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 Alternative Backup Strategy.

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.