Grails 3 Oracle AssertionFailure on Save action with getGeneratedKeys support not enabled

Question

Problem: Error 500: Internal Server Error

URI: /listing/save
Class: org.hibernate.AssertionFailure
Message: getGeneratedKeys() support is not enabled

Configuration

• Environment: development
• App profile: web
• App version: 0.1
• Grails version: 3.0.1
• Groovy version: 2.4.3
• JVM version: 1.8.0_45 (64-bit)
• Reloading active: true

Available Controllers:

• phonebook.ListingController

Operating System: Windows 7 Database: Oracle 11g R2 Enterprise Edition (11.2.0.4 64-bit)

Debug output contains:

Grails application running at http://localhost:8080
ERROR org.hibernate.AssertionFailure - HHH000099: an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session):     org.hibernate.AssertionFailure: getGeneratedKeys() support is not enabled
ERROR org.grails.web.errors.GrailsExceptionResolver - AssertionFailure occurred when processing request: [POST] /listing/save - parameters:
name: Scott
phone: [phone]
create: Create
getGeneratedKeys() support is not enabled. Stacktrace follows:
org.hibernate.AssertionFailure: getGeneratedKeys() support is not enabled
    at phonebook.ListingController.$tt__save(ListingController.groovy:38) ~[main/:na]
    at grails.transaction.GrailsTransactionTemplate$2.doInTransaction(GrailsTransactionTemplate.groovy:93) ~[grails-core-3.0.1.jar:3.0.1]
    at grails.transaction.GrailsTransactionTemplate.execute(GrailsTransactionTemplate.groovy:90) ~[grails-core-3.0.1.jar:3.0.1]
    at grails.transaction.GrailsTransactionTemplate$2.doInTransaction(GrailsTransactionTemplate.groovy:93) ~[grails-core-3.0.1.jar:3.0.1]
    at grails.transaction.GrailsTransactionTemplate.execute(GrailsTransactionTemplate.groovy:90) ~[grails-core-3.0.1.jar:3.0.1]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_45]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_45]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45]

File: grails-app\controllers\phonebook\ListingController
Line: 38
Content:         listing.save flush:true

Reproducing problem:

• C:\Dev> grails create-app phonebook
• C:\Dev> cd phonebook

Edit: build.gradle

dependencies {
  ...
  runtime "com.oracle:jdbc-lib-ojdbc6:11.2.0.4"
  ...
}

Note: Oracle client ojdbc6.jar added to local Maven repository at the coordinates specified above.

Edit: grails-app\conf\application.yml

...
dataSource:
    pooled: true
    jmxExport: true
    driverClassName: oracle.jdbc.OracleDriver
    username: scott
    password: tiger

environments:
    development:
        dataSource:
            dbCreate: update
            url: jdbc:oracle:thin:@localhost:1521/sbx1
...

C:\Dev\phonebook> grails create-domain-class phonebook.listing Edit:grails-app\domain\phonebook\Listing.groovy

package phonebook

class Listing {
    String name
    String phone

    static constraints = {
        name maxSize: 50
        phone maxSize: 14
    }
}

C:\Dev\phonebook> grails generate-all phonebook.listing
C:\Dev\phonebook> grails run-app

The following confirms that the application connected to the database and created the table successfully:

SQL> describe listing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(19)
 VERSION                                   NOT NULL NUMBER(19)
 NAME                                      NOT NULL VARCHAR2(50 CHAR)
 PHONE                                     NOT NULL VARCHAR2(14 CHAR)

There are also two sequences created in the schema:

HIBERNATE_SEQUENCE
LISTING_SEQ

Note: These must have been created as a result at my many attempts to modify mapping attributes in the domain class for ID generation.

Action once embedded Tomcat server is running on http://localhost:8080/ Internet Explorer: http://localhost:8080/ Click on link: Available Controllers > phonebook.ListingController Click on: New Listing Complete form and click: Create Result: Grails exception described above

Research and troubleshooting activities:

• Problem not present is only change is Oracle to H2/HSQL file/memory database
• Discovered hibernate.jdbc.use_get_generated_keys setting but was unsuccessful in resolving issue by placing setting of true in application.yml configuration file
• Found multiple references to settings in grails-app/conf/DataSource.groovy but this is Grails 3 which uses application.yml
• Attempted multiple attributes in domain class for mapping ID column using generators
• Found little to no information in Grails 3 documentation covering this topic
• Hibernate documentation covers configuration settings and ID generators but does not provide Grails / Groovy application of that information
• Hibernate documentation states that not explicitly setting hibernate.jdbc.use_get_generated_keys results in it being set automatically by jdbc connection database metadata

I attempted to resolve the problem by the following section in grails-app\conf\application.yml:

hibernate:
    jdbc:
        use_get_generated_keys: true
    cache:
        queries: false
...

I suspect that the resolution involves specific settings in grails-app\conf\application.yml but have not discovered the correct combination of configuration settings.

Answer 1

Ok, when looking into where in the application.yml configuration file to place the suggestion from the first answer presented I discovered that the hibernate.jdbc.use_get_generated_keys = true setting that I used was actually under the grails block. While never working with yml files previously I was not aware of the potential importance of how the indention and blocks formed configuration settings. When I first made edits to the file I looked to see if there was already a hibernate section, I place this setting in that block thus resulting in a setting of grails.hibernate.jdbc.use_get_generated_keys. I created the setting under a root (no indention) for hibernate and tested. The result was successful completion of the action.

I hope that this post will assist other new users in working with this configuration file which seems out of place in a framework centered around groovy. I will look to see if there is an option in creating a new grails application to utilize a groovy configuration file instead of the yml file.

Answer 2

You could also try switching from the identity-sequence generator (the default for Oracle dialect, I believe), to the seq-hilo:

In Grails 2.x, you do it via:

grails.gorm.default.mapping = {
    id generator: 'seqhilo', params: [max_lo: 1000]
}

I assume it would work similarly in 3.x in the application.yml file. This should prevent hibernate from even needing to use the getGeneratedKeys() method as it will bind the id into the insert from it's own in-memory pool instead of doing a seq.nextval in the insert statement.

Answer 3

Had this problem with Oracle 12, fixed by adding

   jdbc:
      use_get_generated_keys: true

and upgrading oracle jdbc driver to ojdbc7 12.1.0.2 (12.1.0.1 doesn't work)

Answer 4

For Grails 3, Hibernte 4, Oracle 10c+ the following config works.

Hibernate 4 configured in build.gradle, which is by default as of Grails 3.1.6

In application.yml

hibernate:
    jdbc:
        use_get_generated_keys: true
    id:
        new_generator_mappings: true

And then in the domain objects configure the id field to use an Oracle sequence for the key such as the following:

class Person {

    String name

    static constraints = {
        id generator:'sequence-identity', params:[sequence:'person_key_seq']
    }
}

Oracle only came out with auto-generated ID fields recently, I think 12. But Hibernate 4 only has org.hibernate.dialect.Oracle10gDialect, so you can't use the new Oracle auto-key feature without Hibernate 5. If you can use Hibernate 5 then the Oracle12cDialect is available, which will allow Hibernate and Oracle to just take care of the key generation for you, both in GORM and in SQL when working on the database directly. However, as of Grails 3.1.6 there are issues getting Hibernate 5 to successfully deploy on some servers, so beware if you attempt to switch.