I’m trying to figure out the best way to transition our “legacy” database to using liquibase.
On new installs, I want liquibase to create all of our tables, but on upgrade, I want it to detect that we already have them, and mark as ran.
I’m doing this:
<!-- Contains a list of IP Addresses mapped to OpenNMS servers -->
<createTable tableName="servermap">
<!-- IP address of the device to be monitored -->
<column name="ipaddr" type="varchar(16)">
<constraints nullable="false" />
</column>
<!-- Text field to store the server name -->
<column name="servername" type="varchar(64)">
<constraints nullable="false" />
</column>
</createTable>
<createIndex tableName="servermap" indexName="server_name_idx">
<column name="servername" />
</createIndex>
</changeSet></ol>
…but I end up with an error complaining the table exists (which is kind of the point):
- May 12, 2009 10:04:22 AM liquibase.database.template.JdbcTemplate comment
INFO: Lock Database
May 12, 2009 10:04:22 AM liquibase.lock.LockHandler acquireLock
INFO: Successfully acquired change log lock
May 12, 2009 10:04:24 AM liquibase.database.AbstractDatabase getRanChangeSetList
INFO: Reading from databasechangelog
May 12, 2009 10:04:24 AM liquibase.database.template.JdbcTemplate comment
INFO: Changeset 1.6.4/tables/serverMap.xml::1.6.4-serverMap::rangerrick::(MD5Sum: b5b2833197f4a04af2851e34e1ecf)
May 12, 2009 10:04:24 AM liquibase.database.template.JdbcTemplate comment
INFO: Release Database Lock
May 12, 2009 10:04:24 AM liquibase.lock.LockHandler releaseLock
INFO: Successfully released change log lock
Migration Failed: Error executing SQL CREATE TABLE servermap (ipaddr VARCHAR(16) NOT NULL, servername VARCHAR(64) NOT NULL). For more information, use the --logLevel flag)
May 12, 2009 10:04:24 AM liquibase.commandline.Main main
SEVERE: Error executing SQL CREATE TABLE servermap (ipaddr VARCHAR(16) NOT NULL, servername VARCHAR(64) NOT NULL)
liquibase.exception.MigrationFailedException: Migration failed for change set 1.6.4/tables/serverMap.xml::1.6.4-serverMap::rangerrick:
Reason: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE servermap (ipaddr VARCHAR(16) NOT NULL, servername VARCHAR(64) NOT NULL):
Caused By: Error executing SQL CREATE TABLE servermap (ipaddr VARCHAR(16) NOT NULL, servername VARCHAR(64) NOT NULL):
Caused By: ERROR: relation "servermap" already exists
at liquibase.ChangeSet.execute(ChangeSet.java:238)
at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
at liquibase.Liquibase.update(Liquibase.java:112)
at liquibase.commandline.Main.doMigration(Main.java:646)
at liquibase.commandline.Main.main(Main.java:95)
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE servermap (ipaddr VARCHAR(16) NOT NULL, servername VARCHAR(64) NOT NULL)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
at liquibase.ChangeSet.execute(ChangeSet.java:214)
... 5 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "servermap" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:332)
at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
... 9 more
Any idea how to handle this? From what I understand, should match if the table does not exist; if it does exist, then the precondition fails, and should be marked as having run, but it’s just failing out with an exception instead.
PostgreSQL shows the database exists:
- test=# \d servermap;
Table "public.servermap"
Column | Type | Modifiers
------------+-----------------------+-----------
ipaddr | character varying(16) | not null
servername | character varying(64) | not null
Indexes:
"server_name_idx" btree (servername)
…and when I’m running liquibase, I’m setting the property install.database.name:
- ~/Downloads/liquibase-1.9.3/liquibase --classpath=/Users/ranger/.m2/repository/postgresql/postgresql/8.1-407.jdbc3/postgresql-8.1-407.jdbc3.jar:. -Dinstall.database.name=test \
-Dinstall.database.admin.user=postgres -Dinstall.database.user=opennms --username=postgres --password=postgres --url='jdbc:postgresql://localhost/test' \
--driver=org.postgresql.Driver --changeLogFile=changelog.xml --logLevel=finest update