DBException, build history is lost.

Hey,

Last night our TeamCity server had a exception when attempting to write to the DB, we use the default DB configuration (HSQLDB) .  You will notice that the first warning is due to an agent attempting to register that is already registered.  Why this happened I have no idea, the agent shouldn't be attempting to register at all.  Prior to this a warning indicated that the disk space was running low (it indicated around 1Gb of free space, this morning currently it has 4gB free).  The TeamCity folder is not on the system drive.

[2011-08-30 23:04:42,438]   WARN -    jetbrains.buildServer.AGENT - Registration of agent: cam1-server {id=0, host=127.0.0.1:9092}@dcfa1f failed. Agent with the same name cam1-server {id=1, host=127.0.0.1:9092}@d0480b is already registered since 20/08/11 02:33
[2011-08-30 23:04:44,703]   WARN - r.serverSide.impl.BuildStarter - jetbrains.buildServer.serverSide.db.UnexpectedDBException: SQL error when doing: Executing update with parameters: [17218, bt124, 0] caused SQL error:
SQL query: INSERT INTO build_state (ID, MODIFICATION_ID, BUILD_TYPE_ID, IS_PERSONAL, IS_CANCELED) VALUES (?, NULL, ?, ?, 0)
SQL exception: S1000 General error java.lang.NullPointerException in statement [INSERT INTO build_state (ID, MODIFICATION_ID, BUILD_TYPE_ID, IS_PERSONAL, IS_CANCELED) VALUES (?, NULL, ?, ?, 0)]
jetbrains.buildServer.serverSide.db.UnexpectedDBException: SQL error when doing: Executing update with parameters: [17218, bt124, 0] caused SQL error:
SQL query: INSERT INTO build_state (ID, MODIFICATION_ID, BUILD_TYPE_ID, IS_PERSONAL, IS_CANCELED) VALUES (?, NULL, ?, ?, 0)
SQL exception: S1000 General error java.lang.NullPointerException in statement [INSERT INTO build_state (ID, MODIFICATION_ID, BUILD_TYPE_ID, IS_PERSONAL, IS_CANCELED) VALUES (?, NULL, ?, ?, 0)]
     at org.hsqldb.jdbc.Util.throwError(Util.java:58)
     at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(jdbcPreparedStatement.java:441)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery$4.action(GenericQuery.java:153)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery$4.action(GenericQuery.java:152)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery$8.apply(GenericQuery.java:375)
     at jetbrains.buildServer.serverSide.db.DBFunctions.withDB(DBFunctions.java:1897)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery.underPreparedStatement(GenericQuery.java:371)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery.executeUpdate(GenericQuery.java:151)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery$3.run(GenericQuery.java:137)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery$3.run(GenericQuery.java:136)
     at jetbrains.buildServer.serverSide.db.DBFacade$6.doInConnection(DBFacade.java:408)
     at jetbrains.buildServer.serverSide.db.DBFacade$7.doInConnection(DBFacade.java:432)
     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
     at jetbrains.buildServer.serverSide.db.DBFacade._runSql(DBFacade.java:428)
     at jetbrains.buildServer.serverSide.db.DBFacade.runSql(DBFacade.java:406)
     at jetbrains.buildServer.serverSide.db.queries.GenericQuery.executeUpdate(GenericQuery.java:135)
     at jetbrains.buildServer.serverSide.impl.BuildPromotionImpl.createPromotionInDbIfNeeded(BuildPromotionImpl.java:318)
     at jetbrains.buildServer.serverSide.impl.BuildPromotionImpl.doPersist(BuildPromotionImpl.java:286)
     at jetbrains.buildServer.serverSide.impl.BuildPromotionImpl.persist(BuildPromotionImpl.java:132)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$15.processItem(TopBuildDependencyGraphImpl.java:5)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$15.processItem(TopBuildDependencyGraphImpl.java:2)
     at jetbrains.buildServer.serverSide.impl.dependency.BuildDependencyGraphImpl.traverseBottomUp(BuildDependencyGraphImpl.java:2)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$5.run(TopBuildDependencyGraphImpl.java:1)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$4.run(TopBuildDependencyGraphImpl.java:1)
     at jetbrains.buildServer.serverSide.impl.auth.SecurityContextImpl.runAs(SecurityContextImpl.java:11)
     at jetbrains.buildServer.serverSide.impl.auth.SecurityContextImpl.runAsSystem(SecurityContextImpl.java:29)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.runAsSystem(TopBuildDependencyGraphImpl.java:81)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.traverseBottomUp(TopBuildDependencyGraphImpl.java:3)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.setRevisionsAndModificationIdToPromotion(TopBuildDependencyGraphImpl.java:85)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.doCollectChanges(TopBuildDependencyGraphImpl.java:93)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.access$000(TopBuildDependencyGraphImpl.java:36)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$3.run(TopBuildDependencyGraphImpl.java:1)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$3.run(TopBuildDependencyGraphImpl.java:2)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl$4.run(TopBuildDependencyGraphImpl.java:1)
     at jetbrains.buildServer.serverSide.impl.auth.SecurityContextImpl.runAs(SecurityContextImpl.java:11)
     at jetbrains.buildServer.serverSide.impl.auth.SecurityContextImpl.runAsSystem(SecurityContextImpl.java:29)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.runAsSystem(TopBuildDependencyGraphImpl.java:81)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.collectChangesForGraph(TopBuildDependencyGraphImpl.java:6)
     at jetbrains.buildServer.serverSide.impl.dependency.TopBuildDependencyGraphImpl.collectChangesForGraph(TopBuildDependencyGraphImpl.java:56)
     at jetbrains.buildServer.serverSide.impl.BuildChangesCollector.collectChangesForBuild(BuildChangesCollector.java:26)
     at jetbrains.buildServer.serverSide.impl.BuildStarter$1.run(BuildStarter.java:19)
     at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
     at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
     at java.util.concurrent.FutureTask.run(Unknown Source)
     at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
     at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
     at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: S1000 General error java.lang.NullPointerException in statement [INSERT INTO build_state (ID, MODIFICATION_ID, BUILD_TYPE_ID, IS_PERSONAL, IS_CANCELED) VALUES (?, NULL, ?, ?, 0)]
     ... 50 more



