|
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. This storage engine does not support foreign key constraints or transactions. This may cause data corruption and is not recommended for use. You can set the default Storage Engine for MySQL by passing the '--default-storage-engine=InnoDB' option when starting mysql. For more information see: http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html. 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 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 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 ExceededIf 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 this problem, you need to 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 Options > Advanced Networking. You can set Max Packet Size to 16. Here's a screenshot:
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: 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 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. For further information see the MySQL documentation on character sets on collations. 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. |
Labels
Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.
Comments (18)
Oct 20, 2005
Jeff Turner says:
Possibly relevant: the 3.1.11 driver breaks with JIRA (see http://jira.atlassian...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.
Nov 26, 2005
Andrei L. says:
If you are seeing the following exception on MySQL 5.0 during admin account setu...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.
Dec 19, 2005
Daniel Ostermeier says:
We have had a user experience problems with the jdbc driver versions 3.2.0 and 3...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.
Feb 01, 2006
Mark Matthews says:
What specific problems have folks seen with 3.1.12? I only remember ever seeing ...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.
Feb 16, 2006
David Loeng says:
Mark, Thanks for letting us know. I haven't been able to reproduce the 3.1 Con...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
Feb 01, 2006
Keith R. Bennett says:
A MySQL guy I spoke with about this problem wrote me that this bug is fixed. Th...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
atlsi-gisdotcomThe 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
Feb 16, 2006
David Loeng says:
Keith, Thanks for the heads up. Which bug are you referring to here? Cheers,...Keith,
Thanks for the heads up. Which bug are you referring to here?
Cheers,
Dave
Jun 13, 2006
ib says:
MySQL 5.0.22 Confluence 2.2.2 Bug is still here....MySQL 5.0.22
Confluence 2.2.2
Bug is still here....
Jun 13, 2006
Charles Miller says:
Which bug? And which version of the MySQL JDBC drivers are you using? If you're...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.
Oct 27, 2006
Evan Leonard says:
FYI, here's an issue I ran into trying to do a database migration to mysql...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.
Oct 30, 2006
EnBW says:
Is it possible to get confluence run with MySQL 5.0.21 and mysql-connector-java-...Is it possible to get confluence run with MySQL 5.0.21 and mysql-connector-java-3.0.14-1jpp?
Thanks!
Nov 01, 2007
Jerry Isdale says:
Does Confluence 2.6 support MySQL 5 or not? The announcement says it does, but ...Does Confluence 2.6 support MySQL 5 or not?
The announcement says it does, but this and other support docs say no.
Nov 01, 2007
Mei Yan Chan says:
Hi Jerry, Please see my comment regarding this issue at: http://confluence.a...Hi Jerry,
Please see my comment regarding this issue at:
Regards,
Mei
Nov 09, 2007
Stan Sorochan says:
Is there a way during the installation phase to make Confluence create all its' ...Is there a way during the installation phase to make Confluence create all its' tables in innodb format instead of changing the default engine on the mysql server?
Nov 11, 2007
Mei Yan Chan says:
Hi Stan, There is already a feature request to change the configuration to use ...Hi Stan,
There is already a feature request to change the configuration to use Innodb format instead. However, to answer your question, there is still a need to change the format of your storage engine manually. For more information, please see:
Regards,
Mei
May 13
Kelvin D. Olson says:
Yes, it'd be quite excellent for Atlassian to drop the presumption that we're us...Yes, it'd be quite excellent for Atlassian to drop the presumption that we're using a distinct MySQL instance for Confluence Wiki, and nothing else. Turns out, not true. Not even close. We have nearly a dozen MySQL servers, most are running instances on at least 4 ports, many dozens of DBs, hundreds of tables. And our default storage engine? MyISAM, of course, because it performs better. When we need a Foreign Key or transactional single-row locking, we change those tables (and only those tables) to InnoDB.
Rather than only telling us how to configure the entire instance to use InnoDB as its default, why not list the specific tables that need to be InnoDB, and let us run the others as MyISAM?
Why not have install/upgrade scripts that specifically create tables using the storage engine needed for each specific table? Something like "If you're using MySQL, and you're unable to change the default storage engine, also download SomeMySQLInstallScriptsPackage.zip and unzip that into /some/install/scripts/path before running startup.sh."
Jun 09
David Corley says:
Our upgrade from 2.10.3 to 3.0 didn't work. We're using MySQL 5.1.31 with JDBC D...Our upgrade from 2.10.3 to 3.0 didn't work. We're using MySQL 5.1.31 with JDBC Driver 5.1.7
The logs show the following:
==================
2009-06-09 11:24:49,057 ERROR execute Unsuccessful: create index sp_comp_idx on SPACEPERMISSIONS (PERMTYPE, PERMGROUPNAME, PERMUSERNAME)
2009-06-09 11:24:49,059 ERROR execute Specified key was too long; max key length is 1000 bytes
2009-06-09 11:24:49,060 ERROR execute could not complete schema update
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
==================
Anyone else seeing this?
Jun 15
Azwandi Mohd Aris says:
Hi David, Sorry if this comes in a little late. I believe that you have encount...Hi David,
Sorry if this comes in a little late. I believe that you have encountered a known bug for MySQL using MyISAM engine. Please refer to Upgrade to Confluence 3.0 with MySQL database fails with messages like "specified key was too long" for more information.
Cheers,
Awandi
Add Comment