14

I have an image backup that I restore to the MS SQL server 2016. I have an entity that declares its id like that:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@XmlID
@XmlElement
@XmlJavaTypeAdapter(IntToStringXmlAdapter.class)
private Integer id;

when I save the entity I receive:

Hibernate: select next_val as id_val from hibernate_sequence with (updlock, rowlock) 2018-02-28 22:05:41.935 
ERROR 18152 --- [nio-8080-exec-6] o.hibernate.id.enhanced.TableStructure   : could not read a hi value com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'hibernate_sequence'. 

...... 
2018-02-28 22:05:41.942  WARN 18152 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 208, SQLState: S0002 

2018-02-28 22:05:41.942 ERROR 18152 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Invalid object name 'hibernate_sequence'.

I have created by hand the sequence to the SQL server and I make sure that it exist through the SSMS.

CREATE SEQUENCE hibernate_sequence
 AS INTEGER
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 99
 NO CYCLE;

Despite of this I continue to the receive the previous error.

Any ideas what I am doing wrong?

Thank you in advance

Investigator
  • 1,431
  • 2
  • 17
  • 24

5 Answers5

45

Following points to check:

  • What dialect you are using?
  • What hibernate version you are using? Version 5 changed the GenerationType.AUTO behavior
  • Set "hibernate.hbm2ddl.auto" to update and see what it creates in the database
  • Avoid GenerationType.AUTO. Set it explicit to GenerationType.IDENTITY or GenerationType.SEQUENCE depending on what you want or your DB supports.
  • Check if you have the latest SQL Server JDBC driver. I had issues with it migrating from hibertnate 4.3 to 5.0
  • In hibernate 5 set hibernate.id.new_generator_mappings to false
V-tech
  • 194
  • 2
  • 9
Robert Niestroj
  • 15,299
  • 14
  • 76
  • 119
  • 4
    Thank you. It worked with the IDENTITY. The dialect was org.hibernate.dialect.SQLServer2012Dialect. Sprign Boot version1.5.10.RELEASE. – Investigator Mar 01 '18 at 08:02
  • Unfortunately it came up again the same error although I have changed to IDENTITY. I have upgraded to 5.2.3.Final since I have read this bug report: https://hibernate.atlassian.net/browse/HHH-11220 Unfortunately this didn't solve the problem. The new generated SQL now is: Hibernate: select next_val as id_val from hibernate_sequence with (updlock, holdlock, rowlock) 2018-03-03 18:53:14.603 ERROR 5304 --- [nio-8080-exec-1] o.hibernate.id.enhanced.TableStructure : could not read a hi value com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'hibernate_sequence'. – Investigator Mar 03 '18 at 16:56
  • and here are the details about the dialect: spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver spring.jpa.show-sql=true spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect spring.jpa.hibernate.ddl-auto=update I tried both IDENTITY and SEQUENCE but the result is the same. – Investigator Mar 03 '18 at 16:59
  • With the Hibernate version 4.3.5.Final the same code executes successfully but with the 5.0.x it does not (same code, same SQL server). – Investigator Mar 03 '18 at 18:01
  • Look at Hibernate 4->5 Migration guide. While migrating i had problems with my SQL Server driver - look the update in my answer. Also it seems to me like you still use somewhere a sequnce. Maybe not explicitly, sum through some default hibernate 5 behavior. Also look at the last point i added. – Robert Niestroj Mar 03 '18 at 18:57
  • Future readers. If you change to "IDENTITY", then I do not think you are actually using Sequences. I think RobertN is saying "don't use GenerationType.Auto, be more explicit about what you are using" "IF you want GT.Sequence, set it to GT.Sequence". Another big hammer hint is to .. create a new (temporary, you can delete later) DB, and set "hibernate.hbm2ddl.auto" to update or create....and see what the ORM creates. Note, is SSMS or Azure-Data-Studio, later versions of MsServerServer had true "Sequences", where as earlier versions of MsSqlServer used tables to simulate Sequences. – granadaCoder Mar 29 '21 at 15:09
  • I am curious if there is any solution for production purposes where I won't update any SQL data structure but need a higher hibernate version. I hope there exists some better way than manual sequences creation. – Marek Bernád Apr 19 '22 at 07:07
  • Hi @RobertNiestroj, Set "hibernate.hbm2ddl.auto" to update and Avoid GenerationType.AUTO. Set it explicit to GenerationType.IDENTITY worked for me. Thanks a lot !! – Jayanth Apr 22 '22 at 03:23
1

It seems you are upgrading SqlServer version. I faced it when i was upgrading SqlServer 2012 to SqlSever 2017.

Caused by : talk between driver (jtds or sqlserver ) and SqlServer 2017+ no more consider table "dbo.hibernate_sequence" as work-around. It needs specifically sequence with name hibernate_sequence.

solution : delete table named as dbo.hibernate_sequence and create sequence

Example :

USE [your_database_name]
GO
CREATE SEQUENCE [dbo].[hibernate_sequence] 
 AS [bigint]
 START WITH 10000000
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE 
GO

SqlServer Studio screenshot

javax4u
  • 21
  • 1
0

You really need to look at the documentation for this. The query you have here is totally wrong. And why are you trying to use query hints on a sequence?

The correct syntax would look this.

select next value FOR hibernate_sequence as next_val

Here is the documentation on this. https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
0

I was also getting the same error in spring boot project. In my case the error com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'sys.sequences' is showing because I was using sql server 2008 version which doesn't have sys.sequences object.

I switched to sql server 2017 and it is working fine.

Sushil Sharma
  • 81
  • 9
0

I had this problem and in my case i had to change the dialect from SQLServerDialect to SQLServer2012Dialect. The first has no implementation for sequences.

Edgar Hernandez
  • 338
  • 2
  • 14