sqlite run liquibase more than once

General Discussion
1

Hey,


I am running liquibase to create some tables initially for my database(other change sets applied later to do other things) I have tested my changelogs for postgres and mysql. But for sqlite it’s strange. The 1st time it works but after that I get the following error


SEVERE 3/30/11 3:09 PM:liquibase: Error executing SQL ALTER TABLE DATABASECHANGELOG ALTER COLUMN MD5SUM TYPE TEXT

java.sql.SQLException: near “ALTER”: syntax error

        at org.sqlite.DB.throwex(DB.java:288)

        at org.sqlite.NativeDB.prepare(Native Method)

        at org.sqlite.DB.prepare(DB.java:114)

        at org.sqlite.Stmt.execute(Stmt.java:82)

        at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:105)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70)

        at liquibase.database.AbstractDatabase.checkDatabaseChangeLogTable(AbstractDatabase.java:436)

        at liquibase.Liquibase.checkDatabaseChangeLogTable(Liquibase.java:535)

        at liquibase.Liquibase.update(Liquibase.java:108)

        at liquibase.integration.commandline.Main.doMigration(Main.java:684)

        at liquibase.integration.commandline.Main.main(Main.java:116)

INFO 3/30/11 3:09 PM:liquibase: Successfully released change log lock

Liquibase Update Failed: near “ALTER”: syntax error

SEVERE 3/30/11 3:09 PM:liquibase: near “ALTER”: syntax error

liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE DATABASECHANGELOG ALTER COLUMN MD5SUM TYPE TEXT: near “ALTER”: syntax error

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:105)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:70)

        at liquibase.database.AbstractDatabase.checkDatabaseChangeLogTable(AbstractDatabase.java:436)

        at liquibase.Liquibase.checkDatabaseChangeLogTable(Liquibase.java:535)

        at liquibase.Liquibase.update(Liquibase.java:108)

        at liquibase.integration.commandline.Main.doMigration(Main.java:684)

        at liquibase.integration.commandline.Main.main(Main.java:116)

Caused by: java.sql.SQLException: near “ALTER”: syntax error

        at org.sqlite.DB.throwex(DB.java:288)

        at org.sqlite.NativeDB.prepare(Native Method)

        at org.sqlite.DB.prepare(DB.java:114)

        at org.sqlite.Stmt.execute(Stmt.java:82)

        at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)

        … 7 more


Here’s the command I use to run liquibase

java -jar /opt/liquibase/liquibase.jar --driver=org.sqlite.JDBC --classpath=/opt/sqlite-jdbc-3.6.0.jar --changeLogFile=db/db.changelogBoot-master.xml --url=jdbc:sqlite:test_boot --username=user --password=pass update


Am I doing something wrong or is this a bug?

2

That looks like you are doing it right. I wasn’t able to test liquibase 2.0 well on sqlite due to me not being able to figure out how to configure it (the sqlite support was contributed code). 


Are you using 2.0, or 1.9?

Nathan

3

2.0.1

4

sqlite does not support column type’s modifying. 

See http://www.sqlite.org/lang_altertable.html


I’m not sure it’s looks like you try to do update 1.9 => 2.01. If yes, you can not do it for sqlite  , at least using current codebase. As I suggested early it would be better do such kind of upgrade as a separate liquibase task. It would be nice if the upgrade task could be customizable too using lqb extensions, etc.


Cheers, Oleg

5

Sorry it took me so long to reply.


Here’s exactly what I did.


Take an existing postgres database

Generate changelogs for it. (using 2.0.1)

Modify the changelogs to support mysql, postgres & sqlite

Everything is working for postgres & mysql 

BUT for sqlite I get the above exception.


I’m not too sure what’s wrong coz I too am not 100% sure what to do to set it up correctly. So for now I’ve decided to use hsqldb instead and everything works well with liquibase.

6

Upon investigation, in CORE/trunk/liquibase-core/src/main/java/liquibase/database/AbstractDatabase.java 

7

I can reproduce the problem without upgrading.  Attached is a log of me running an empty changelog to create a database, then rerunning the same (empty) changelog to upgrade the database, which fails with the invalid ALTER TABLE syntax.


I hope this can be resolved sometime relatively soon… it’s quite a showstopper for sqlite, and I don’t have a better way of accomplishing what I need (to use sqlite to bootstrap my application, and hold various master configuration options including database credentials for other database servers).


Thanks,

Michael.


Script started on Mon 18 Apr 2011 04:43:35 PM CST

michael@michael:~$ cat core.xml

<databaseChangeLog

    xmlns=“http://www.liquibase.org/xml/ns/dbchangelog”

    xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

    xmlns:ext=“http://www.liquibase.org/xml/ns/dbchangelog-ext”

    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd

    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

michael@michael:~$ ls -l test.db

ls: cannot access test.db: No such file or directory

michael@michael:~$ lai/lib/liquibase/liquibase --driver=org.sqlite.JDBC --url=jdbc:sqlite:test.db --changeLogFile=core.xml --logLevel=debug update

Liquibase Home is not set.

Liquibase Home: /home/michael/lai/lib/liquibase

DEBUG 18/04/11 4:43 PM:liquibase: Unable to load/access Apache Derby driver class org.apache.derby.tools.sysinfo to check version: org.apache.derby.tools.sysinfo

DEBUG 18/04/11 4:43 PM:liquibase: Connected to null@jdbc:sqlite:test.db

