j...@revolution-wines.com

unread,

Nov 5, 2014, 3:07:42 PM11/5/14

to grails-de...@googlegroups.com

Hello

Complete NB here so please bear with me.

Running 2.4.4 grails stack: (Full details below)

database-migration:1.4.0

Windows 7

I'm following along on a Pluralsight (online training) and am getting "Table already exists" error. Here are the steps to reproduce.

This is from a fresh app. The initial step is "grails create-app MyAPP"

1) Change DataSource.groovy

in DataSource.groovy

environments {

development {

dataSource {

dbCreate = "" // one of 'create', 'create-drop', 'update', 'validate', ''

url = "jdbc:h2:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"

}

2) from command line:

grails dbm-generate-changelog changelog.groovy

3) contents of changelog.groovy

databaseChangeLog = {

}

4) from command line:

grails create-domain-class Car

5) from command line

grails dbm-status

(no pending changes)

6) from command line

grails run-app

(Starts Fine)

verfied that localhost:8080/MyAPP/dbdoc is up too

7) from command line

grails create-domain-class Car

(domain class "Car" is created)

8) This is the Car domain class:

package myapp

class Car {

String manufacturer

String make

String modelYear

String engine

Double price

static constraints = {

}

9) from command line

grails dbm-generate-gorm-changelog --add createCar.groovy

(Execution completes okay)

Here is the file createCar.groovy

databaseChangeLog = {

changeSet(author: "jgenshle (generated)", id: "1415215848019-1") {

createTable(tableName: "car") {

column(autoIncrement: "true", name: "id", type: "bigint") {

constraints(nullable: "false", primaryKey: "true", primaryKeyName: "carPK")

}

column(name: "version", type: "bigint") {

constraints(nullable: "false")

}

column(name: "engine", type: "varchar(255)") {

constraints(nullable: "false")

}

column(name: "make", type: "varchar(255)") {

constraints(nullable: "false")

}

column(name: "manufacturer", type: "varchar(255)") {

constraints(nullable: "false")

}

column(name: "model_year", type: "varchar(255)") {

constraints(nullable: "false")

}

column(name: "price", type: "double") {

constraints(nullable: "false")

}

And here is changelog.groovy

databaseChangeLog = {

include file: 'createCar.groovy'

}

10) from command line

grails dbm-update

here is where the error happens:

| Starting dbm-update for database sa @ jdbc:h2:devDb;MVCC=TRUE;LOCK_TIMEOUT=100

00;DB_CLOSE_ON_EXIT=FALSE

| Error 2014-11-05 11:56:01,309 [main] ERROR liquibase - Change Set createCar.g

roovy::1415215848019-1::jgenshle (generated) failed. Error: Error executing SQL

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version

BIGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manuf

acturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NO

T NULL, CONSTRAINT carPK PRIMARY KEY (id)): Table "CAR" already exists; SQL stat

ement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

Message: Error executing SQL CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS

IDENTITY NOT NULL, version BIGINT NOT NULL, engine VARCHAR(255) NOT NULL, make

VARCHAR(255) NOT NULL, manufacturer VARCHAR(255) NOT NULL, model_year VARCHAR(25

5) NOT NULL, price DOUBLE NOT NULL, CONSTRAINT carPK PRIMARY KEY (id)): Table "C

AR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

Line | Method

->> 62 | execute in liquibase.executor.jvm.JdbcExecutor

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

| 104 | execute in ''

| 1091 | execute . . . . . in liquibase.database.AbstractDatabase

| 1075 | executeStatements in ''

| 317 | execute . . . . . in liquibase.changelog.ChangeSet

| 27 | visit in liquibase.changelog.visitor.UpdateVisitor

| 58 | run . . . . . . . in liquibase.changelog.ChangeLogIterator

| 114 | update in liquibase.Liquibase

| 26 | doCall . . . . . in DbmUpdate$_run_closure1_closure2

| 59 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2_cl

osure11

| 133 | executeInSession in grails.plugin.databasemigration.MigrationUtils

| 51 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2

^ 25 | doCall . . . . . in DbmUpdate$_run_closure1

Caused by JdbcSQLException: Table "CAR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

->> 344 | getJdbcSQLException in org.h2.message.DbException

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

| 178 | get in ''

| 154 | get . . . . . . . in ''

