This message was deleted.

I am sure this is something I missed but I am having issues with my integration test after upgrading to Grails 6.1.1 from Grails 5.3.x app (which has been upgraded from Grails 4 .1.x - > 4.0.x -> 2.0) . The app and unit test appear to be working but my sticking point is that when I run the integration test I use an H2 db and the objects don't seems to be creating because I get the following error message. The User domain class comes from spring security

ava.lang.IllegalStateException: Failed to load ApplicationContext
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:98)
	at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:124)
....... Removed to make it readable
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResultViaListCall(AbstractHibernateQuery.java:808)
	at org.grails.orm.hibernate.query.AbstractHibernateQuery.singleResult(AbstractHibernateQuery.java:795)
	at org.grails.datastore.gorm.finders.CountByFinder.invokeQuery(CountByFinder.java:55)
	at org.grails.datastore.gorm.finders.CountByFinder$1.doInSession(CountByFinder.java:49)
	at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:319)
	at org.grails.datastore.gorm.finders.AbstractFinder.execute(AbstractFinder.java:42)
	at org.grails.datastore.gorm.finders.CountByFinder.doInvokeInternal(CountByFinder.java:46)
	at org.grails.datastore.gorm.finders.DynamicFinder.invoke(DynamicFinder.java:254)
	at org.grails.datastore.gorm.finders.DynamicFinder.invoke(DynamicFinder.java:392)
	at org.grails.datastore.gorm.GormStaticApi.methodMissing(GormStaticApi.groovy:186)
	at org.grails.datastore.gorm.GormEntity$Trait$Helper.staticMethodMissing(GormEntity.groovy:777)
....... Removed to make it readable 
at grails.gorm.transactions.GrailsTransactionTemplate.execute(GrailsTransactionTemplate.groovy:91)
	at galen.api.BootStrap$_closure1.doCall(BootStrap.groovy:41)
	at groovy.lang.Closure.call(Closure.java:412)
	at groovy.lang.Closure.call(Closure.java:406)
	at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.groovy:585)
	at grails.util.Environment.executeForEnvironment(Environment.groovy:578)
	at grails.util.Environment.executeForCurrentEnvironment(Environment.groovy:554)
....... Removed to make it readable 
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select count(*) as y0_ from [*]user this_ where this_.username=? limit ?"; expected "identifier"; SQL statement:
select count(*) as y0_ from user this_ where this_.username=? limit ? [42001-224]

The actual grails code being executed is

User.countByUsername(username)

In my build.gradle I have the following (with other things, as well the standard grails stuff)

runtimeOnly("com.h2database:h2")

 implementation 'org.mariadb.jdbc:mariadb-java-client:3.1.4'
 implementation 'com.microsoft.sqlserver:mssql-jdbc:10.2.1.jre11'

implementation 'org.grails.plugins:spring-security-core:6.1.1'
implementation 'org.grails.plugins:spring-security-oauth2-provider:4.0.0-RC1'

Here is is my H2 configuration settings

environments:
  test:
    spring:
      jpa:
        hibernate:
          ddl-auto: create
    hibernate:
      hbm2ddl:
        auto: create-drop
   grails:
      plugin:
        databasemigration:
          updateOnStart: false
          reporting:
            updateOnStart: false
          report:
            updateOnStart: false
    dataSource:
      driverClassName: org.h2.Driver
      dialect: org.hibernate.dialect.H2Dialect
      username: sa
      password: ''
      dbCreate: create-drop
      url: jdbc:h2:mem:security;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    dataSources:
      reporting:
        driverClassName: org.h2.Driver
        dialect: org.hibernate.dialect.H2Dialect
        username: sa
        password: ''
        url: jdbc:h2:mem:reporting;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        dbCreate: create-drop
      report:
        driverClassName: org.h2.Driver
        dialect: org.hibernate.dialect.H2Dialect
        username: sa
        password: ''
        url: jdbc:h2:mem:report;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        dbCreate: create-drop

I recently ran into an H2 SQL syntax issue, Grails 6 uses H2 version 2.x and earlier Grails version uses 1.4.199 Try temporarily setting the version to 1.4.199 and see if that fixes the issue.... We where using reserved word 'key' as table attribute name and 2.x versions no longer tolerate that.

Thank you @Thomas Rasmussen this worked, After that I tried playing around with https://h2database.com/html/commands.html#set_non_keywords and https://h2database.com/html/features.html#compatibility_modes but neither seemed to work, Once this bug gets fixed maybe I will try with a more vanilla grails app and with some basic spring security tables and keep adding tables until I can find what is causing it to break 🙂

It is the user table name that does H2 v2 does not like. Add this to your User domain class to get around this:

static mapping = {
    table name: '`user`'
}