CODEWITHSUNDEEP
jpaspring-boot
Rares
Rares

Reputation: 637

Schema-validation: missing table [game]

I think it may be possible dupplicate of this: Schema-validation: missing table [hibernate_sequences] but I can't figure it out.

So in my application.properties file I have this option: spring.jpa.hibernate.ddl-auto=validate and I receive this error:

Schema-validation: missing table [game]

Why I am receiving this?

Here is my Game class and User class:

Game:

@Entity
public class Game {
    @Id
    @Column(name = "GAME_NUMBER")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long gameNumber;

    private int playerScore;
    private int NPCScore;
    private Date datetime;

    @ManyToOne
    @JoinColumn(name="USER_ID")
    private User user;

    public Game() {}

    public Game(int playerScore, int nPCScore, Date datetime) {
        super();
        this.playerScore = playerScore;
        this.NPCScore = nPCScore;
        this.datetime = datetime;
    }

    public User getUser() {
        return user;
    }
} + getters & setters

User:

@Entity
public class User {
    @Id
    @Column(name = "USER_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long userId;

    private String username;
    private String password;

    @OneToMany(mappedBy="user",cascade=CascadeType.ALL)
    private List<Game> games;

    @ElementCollection
    private List<Date> startSessions;

    public User() {}

    public User(String username, String password, List<Game> games, List<Date> startSessions) {
        super();
        this.username = username;
        this.password = password;
        this.games = games;
        this.startSessions = startSessions;
    }
}

Upvotes: 19

Views: 108852

Answers (12)

Shamsul Arefin
Shamsul Arefin

Reputation: 1917

For me the issue was a programming error, strange how the error i got. Instead of List on the ORM for the many to one field, i mistakenly wrote Item[].

Coming from JS background.

Upvotes: 0

Pavel Ryabykh
Pavel Ryabykh

Reputation: 45

Sometimes this may happen when you use @JoinTable annotation without specifying schema.

@Fetch(FetchMode.SUBSELECT)
@ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST})
@JoinTable(name = "entities_documents",
        joinColumns = @JoinColumn(name = "enitty_id"),
        inverseJoinColumns = @JoinColumn(name = "file_id"),
        schema = "my_schema_name"
)

In my case this was the problem (the lack of this code: schema = "my_schema_name")

Upvotes: 0

Wilson Ikner
Wilson Ikner

Reputation: 1

If you look at the logs, the idea does not find the migration file... In short, if you create a db.migration folder as in the video (where the author directly creates one directory, through a dot at once), then if you look at the Idea of the path to the migration file, it will be like a single db.migration directory, while Fly is looking for migration along the db/migration path. If I understand correctly, multiple dot-separated directories are created only when creating PACKAGES, and here we are creating a directory outside the java code/folder. Therefore, I first created a directory, db, and then a migration directory in it. there is no need to add any properties.

Upvotes: 0

Faouzi Mohamed
Faouzi Mohamed

Reputation: 431

In my case, i'm using Flyway and have set in the propertie file hibernate.hbm2ddl.auto = validate.

The Error happens because I forgot to name the migration file correctly.

Here instead of V3__MIGRATION_NAME.sql I forgot the an underscore and had this : V3_MIGRATION_NAME.sql

V3_MIGRATION_NAME.sql --> V3__MIGRATION_NAME.sql

---^-------------------------------^^

Upvotes: 0

Shreyansh Jain
Shreyansh Jain

Reputation: 422

For anyone coming here in 2023, I resolved the issue by adding the following: spring.jpa.properties.hibernate.default_schema = dbName

This is how my application.properties look like:

spring.datasource.url=jdbc:postgresql://localhost:5432/dbName
spring.datasource.username=//your username
spring.datasource.password=//your password

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto= validate
spring.jpa.properties.hibernate.jdbc.time_zone = UTC
spring.jpa.properties.hibernate.default_schema = dbName

#flyway configuration
spring.flyway.user = //your username
spring.flyway.password = //your password
spring.flyway.defaultSchema = dbName

#SHOW-SQL
spring.jpa.properties.hibernate.show_sql = true

Upvotes: 1

Jolly Good
Jolly Good

Reputation: 550

Hibernate version 5.6.9, Case-sensitive implementation:

hibernate:
        physical_naming_strategy: 'org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl'

Upvotes: 0

xMilos
xMilos

Reputation: 2018

Add this in application.yml:

spring:
  jpa:
    properties:
      hibernate:
        default_schema: game

Upvotes: 1

Hett
Hett

Reputation: 3825

The SQL standard requires names stored in uppercase. If you named the table/fields in lowercase - JPA can automatically convert case to upper and trying to search names in this case, but write to logs in lower ¯\_(ツ)_/¯

Upvotes: 0

Makatun
Makatun

Reputation: 1017

Don't forget permissions: GRANT select, insert, update, delete, alter ON table_name TO usr_name;

Upvotes: 7

Vineeth Bhaskaran
Vineeth Bhaskaran

Reputation: 2271

This error can appear while using spring boot with flywayDB. The issue might be due to the wrong naming convention of script files, which were used by flywayDB.

https://flywaydb.org/documentation/migrations#naming

Upvotes: 5

Bevor
Bevor

Reputation: 8605

I got the same as I changed to Hibernate 5.4.0.Final. Either Hibernate suddenly has problems to recognize the default schema or the driver does not return the schema properly. I was able to bypass it by either adding the schema definition to the table definition. 

@Table(name = "GAME", schema = "PUBLIC")

or by adding a default schema in persistence.xml.

<property name="hibernate.default_schema" value="PUBLIC" />

Upvotes: 12

Darren Forsythe
Darren Forsythe

Reputation: 11411

validate validates that the entities are compatible against the target, to a degree it's not foolproof. Anyway, whatever database you are trying to validate against does not have a table called game in which to store the entities.

This answer goes into more detail about what validate does.

Hibernate - hibernate.hbm2ddl.auto = validate

specifically,

checks the presence of tables, columns, id generators

Without knowing your database/expectations (are you expecting it to be created, or using Flyway/Liquibase to create/update the database etc.) I can't answer if validate is correct for your use case.

You could try create-drop to create and drop the table on startup/shutdown, but this isn't a solution for any production control over a database.

Upvotes: 22

Related Questions