Table 'DBNAME.hibernate_sequence' doesn't exist
JavaMysqlSpringSpring BootSpring DataJava Problem Overview
I have a SpringBoot 2.0.1.RELEASE application using spring data / jpa
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
But when I do an update in the Amazon Aurora DB, I got this error:
> 2018-04-13 09:20 [pool-1-thread-1] ERROR o.h.id.enhanced.TableStructure.execute(148) - could not read a hi value com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'elbar.hibernate_sequence' doesn't exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
I have this in the entity I want to save
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
I would like to avoid also any drawback in the DB to fetch the ID .
Java Solutions
Solution 1 - Java
With the generation GenerationType.AUTO
hibernate will look for the default hibernate_sequence
table , so change generation to IDENTITY
as below :
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
Solution 2 - Java
Add the following config in your application.yml:
spring: jpa: hibernate: use-new-id-generator-mappings: false
Or this if you use application.properties
spring.jpa.hibernate.use-new-id-generator-mappings= false
Solution 3 - Java
If you are using Hibernate version prior to Hibernate5 @GeneratedValue(strategy = GenerationType.IDENTITY)
works like a charm. But post Hibernate5 the following fix is necessary.
@Id
@GeneratedValue(strategy= GenerationType.AUTO,generator="native")
@GenericGenerator(name = "native",strategy = "native")
private Long id;
DDL
`id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
REASON
Excerpt from hibernate-issue
> Currently, if the hibernate.id.new_generator_mappings is set to false, > @GeneratedValue(strategy = GenerationType.AUTO) is mapped to native. > If this property is true (which is the defult value in 5.x), the > @GeneratedValue(strategy = GenerationType.AUTO) is always mapped to > SequenceStyleGenerator. > > For this reason, on any database that does not support sequences > natively (e.g. MySQL) we are going to use the TABLE generator instead > of IDENTITY. > > However, TABLE generator, although more portable, uses a separate > transaction every time a value is being fetched from the database. In > fact, even if the IDENTITY disables JDBC batch updates and the TABLE > generator uses the pooled optimizer, the IDENTITY still scales better.
Solution 4 - Java
Just in case you migrate from a previous boot version:
setting the following in your application.yml
will prevent hibernate from looking for hibernate_sequence
entries.
spring.jpa.hibernate.use-new-id-generator-mappings
That was the default in Boot 1.x
Solution 5 - Java
JPA and Auto-DDL
When I run into mapping mismatches between a table schema and a java entity I like to do the following.
- Drop the schema
- Add the
spring.jpa.hibernate.ddl-auto=create
property to application.properties - Restart your application
This will now recreate the schema based on your entity. You can then compare the created table against your old schema to see the difference, if necessary.
> Warning : This will truncate the data in all tables specified as entities in your application
Solution 6 - Java
Adding spring.jpa.hibernate.ddl-auto=create
in the propoerties file solved the issue
Solution 7 - Java
You can just create the table:
create table hibernate_sequence(
next_val INTEGER NOT null
);
Solution 8 - Java
just add this code to your application.properties file
spring.jpa.hibernate.ddl-auto=update
Solution 9 - Java
I got the same error when I used the following dialect setting:
# Hibernate SQL dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
I am using MySQL version 8.0.27, aftter I updated the above setting to:
# Hibernate SQL dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
Then it worked as expected.