How to upgrade to Confluence 5.9?

[Sergei Dorogin]

I have an very old Confluence - 3.1.2 which I want to upgrade to the latest (5.9 at the moment).

My data is in MS SQL Server DB with Cyrillic_General_CI_AS collation.

I tried to make export/import steps through 3.1.2 -> 3.5 -> 5.0.3 -> 5.9. But it doesn't work. I successfully imported my data into 5.0.3 instance but importing into 5.9 fails. Then I find some into that export/import are supposed to work only inside one major version. So it's an option.

Then I started to make series of upgrades:

1. I setup a blank 3.1.2 and restored my backup from production version
2. upgraded to 3.5: setup a blank 3.5 and point it to home from 3.1.2). Confluence successfully upgraded my 3.1.2 to 3.5 (home/db)
3. upgrade to 5.0.3: setup a blank 5.0.3 and point it to home from 3.5. Confluence successfully upgraded my 3.5 to 5.0.3 (home/db)

Now I need to make the last step - upgrade to 5.9.

First of all I tried the similar method  - setup blank 5.9 and point it to the previous home. But it fails on start with the error: "checkConfigurationOnStartup Database collation is incorrect, it must be set to 'SQL_Latin1_General_CP1_CS_AS'"

5.9 requires "SQL_Latin1_General_CP1_CS_AS" collation. This KB https://confluence.atlassian.com/confkb/can-t-start-or-upgrade-confluence-due-to-an-error-with-ms-sql-server-database-collation-600114597.html  says that I need to just chang collation. I changed:

ALTER DATABASE wiki COLLATE SQL_Latin1_General_CP1_CS_AS

I started 5.9 again.

Upgrading failed with the errors:

1. failed during the SCHEMA_UPGRADE phase due to: Unable to complete user mapping creation.
   Caused by: org.springframework.dao.TransientDataAccessResourceException: StatementCallback; SQL [select distinct t.PERMUSERNAME from SPACEPERMISSIONS t left outer join user_mapping um1 on t.PERMUSERNAME = um1.user_key left outer join user_mapping um2 on t.PERMUSERNAME = um2.lower_username where um1.user_key is null and um2.user_key is null]; Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Cyrillic_General_CI_AS" in the equal to operation.; nested exception is java.sql.SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Cyrillic_General_CI_AS" in the equal to operation.

So it's kind of a deadlock. I can't leave the collation as it require a new one, and if I changed to that new one it fails anyway .

But it's not the only error there're other ones:

2.

2016-03-04 21:06:13,693 WARN [localhost-startStop-1] [atlassian.plugin.impl.AbstractPlugin] enable Because of this exception

com.atlassian.plugin.osgi.container.OsgiContainerException: Cannot start plugin: remotable.plugins.i18n

3.

2016-03-04 21:06:36,564 ERROR [StorageToAoMigrationUpgradeTask-PageFinder] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Invalid column name 'HIBERNATEVERSION'.

2016-03-04 21:06:36,566 ERROR [StorageToAoMigrationUpgradeTask-PageFinder] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Invalid column name 'HIBERNATEVERSION'.

2016-03-04 21:06:36,568 ERROR [StorageToAoMigrationUpgradeTask-PageFinder] [plugins.tasklist.upgradetask.InterruptingUncaughtExceptionHandler] uncaughtException Uncaught exception in thread StorageToAoMigrationUpgradeTask-PageFinder. Interrupting all threads.

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLException: Invalid column name 'HIBERNATEVERSION'.

Upgrade process never finishes, it hangs:

2016-03-04 21:06:53,418 INFO [localhost-startStop-1] [confluence.upgrade.impl.DefaultUpgradeGate] waitForBooleanValue Waiting to find if plugin dependent upgrades are complete. Maximum wait time will be 1800 seconds.

Confluence isn't responding.

How upgrading to 5.9 is suppose to work?

[NotTheRealStephenSifersNOPENOPENOPENOPE]

See is this helps the DB issues:

https://confluence.atlassian.com/confkb/how-to-fix-the-collation-of-a-microsoft-sql-server-confluence-database-687216725.html

[Sergei Dorogin]

I did that the KB describes. But the import in described conditions fails with errors:

"The column XX cannot be processed because more than one code page (1251 and 1252) are specified for it".

This error happens for every varchar column  in every table.

I googled this error and found out that everybody recommend specifying setting "AlwaysUseDefaultCodePage=true" for "data flow component".

It takes some time to understand what "data flow component" is and where the setting can be specified as in Export/Import wizard they are absent. It turned out that there's SQL Server BI Studio which now distributed separately  from SQL Server 2014. I installed it and opened SSIS package create the Export/Import wizard. In BI Studio it's possible to see what a SSIS packages consist of. I found that setting - AlwaysUseDefaultCodePage and specified it for every "data flow" tasks. Run. Success. Wow.

But.

All after migration non English text look like:

confluence_after_ssis_import.png

DB collation is SQL_Latin1_General_CP1_CS_AS so do for all columns. All my text was in 1251 now in 1252. And it's broken.

I just can't understand how can it work.