Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

hibernate says table does not exist but it is creating the table

I am developing a Spring app and I am using JPA with MariaDB for my databases. When the app starts it first throws some exceptions about not existing tables but it creates them. The app does not terminate after errors.

Am I doing some thing wrong?

update:

when I change

spring.jpa.hibernate.ddl-auto=create-drop

to

spring.jpa.hibernate.ddl-auto=update

hibernate does not throw an exception any more.

why?

Logs summery:

    org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table roles_privileges drop foreign key FK5yjwxw2gvfyu76j3rgqwo685u" via JDBC Statement
...

Caused by: java.sql.SQLSyntaxErrorException: (conn=105) Table 'users.roles_privileges' doesn't exist
...

Caused by: java.sql.SQLException: Table 'users.roles_privileges' doesn't exist
...
2019-10-29 18:13:00.866  WARN 821 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "alter table roles_privileges drop foreign key FK9h2vewsqh8luhfq71xokh4who" via JDBC Statement
...
Caused by: java.sql.SQLSyntaxErrorException: (conn=105) Table 'users.roles_privileges' doesn't exist

User Entity:

    @Entity
    public class User implements UserDetails {

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private long id;

        @Email
        private String email;

        @Column(nullable = false, unique = true)
        private String username;

        @Column(nullable = false)
        private String password;


        @ManyToMany
        @JoinTable(
                name = "users_roles",
                joinColumns        = @JoinColumn(name = "user_id", referencedColumnName = "username"),
                inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "name"))
        private Collection<Role> roles;
...
    }

Role Entity:

@Entity
    public class Role {

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        private long id;

        @Column(unique = true, nullable = false)
        private String name;

        @ManyToMany(mappedBy = "roles")
        private Collection<User> users;

        @ManyToMany
        @JoinTable(name = "roles_privileges",
        joinColumns        = @JoinColumn(name = "role_id",      referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "privilege_id", referencedColumnName = "id")
        )
        private Collection<Privilege> privileges;
...
    }

Privilege Entity:

@Entity
    public class Privilege {

        @Id
        private long id;

        @Column(unique = true, nullable = false)
        private String name;

        @ManyToMany(mappedBy = "privileges")
        private Collection<Role> roles;
....
    }

application.properties

#User datasource
spring.datasource.url=jdbc:mariadb://localhost:3306/users
spring.datasource.username=user01
spring.datasource.password=user01pass

#`hibernate_sequence' doesn't exist
spring.jpa.hibernate.use-new-id-generator-mappings=false

spring.jpa.hibernate.ddl-auto=create-drop
like image 201
mhrsalehi Avatar asked Jan 25 '23 16:01

mhrsalehi


1 Answers

You are probably not doing anything wrong.

When you use Hibernate's hbm2ddl auto with create or create-drop, Hibernate will try to create your database schema on startup.

You can turn on the show-sql property in your application.properties to see the generated SQL queries:

spring.jpa.show-sql=true

The queries might look something like this:

alter table `your_table` drop foreign key `FK...`
drop table if exists `your_table`
create table `your_table` (...)
alter table `your_table` add constraint `UK...` unique (`name`)
alter table `your_table` add constraint `FK...` foreign key (`foreign_id`) references `other_table` (`id`)

First Hibernate drops foreign key constraints, then drops the table, then creates the table, then adds constraints to it.

The error happens on the first query, when trying to drop a foreign key and the table doesn't exist yet. Nothing bad happens, you can ignore the error, the other queries will work and the table will be created correctly.

As far as I know, MySQL and MariaDB don't support something like a "drop foreign key if table exists" which would help to suppress the error.

like image 89
Dario Seidl Avatar answered Jan 29 '23 23:01

Dario Seidl