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. To avoid problems with 4-byte characters, we recommend utf8mb4. See Configuring Database Character Encoding for more details.
You should not need to specify a characterEncoding on the connection url, as the connection will default to the server's default character set. If you do decide to specify 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.
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 utf8mb4, use this command:
CREATE DATABASE confluence CHARACTER SET utf8mb4 COLLATE utf8mb4_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 JDBC Drivers
Ensure that you are using the latest drivers. See Database JDBC Drivers for a list.
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
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.
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.Command Line
shell> mysqld --max_allowed_packet=32MMySQL Administrator
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:
http://mysql.bigbiz.com/doc/refman/4.1/en/packet-too-large.html