DEBUG 18/04/11 4:43 PM:liquibase: Create Database Lock Table

DEBUG 18/04/11 4:43 PM:liquibase: Executing EXECUTE database command: CREATE TABLE DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TEXT, LOCKEDBY TEXT, CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID))

DEBUG 18/04/11 4:43 PM:liquibase: Executing EXECUTE database command: INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, 0)

DEBUG 18/04/11 4:43 PM:liquibase: Created database lock table with name: DATABASECHANGELOGLOCK

DEBUG 18/04/11 4:43 PM:liquibase: Executing QUERY database command: SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1

DEBUG 18/04/11 4:43 PM:liquibase: Lock Database

DEBUG 18/04/11 4:43 PM:liquibase: Executing UPDATE database command: UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘fe80:0:0:0:21f:29ff:fe3a:256f%2 (fe80:0:0:0:21f:29ff:fe3a:256f%2)’, LOCKGRANTED = ‘2011-04-18 16:43:47.396’ WHERE ID = 1 AND LOCKED = 0

INFO 18/04/11 4:43 PM:liquibase: Successfully acquired change log lock

DEBUG 18/04/11 4:43 PM:liquibase: Create Database Change Log Table

INFO 18/04/11 4:43 PM:liquibase: Creating database history table with name: DATABASECHANGELOG

DEBUG 18/04/11 4:43 PM:liquibase: Executing EXECUTE database command: CREATE TABLE DATABASECHANGELOG (ID TEXT NOT NULL, AUTHOR TEXT NOT NULL, FILENAME TEXT NOT NULL, DATEEXECUTED TEXT NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE TEXT NOT NULL, MD5SUM TEXT, DESCRIPTION TEXT, COMMENTS TEXT, TAG TEXT, LIQUIBASE TEXT, CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME))

INFO 18/04/11 4:43 PM:liquibase: Reading from DATABASECHANGELOG

DEBUG 18/04/11 4:43 PM:liquibase: Executing QUERY database command: SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,TAG,EXECTYPE FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC

INFO 18/04/11 4:43 PM:liquibase: Reading from DATABASECHANGELOG

DEBUG 18/04/11 4:43 PM:liquibase: Executing QUERY database command: SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,TAG,EXECTYPE FROM DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC

DEBUG 18/04/11 4:43 PM:liquibase: Release Database Lock

DEBUG 18/04/11 4:43 PM:liquibase: Executing UPDATE database command: UPDATE DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1

INFO 18/04/11 4:43 PM:liquibase: Successfully released change log lock

Liquibase Update Successful

michael@michael:~$ ls -l test.db

-rw-r–r-- 1 michael michael 4096 2011-04-18 16:43 test.db

michael@michael:~$ lai/lib/liquibase/liquibase --driver=org.sqlite.JDBC --url=jdbc:sqlite:test.db --changeLogFile=core.xml --logLevel=debug update

Liquibase Home is not set.

Liquibase Home: /home/michael/lai/lib/liquibase

DEBUG 18/04/11 4:43 PM:liquibase: Unable to load/access Apache Derby driver class org.apache.derby.tools.sysinfo to check version: org.apache.derby.tools.sysinfo

DEBUG 18/04/11 4:43 PM:liquibase: Connected to null@jdbc:sqlite:test.db

DEBUG 18/04/11 4:43 PM:liquibase: Executing QUERY database command: SELECT LOCKED FROM DATABASECHANGELOGLOCK WHERE ID=1

DEBUG 18/04/11 4:43 PM:liquibase: Lock Database

DEBUG 18/04/11 4:43 PM:liquibase: Executing UPDATE database command: UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = ‘fe80:0:0:0:21f:29ff:fe3a:256f%2 (fe80:0:0:0:21f:29ff:fe3a:256f%2)’, LOCKGRANTED = ‘2011-04-18 16:43:53.125’ WHERE ID = 1 AND LOCKED = 0

INFO 18/04/11 4:43 PM:liquibase: Successfully acquired change log lock

DEBUG 18/04/11 4:43 PM:liquibase: Modifying size of databasechangelog.md5sum column

DEBUG 18/04/11 4:43 PM:liquibase: Modifying size of databasechangelog.liquibase column

DEBUG 18/04/11 4:43 PM:liquibase: Executing QUERY database command: SELECT MD5SUM FROM DATABASECHANGELOG WHERE MD5SUM IS NOT NULL

DEBUG 18/04/11 4:43 PM:liquibase: Executing EXECUTE database command: ALTER TABLE DATABASECHANGELOG ALTER COLUMN MD5SUM TYPE TEXT

SEVERE 18/04/11 4:43 PM:liquibase: Error executing SQL ALTER TABLE DATABASECHANGELOG ALTER COLUMN MD5SUM TYPE TEXT

java.sql.SQLException: near “ALTER”: syntax error

… 7 more


michael@michael:~$ exit


Script done on Mon 18 Apr 2011 04:43:55 PM CST


8

I have made a workaround for this.  It’s in the pull request at https://github.com/liquibase/liquibase/pull/19

9

Great, thanks! I’ll get the pull request in.


Nathan

10

nvoxland: Still waiting for the pull :slight_smile:

11

Yeah, I’ve seen it and looked at it a bit. The current code is currently in lots of pieces on my machine working on how type mappings work, and that is taking longer than I was hoping.


I’ll hopefully get to your pull request soon.


Nathan