new Grails App : Annoying gripe and time waster : create-drop with default h2 db

fell over this is with latest grails builds GGTS 3.6.1 / grails 2.4.2

if your new to grails what you want to do is quickly add a domain object and try and run some tests quickly etc

instead you get a silly error like this

HHH000389: Unsuccessful: alter table … drop constraint …

and it fails to start. This is frankly annoying and ought to be handled properly – but isn’t fixed yet.

The problem is with in memory DB use and using the create-drop default for hibernate with the H2 drivers. it gets confused as the db doesn’t exist and you can’t drop whats not there .

the easy fix is to override the default H2 dialect. create a file like this in your src/groovy (or java) folders

package com.softwood;

import org.hibernate.dialect.H2Dialect;

/**
 * Workaround.
 * 
 * @see https://hibernate.onjira.com/browse/HHH-7002
 * 
 */
public class ImprovedH2Dialect extends H2Dialect {
    @Override
    public String getDropSequenceString(String sequenceName) {
        // Adding the "if exists" clause to avoid warnings
        return "drop sequence if exists " + sequenceName;
    }

    @Override
    public boolean dropConstraints() {
        // We don't need to drop constraints before dropping tables, that just
        // leads to error messages about missing tables when we don't have a
        // schema in the database
        return false;
    }
}

then in your conf/DataSource.groovy add this line at the top of the file

dataSource.dialect = com.softwood.dialects.ImprovedH2Dialect

then re-run the grails run-app and the problem should disappear

this never used to happen so they ought to provide a fix for it as its very easy to put off new entrants from getting started

alternatively you can set the dbCreate = “update” which avoids the drop call on the empty in memory db

pretty annoying and hope this sames someone some heartache wondering what to do next