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!
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.
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.
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
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.