This page last changed on Mar 14, 2007 by don.willis@atlassian.com.

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.

MySQL JDBC Drivers

For users using MySQL with Confluence 2.2 (or higher), please ensure that you are using the latest (3.1.14) 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 3.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

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 and Character encoding:

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 servers default character set.

Full details of MySQLs character support is available here: http://dev.mysql.com/doc/mysql/en/charset.html

There are known issues with MySQL 3.x and Unicode characters. Please see MySQL 3.x Character Encoding Problems


Confluence installation Hangs

If the confluence installation hangs after creating the mysql jdbc connection, try setting the jdbc property useServerPrepStmts to false.

Example:

jdbc:mysql://localhost/confluence?autoReconnect=true&useServerPrepStmts=false

This issue is known to happen on Ubuntu and Mysql 4.1.x.


Find a list of MySQL related configuration hints below:


MySQL Connector-J 3.1.10 bug.

If you are wondering why a large number of '!' marks are appearing in your log files, it is likely to be caused by http://bugs.mysql.com/bug.php?id=11629.


BadSqlGrammarException and character encoding exceptions using MySQL Connector-J 3.1.10

User reports that adding a "useOldUTF8Behavior=true" parameter to the JDBC URL allowed Confluence to work with this driver.


DataIntegrityViolationException when running MySQL 5

Running MySQL 5 as the database for Confluence is currently not a supported configuration. However, if it is used, the following exception may occur in the logs:

org.springframework.dao.DataIntegrityViolationException: (Hibernate operation): data integrity violated by SQL ''; nested exception is
java.sql.BatchUpdateException: Data too long for column 'boolean_val' at row 1

A temporary fix for this is to manually modify the boolean_val column in the OS_PROPERTYENTRY table, so that it is of type bit(8). Note that this won't allow you to restore a backup, as the first thing the restore does is rebuild the DB.


java.sql.SQLException: Invalid authorization specification

Some users reported problems getting MySQL running under Tomcat, throwing an exception similar to the following:

Connection failed to open on the JDBC URL: java.sql.SQLException: Invalid authorization specification, message from server: "Access denied for user: 'your_username@localhost.localdomain' (Using password: YES)"

However, it is still possible to easily connect to MySQL from the shell and from PhpMyAdmin.

The problem seems to be a known bug and seems to be related to the hostname portion in mysql:users.

I figured out the problem. Changing "host" in mysql:users to "127.0.0.1" enabled Confluence to access the database.

Therefore instead of granting permissions using the following command:

GRANT ALL PRIVILEGES ON .\* to 'example'@'localhost.localdomain' identified by '<the password>';
 FLUSH PRIVILEGES;

you should use:

GRANT ALL PRIVILEGES ON .\* to 'example'@'127.0.0.1' identified by '<the password>';
 FLUSH PRIVILEGES;


You can do it this way, but it seems you should ensure that the canonical host name is the first item in the /etc/hosts line for 127.0.0.1. Make sure it's not localhost.localdomain but localhost. e.g.:

127.0.0.1     localhost localhost.localdomain myfunkyboxname anotheralias.foobar.com

And then make sure you use the same thing, i.e. localhost in the GRANT line and the JDBC URL. Inconsistency across these things is the root problem (and perhaps we can blame the JDBC Driver?)

This information is just as useful for JIRA as Confluence.

Max Allowed Packet Size Exceeded

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.

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.

To resolve this problem, you need to increase the value for max_allowed_packet

Prior MySQL 4.0, use this syntax instead:
shell> mysqld --set-variable=max_allowed_packet=16M
From MySQL 4.0, use this syntax
shell> mysql --max_allowed_packet=32M

For more information, please refer to MySQL manual:
http://mysql.bigbiz.com/doc/refman/4.1/en/packet-too-large.html


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 insensitive

MySQL uses collations for sorting data and for evaluating uniqueness.

To set the collation to case insensitive 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.

