TeamCity 9.1.5 database upgrade error

Answered

Hello,

I upgraded from TeamCity 8.1.5 to TeamCity 9.1.5 and in the course of the database upgrade got the following error:

[2016-02-16 22:49:55,961]  ERROR -  jetbrains.buildServer.STARTUP - Exception UnknownUpgradeError in stage UPGRADE: Upgrading error: Unexpected exception SQLException: SQL error when doing: DML query

SQL query: insert into duplicate_fragments (id, file_id, line, offset_info)

select distinct id, file_id, line, offset_info                   

from duplicate_fragments_sh                                      

where id is not null and file_id is not null                     

  and line is not null and offset_info is not null

SQL exception: Incorrect key file for table '/tmp/#sql_613_0.MYI'; try to repair it

jetbrains.buildServer.maintenance.exceptions.UnknownUpgradeError: Upgrading error: Unexpected exception SQLException: SQL error when doing: DML query

SQL query: insert into duplicate_fragments (id, file_id, line, offset_info)

select distinct id, file_id, line, offset_info                   

from duplicate_fragments_sh                                      

where id is not null and file_id is not null                     

  and line is not null and offset_info is not null

SQL exception: Incorrect key file for table '/tmp/#sql_613_0.MYI'; try to repair it

at jetbrains.buildServer.maintenance.StartupProcessor.doUpgrade(StartupProcessor.java:350)

at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStage(StartupProcessor.java:579)

at jetbrains.buildServer.maintenance.StartupProcessor.processConcreteStageSafe(StartupProcessor.java:112)

at jetbrains.buildServer.maintenance.StartupProcessor.processTeamCityLifecycle(StartupProcessor.java:242)

at jetbrains.buildServer.maintenance.StartupProcessor.access$000(StartupProcessor.java:141)

at jetbrains.buildServer.maintenance.StartupProcessor$1.run(StartupProcessor.java)

at java.lang.Thread.run(Thread.java:662)

Caused by: jetbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception SQLException: SQL error when doing: DML query

SQL query: insert into duplicate_fragments (id, file_id, line, offset_info)

select distinct id, file_id, line, offset_info                   

from duplicate_fragments_sh                                      

where id is not null and file_id is not null                     

  and line is not null and offset_info is not null

SQL exception: Incorrect key file for table '/tmp/#sql_613_0.MYI'; try to repair it

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)

at jetbrains.buildServer.serverSide.db.DBFunctions$9.run(DBFunctions.java:901)

at jetbrains.buildServer.serverSide.db.DBFunctions$9.run(DBFunctions.java:898)

at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:72)

at jetbrains.buildServer.serverSide.db.DBFunctions.executeDmlInternal(DBFunctions.java:897)

at jetbrains.buildServer.serverSide.db.DBFunctions.executeDmls(DBFunctions.java:882)

at jetbrains.buildServer.serverSide.versioning.converters.FixDuplicatesSchemaConverter.populateNewTables(FixDuplicatesSchemaConverter.java:50)

at jetbrains.buildServer.serverSide.versioning.converters.TableTransformingBaseConverter.convert(TableTransformingBaseConverter.java:71)

at jetbrains.buildServer.serverSide.versioning.converters.TableTransformingBaseConverter.convert(TableTransformingBaseConverter.java:49)

at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterStepJavaClass(ConvertPerformer.java:173)

at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverterSteps(ConvertPerformer.java:143)

at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverter(ConvertPerformer.java:94)

at jetbrains.buildServer.serverSide.versioning.ConvertPerformer.runConverters(ConvertPerformer.java:82)

at jetbrains.buildServer.serverSide.versioning.VersionManager.upgradeIt(VersionManager.java:401)

at jetbrains.buildServer.maintenance.StartupProcessor$9.run(StartupProcessor.java)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$1.run(TeamCityDatabaseManager.java:706)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager$1.run(TeamCityDatabaseManager.java:703)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:686)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withMaintenanceDBF(TeamCityDatabaseManager.java:703)

at jetbrains.buildServer.maintenance.StartupProcessor.doUpgrade(StartupProcessor.java:368)

... 6 more

Caused by: java.sql.SQLException: Incorrect key file for table '/tmp/#sql_613_0.MYI'; try to repair it

... 35 more

[2016-02-16 22:49:55,962]   INFO -  jetbrains.buildServer.STARTUP - Current stage: TeamCity server startup error

 

How can I fix this?

Thanks,

-Dave

5 comments

Our database architect indicated that the problem was a disk space error for the /tmp dir that MySQL uses. I changed the tmpdir variable in /etc/mysql to point to a larger disk partition and restarted the teamcity server. This caused the following error to be added to the teamcity server log:

etbrains.buildServer.serverSide.db.UnexpectedDBException: Unexpected exception MySQLSyntaxErrorException: SQL error when doing: Export table build_queue

