Change to table "components" fails on MS SQL Server

  • What version are you upgrading from?
    8.9.10 LTS

  • System information (Operating system, Java version, Database provider/version)
    Red Hat Enterprise Linux 8 / OpenJDK 17 / MS SQL Server 2016

  • What’s the issue you’re facing?
    The database schema migration from 8.9.10 to 9.9.0 fails with the following error:

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #6108 ‘Change size of column ‘kee’ in ‘components’’ failed
[…]
Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE components ALTER COLUMN kee NVARCHAR (1000) NULL
[…]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index ‘projects_kee’ is dependent on column ‘kee’.

According to our database administrators, changing the ‘kee’ column to type NVARCHAR(1000) cannot work because:

Is this a known issue? Is there anything we can do?

Small correction: We use MS SQL Server 2019, not 2016.

Hello,

I was not able to reproduce the issue by upgrading from 8.9 to 9.9, even when simulating a component kee of 400 characters (max length for 8.9).
In any case, the offending index projects_kee was dropped in 9.7, so you could remove this index before upgrading to 9.9 if this is blocking.
However, I’m not sure it is the root cause of the issue here. Feel free to come back with more info if you wish additional assistance!

Regards,
Steve MARION

Hello Steve,

thanks for the hint. We will drop the index manually and try again. I will let you know whether this solved our problem or not.

Best regards,
Holger

Hello Steve,

we dropped the index and the update went smoothly. Surprisingly, it was not a problem to create a new index on table components which included columns kee and branch_uuid, which should have even larger entries. We therefore assume that this issue was caused by the fact that the index was created with an older version of SQL Server or some compatibility settings that are in effect.

Best regards,
Holger

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.