Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-7917

Support migration from a case-sensitive database (including HSQL) to a case-insensitive database

    • 10
    • We collect Confluence feedback from various sources, and we evaluate what we've collected when planning our product roadmap. To understand how this piece of feedback will be reviewed, see our Implementation of New Features Policy.

      NOTE: This suggestion is for Confluence Server. Using Confluence Cloud? See the corresponding suggestion.

      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

      or

      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)

            [CONFSERVER-7917] Support migration from a case-sensitive database (including HSQL) to a case-insensitive database

            BillA added a comment -

            Thank you for raising this issue. While I can see how this feature would be useful, we have no plans to implement it in the foreseeable future. In order to set expectations, we're closing this request now. Thanks again for your idea.

            BillA added a comment - Thank you for raising this issue. While I can see how this feature would be useful, we have no plans to implement it in the foreseeable future. In order to set expectations, we're closing this request now. Thanks again for your idea.

            Don Willis added a comment -

            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

            Don Willis added a comment - 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

            Don Willis added a comment -

            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.

            Don Willis added a comment - 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.

            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

            dave (Inactive) added a comment - 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

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

            ALTER DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

            dave (Inactive) added a comment - If you are using a unicode encoded database, then you can use this collation: ALTER DATABASE confluence CHARACTER SET utf8 COLLATE utf8_bin;

            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.

            m@ (Inactive) added a comment - 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.

              Unassigned Unassigned
              mjensen m@ (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: