Confluence Docs 3.0 : Known Issues for MySQL This page last changed on Sep 21, 2009 by ggaskell. On this page: No 'toLower' Capability - Database Case Sensitivity Configuring Database Character Encoding MySQL Storage Engine Upgrading to Confluence 3.0 or later fails MySQL JDBC Drivers Access Denied Max Allowed Packet Size Exceeded Prior MySQL 4.0, use this syntax instead: From MySQL 4.0, use this syntax Duplicate Key Exception During Import Setting the MySQL Collation to be case sensitive Database Timeout Issues when creating a Manual Backup After a while, database errors are generated and Confluence stops working Troubleshooting Tips This page provides tips on configuring MySQL as Confluence's database. ## 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. ## 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 ## 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.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 fails Some 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 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 ## 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 A 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
| 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.
#### 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 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 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. ## After a while, database errors are generated and Confluence stops working MySQL'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. If you are using JNDI data-sources, you will do this in your application server's configuration files. If you have configured Confluence to access the database directly, you will need to manually edit the `hibernate.connection.url` property in the `confluence.cfg.xml` file in your `confluence.home` directory. After you have changed the URL in this file, restart Confluence. ## Troubleshooting Tips The following are tips for Troubleshooting MySQL.
Cannot Restore XML Backup due to Data Truncation - MySQL Driver 3.1
Setup Fails Creating MySQL Schema due to Tomcat Incompatibility
Upgrade to Confluence 3.0 with MySQL database fails with messages like "specified key was too long"
MySQL Table is Marked as Crashed and Should be Repaired
Cannot Create XML Backup due to Corrupt Table
Error loading plugins or saving content larger than 1Mb
Login after a long inactivity on confluence fails using MySQL
Characters appear as question marks using MySQL
ClientAbortException java.net.SocketException Broken Pipe with Jira User management on MySQL
Database errors when using MySQL and MyISAM tables
Error- Invalid use of group function
Attachments: MySQLAdmin.png (image/png) MySQLAdmin.png (image/png)

Document generated by Confluence on Nov 05, 2009 23:34