Change to table "components" fails on MS SQL Server

[holgerknoche (Holger Kn)]

• 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:

• it is part of an index,
• NVARCHAR(1000) occupies (at least) 2,000 bytes,
• and index entries in SQL Server are limited to 1,700 bytes (see https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16)

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

[holgerknoche (Holger Kn)]

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

[steve.marion]

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

[holgerknoche (Holger Kn)]

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

[holgerknoche (Holger Kn)]

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