Originally posted by: Nathan
....
I changed the behavior of firebird based on http://liquibase.jira.com/browse/CORE-405. Is there some versions of firebird that supports ddl in transactions and others that don’t?
Nathan
Firebird (all versions AFAIK) do support transactions around DDL statements. However, a database object cannot be used by another DDL statement, or by a DML statement until the object actually exists via a COMMIT.
When liquibase is run against an empty database, liquibase CREATEs the DATABASECHANGELOGLOCK, and then attempts to INSERT a row into that table w/o an intervening call to commit().
This results in the INSERT failing. (and the CREATE getting rolled back as part of the failed transaction.)
Using the output of the liquibase updateSQL operation on an empty database results in failure as well.
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: dbschema/APS-DBChangeLog.xml
-- Ran at: 6/8/10 1:32 PM
-- Against: sysdba@jdbc:firebirdsql:java://localhost:3050/apsdev
-- LiquiBase version: 2.0-rc2-SNAPSHOT
-- *********************************************************************
-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED SMALLINT NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));
INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);
-- Lock Database
-- Create Database Change Log Table
CREATE TABLE DATABASECHANGELOG (ID VARCHAR(63) NOT NULL, AUTHOR VARCHAR(63) NOT NULL, FILENAME VARCHAR(200) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME), CONSTRAINT UQ_DBCL_ORDEREXEC UNIQUE (ORDEREXECUTED));
-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED SMALLINT NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));
INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);
Note that the DATABASECHANGELOGLOCK table is CREATEd twice (which will fail if I turn on AUTOCOMMIT).
I believe this may be related to a fix for http://liquibase.jira.com/browse/CORE-493?
With autocommit off, the migration fails with the following Firebird output:
-- Create Database Lock Table
CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED SMALLINT NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))
Prepare time: 0.046s
Plan not available.
Executing...
Done.
608 fetches, 82 marks, 29 reads, 0 writes.
21 inserts, 2 updates, 0 deletes, 49 index, 1 seq.
Delta memory: 183148 bytes.
RDB$FIELDS: 4 inserts.
RDB$INDEX_SEGMENTS: 1 inserts.
RDB$INDICES: 1 inserts.
RDB$RELATION_FIELDS: 4 inserts.
RDB$RELATIONS: 1 inserts. 2 updates.
RDB$USER_PRIVILEGES: 5 inserts.
RDB$RELATION_CONSTRAINTS: 3 inserts.
RDB$CHECK_CONSTRAINTS: 2 inserts.
Total execution time: 0.062s
Preparing query:
INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0)
Error: *** IBPP::SQLException ***
Context: Statement::Prepare(
INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0) )
Message: isc_dsql_prepare failed
SQL Message : -204
Undefined name
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -204
Table unknown
DATABASECHANGELOGLOCK
At line 3, column 13
This error occurs because there is no commit between the CREATE (DDL) and the INSERT (DML) statements while create/populate the DATABASECHANGELOGLOCK.
The output of the “updateSQL” command never seem to include transactional statements like BEGIN WORK, COMMIT WOR, or ROLLBACK.
Is the intention to run the entire changelog as a single transaction?
It appears to me that the intent is for each changeSet to be a transaction, based on the call to commit() on line 416 in http://liquibase-core/src/main/java/liquibase/database/AbstractDatabase.java (r1462).
