liquibase 2.0.1 (sqlite) problems

General Discussion
1

Hi,

I am using liquibase 2.0.1 and get a weird error. The first run of applying db changelogs goes well (when the DB does not exist and is created for the first time), second time the liquibase script crashes:

First run:

INFO 5/29/11 8:41 PM:liquibase: Successfully acquired change log lock
INFO 5/29/11 8:41 PM:liquibase: Creating database history table with name: DATABASECHANGELOG
INFO 5/29/11 8:41 PM:liquibase: Reading from DATABASECHANGELOG
INFO 5/29/11 8:41 PM:liquibase: Reading from DATABASECHANGELOG
INFO 5/29/11 8:41 PM:liquibase: ChangeSet db.changelog-1.0.xml::1::jorgen ran successfully in 158ms
INFO 5/29/11 8:41 PM:liquibase: Successfully released change log lock
Liquibase Update Successful

Second run, nothing is changed to the XML file(s) I just wanted to see if no changes would be applied to the sqlite db:

jorg@shale-m4400:~/Projects/cashme/CashMePyGTK/setup$ ./update.sh
Liquibase Home is not set.
Liquibase Home: /home/jorg/Projects/cashme/CashMePyGTK/setup/liquibase
INFO 5/29/11 8:41 PM:liquibase: Successfully acquired change log lock
SEVERE 5/29/11 8:41 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 5/29/11 8:41 PM:liquibase: Successfully released change log lock
Liquibase Update Failed: near “ALTER”: syntax error
SEVERE 5/29/11 8:41 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

Liquibase 1.9 works fine. My update script is:

cd changelogs
…/liquibase/liquibase --driver=org.sqlite.JDBC --classpath=…/lib/sqlitejdbc-v056.jar --changeLogFile=db.changelog-master.xml --url=“jdbc:sqlite:/home/jorg/.cashme/cashme.db” --diffTypes=“tables,columns,primaryKeys,indexes,foreignKeys,sequences,data” update

The upgrade XML:

<databaseChangeLog 
    xmlns=“http://www.liquibase.org/xml/ns/dbchangelog/1.9” 
    xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance” 
    xsi:schemaLocation=“http://www.liquibase.org/xml/ns/dbchangelog/1.9
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd”> 
    
   
       
           
               
           
           
               
           
           
               
           
           
               
           
           
               
           
           
       
   
    


I will be checking in here regulary, for now liquibase 1.9 works fine so I will continue using that.

With regards,
- Jorgen

2

Hi Michael,

Thanks for mentioning that. Oddly liquibase 1.9 works so I can use that one until the bug is fixed. I rely on sqlite as I do not want my (potential) users to install a complete DB engine for a simple tool.

With regards,
- Jorgen

3

The problem is that LiquiBase internally tries to check the column sizes and types of its DATABASECHANGELOG table.  SQLite is dynamically typed, so it doesn’t keep the type/size information of the columns… you can put any kind of data in any column.


LiquiBase thinks that the column is of the wrong type, and crashes.


I hope somebody with LiquiBase internals knowledge can think of a good resolution for this bug.


I know this doesn’t help a lot, but at least it’s an understood problem,

Michael.