Grails 3 Oracle AssertionFailure on Save action with getGeneratedKeys not activated
Problem: Error 500: Internal Server Error
URI: /listing/save
Class: org.hibernate.AssertionFailure
Message: getGeneratedKeys() support is not enabled
Configuration
- Environment: development
- Application profile: web
- App version: 0.1
- Grails version: 3.0.1
- Groovy version: 2.4.3
- JVM version: 1.8.0_45 (64-bit)
- Reload active: true
Available controllers:
- phonebook.ListingController
Operating system: Windows 7 Database: Oracle 11g R2 Enterprise Edition (11.2.0.4 64-bit)
The 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: 555-1212
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
Playback problem:
- C: \ Dev> Griles create phonebook app
- C: \ Dev> cd phonebook
Edit: build.gradle
dependencies {
...
runtime "com.oracle:jdbc-lib-ojdbc6:11.2.0.4"
...
}
Note. Oracle client ojdbc6.jar has been added to the local Maven repository at the above coordinates.
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 has connected to the database and successfully created the table:
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)
The schema also creates two sequences:
HIBERNATE_SEQUENCE
LISTING_SEQ
Note. They must have been created as a result of my many attempts to change the display attributes in the domain class to generate the identifier.
Action when embedded Tomcat is running at http: // localhost: 8080 / Internet Explorer: http: // localhost: 8080 / Click link: Available Controllers> phonebook.ListingController Click: New List Fill in the form and click: New Result: Grails exception described above
Investigation and Troubleshooting:
- The problem isn't just a change - it's an Oracle / H2 / HSQL database with files / memory.
- Found the hibernate.jdbc.use_get_generated_keys parameter, but could not resolve the issue by setting the parameter to true in the application.yml config file.
- Found some links to settings in grails-app / conf / DataSource.groovy but this is Grails 3 which uses application.yml
- Attempting multiple attributes in domain class to map id column using generators
- The Grails 3 documentation does not mention any documentation dedicated to this topic.
- The Hibernate documentation covers configuration settings and identity generators, but does not provide the Grails / Groovy application with this information.
- The Hibernate documentation states that hibernate.jdbc.use_get_generated_keys is not explicitly set and it is automatically set by the jdbc connection database metadata.
I tried to solve the problem in the following section in grails-app \ conf \ application.yml:
hibernate:
jdbc:
use_get_generated_keys: true
cache:
queries: false
...
I suspect the permission is related to certain settings in grails-app \ conf \ application.yml, but no correct combination of config settings was found.
source to share
You can also try switching from the id sequence generator (the default for Oracle dialect, I suppose) to seq-hilo:
In Grails 2.x, you do this via:
grails.gorm.default.mapping = {
id generator: 'seqhilo', params: [max_lo: 1000]
}
I assume it will work similarly in the 3.x file in the application.yml file. This should prevent hibernate from even needing to use the getGeneratedKeys () method, as it will associate an identifier with an insert from its own memory pool, rather than executing seq.nextval on the insert statement.
source to share
Okay, looking at where in the application.yml config file to place the suggestion from the first answer provided, I found that the hibernate.jdbc.use_get_generated_keys = true setting I was using was actually under the grails block. Although I had never worked with yml files before, I was unaware of the potential importance of how the indentation and blocks form the config options. When I first edited the file, I looked to see if there was still a hibernate section, I put this parameter in this block, which results in the grails.hibernate.jdbc.use_get_generated_keys value. I created a root (no indentation) setting for hibernation and tested. The result was the successful completion of the action.
I hope this post helps other new users to work with this config file, which looks out of place in a groovy-centric framework. I'll see if there is a way to create a new grails app to use the groovy config file instead of the yml file.
source to share
For Grails 3, Hibernte 4, Oracle 10c +, the following configuration works.
Hibernate 4 is configured in build.gradle which defaults to 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, set up the id field to use the Oracle sequence for the key, like so:
class Person {
String name
static constraints = {
id generator:'sequence-identity', params:[sequence:'person_key_seq']
}
}
Oracle only recently released fields with autogenerated ids, I think 12. But Hibernate 4 only has org.hibernate.dialect.Oracle10gDialect, so you cannot use Oracle's new auto-key feature without Hibernate 5. If you can use Hibernate 5 then available Oracle12cDialect, which will allow Hibernate and Oracle to simply take care of generating keys for you, both in GORM and in SQL, while working directly with the database. With Grails 3.1.6, however, there are problems with Hibernate 5 deploying successfully to some servers, so be careful if you try to switch.
source to share