DBException, build history is lost. Follow
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
Please sign in to leave a comment.
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
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
Look at MySQL - it's free, and easy to setup.
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.
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.
Thank you for the info and link Bill, much appreciated.
Cheers,
Andrew
I am currently attempting to migrate to MSSQL but am running into problems:
My database properties file looks like this:
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
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
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
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.
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.
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
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!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!