This error occured a number of times.  This morning some of the build configuraiton histories have been lost.  The affected build configurations show a large number of pending changes and have no history.  Inspecting the artifact folder in the TeamCity data directory shows that all the artifacts are still in place, even up to builds occuring last night before the error occured.

This seems to imply that the DB has become corrupted? Surely the DB should survive low disk space without losing all the history, also all the configuration information is not lost, just the history.  Any help would be greatly appreciated.  We do have backups that we will attempt to restore but it would be good to understand the scope and nature of this problem.

Cheers,
Andrew
13 comments
Comment actions Permalink

Andrew,

Teamcity data is stored in two places - a part like configuration and artifact is stored in data directory. Another part like build history is stored in SQL.
It seems your HSQLDB instance become corrupted.
We recommend to restore it from backup, and migrate to more reliable database engine.

Michael

0
Comment actions Permalink

Hi Michael,

We have restored the db and succesfully reverted to the state prior to the crash.  We are looking at a migration, why do you say that other databases are more reliable?  

Cheers,
Andrew

0
Comment actions Permalink

Look at MySQL - it's free, and easy to setup.

0
Comment actions Permalink

Given that you indicate that HSQLDB is not reliable, I presume you have some knowledge of the circumstances in which it is unreliable.  Could you let me know what those circumstances are?

Cheers,
Andrew.

0
Comment actions Permalink

From the HSQLDB's website:


http://hsqldb.org/web/hsqlFAQ.html

How solid is HSQLDB when a machine crashes

HSQLDB employs a redo log for data recovery. All the changes to the database are reflected in this log. Extensive user tests have demonstrated this mechanism to be effective and fail-safe in most cases. For added security, you can backup the database files while the engine is running using the BACKUP DATABASE command.

By default, a FileDescriptor.sync() call is made every 500 milliseconds on the redo log file. If the machine or the Java process is likely to crash often, you can reduce this down to 20 milliseconds for more frequent sync() calls. You can also specify 0 to force a sync() on each commit. This setting can be changed with "SET FILES WRITE DELAY MILLIS m" or the equivalent connection property.

With all the rest of the database files, calls to sync() are made at all critical points to ensure the files are consistent both after a shutdown and after a crash.

0
Comment actions Permalink

Thank you for the info and link Bill, much appreciated.

Cheers,
Andrew

0
Comment actions Permalink

I am currently attempting to migrate to MSSQL but am running into problems:

C:\TeamCity\bin>maintainDB.cmd migrate -T E:\TeamCity\DBMigration\database.properties
TeamCity maintenance tool. Copyright 2010 JetBrains s.r.o. All Rights Reserved.

