Upgrading from 3.2.7 to 3.6 fails

Legacy Uncategorised
1

Hi,

I tried to upgrade our 3.2.7 installation to 3.6. Installation worked by the server stopped after the first request. The logs showed errors saying that a table called MachinePolicy already existed.

Then I tried to first upgrade to 3.3.x instead, where a collation issue was reported in the logs, so the server stopped as well.

Then I tried to upgrade to 3.4.x, where the MachinePolicy issue showed up in the logs again.

I reinstalled 3.2.7 again and everything still works, so I guess no database migrations were executed.

What can I do to get to 3.6?
I’ll attach my log file.
Thanks!

OctopusServer.txt (418 KB)

2

I needed to restore a backup since apparently a part of the migrations were executed. At least there were more tables/columns as before and some with a different collation.

3

I worked now, the upgrade failed due to a collation issue in the database. I resolved it by changing the collation and then upgrade ran through fine.

4

Hi,

Great to hear you figured out a way to get up and running again with 3.6, I’ve raised a GitHub issue (#2976) to have this investigated and fixed in a future release.

Thank you and best regards,
Henrik

5

Just tried to upgrade from 3.3.12 to 3.7.11 total nightmare. Collation errors all over the place. Couldn’t solve it by changing the collation.

6

Hi Ole (and Sebastian),

I’m sorry to hear you’ve had issues upgrading.

Our guess at what is happened here is the default collation on your Octopus Database has been changed at some point. This means some existing objects (tables, views, etc) will have been created with the original collation, while the new objects being created as part of the upgrade are being created with the new default collation. When an is attempt is made to JOIN between them, SQL Server is reporting an error.

To test this hypothesis, would be able to execute the following script against your Octopus Database?

DECLARE @DatabaseCollation VARCHAR(100)

SELECT 
    @DatabaseCollation = collation_name 
FROM 
    sys.databases
WHERE 
    database_id = DB_ID()

SELECT 
    @DatabaseCollation 'Default database collation'

SELECT 
    t.Name 'Table Name',
    c.name 'Col Name',
    ty.name 'Type Name',
    c.max_length,
    c.collation_name,
    c.is_nullable
FROM 
    sys.columns c 
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.types ty ON c.system_type_id = ty.system_type_id    
WHERE 
    t.is_ms_shipped = 0
    AND 
    c.collation_name <> @DatabaseCollation

(taken from this StackOverflow answer.)

If you could report back the results of this it would be much appreciated. We are investigation ways we can prevent this from occurring in the future.

Regards,
Michael

7

Collation wasn’t changed but we changed sql server a while ago.

Default collation is now SQL_Latin1_General_CP1_CI_AS and all table columns is Danish_Norwegian_CI_AS

So the solutions is to change default collation to Danish_Norwegian_CI_AS before trying to upgrade?

8

When you changed SQL Server, the default collation of the database must have been changed.

Yes, the best option is to convert the default collation of the database to Danish_Norwegian_CI_AS to match your columns. Once you have done this, you should be able to upgrade successfully.

I hope that helps. We’re sorry for the inconvenience. As I mentioned, we are implementing this in an attempt to prevent these issues in the future.

Regards,
Michael