| 110 | update in org.h2.command.ddl.CreateTable

| 79 | update . . . . . in org.h2.command.CommandContainer

| 254 | executeUpdate in org.h2.command.Command

| 186 | executeInternal . in org.h2.jdbc.JdbcStatement

| 160 | execute in ''

| 92 | doInStatement . . in liquibase.executor.jvm.JdbcExecutor$1ExecuteStat

ementCallback

| 55 | execute in liquibase.executor.jvm.JdbcExecutor

| 104 | execute . . . . . in ''

| 1091 | execute in liquibase.database.AbstractDatabase

| 1075 | executeStatements in ''

| 317 | execute in liquibase.changelog.ChangeSet

| 27 | visit . . . . . . in liquibase.changelog.visitor.UpdateVisitor

| 58 | run in liquibase.changelog.ChangeLogIterator

| 114 | update . . . . . in liquibase.Liquibase

| 26 | doCall in DbmUpdate$_run_closure1_closure2

| 59 | doCall . . . . . in _DatabaseMigrationCommon_groovy$_run_closure2_cl

osure11

| 133 | executeInSession in grails.plugin.databasemigration.MigrationUtils

| 51 | doCall . . . . . in _DatabaseMigrationCommon_groovy$_run_closure2

^ 25 | doCall in DbmUpdate$_run_closure1

liquibase.exception.MigrationFailedException: Migration failed for change set cr

eateCar.groovy::1415215848019-1::jgenshle (generated):

Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE T

ABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version BIGINT NO

T NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufacturer V

ARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT NULL, C

ONSTRAINT carPK PRIMARY KEY (id)): Table "CAR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]:

Caused By: Error executing SQL CREATE TABLE car (id BIGINT GENERATED B

Y DEFAULT AS IDENTITY NOT NULL, version BIGINT NOT NULL, engine VARCHAR(255) NOT

NULL, make VARCHAR(255) NOT NULL, manufacturer VARCHAR(255) NOT NULL, model_yea

r VARCHAR(255) NOT NULL, price DOUBLE NOT NULL, CONSTRAINT carPK PRIMARY KEY (id

)): Table "CAR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]:

Caused By: Table "CAR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)

at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27

)

at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)

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

at DbmUpdate$_run_closure1_closure2.doCall(DbmUpdate:26)

at _DatabaseMigrationCommon_groovy$_run_closure2_closure11.doCall(_Datab

aseMigrationCommon_groovy:59)

at grails.plugin.databasemigration.MigrationUtils.executeInSession(Migra

tionUtils.groovy:133)

at _DatabaseMigrationCommon_groovy$_run_closure2.doCall(_DatabaseMigrati

onCommon_groovy:51)

at DbmUpdate$_run_closure1.doCall(DbmUpdate:25)

Caused by: liquibase.exception.DatabaseException: Error executing SQL CREATE TAB

LE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version BIGINT NOT

NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufacturer VAR

CHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT NULL, CON

STRAINT carPK PRIMARY KEY (id)): Table "CAR" already exists; SQL statement:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

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

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

at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:109

1)

at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabas

e.java:1075)

at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)

... 8 more

Caused by: org.h2.jdbc.JdbcSQLException: Table "CAR" already exists; SQL stateme

nt:

CREATE TABLE car (id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, version B

IGINT NOT NULL, engine VARCHAR(255) NOT NULL, make VARCHAR(255) NOT NULL, manufa

cturer VARCHAR(255) NOT NULL, model_year VARCHAR(255) NOT NULL, price DOUBLE NOT

NULL, CONSTRAINT carPK PRIMARY KEY (id)) [42101-176]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:178)

at org.h2.message.DbException.get(DbException.java:154)

at org.h2.command.ddl.CreateTable.update(CreateTable.java:110)

at org.h2.command.CommandContainer.update(CommandContainer.java:79)

at org.h2.command.Command.executeUpdate(Command.java:254)

at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186)

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)

at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInSta

tement(JdbcExecutor.java:92)

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

... 12 more

Lastly, if i start the app and navigate to

http://localhost:8080/MyAPP/dbdoc

and click "Pending Changes"

I can see the pending change

Pending Changes

Pending Changes
createCar.groovy	1415215848019-1	jgenshle (generated)	NOT YET RAN [SQL]
Table car created

