Bamboo HSQL Database Corruption caused by DBVisualizer
Symptoms
This KB only applies if you're using Embedded HSQL Database.
Bamboo fails to start and shows the following in the logs after using a DBVisualizer tool to connect Bamboo DB:
2013-06-17 14:22:36,454 WARN [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] [BasicResourcePool] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@170e3e70 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: error in script file line: 1 Unexpected token UNIQUE, requires COLLATION in statement [SET DATABASE UNIQUE]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbcDriver.connect(Unknown Source)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
...
2013-06-17 14:22:36,455 ERROR [main] [SchemaUpdate] could not get database metadata
java.sql.SQLException: Connections could not be acquired from the underlying database!
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:33)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:138)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:112)
...
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
... 18 moreCause
The embedded HSQL database in BAMBOO_HOME/database/ database is changed by DBVisualizer when using a different driver than the one that Bamboo uses (BambooInstall/webapp/WEB-INF/lib/hsqldb-1.8.0.7.jar).
Resolution
Make sure to use the HSQL 1.8.0 version driver (or whichever version your Bamboo instance is using) for DBVisualizer when connecting to Bamboo HSQL DB in the future. You just need to download and add the correct version of HSQL driver for DBVisualizer (Tolls -> Driver Manager. Duplicate the existing HSQL driver and configure it to point to the newly downloaded version of HSQL driver), and use that version of driver when you need to connect to a Bamboo DB.
In addition, please keep in mind that using the embedded HSQL DB is not recommended for production Bamboo instances as per - Supported platforms.