Command line arguments: migrate -T E:\TeamCity\DBMigration\database.properties
Using TeamCity data directory: E:\TeamCity\Configuration
Using database settings from the TeamCity data directory for source database: E:\TeamCity\Configurat
ion\config\database.properties
Using source database
        database type: MSSQL
        database driver: <not specified>
        connection: jdbc:jtds:sqlserver://cam-sql2008r2:1433/TeamCity
        login as: TeamCity
Cannot proceed with 'migrate' command: Source database is empty or corrupted.
Critical error has occurred during command execution.



My database properties file looks like this:

# This is a sample configuration file for configuring TeamCity to use external database.
# To make it effective, copy it to the "database.properties" file and modify settings
# according to your environment.
# Do not modify this file, it will be overwritten on TeamCity server startup.
# See documentation at http://www.jetbrains.net/confluence/display/TCD5/Setting+up+an+External+Database

# Database: Microsoft SQL server  (via jtds)

connectionUrl=jdbc:jtds:sqlserver://cam-sql2008r2:1433/TeamCity
# connectionUrl=jdbc:sqlserver://cam-sql2008r2:1433;databaseName=TeamCity
connectionProperties.user=TeamCity
connectionProperties.password=xxxxxx


# The maximum number of connections TeamCity can open on database server.
maxConnections=50

# Specifies whether TeamCity should check each jdbc connection before use.
# It reduces the system performace but avoids cases when the system uses
# a connection that was closed by database server due to time out or this
# connection was broken at the previous operation.
testOnBorrow=true


I have attempted to do the migration with both the natice and jtds drivers but get the same result.  I can log into the db with the credentials supplied using Microsoft SQL Server Management studio wihtout a problem and I have confirmed that the server does allow TCP connections.  We also only have the default instance on the db server which is why I don't specifiy an instance.

Any ideas what could be going wrong?

Cheers,
Andrew
0
Comment actions Permalink

You need to specify -S (source database properties)

Options:                                                                      
-A <data-path> or --data-dir=<data-path>                                  
specifies the absolute path to the TeamCity data directory;          
if omitted, value of TEAMCITY_DATA_PATH environment variable or
default path '%HOME%\.BuildServer' is used
-S <file> or --source-db=<file>  (backup and migrate only)
specifies the absolute path to file with the source database properties;
if omitted, <data-path>/config/database.properties is used          
-T <file> or --target-db=<file>  (restore and migrate only)
specifies the absolute path to file with the target database properties;
if omitted, database.properties from the backup file will be used
-F <backup-file> or --backup-file=<backup-file>                           
during backup and migrate: file name (without extension) to save backup to.
during restore: full name of the backup zip archive to read backup from.
Can be absolute or relative to <data-path>/backup directory
-M or --timestamp  (backup only)
adds a timestamp suffix to the backup file name

So -S would point to HSQL db properties and -T point to MSSQL db properties

0
Comment actions Permalink

Thank you very much, the migration is now running.  There is definitely room for some improvement in the docuemntation of the migration process, the options you describe (unless I missed them) are not currently on the migration page.

Cheers,
Andrew

0
Comment actions Permalink

I agree with your statements. I think ran migrateDB.sh with no options to see the available options.

The one problem with migrateDB.sh is that in order to take a backup, TC cannot be running.  We've been using it to do our backups but we're now using the "online" backup via the REST api.

0
Comment actions Permalink

Thank you for feedback.

From the logs I see TeamCity data directory already had modified database.properties file before migration - E:\TeamCity\Configuration\config\database.properties pointed to MS SQL.
In documentation we recommend to create new database.properties file in external location. In This case you don't need to specify additional options for MaintainDB tool - it can connect to source database using default settings, and update database.prooperties file automatically when migration is finished.

0
Comment actions Permalink

Indeed, the problem was that following your documentation in http://confluence.jetbrains.net/display/TCD65/Migrating+to+an+External+Database led me to http://confluence.jetbrains.net/display/TCD65/Setting+up+an+External+Database which instructs you to create said database.properties file.  Bills explanation is a lot clearer, to me at least.  It would be helpful if the different command line options were in your documentation.

In any case I have now managed toi successfully migrate the DB and am running smoothly now, so thank you for the support.

Cheers,
Andrew

0
Comment actions Permalink

Guys - fix the docs ... its an utter, utter mess and I've just been caught out by this as well.

How can your products be SO GOOD and your docs just horrific?  3 YEARS down the line and your docs are still tripping people up!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

0

Please sign in to leave a comment.