MSSQL Migration fails

upgrade
database
mssql

(Chris) #1

This bug is in every version of SonarQube past 6.0
sonar-db-migration-6.7.4\org\sonar\server\platform\db\migration\charset\MssqlMetadataReader.class

The above reader class has the decompiled code of:

public String getDefaultCollation(Connection connection) throws SQLException {
    return sqlExecutor.selectSingleString(connection, "SELECT CONVERT(VARCHAR, DATABASEPROPERTYEX(DB_NAME(), 'Collation'))");
  }

The collation required for mssql migration is SQL_Latin1_General_CP1250_CS_AS
The default length of varchar cuts off the ‘S’, so you’d receive an error message in the log that collation SQL_Latin1_General_CP1250_CS_A but needs to be SQL_Latin1_General_CP1250_CS_A.
All following does this have the right collations fail because the ‘S’ is cut off. No migration occurs, and the error message is not descriptive to the problem. My suggested code change:

"SELECT CONVERT(VARCHAR(50), DATABASEPROPERTYEX(DB_NAME(), 'Collation'))"

To reproduce the issue use mssql as a backend db for SonarQube using version 6.0 or less. Analyze one project and install the LTS version 6.7.4. The DB migration will fail.

sonar.log:
org.sonar.api.utils.MessageException: Database collation must be case-sensitive and accent-sensitive. It is SQL_Latin1_General_CP1250_CS_A but should be SQL_Latin1_General_CP1250_CS_A.

After altering the MssqlMetadataReader.class with a hex editor I was able to do the migration from 5.6.6 -> 6.7.4. This bug still appears be in 7.1


(Simon Brandhof) #2

Hi,

Could you please give more details ? I wonder if it’s a configuration specific to your mssql installation. You’re the first one to mention this behavior since version 6.7 has been released few months ago.

Note that SonarQube is open-source. You don’t need to decompile classes. Just read the code from GitHub : https://github.com/SonarSource/sonarqube/blob/branch-6.7/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/charset/MssqlCharsetHandler.java

Regards


(Chris) #4

I used dirty Joe’s hex editor to add VARCHAR(50) and the migration was then a success. I guess I could have forked the open source project, and used maven to rebuild it with my changes.

I clicked next through the DB install. I did set the collation on the DB to SQL_Latin1_General_CP1250_CS_AS, thinking that perhaps the system tables had something to do with the failed migration. I had restored 5.6.6 the DB to several different instances, and all of them had the same issue.

en_sql_server_2016_developer_x64_dvd_8777069.iso

I don’t know how I would change the default length of built in type, but I try not to depend on defaults. I’m betting the default is 30, and that’s why it’s cutting off the S.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017


(Simon Brandhof) #5

Thanks for the investigation. It was not obvious that the CONVERT function truncates to 30 characters by default. This will be fixed in 7.4 (SONAR-11168).


(Chris) #6

No problem, it’s the tyranny of the default. Took a good while to figure out what exactly was happening. Thanks for the support.