Liquibase: relation "databasechangeloglock" already exists, using grails plugin and non-default schema

Question

I am using grails 2.0.3, database-migration (liquibase) plugin 1.1, and postgres 9.1.

I am seeing what I think is the same issue described by these other users, but with wrinkles:

• problem running liquibase with maven and postgres-db
• https://liquibase.jira.com/browse/CORE-183
• [other references removed, don't have enough reputation :(]

The wrinkles are that:

1. I'm using grails and the database-migration plugin.
2. The production database does not use the default schema.
3. I must use the automatic database migration on start (grails.plugin.databasemigration.updateOnStart = true), because no developer has access to the actual production database(s).

My understanding of the issue is that liquibase is checking the default schema for the existence of its maintenance tables, and then attempts to create the tables in the right place, the non-default schema. But of course they already exist after the first execution. There seems to be a workaround by specifying a command-line option, but I don't have that option because of the requirement to run automated, within the grails app as-deployed.

Is there a way to make the database-migration plugin do what I need? Telling the DBAs to organize the schemas differently is not an option.

Thanks in advance, Ray A. Conner

Acrolein

Answer 1

with me it was the missing owner rights. That way the table could not be found by the logged in user, but it could also not be created, since it was there.

My fix was to change the owner to the correct person.

Rhetor

Answer 2

Preconditions and refactoring commands take an attribute schemaName.

<tableExists schemaName="myschema" tableName="..."/>
<createTable schemaName="myschema" tableName="..."/>

You can also parametrize it:

<databaseChangeLog ..>
  <property name="schema.name" value="myschema"/>
  ....
  <changeset ...>
    <createTable schemaName="${schema.name]" tableName="..."/>
  </changeset>
</databaseChangeLog>

For Liquibase itself, you can set defaultSchemaName, in your case (Grails) this should be:

grails.plugin.databasemigration.updateOnStartDefaultSchema

Pinwork

That's for refactoring tables in my app's schema. This issue is with tables that liquibase creates for itself, to hold the changesets and for internal locking. – Acrolein

I've left the job where this was an issue, and I haven't yet been able to get an environment set up to verify your edit. Hopefully before too long I'll be able to do that. – Acrolein