16

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

Improve this question

5 Answers 5

Reset to default
46

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you. It worked with the IDENTITY. The dialect was org.hibernate.dialect.SQLServer2012Dialect. Sprign Boot version1.5.10.RELEASE.
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: 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'.
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.
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).
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.
|
2

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.

Improve this answer

Comments

2

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.

Improve this answer

Comments

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

Improve this answer

Comments

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

Improve this answer

5 Comments

The query was auto generated by Hibernate
Then it is a bug with Hibernate because the code it generated isn't even close to correct.
Hibernate creates the query with the help of the dialect. Sql Server 2012 introduced sequences, hence it is necessary to use org.hibernate.dialect.SQLServer2012Dialect in order to produce the correct query.
I found that this value must be org.hibernate.dialect.SQLServer2012Dialect. One might think that the default value org.hibernate.dialect.SQLServerDialect would be sufficient, but the 2012 dialect is required. One might think newer dialects 2016 or 2017, etc would be more up to date, but 2012 dialect is preferred.
Also, the dialect cannot always be overridden in Spring Boot by setting spring.jpa.hibernate.properties.dialect nor spring.jpa.database-platform. I found that hibernate is picking up this setting from our PersistenceConfig class. We create a HibernateJpaVendorAdapter and setDatabasePlatform("org.hibernate.dialect.SQLServer2012Dialect"). This vendor adapter is then added to an entityManagerFactory of type LocalContainerEntityManagerFactoryBean, along with our DataSource. Hibernate picks up the dialect from the JpaVendorAdapter's properties and does not look at system properties.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.