Derby fails to create databasechangelog tables

Description

When using Derby as the target database, liquibase fails to create the DatabaseChangeLog table.

liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)); on jdbc:derby://localhost:1527/emconex;create=trueINSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE): Syntax error: BOOLEAN.
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70)
at liquibase.database.AbstractDatabase.checkDatabaseChangeLogLockTable(AbstractDatabase.java:564)
at liquibase.lockservice.LockService.acquireLock(LockService.java:94)
... 116 more
Caused by: java.sql.SQLSyntaxErrorException: Syntax error: BOOLEAN.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Statement.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
... 120 more
Caused by: org.apache.derby.client.am.SqlException: Syntax error: BOOLEAN.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.am.Statement.completeExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseEXCSQLIMMreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.StatementReply.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.net.NetStatement.readExecuteImmediate_(Unknown Source)
at org.apache.derby.client.am.Statement.readExecuteImmediate(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeX(Unknown Source)
... 125 more

Environment

None

Activity

Show:
Rafał Krzewski
March 14, 2012, 12:18 AM

Derby supports BOOLEAN type since release 10.7.1.1 See https://issues.apache.org/jira/browse/DERBY-499

It does not support however inserting BOOLEAN values into SMALLINT fields.

Here's the exception I am getting:

liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE APP.DATABASECHANGELOGLOCK (ID int
NOT NULL, LOCKED SMALLINT NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY varchar(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)); on j
dbc:derby:target/derby/jpa-sampleINSERT INTO APP.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE): Columns of type 'SMALLINT' cannot hold
values of type 'BOOLEAN'.
at liquibase.lockservice.LockService.acquireLock(LockService.java:121)
at liquibase.lockservice.LockService.waitForLock(LockService.java:61)
at liquibase.Liquibase.update(Liquibase.java:106)

Undoing the change 76883d54 fixes this problem. A more comprehensive fix is needed, taking Derby database version into account.

I'd like to request this issue to be reopened.

Nathan Voxland
March 15, 2012, 5:07 PM

If newer derby versions support boolean, that is a better fix

A
March 22, 2012, 2:18 PM

I have the same issue on some older derby databases that need updating - The specific updates were actually to modify tables to use boolean rather than smallint columns.

This cannot be done through liquibase (using <sql> tags), because it is suffering from the exact same issue, ie. it created tables with smallint and is now trying to insert a boolean during locking. Currently the only solution is to use jdbc directly before using liquibase - kind of defeats the object.

The following sql is useful for testing for the presence of either a smallint or boolean column type without looking up meta-data:

select min(boolorsmallcol) as test from mytable fetch first row only - Returns 0 or false

The result can be used to determine if an update is required. I think liquibase should run this check on the lock tables and update them accordingly.

Note: It is not possible to 'alter' such column types in derby, rather add update drop.

Andy Gumbrecht
May 13, 2013, 12:32 PM

A related issue here is that lock tables created with a version prior to 2.0.3 used SMALLINT and later versions assume it is a BOOLEAN. The lock table meta-data should also be used to determine the insert/update type.

Nathan Voxland
October 3, 2013, 5:31 PM

Derby will now use BOOLEAN for versions that support it and will update the databsechangeloglock table if it is in smallint format

Reporter

P

Components

Fix versions

Affects versions

Priority

Major
Configure