java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist

java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist error occurs when the hibernate_sequence table does not exist in the database and auto id generation in hibernate is enabled. Hibernate generates and stores the current id for auto-increment column values in the hibernate sequence table. The error java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist will be thrown if the hibernate sequence table does not exist in the database.

Hibernate will search the hibernate sequence table for the current value and increment it if an entity class in Java is configured with hibernate auto id value generation using the annotation @GeneratedValue. The error will be thrown if the hibernate sequence table does not exist in the database. Hibernate auto id generation should be turned off, or the hibernate sequence table should be created, or auto id generation should be set to use database sequence.

In Hibernate, the annotation @GeneratedValue is used to configure the auto id increment. The next sequence number will be generated by hibernate and placed into the sql to insert the incremented value in a database table column. Hibernate will look in the hibernate sequence table for the current sequence value. The error java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist will be thrown if the table hibernate sequence does not exist.

Exception

The stack trace of the sql exception java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist is as shown below. The error message will show the actual database name. Hibernate could not find the hibernate_sequence table from the database.

java.sql.SQLSyntaxErrorException: Table 'testdb2.hibernate_sequence' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.26.jar:8.0.26]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.26.jar:8.0.26]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.26.jar:8.0.26]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) ~[mysql-connector-java-8.0.26.jar:8.0.26]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.hibernate.id.enhanced.TableStructure.executeQuery(TableStructure.java:216) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.id.enhanced.TableStructure.access$300(TableStructure.java:46) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:138) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.id.enhanced.TableStructure$1$1.execute(TableStructure.java:126) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.jdbc.WorkExecutor.executeReturningWork(WorkExecutor.java:55) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.jdbc.AbstractReturningWork.accept(AbstractReturningWork.java:34) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:57) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
	... 95 common frames omitted

Root Cause

If the hibernate_sequence table does not exist in the database and auto id generation in hibernate is enabled for a table in the entity java class, the exception java.sql.SQLSyntaxErrorException: Table ‘dbname.hibernate_sequence’ doesn’t exist will be thrown at the start of the spring boot application. Hibernate auto id generation should be turned off, or the hibernate sequence table should be created, or auto id generation should be set to use database sequence.

Solution 1

If the entity java class is configured with the annotation @GeneratedValue and the strategy to generate is auto, hibernate will search for auto id generation using hibernate_sequence table. The strategy should be changed to identity that will use the database sequence.

Problem

package com.yawintutor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Manager {
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Id
	private int id;
	
	.........
	.........
}

Solution

package com.yawintutor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Manager {
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Id
	private int id;
	
	.........
	.........
}

Solution 2

The auto id generation in hibernate can be disabled using a properties in the application.properties file. The “spring.jpa.hibernate.use-new-id-generator-mappings” is used to disable the auto id generation in hibernate. The flag is set to false to disable the auto id generation in hibernate.

application.properties

spring.jpa.hibernate.use-new-id-generator-mappings=false

Solution 3

The auto id generation in hibernate can be disabled in application.yaml file in spring boot application. The below code should be added in the application.yaml file to disable auto id generation in hibernate.

application.yaml

spring:
  jpa:
    hibernate:
      use-new-id-generator-mappings: false

Solution 4

The latest version of hibernate will not work with the above solutions. Hibernate version 5 onwards should use native method to generate auto id in the entity java class.

package com.yawintutor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Manager {
	@GeneratedValue(strategy= GenerationType.AUTO,generator="native")
	@GenericGenerator(name = "native",strategy = "native")
	@Id
	private int id;
	
	.........
	.........
}

Solution 5

If you’re in development mode, add the following command to the application.properties file to fix the problem. When the spring boot application is restarted, the command below will rebuild the database tables.

spring.jpa.hibernate.ddl-auto=create