无法执行从 SonarQube 5.6.1 LTS 到 6.0 的数据库迁移

2022-01-17 00:00:00 sonarqube sql-server sonarqube-ops

我最近尝试从 SonarQube 5.6.1 LTS 升级到 6.0.

i recently tried to upgrade from SonarQube 5.6.1 LTS to 6.0.

我使用了升级指南，但遇到了数据库迁移错误.

I used the Upgrade Guide but i have got an database migration error.

执行数据库迁移失败:org.sonar.db.version.v60.CleanUsurperRootComponentscom.microsoft.sqlserver.jdbc.SQLServerException:无法解决等于操作中SQL_Latin1_General_CP1_CS_AS"和Latin1_General_CS_AS"之间的排序规则冲突.

Fail to execute database migration: org.sonar.db.version.v60.CleanUsurperRootComponents com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CS_AS" in the equal to operation.

我正在使用带有 SQL_Latin1_General_CP1_CS_AS 的 SQL 2014.

I'm usings SQL 2014 with SQL_Latin1_General_CP1_CS_AS.

我使用以下指南进行设置:

I used for the setup the following guide:

SonarQube Setup Gruide for .Net Users v.1.3

Sonar 数据库中有 157 列使用错误的排序规则Latin1_General_CS_AS".

There are 157 columns with the wrong collation "Latin1_General_CS_AS" in the Sonar database.

问候

丹尼尔

推荐答案

选项 1:从一个新的空数据库开始.

Option 1: Start with a new and empty database.

选项 2:恢复 SonarQube 数据库并使用 SQL Management Studio 更改数据库排序规则(假设数据库名为 SonarQube)，然后再次开始升级:

Option 2: Restore the SonarQube database and change the database collation using SQL Management Studio (assuming that the database is called SonarQube) before starting the upgrade again:

 -- Show current collation
 USE [master]
 GO

 SELECT [collation_name]
 FROM   [sys].[databases]
 WHERE  name = 'SonarQube'
 GO

结果应该是Latin1_General_CS_AS.如果查询返回 SQL_Latin1_General_CP1_CS_AS 在升级到 6.0 之前更改数据库排序规则:

The result should be Latin1_General_CS_AS. If the query returns SQL_Latin1_General_CP1_CS_AS change the database collation before upgrading to 6.0:

USE [master]
GO

ALTER DATABASE [SonarQube] SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE [SonarQube] COLLATE Latin1_General_CS_AS;  
ALTER DATABASE [SonarQube] SET MULTI_USER
GO

选项 3:(最后的手段)更改数据库排序规则(参见选项 2)并手动更改数据库(使用 SQL Management Studio).首先执行以下查询

Option 3: (last resort) Change the database collation (see option 2) and change the database manually (using SQL Management Studio). First execute the following query

USE [SonarQube]
GO

SELECT  '[' + SCHEMA_NAME(t.[schema_id]) + '].[' + t.[name] + '] -> ' + c.[name]
,       'ALTER TABLE [' + SCHEMA_NAME(t.[schema_id]) + '].[' + t.[name] + ']
          ALTER COLUMN [' + c.[name] + '] ' + UPPER(tt.name) + 
         CASE WHEN t.name NOT IN ('ntext', 'text') 
             THEN '(' + 
                 CASE 
                     WHEN tt.name IN ('nchar', 'nvarchar') AND c.max_length != -1 
                         THEN CAST(c.max_length / 2 AS VARCHAR(10))
                     WHEN tt.name IN ('char', 'varchar') AND c.max_length != -1 
                         THEN CAST(c.max_length AS VARCHAR(10))
                     WHEN tt.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1 
                         THEN 'MAX'
                     ELSE CAST(c.max_length AS VARCHAR(10)) 
                 END + ')' 
             ELSE '' 
         END + ' COLLATE Latin1_General_CS_AS' + 
         CASE WHEN c.[is_nullable] = 1 
             THEN ' NULL'
             ELSE ' NOT NULL'
         END
 FROM  [sys].[columns] c
 JOIN  [sys].[tables]  t ON c.[object_id] = t.[object_id]
 JOIN  [sys].[types]   tt ON c.[system_type_id] = tt.[system_type_id] AND c.[user_type_id] = tt.[user_type_id]
 WHERE c.[collation_name] IS NOT NULL
 AND   c.[collation_name] != 'Latin1_General_CS_AS'
 AND   t.[type] = 'U'
 GO

这将返回几行；例如:

ALTER TABLE [dbo].[resource_index] 
ALTER COLUMN [root_component_uuid] NVARCHAR(50) COLLATE Latin1_General_CS_AS NOT NULL

某些列无法以这种方式更改，因为该列用于必须首先删除的索引中.例如:

Some columns cannot be changed in this way because the column is used in an index that must be dropped first. For example:

-- Pay attention: generate script first!
DROP INDEX [resource_index_component] ON [dbo].[resource_index]
GO

ALTER TABLE [dbo].[resource_index] 
ALTER COLUMN [component_uuid] NVARCHAR(50) COLLATE Latin1_General_CS_AS NOT NULL
GO

-- Generate the create script in SQL Management Studio...
CREATE NONCLUSTERED INDEX [resource_index_component] 
  ON [dbo].[resource_index] ([component_uuid] ASC) 
  WITH (...) ON ...
GO

重新启动 SonarQube 并再次开始升级.

Restart SonarQube and start the upgrade again.

相关文章