[#CONF-7917] Problems migrating from an embedded database to an external database

Confluence

Problems migrating from an embedded database to an external database

Created: 20/Feb/07 09:20 PM Updated: Thursday 06:21 AM

Component/s:

Backup / Restore

Affects Version/s:

2.3

Fix Version/s:

None

Time Tracking:

Not Specified

Participants:	Dave Loeng [Atlassian], Don Willis [Atlassian], Ivan Benko [Atlassian] and Matthew Jensen [Atlassian]
Since last comment:	48 weeks, 3 days ago
Internal Complexity:	4
Internal Value:	5
Labels:
Support reference count:	10

Description

« Hide

Different databases have different settings for case sensitivity. The case sensitivity of the database is usually set through the collation that it uses.

The default embedded database (HSQL) in Confluence is a CASE SENSITIVE database. When you attempt to migrate this data to an external database that is set up with a case insensitive collation, you may experience problems.

You may encounter this error for example:

The data import process reported the duplicate error on the wrong object:
Import failed. (HibernateTemplate): data integrity violated by SQL ''; nested exception is java.sql.BatchUpdateException: Duplicate entry 'OSUser_user-11-confluence.prefs.email.notify' for key 1

2007-02-21 15:02:03,676 ERROR [sf.hibernate.util.JDBCExceptionReporter] logExceptions Violation of PRIMARY KEY constraint 'PK_OS_PROPERTYENTRY_619B8048'. Cannot insert duplicate key in object 'OS_PROPERTYENTRY'.
2007-02-21 15:02:03,692 ERROR [confluence.importexport.impl.ReverseDatabinder] endElement net.sf.hibernate.exception.ConstraintViolationException: could not insert: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@d88630dc]
net.sf.hibernate.exception.ConstraintViolationException: could not insert: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@d88630dc]
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:62)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)

All

Comments

Work Log

Change History

Sort Order:

[ Permalink | « Hide ]

Matthew Jensen [Atlassian] added a comment - 04/Mar/07 08:08 PM

If this issue is seen when importing into MySQL you can overcome it by changing the collation of the database like this:

ALTER DATABASE <your database name> COLLATE latin1_general_cs

This will cause MySQL to perform all its comparisons in a case sensitive mode.

[ Permalink | « Hide ]

Dave Loeng [Atlassian] added a comment - 21/Mar/07 12:28 AM

If you are using a unicode encoded database, then you can use this collation:

ALTER DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

[ Permalink | « Hide ]

Dave Loeng [Atlassian] added a comment - 21/Mar/07 12:36 AM

Here's a doc on changing collations in MS SQL Server:

http://msdn2.microsoft.com/en-us/library/ms175835.aspx

Here's a list of available collations in MS SQL Server:

http://msdn2.microsoft.com/en-us/library/ms144250.aspx

[ Permalink | « Hide ]

Don Willis [Atlassian] added a comment - 15/Apr/08 03:00 AM

This problem strikes down quite a few customers migrating to MySQL. The simplest fix would be for us to check the case sensitivity of the database by some sort of test insert or query and warn the user that they should set their collation to be case sensitive if it isn't. Note that moving from a case-insensitive to a case-sensitive database is not dangerous, but the other way round is.

[ Permalink | « Hide ]

Don Willis [Atlassian] added a comment - 30/Jul/08 02:44 AM

The ALTER DATABASE x CHARACTER SET y COLLATE z command on MySQL actually only sets the default collation for newly created tables. Tables that have already been created will need to be updated individually using ALTER TABLE x COLLATE z