TeamCity has failed to start after 2019.1 to 2019.2 upgrade

Answered

<pre>jetbrains.buildServer.maintenance.exceptions.UnknownUpgradeError: Upgrading error: Unexpected exception: SQL error when doing 'Executing a SQL statement' while performing SQL query: SQL DDL: drop index status_index_light on light_history: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index 'light_history.status_index_light', because it does not exist or you do not have permission.
at jetbrains.buildServer.maintenance.StartupProcessor.doUpgrade(StartupProcessor.java:955)
at jetbrains.buildServer.maintenance.StartupProcessor.access$1600(StartupProcessor.java:1297)
at jetbrains.buildServer.maintenance.StartupProcessor$3.call(StartupProcessor.java:23)
at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:91)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStage(StartupProcessor.java:533)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStageSafe(StartupProcessor.java:873)
at jetbrains.buildServer.maintenance.StartupProcessor.processTeamCityLifecycle(StartupProcessor.java:549)
at jetbrains.buildServer.maintenance.StartupProcessor.access$000(StartupProcessor.java:1070)
at jetbrains.buildServer.maintenance.StartupProcessor$1.run(StartupProcessor.java:3)
at java.lang.Thread.run(Thread.java:748)
caused by: jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception: SQL error when doing 'Executing a SQL statement' while performing SQL query: SQL DDL: drop index status_index_light on light_history: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index 'light_history.status_index_light', because it does not exist or you do not have permission.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:729)
at jetbrains.buildServer.serverSide.db.DBFunctions$12.run(DBFunctions.java:1124)
at jetbrains.buildServer.serverSide.db.DBFunctions$12.run(DBFunctions.java:1119)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:70)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:55)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:1119)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:1104)
at jetbrains.buildServer.serverSide.db.DBFunctionsForMSSQL.executeSimpleInternal(DBFunctionsForMSSQL.java:243)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeParsedDdls(DBFunctions.java:1070)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeDdls(DBFunctions.java:1053)
at jetbrains.buildServer.serverSide.versioning.converters.OptimizeHistoryTableIndexesConverter.convert(OptimizeHistoryTableIndexesConverter.java:14)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterStepJavaClass(ConvertPerformer.java:195)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterSteps(ConvertPerformer.java:161)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverter(ConvertPerformer.java:111)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverters(ConvertPerformer.java:87)
at jetbrains.buildServer.serverSide.versioning.VersionManager.upgradeIt(VersionManager.java:381)
at jetbrains.buildServer.maintenance.StartupProcessor$UpgradePerformer.doUpgrade(StartupProcessor.java:3)
at jetbrains.buildServer.maintenance.StartupProcessor.lambda$doUpgrade$10(StartupProcessor.java:177)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$3.run(TeamCityDatabaseManager.java:934)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$3.run(TeamCityDatabaseManager.java:929)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:913)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:929)
at jetbrains.buildServer.maintenance.StartupProcessor.doUpgrade(StartupProcessor.java:898)
at jetbrains.buildServer.maintenance.StartupProcessor.access$1600(StartupProcessor.java:1297)
at jetbrains.buildServer.maintenance.StartupProcessor$3.call(StartupProcessor.java:23)
at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:91)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStage(StartupProcessor.java:533)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStageSafe(StartupProcessor.java:873)
at jetbrains.buildServer.maintenance.StartupProcessor.processTeamCityLifecycle(StartupProcessor.java:549)
at jetbrains.buildServer.maintenance.StartupProcessor.access$000(StartupProcessor.java:1070)
at jetbrains.buildServer.maintenance.StartupProcessor$1.run(StartupProcessor.java:3)
at java.lang.Thread.run(Thread.java:748)
caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot drop the index 'light_history.status_index_light', because it does not exist or you do not have permission.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:729)
at jetbrains.buildServer.serverSide.db.DBFunctions$12.run(DBFunctions.java:1124)
at jetbrains.buildServer.serverSide.db.DBFunctions$12.run(DBFunctions.java:1119)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:70)
at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:55)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:1119)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeSimpleInternal(DBFunctions.java:1104)
at jetbrains.buildServer.serverSide.db.DBFunctionsForMSSQL.executeSimpleInternal(DBFunctionsForMSSQL.java:243)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeParsedDdls(DBFunctions.java:1070)
at jetbrains.buildServer.serverSide.db.DBFunctions.executeDdls(DBFunctions.java:1053)
at jetbrains.buildServer.serverSide.versioning.converters.OptimizeHistoryTableIndexesConverter.convert(OptimizeHistoryTableIndexesConverter.java:14)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterStepJavaClass(ConvertPerformer.java:195)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterSteps(ConvertPerformer.java:161)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverter(ConvertPerformer.java:111)
at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverters(ConvertPerformer.java:87)
at jetbrains.buildServer.serverSide.versioning.VersionManager.upgradeIt(VersionManager.java:381)
at jetbrains.buildServer.maintenance.StartupProcessor$UpgradePerformer.doUpgrade(StartupProcessor.java:3)
at jetbrains.buildServer.maintenance.StartupProcessor.lambda$doUpgrade$10(StartupProcessor.java:177)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$3.run(TeamCityDatabaseManager.java:934)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$3.run(TeamCityDatabaseManager.java:929)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:913)
at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:929)
at jetbrains.buildServer.maintenance.StartupProcessor.doUpgrade(StartupProcessor.java:898)
at jetbrains.buildServer.maintenance.StartupProcessor.access$1600(StartupProcessor.java:1297)
at jetbrains.buildServer.maintenance.StartupProcessor$3.call(StartupProcessor.java:23)
at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:91)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStage(StartupProcessor.java:533)
at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStageSafe(StartupProcessor.java:873)
at jetbrains.buildServer.maintenance.StartupProcessor.processTeamCityLifecycle(StartupProcessor.java:549)
at jetbrains.buildServer.maintenance.StartupProcessor.access$000(StartupProcessor.java:1070)
at jetbrains.buildServer.maintenance.StartupProcessor$1.run(StartupProcessor.java:3)
at java.lang.Thread.run(Thread.java:748)
</pre>

