Invalid sleep mode dialect for MSSQL 2014

Question

I have a problem inserting entities that use sequences into the MSSQL 2014 database. I use the sleep mode that comes with Wildfly 10 CR4 (but in CR1 and CR2 I have the same problem).

Here is general information about the webapp runtime:

• Wildfly 10 (CR4)
• Java 8 u 51
• Windows 7 Proffesional 64bit
• MSSQL 2014 Server
• MSSQL driver: sqljdbc42.jar deployed to the application server.

My persistence.xml file looks like this:

<persistence-unit name="mb_managed_pu" transaction-type="JTA"> <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider> <jta-data-source>java:/jdbc/datasource</jta-data-source> <properties> <property name="hibernate.archive.autodetection" value="class, hbm" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.jdbc.batch_size" value="0" /> <property name="hibernate.default_schema_" value="openmap"/> <property name="hibernate.connection.useUnicode" value="yes"/> <property name="hibernate.connection.characterEncoding" value="UTF-8"/> </properties> </persistence-unit> 

Now this is what happens when I encounter an error.

Firstly, when Wildfly is running, I see this warning:

WARN [org.hibernate.engine.jdbc.dialect.internal.StandardDialectResolver] (ServerService thread pool - 68) HHH000385: unknown major version of Microsoft SQL Server [12] using SQL Server 2000 dialect

I browsed the web and found that this problem has already been known since January 2015, but unfortunately it is still an open problem .

The error itself occurs when I try to save a new object with an identifier configured to use sequences:

 @Id @Column(name = "MAP_BOOKMARK_ID") @SequenceGenerator(name = "SEQ_MAP_BOOKMARKS", sequenceName = "SEQ_MAP_BOOKMARKS", allocationSize = 1) @GeneratedValue(generator = "SEQ_MAP_BOOKMARKS", strategy = GenerationType.SEQUENCE) private long id; 

The exception thrown is as follows:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name "SEQ_MAP_BOOKMARKS".

This is not surprising, since sleep mode uses the wrong dialect - one that knows nothing about sequences.

When I modify persistence.xml and add this line:

 <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/> 

everything works like a charm.

The problem is that the application will also work with the Oracle database on another server and in Postgres on another. I would like to avoid preparing multiple versions of the same application.

Does anyone know a solution to this problem? Or should I wait for another version of Wildfly and / or hibernate?

Answer 1

At the same time, the team does not solve this problem; you can create a custom dialect recognizer:

 public class ScopeStandardDialectResolver implements DialectResolver { private static final long serialVersionUID = 1L; @Override public Dialect resolveDialect(DialectResolutionInfo info) { Dialect customDialectResolver = customDialectResolver(info); Log.getInstance().logInfo(Thread.currentThread().getStackTrace(), customDialectResolver.getClass().getName()); return customDialectResolver; } private Dialect customDialectResolver(DialectResolutionInfo info) { final String databaseName = info.getDatabaseName(); final int majorVersion = info.getDatabaseMajorVersion(); if (isSqlServer2014(databaseName, majorVersion)) { return new SQLServer2012Dialect(); } else { return StandardDialectResolver.INSTANCE.resolveDialect(info); } } private boolean isSqlServer2014(final String databaseName, final int majorVersion) { return databaseName.startsWith("Microsoft SQL Server") && majorVersion == 12; } } 

Then you set up your persistence block:

 <property name="hibernate.dialect_resolvers" value="com.oki.scope.hibernate.ScopeStandardDialectResolver" /> 

In this example: http://blog.exxeta.com/2016/03/23/dynamically-resolve-hibernate-database-dialect/

Answer 2

I think your problem may be related to this known issue of HHH-9570 . (The link does indeed contain my transfer request to my suggested workaround)

Basically, as it was before with SQL Server 2012, StandardDialectResolver Hibernate does not recognize SQL Server 2014 ([Major version 12]), and then the default dialect SQLServerDialect, which is one of SQL Server 2000, is returned

Answer 3

For Springboot 1.4.7 and below, add below to the properties file

 spring.jpa.properties.hibernate.dialect =org.hibernate.dialect.SQLServer2012Dialect 

Answer 4

In fact, this problem occurs due to incorrect display of tables. Checklists and Entity class mappings.