Database Troubleshooting for MySQL
On this page:
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
Case Sensitive Collation Recommended
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.
If you are running into character set or collation problems, please refer to the following KB on how to convert to utf8 character set with utf8_bin collation at the database, table, and column levels: How to Fix the Collation and Character Set of a MySQL Database.
MySQL Transaction Isolation Level
When upgrading an older version of Confluence to 3.5 or higher you may receive an error stating:
MySQL session isolation level 'REPEATABLE-READ' is no longer supported. Session isolation level must be 'READ-COMMITTED'
To fix this, set your isolation level to 'READ-COMMITTED', see Confluence fails to start and throws 'MySQL session isolation level 'REPEATABLE-READ' is no longer supported' error
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.cnf) configuration file.
For more information, please refer to: 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
Do not 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.
No 'toLower' Capability - Database Case Sensitivity
Some of the database indices are not available for MySQL. For more detail, see the workaround at Creating a Lowercase Page Title Index
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.
MySQL Fails to start with server quit without updating PID file
This happens when the innoDB logs files size are different from the value set on mysql configuration file (my.ini on windows or my.cnf on linux). To address that you will need to shutdown mysql, remove the ib_logfile0 and iblogfile1 files and then start MySQL server.
Those files can be found at:
- '/var/lib/mysql/' on linux
- '%PROGRAMDATA%\MySQL\MySQL Server x.x\data' on windows
Max Allowed Packet Size Exceeded
When connecting to MySQL, Confluence comes across an exception with exceeding max_allowed_packet size.
java.lang.IllegalArgumentException: Packet is larger than max_allowed_packet from server configuration of **** bytes.
shell> mysqld --max_allowed_packet=32M
If you use MySQL Administrator, you can set this parameter from the Startup Variables section on Windows OS (Options on MacOS) >> Advanced Networking subsection. Set max_allowed_packet option to 32 (MB), which in the screenshot below, is currently set at 1 MB.
For more information, please refer to MySQL manual:
Knowledge Base Articles
List of Unresolved Issues from our Issue Tracker