7 comments
Comment actions Permalink

Hi,

 

during the start after an upgrade, teamcity runs a data upgrade. This updates the data structures on the XML files in the data directory as well as the tables in the database to make sure they are in line with the data structures used in the code. With this in mind, make sure that the user that is used to connect to the database has permissions to alter the database structure, as it's required during this process.

 

If the user has the appropriate permissions, could you check whether the indexes exist at all in the database? They should unless the database has been manually modified and this is likely just a permissions issue, but just in case.

0
Comment actions Permalink

Hi Denis,

Thanks for the quick reply. I checked the permissions multiple times, the user has access to the database but the index doesn't exist. I did try to upgrade from 2019.1.1 to 2019.2.2, skipping many minor updates. Do you think that might be the case? Not sure on which version `index status_index_light` was introduced to light_history in the database.

0
Comment actions Permalink

The upgrade process should be aware of that and skipping versions should be just fine. I just tried upgrading a 2019.1.1 installation to 2019.2.2 and it has worked just fine on my end. If permissions are ok (just to make sure, it's not just access, it needs permissions to alter the database), and the indexes aren't present, it usually means that something external has altered it. We had an issue in the past where this exact index was problematic to be dropped, but it was fixed in 2019.2 and it should be fixed in 2019.2.2 (and we have not received any external report about it, we detected it internally and fixed it prior to the release).

 

I'm going to pass this along for investigation, in the meantime, typically the process will roll back the upgrade after the failure and will try again on the next startup. You might be able to create the missing indexes as a workaround so that the next attempt succeeds. Could you try that?

1
Comment actions Permalink

A quick follow up.

 

After some internal discussion, this can happen without external interference when using the maintainDb script to restore a backup or move from the internal database to the external one, if the script fails to create the indexes. This errors should be logged but because the process succeeds it's easy to overlook. There should be error logs, teamcity-maintenance.log or teamcity-upgrade.log might contain some of that information.

 

If this is the case, it's likely that this will not be the only missing index, and while restoring the missing ones for the upgrade process will allow you to continue moving forward, it's possible that some indexes might be missing that are actually useful for teamcity's functionality. If there are very many missing indexes, a possibility would be to create a backup of the database content, set up another new database for teamcity, then restore on top of that database where the indexes are properly created.

1
Comment actions Permalink

You are right about other missing indexes. When I tried restoring the database from a backup, maintainDb threw some errors about not being able to create indexes( 3 of them, if I remember correctly). I deleted the tables to restore the database, and it worked. I checked the indexes just now and the status_index_light actually exists. I'll try upgrading again this weekend, and hopefully, it will go well. The teamcity user has db_owner role, so it must be able to alter the database (unless I'm missing any other permissions)

Thanks for looking deeper into this, I really appreciate it!

0
Comment actions Permalink

I'm also having this issue after trying to upgrade to 2019.2.2. Can you please assist in fixing the problem?

0

Please sign in to leave a comment.