Generated: 11/5/14 11:41 AM
Against: SA @ jdbc:h2:devDb
Change Log: createCar.groovy

Generated By: LiquiBase 2.0.5

-------------

I can also see that the table "CAR" was created.

So it looks like db-migrate is trying to create the "CAR" table twice. This is what I can't figure out.

Here is my exact stack:

APPLICATION STATUS

App version: 0.1
Grails version: 2.4.4
Groovy version: 2.3.7
JVM version: 1.8.0_25
Reloading active: true
Controllers: 2
Domains: 1
Services: 3
Tag Libraries: 15

INSTALLED PLUGINS

dataBinding - 2.4.4
restResponder - 2.4.4
logging - 2.4.4
core - 2.4.4
dataSource - 2.4.4
i18n - 2.4.4
servlets - 2.4.4
urlMappings - 2.4.4
webxml - 1.4.1
assetPipeline - 1.9.9
databaseMigration - 1.4.0
jquery - 1.11.1
controllers - 2.4.4
controllersAsync - 2.4.4
codecs - 2.4.4
mimeTypes - 2.4.4
domainClass - 2.4.4
filters - 2.4.4
hibernate4 - 4.3.6.1
validation - 2.4.4
groovyPages - 2.4.4
converters - 2.4.4
services - 2.4.4
scaffolding - 2.1.2
cache - 1.1.8

Thanks for any and all help!!!

Burt Beckwith

unread,

Nov 5, 2014, 3:23:07 PM11/5/14

to grails-de...@googlegroups.com

That's an in-memory database, created when the app or script starts and destroyed when it shuts down, so the possible sources of what's creating the extra table are very limited. The most likely candidate is Hibernate via dbCreate, but you've set that to "". Try changing that to "none" - it's possible that there's a groovy-truth check somewhere and a blank string is interpreted as having no value set and a default is used instead, but any string that's not one of the expected ones will disable auto-create.

If that's not it, try adding Hibernate SQL logging and you should see the statements running; the easier way to do this is logSql=true in the hibernate block in DataSource.groovy:

   hibernate {
      ...
logSql=true
   }

Burt

j...@revolution-wines.com

unread,

Nov 5, 2014, 3:30:44 PM11/5/14

to grails-de...@googlegroups.com

Thank you.

the jdbc url is - notice "mem" is removed so this is instantiated on disk (or so I'm told). I will add the logging setting to DataSource.groovy.

url = "jdbc:h2:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"

Burt Beckwith

unread,

Nov 5, 2014, 3:39:38 PM11/5/14

to grails-de...@googlegroups.com

Logging isn't going to help then, the table is already there. You can drop it though; start the app with run-app and use the dbconsole (see http://grails.org/doc/latest/guide/conf.html#databaseConsole) to connect to the database (use the settings from DataSource.groovy) and drop it from there. As long as you have dbCreate disabled, it shouldn't magically reappear again.

Burt

j...@revolution-wines.com

unread,

Nov 5, 2014, 3:57:22 PM11/5/14

to grails-de...@googlegroups.com

Thanks.

Will the table be created when the command

grails create-domain-class Car

is executed?

Just curious. Trying to figure out when this bad boy is created. Or does it automatically create when run-app is submitted?

I thought I had set dbCreate="" at the very first step.

Joe

j...@revolution-wines.com

unread,

Nov 5, 2014, 4:40:34 PM11/5/14

to grails-de...@googlegroups.com

Also, this warning is being thrown when run-app is executed.

Nov 05, 2014 1:37:27 PM java.util.prefs.WindowsPreferences <init>

WARNING: Could not open/create prefs root node Software\JavaSoft\Prefs at root 0

I have no idea what this means or if it's at all related.

j...@revolution-wines.com

unread,

Nov 5, 2014, 5:50:21 PM11/5/14

to grails-de...@googlegroups.com

I figured this issue out.

I simply didn't set the dbCreate ="none" until I ran a run-app. Because I was watching a tutorial video, i repeated this mistake over and over.

NB: db migrate SQL error - table already exists

j...@revolution-wines.com

Pending Changes

APPLICATION STATUS

INSTALLED PLUGINS

Burt Beckwith

j...@revolution-wines.com

Burt Beckwith

j...@revolution-wines.com

j...@revolution-wines.com

j...@revolution-wines.com