SQL exception: Unknown column 'when_queued' in 'field list'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)

at com.mysql.jdbc.Util.getInstance(Util.java:383)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$4.run(BackupProcessor.java:1212)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$4.run(BackupProcessor.java:1209)

at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:72)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.executeSimpleQuery(BackupProcessor.java:1209)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableRowsExportNormal(BackupProcessor.java:1090)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableRowsExport(BackupProcessor.java:1036)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableExport(BackupProcessor.java:1002)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTablesExport(BackupProcessor.java:969)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processDatabaseExport(BackupProcessor.java:951)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.performBackup(BackupProcessor.java:326)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.access$000(BackupProcessor.java:50)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$1.run(BackupProcessor.java:235)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$1.run(BackupProcessor.java:228)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withDB(TeamCityDatabaseManager.java:845)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.runProcessWithDatabaseAndFiles(BackupProcessor.java:228)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.runProcess(BackupProcessor.java:190)

at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess$1.run(AbstractMaintenanceProcess.java:160)

at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:89)

at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess.run(AbstractMaintenanceProcess.java:157)

at jetbrains.buildServer.serverSide.maintenance.MaintenanceProcessManagerImpl$ProcessProxy.run(MaintenanceProcessManagerImpl.java:160)

at java.lang.Thread.run(Thread.java:662)

caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'when_queued' in 'field list'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)

at com.mysql.jdbc.Util.getInstance(Util.java:383)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$4.run(BackupProcessor.java:1212)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$4.run(BackupProcessor.java:1209)

at jetbrains.buildServer.serverSide.db.DBInternalUtils.rawOperation(DBInternalUtils.java:72)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.executeSimpleQuery(BackupProcessor.java:1209)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableRowsExportNormal(BackupProcessor.java:1090)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableRowsExport(BackupProcessor.java:1036)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTableExport(BackupProcessor.java:1002)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processTablesExport(BackupProcessor.java:969)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.processDatabaseExport(BackupProcessor.java:951)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.performBackup(BackupProcessor.java:326)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.access$000(BackupProcessor.java:50)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$1.run(BackupProcessor.java:235)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor$1.run(BackupProcessor.java:228)

at jetbrains.buildServer.serverSide.db.TeamCityDatabaseManager.withDB(TeamCityDatabaseManager.java:845)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.runProcessWithDatabaseAndFiles(BackupProcessor.java:228)

at jetbrains.buildServer.serverSide.maintenance.BackupProcessor.runProcess(BackupProcessor.java:190)

at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess$1.run(AbstractMaintenanceProcess.java:160)

at jetbrains.buildServer.util.NamedThreadFactory.executeWithNewThreadName(NamedThreadFactory.java:89)

at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProcess.run(AbstractMaintenanceProcess.java:157)

at jetbrains.buildServer.serverSide.maintenance.MaintenanceProcessManagerImpl$ProcessProxy.run(MaintenanceProcessManagerImpl.java:160)

at java.lang.Thread.run(Thread.java:662)

0

I tried the following:

- Creating a dump of the existing database

- Drop/remake the database

- Restore the database backup created before the upgrade.

I now get:

[2016-02-17 00:18:08,110]  ERROR -  jetbrains.buildServer.STARTUP - Version mismatch: Data format of the data directory (699) and the database (658) do not match.

Please ensure that the data directory and the database in use were upgraded/restored consistently and were always used together.

See details at https://confluence.jetbrains.com/display/TCD9/Upgrade.

[2016-02-17 00:18:08,110]   INFO -  jetbrains.buildServer.STARTUP - Current stage: TeamCity server startup error

Is there any way I can run the database upgrade by hand?

 

 

0

After finding a backup of /data/config and copying it into place, I was able to run the upgrade after restarting the db server. BTW, I needed 7GB of space in /tmp in order to do the upgrade. The fact that I had on 2GB originally was the root cause of the problem. 

0

Hello Dave,

There were some changes in database from 8.1.x to 9.0. TeamCity starts a background process to move the entries from the vcs_changes database table to vcs_change table. The data copying increases the size of the raw database storage. It is noted in Upgrade Notes. Please review this page before starting the upgrade next time.

Sorry for the inconvenience. 

0

The specific upgrade problems were as follows:

  • The database server (mysql 5.5) needed about 8GB to do the upgrade.
  • It started the upgrade which modified the data/config dir and partially upgraded the db and then failed part way through causing the database to be useless.
  • I restored the database backup but because the config dir was already upgraded was unable to continue until I restored just the config dir. After that and changing the location of the db server's mysql temp dir, I was able to proceed with the upgrade.

Had I known that the config dir was upgraded and that a rollback only consisted of just the config dir + the database, I would have saved hours of time.

-Dave

 

0

Please sign in to leave a comment.