org.h2.jdbc.JdbcSQLException: Schema "DBO" not found

Question

This is a follow up question of Hibernate version mismatch in WildFly 10.0.

For completeness, restating the problem briefly. I have 2 projects, one is a normal java project (with maven): core, and the other is non-maven dynamic web project: webapi. The later acts as a rest layer on top of core.

core uses hibernate without any problem. It defines the persistence.xml (inside src/META-INF/) as follows:

<?xml version="1.0" encoding="utf-8"?>

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">

    <persistence-unit name="app" transaction-type="RESOURCE_LOCAL">

        <class>data.entities.Anything</class>
        <class>data.entities.Something</class>

        <properties>
            <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"></property>
            <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://localhost;databaseName=AppDb"></property>
            <property name="javax.persistence.jdbc.user" value="****"></property>
            <property name="javax.persistence.jdbc.password" value="****"></property>
            <property name="hibernate.default_schema" value="dbo"></property>           
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"></property>
            <property name="hibernate.hbm2ddl.auto" value="update"></property>
            <property name="jboss.as.jpa.providerModule" value="org.hibernate:5.2.4.Final" />
        </properties>
    </persistence-unit>
</persistence>

Both core and webapi is now using hibernate-core 5.2.4.Final, using maven, and WildFly module installation as described here, respectively.

However, now when I deploy webapi to WildFly 10.0, I get the following error:

[org.jboss.as.server.deployment] (MSC service thread 1-8) WFLYSRV0027: Starting deployment of "webapi.war" (runtime-name: "webapi.war")

[org.jboss.as.jpa] (MSC service thread 1-1) WFLYJPA0002: Read persistence.xml for app

[org.jboss.as.jpa] (ServerService Thread Pool -- 67) WFLYJPA0010: Starting Persistence Unit (phase 1 of 2) Service 'webapi.war#app'

[org.hibernate.jpa.internal.util.LogHelper] (ServerService Thread Pool -- 67) HHH000204: Processing PersistenceUnitInfo [ name: app ...]

[org.hibernate.orm.deprecation] (ServerService Thread Pool -- 67) HHH90000001: Found usage of deprecated setting for specifying Scanner [hibernate.ejb.resource_scanner]; use [hibernate.archive.scanner] instead

[org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-1) WFLYJCA0004: Deploying JDBC-compliant driver class com.microsoft.sqlserver.jdbc.SQLServerDriver (version 6.0)

[org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-6) WFLYJCA0018: Started Driver service with driver-name = webapi.war_com.microsoft.sqlserver.jdbc.SQLServerDriver_6_0

[org.jboss.as.jpa] (ServerService Thread Pool -- 67) WFLYJPA0010: Starting Persistence Unit (phase 2 of 2) Service 'webapi.war#app'

[org.hibernate.dialect.Dialect] (ServerService Thread Pool -- 67) HHH000400: Using dialect: org.hibernate.dialect.SQLServer2008Dialect

[org.hibernate.envers.boot.internal.EnversServiceImpl] (ServerService Thread Pool -- 67) Envers integration enabled? : true

[org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl] (ServerService Thread Pool -- 67) GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:524) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:470) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:273) at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:203) at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:177) at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:66) at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:309) at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:445) at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:877) at org.jboss.as.jpa.hibernate5.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:44) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:154) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:117) at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:667) at org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1.run(PersistenceUnitServiceImpl.java:182) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) at org.jboss.threads.JBossThread.run(JBossThread.java:320)

Caused by: org.h2.jdbc.JdbcSQLException: Schema "DBO" not found; SQL statement: create table dbo.Anythings (Id binary(255) not null, CreatedOn datetime not null, IsActive bit, primary key (Id)) [90079-173] at org.h2.message.DbException.getJdbcSQLException(DbException.java:331) at org.h2.message.DbException.get(DbException.java:171) at org.h2.message.DbException.get(DbException.java:148) at org.h2.command.Parser.getSchema(Parser.java:616) at org.h2.command.Parser.getSchema(Parser.java:623) at org.h2.command.Parser.parseCreateTable(Parser.java:5302) at org.h2.command.Parser.parseCreate(Parser.java:3873) at org.h2.command.Parser.parsePrepared(Parser.java:324) at org.h2.command.Parser.parse(Parser.java:279) at org.h2.command.Parser.parse(Parser.java:251) at org.h2.command.Parser.prepareCommand(Parser.java:218) at org.h2.engine.Session.prepareLocal(Session.java:428) at org.h2.engine.Session.prepareCommand(Session.java:377) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1138) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:168) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:156) at org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:198) at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ... 20 more

Is there anyway this can be corrected?

Update: I think, it is not able to connect the database. When I use hibernate.hbm2ddl.auto as validate instead of update, I get the following error:

org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [dbo.Anythings]

Additional info: I have already placed sqljdbc42.jar in wildfly-10.0.0.Final\modules\system\layers\base\com\microsoft\sqlserver\main, and the module.xml looks like below:

<?xml version="1.0" encoding="UTF-8"?>
<!-- JDBC Drivers module.xml file to configure your JDBC drivers-->

<module xmlns="urn:jboss:module:1.3" name="com.microsoft.sqlserver" slot="main">
  <resources>
    <resource-root path="sqljdbc42.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
  </dependencies>
</module>

I also tried to add sqlserver as global subsystem in standalone.xml:

<subsystem xmlns="urn:jboss:domain:ee:4.0">
    <global-modules>
        <module name="com.microsoft.sqlserver" slot="main"/>
    </global-modules>
...
</subsystem>

However, none of these worked.

Answer 1

It seems to be caused by the way how you specify DB connection. On EE environment, you are supposed to use data source definition, either specified as jta-data-source or non-jta-data-source in your persistence.xml. As stated in JPA 2.1 spec(ch. 8.2.1.5), if you do not specify any of them, your wildfly will deploy your app using its default datasource - named ExampleDS - which uses h2 in-memory DB. The connection parameters via properties(javax.persistence.jdbc.url etc) are ignored as they are intended for Java SE environment not Java EE.
You need to either add a new datasource definition to the wildfly configuration(standalone.xml or domain.xml depending on your setup) or provide the datasource definition within your app as a file named <something>-ds.xml located at WEB-INF folder. In both cases, you need to setup your MSSQL driver correctly(deploy to modules, add module.xml and add the driver definition to widlfly config /subsytem=datasources). These operations can also be done via jboss-cli interface to avoid manual edits of the configuration file. After that remove the connection properties from your persistence.xml and add the jta/non-jta-data-source element with the jndi name of your newly created DS. Also, please remove the global definition of your mssql module

This links might be useful for you: Wildfly Hibernate + In app datasource definition
JBoss DB Driver+Data source definition via cli

Hope that helps.