Database Troubleshooting for MySQL

Still need help?

The Atlassian Community is here for you.

Ask the community

This article requires fixes

This article has been Flagged for fixing. Use caution when using it and fix it if you have Publisher rights.


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.
To resolve: change the 'max_allowed_packet' setting on MySQL server from its default of 1M. For more information, please refer to the MySQL manual: 

Command Line

shell> mysqld --max_allowed_packet=32M

MySQL 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

Knowledge Base Articles

List of Unresolved Issues from our Issue Tracker

T Key Summary
Loading...
Refresh

Last modified on Feb 4, 2020

Was this helpful?

Yes
No
Provide feedback about this article

In this section

Powered by Confluence and Scroll Viewport.