updateSQL on a fresh database

General Discussion
1

I have an issue when using liquibase:updateSQL goal in maven.
I have a fresh database and this is the first time liquibase accesses the database.

When using mvn install liquibase creates the two tables DATABASECHANGELOG and DATABASECHANGELOGLOCK into the database,
and it also adds the creation of these tables to the migration script that is generated alongwith other changes that need to be applied to the database.

    – Create Database Lock Table
    CREATE TABLE my_db.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED TINYINT(1) NOT NULL, LOCKGRANTED DATETIME, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

    INSERT INTO my_db.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0);

    SELECT LOCKED FROM my_db.DATABASECHANGELOGLOCK WHERE ID=1;

    – Lock Database
    UPDATE my_db.DATABASECHANGELOGLOCK SET LOCKEDBY = ‘192.168.1.110’, LOCKGRANTED = ‘2010-10-01 11:50:14.140’, LOCKED = 1 WHERE ID  = 1;

    – Create Database Change Log Table
    CREATE TABLE my_db.DATABASECHANGELOG (ID VARCHAR(63) NOT NULL, AUTHOR VARCHAR(63) NOT NULL, FILENAME VARCHAR(200) NOT NULL, DATEEXECUTED DATETIME NOT NULL, MD5SUM VARCHAR(32), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(10), CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME));

Obviously either 1 of the two should happen.
Thus when I use the migration script IT WILL throw an error as the two tables DATABASECHANGELOG and DATABASECHANGELOGLOCK already exist.

I know I can manually remove the above sql from the migration.sql file that has been generated. But I don’t want to do that inorder to keep the build automated.

Maybe I have missed something. Any help or solution would be greatly appreciated.

2

What version of liquibase are you running?  1.9.5?

Nathan

3

yes, 1.9.5.0
ideally it would be great if all is done via the migration script and there is no handshake with the db during the maven build process.
so updateSQL should only produce the migration script without creating any tables in the db.

4

Yes.  That is a bug that is in 1.9 that has been fixed for the upcoming 2.0.

Nathan

5

Great!! could you tell me what is the fix that you have done?
So that I can accordingly modify my build.
Also, what is the expected release date for 2.0?

6

The change was wrapped up with the fairly major refactoring that I did for liquibase 2.0.  The easiest approach for you in 1.9 may be a post-processing step in your maven script that reads the generated file in and strips out the bad SQL calls.

Nathan

7

Thanks.
Any idea for the release of 2.0?

8

I just released RC6, hopefully the last one.  I’ll have to see what comes in for bug reports to know for sure, though.

Nathan

9

hi I tired 2.0-rc6 and it’s still doing the same as 1.9.5.0
it connects to the database, creates the two LOG tables

then writes them to the migration script.

I thought you mentioned that, this was a bug and had been removed in version 2.0?
or did I understand incorrectly?

let me know
ideally it would be great if all is done via the migration script and there is no handshake with the db during the maven build process.
so updateSQL should only produce the migration script without creating any tables in the db.

10

I have a similar problem. I use Liquibase 2.0.0 RC6 on a fresh (postgresql) database as well. Running in updateSQL mode, the modifications have not been executed on the database, but
as you can see the sql script generated by Liquibase tries to create the lock table twice.

    -- Create Database Lock Table CREATE TABLE databasechangeloglock (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITH TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

    INSERT INTO databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

    – 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 WITH TIME ZONE 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));

    – Create Database Lock Table
    CREATE TABLE databasechangeloglock (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITH TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

    INSERT INTO databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

11

hey nathan
any info on this?

12

It’s on my list to look at but haven’t gotten to yet.  I created http://liquibase.jira.com/browse/CORE-731 to help track it.

Nathan

13

cool thanks Nathan.
It would be good if the statements are IN the migration script instead during execution of updateSQL
Thus, it might also be useful that you don’t need to provide db url, username and password for updateSQL.

What do you think?

14

Yes, that is what it should do.  UpdateSQL should not actually create the tables, just add to the script that they need to be made.

Nathan

15

Great. so we wouldn’t need to provide db url and user/pass as well. right?

16

You will still need an active database to connect to, since we use the database type and version to determine the correct SQL to generate.  There is a feature request in jira to remove this for blank databases, but it will not be in 2.0.

Nathan

17

surely the version and db type can be provided in the master change log file, isn’t it?