CODEWITHSUNDEEP
javadatabasehibernatespring-bootspring-data-jpa
WebNoob
WebNoob

Reputation: 259

SchemaManagementException: Schema-validation: missing table [chk_groups] even when table is present in DB

First of all, I know there are lot of questions related to this on stackoverflow, but none of them solve my issue. That's why I am posting this.

I have a very weird issue while trying to connect to the SQL server DB and trying to validate the existing DB structure using "validate" value for hibernate.hbm2ddl.auto field. But I get below error,

SchemaManagementException: Schema-validation: missing table [chk_groups]

Below is my entities configurations,

@Table(name="chk_groups")
public class GroupEntity {
}

@Entity
@Table(name="chk_members")
public class MemberEntity {
}

I am using proper configuration in properties file like below,

spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost\instanceName;databaseName=companyDB
spring.datasource.username=sa
spring.datasource.password=sa

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.hibernate.ddl-auto = validate

Now in MS sql server, the default schema name is "dbo" which should ideally get selected while validating the DB structure. But just to be sure, I also tried configuring it manually like below when it I got missing table error first time. Still no luck.

@Entity
@Table(name="chk_groups", schema="dbo")
public class GroupEntity {

@Entity
@Table(name="chk_members", schema="dbo")
public class MemberEntity {

I tried setting below property too,

spring.jpa.properties.hibernate.default_schema= dbo

I even tried,

@Entity (name="chk_groups")
public class GroupEntity {
}

I am 100% sure that the DB table exists because I can access it using the old JDBC template way and can perform all CRUD operations on it without any errors.

The issue comes when I try to validate the existing DB structure with the entities that I have created through java code using spring boot and data JPA way. I can NOT use update or create or anything else as the DB structure has been created already. I am supposed to map to it using java entities.

I am not able to figure out what can be the issue that can be causing this? Can this be related to any specific permissions the user needs to have to simply validate the schema? Is there a way to check it using any DB query of permission? Or can it be something else all together?

I have tried all that I could. Please help me out.

Upvotes: 6

Views: 6395

Answers (2)

gavenkoa
gavenkoa

Reputation: 48753

Had a long debug session till I found culprit:

Hibernate: Schema-validation: missing table

I had to redefine some parts of CamelCaseToUnderscoresNamingStrategy:

public class CustomCamelCaseToUnderscoresNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {

    private static final Pattern LOWER_RE = Pattern.compile("[a-z]");

    /**
     * Lowercase only if there is lowercase (like if entity name is "User" we want a table with name "user").
     * Do not change the name if it is all uppercase.
     */
    @Override
    protected Identifier getIdentifier(String name, boolean quoted, JdbcEnvironment jdbcEnvironment) {
        if (LOWER_RE.matcher(name).find()) {
            name = name.toLowerCase(Locale.ROOT);
        }
        return new Identifier( name, quoted );
    }
}

Upvotes: 0

Marc Bouvier
Marc Bouvier

Reputation: 702

I solved it by adding the following property in application.properties

spring.jpa.properties.hibernate.hbm2ddl.jdbc_metadata_extraction_strategy=individually

Upvotes: 3

Related Questions