Octopus 2018.8.8 has index key longer than 900 bytes (IX_ServerTask_Common)


(kbrandenburg) #1

During our recent Octo-Upgrade (link) we found a bad-practice on SQL server indexes. Changescript-91 created a index, with a key that allows for values beyond 900 bytes, which can lead to insert errors in the application.
It would be advisable to reduce the key-length to less than 900 bytes.

Upgrade log shows the following:

2018-10-15 06:39:21.7462 6780 8 INFO Executing Database Server script ‘Octopus.Core.UpgradeScripts.Script0091 - Move ServerTask.ServerNode to ServerTask.ServerNodeId.sql’
2018-10-15 06:39:21.8152 6780 8 INFO Transforming all ServerTasks to the new format. This may take a few minutes…
2018-10-15 06:39:21.8752 6780 8 INFO Creating Tmp_ServerTask table…
2018-10-15 06:39:22.7843 6780 8 INFO Copying ServerTask data to Tmp_ServerTask table…
2018-10-15 06:39:22.8593 6780 8 INFO Renaming table Tmp_ServerTask to ServerTask…
2018-10-15 06:39:22.9173 6780 8 INFO Caution: Changing any part of an object name could break scripts and stored procedures.
2018-10-15 06:39:23.2083 6780 8 INFO Transforming ServerNodeId to the correct format…
2018-10-15 06:39:24.1713 6780 8 INFO Recreating indexes and constraints on ServerTask table…
2018-10-15 06:39:26.8985 6780 8 INFO Warning! The maximum key length is 900 bytes. The index ‘IX_ServerTask_Common’ has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.


(Shane Gill) #3

Hi,

Sorry for the delay getting back to you, we missed your post. I’ve opened an issue to address the length of IX_ServerTask_Common: https://github.com/OctopusDeploy/Issues/issues/5045

Cheers,
Shane