Confluence Docs 3.1 : Known Issues for MySQL
This page last changed on Sep 21, 2009 by ggaskell.
On this page:
This page provides tips on configuring MySQL as Confluence's database. No 'toLower' Capability - Database Case SensitivitySome 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 EncodingTo 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 EngineThe 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 failsSome 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 DriversEnsure 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 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 DeniedIf 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 ExceededA 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
From MySQL 4.0, use this syntaxshell> 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. 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 ImportWhen 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: This problem is avoidable by setting the collation on the database to be case sensitive. Setting the MySQL Collation to be case sensitiveMySQL 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. Database Timeout Issues when creating a Manual BackupA 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: 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. After a while, database errors are generated and Confluence stops workingMySQL'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.
Troubleshooting TipsThe following are tips for Troubleshooting MySQL. |
![]() |
Document generated by Confluence on Dec 10, 2009 18:45 |