To alter the collation on an existing database using utf8, use this command:

ALTER 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.

Possibly relevant: the 3.1.11 driver breaks with JIRA (see http://jira.atlassian.com/browse/JRA-8231), whereas 3.1.10 and 3.2-alpha work.

Posted by jeff at Oct 20, 2005 23:32

If you are seeing the following exception on MySQL 5.0 during admin account setup

org.springframework.dao.DataIntegrityViolationException: (Hibernate operation): data integrity violated by SQL ''; nested exception is java.sql.BatchUpdateException: Data too long for column 'boolean_val' at row 1

then changing the type of boolean_val column in os_propertyentry database table to bit(8) fixes it. See the bug for details.

Posted by xvosil at Nov 26, 2005 00:51

We have had a user experience problems with the jdbc driver versions 3.2.0 and 3.1.12 and Confluence 1.4.1. Switching to 3.1.11 fixed this problem for them.

Because other customers we have had numerous problems with 3.1.x drivers, we still recommend the 3.0.x. series.

Posted by daniel@atlassian.com at Dec 19, 2005 18:36

What specific problems have folks seen with 3.1.12? I only remember ever seeing one bug related to JIRA and/or Confluence reported to us, so we're kind of in the dark here.

Feel free to contact me directly at mark@mysql.com with more information, or post bugs to http://bugs.mysql.com/

We can't fix bugs we don't know about, especially when they're related to how the driver works with a large application.

 Most of the things you've listed here are actually the 3.1 series of driver exposing functionality of MySQL-4.1 and newer that the 3.0 drivers don't know anything about.

Posted by mark@thematthews.org at Feb 01, 2006 16:59

A MySQL guy I spoke with about this problem wrote me that this bug is fixed. They seem eager to resolve this, so can someone at Confluence follow this up and let us know where it stands? I didn't want to post his contact info here without his ok, but feel free to contact me directly and I will give you his contact information.

Thanks,
Keith Bennett
kbennett at lsi-gis dot com


The MySQL message to me:

Keith,

I got a response from some developers and they said the bug was fixed.  They said to use Connector/J 3.1.12 and MySQL 5.0.18.  I hope that helps.

Richard Taylor
MySQL Corporate Sales

Posted by keithrbennett at Feb 01, 2006 17:29

Mark,

Thanks for letting us know. I haven't been able to reproduce the 3.1 Connector/J problem reported here:

http://jira.atlassian.com/browse/CONF-2718

We'll be in touch if we find anything concrete.

Cheers,
Dave

Posted by dave@atlassian.com at Feb 16, 2006 21:18

Keith,

Thanks for the heads up. Which bug are you referring to here?

Cheers,
Dave

Posted by dave@atlassian.com at Feb 16, 2006 21:19

MySQL 5.0.22

Confluence 2.2.2

Bug is still here....

Posted by ib at Jun 13, 2006 03:35

Which bug? And which version of the MySQL JDBC drivers are you using?

If you're having problems, please open a support request on http://support.atlassian.com.

Posted by cmiller at Jun 13, 2006 08:20

 FYI, here's an issue I ran into trying to do a database migration to mysql 4.1 using the xml export from confluence.

Everytime I tried to restore the backup I would get an error between 10% and 30% done saying there was an "duplicate entry" with some other details and the import would fail.  Upon (much) further investigation I found buried in the console output some errors about the mysql max packet size. After some online research I found out how to adjust that by adding this line to my "my.ini" file:

max_allowed_packet=16M

 This seemed to solve the problem and allowed me to restore my confluence from the xml backup.

Posted by evan.leonard at Oct 27, 2006 09:21

Is it possible to get confluence run with MySQL 5.0.21 and mysql-connector-java-3.0.14-1jpp?
Thanks!

Posted by f.marx@enbw.com at Oct 30, 2006 04:22
Document generated by Confluence on Mar 22, 2007 20:58