Entities mapped with @OneToMany and @ManyToOne making Quarkus fail database drop-create

[mavina]

Hi team,

First of all, I hope I'm posting at the proper place.

I'm trying to achieve a simple Quarkus project with a database, a User object associated with a Feed object. User can have multiple feeds. Database is MySQL with panache hibernate, all reactive.
I coded a User entity along with its services. Works like a charm.

Then I tried to add my Feed entity. Now, when I try to start my quarkus env, quarkus reports below error :

2024-03-26 17:22:53,939 INFO [org.hib.rea.pro.imp.ReactiveIntegrator] (JPA Startup Thread) HR000001: Hibernate Reactive
2024-03-26 17:22:54,984 WARN [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-0) SQL Error: 1146, SQLState: 42S02
2024-03-26 17:22:54,984 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-0) {errorMessage=Table 'focusfeedrtest.Feed' doesn't exist, errorCode=1146, sqlState=42S02}
2024-03-26 17:22:54,986 WARN [org.hib.rea.pro.ser.ReactiveGenerationTarget] (vert.x-eventloop-thread-0) HR000021: DDL command failed [org.hibernate.exception.SQLGrammarException: error executing SQL statement [{errorMessage=Table 'focusfeedrtest.Feed' doesn't exist, errorCode=1146, sqlState=42S02}] [alter table Feed drop foreign key user_id_fk]]
2024-03-26 17:22:55,000 WARN [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-0) SQL Error: 1146, SQLState: 42S02
2024-03-26 17:22:55,001 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-0) {errorMessage=Table 'focusfeedrtest.User' doesn't exist, errorCode=1146, sqlState=42S02}
2024-03-26 17:22:55,001 WARN [org.hib.rea.pro.ser.ReactiveGenerationTarget] (vert.x-eventloop-thread-0) HR000021: DDL command failed [org.hibernate.exception.SQLGrammarException: error executing SQL statement [{errorMessage=Table 'focusfeedrtest.User' doesn't exist, errorCode=1146, sqlState=42S02}] [alter table User drop foreign key FKsq0vxeiq66grjyl85gew6t7ha]]
2024-03-26 17:22:57,920 INFO [io.quarkus] (main) services 1.0.0-SNAPSHOT on JVM (powered by Quarkus 3.8.3) started in 7.865s. Listening on: http://localhost:8081
2024-03-26 17:22:57,920 INFO [io.quarkus] (main) Profile test activated.
2024-03-26 17:22:57,920 INFO [io.quarkus] (main) Installed features: [cdi, hibernate-orm, hibernate-reactive, hibernate-reactive-panache, reactive-mysql-client, resteasy-reactive, resteasy-reactive-jackson, security, security-jpa-reactive, smallrye-context-propagation, vertx]

2024-03-26 17:22:58,326 WARN [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-1) SQL Error: 1213, SQLState: 40001
2024-03-26 17:22:58,326 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (vert.x-eventloop-thread-1) {errorMessage=Deadlock found when trying to get lock; try restarting transaction, errorCode=1213, sqlState=40001}

User entity class :

package micro.user.entity;

import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.List;

import jakarta.persistence.*;
import micro.feed.entity.Feed;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import com.fasterxml.jackson.annotation.JsonIgnore;

import io.quarkus.elytron.security.common.BcryptUtil;
import io.quarkus.hibernate.reactive.panache.PanacheEntityBase;
import io.quarkus.security.jpa.Password;
import io.quarkus.security.jpa.Roles;
import io.quarkus.security.jpa.UserDefinition;
import io.quarkus.security.jpa.Username;
import io.smallrye.mutiny.Uni;

@Entity
@UserDefinition
public class User extends PanacheEntityBase {
	
	@Column(nullable = false, unique = true)
	private String displayedName;

	@Column
	@Password
	@JsonIgnore
	private String password;
	
	@Column(nullable = false, unique = true)
	@Username
	@Id
	private String email;
	
	@Roles
	@JsonIgnore
	private String role;

	@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
	//we instantiate this as we want it to be created at user creation
	private UserPreferences userPreferences = new UserPreferences();

	@OneToMany(mappedBy = "user",
			cascade = CascadeType.ALL,
			orphanRemoval = true)
	private List<Feed> feeds = new ArrayList<>();

    @CreationTimestamp
    public ZonedDateTime createdAt;

    @UpdateTimestamp
    public ZonedDateTime updatedAt;

	public String getDisplayedName() {
		return displayedName;
	}

	public void setDisplayedName(String displayedName) {
		this.displayedName = displayedName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getRole() {
		return role;
	}

	public void setRole(String role) {
		this.role = role;
	}

	public UserPreferences getUserPreferences() {
		return userPreferences;
	}

	public void setUserPreferences(UserPreferences userPreferences) {
		this.userPreferences = userPreferences;
	}

	public List<Feed> getFeeds() {
		return feeds;
	}

	public void setFeeds(List<Feed> feeds) {
		this.feeds = feeds;
	}

	public void addFeed(Feed feed) {
		this.feeds.add(feed);
		feed.setUser(this);
	}
	public static Uni<User> add( String displayedName, String email, String password, String role) {
        return of(displayedName, email, password, role).persist();
    }
    
    public static User of(String displayedName, String email, String password, String role) {
        User user = new User();
        user.displayedName=displayedName;
        user.email = email;
        user.password = BcryptUtil.bcryptHash(password);
        user.role = role;
        return user;
    }


}

Feed entity class :

@Entity (name = "Feed")
public class Feed extends PanacheEntity {
    @Column(nullable = false)
    private String message;
    @ManyToOne(fetch = FetchType.LAZY)
    private User user;

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }


    public static Uni<Feed> add(String message, User user) {
        return Feed.of(message, user).persist();
    }

    public static Feed of(String message, User user) {
        Feed result = new Feed();
        result.setMessage(message);
        result.setUser(user);

        return result;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

I'm not sure if my entities are unproperly coded or if it is an issue with the framework. I searched for quite a long time and most of what I found as solution for such use case is what I coded.
OneToOne relationship between User and UserPreference entities works fine.

Thank you in advance for the support you will provide.

Best regards

[mavina]

I forgot to put my application.properties. Here it is :

quarkus.datasource.db-kind=mysql
quarkus.datasource.username=focusfeedruser
quarkus.datasource.password=password
quarkus.datasource.reactive.url=mysql://localhost:3306/focusfeedr

%test.quarkus.datasource.db-kind=mysql
%test.quarkus.datasource.username=focusfeedruser
%test.quarkus.datasource.password=password
%test.quarkus.datasource.reactive.url=mysql://localhost:3306/focusfeedrtest
%test.quarkus.hibernate-orm.database.generation=drop-and-create

quarkus.hibernate-orm.database.generation=drop-and-create

quarkus.http.auth.basic=true

[TukaWarrior]

Hey, sorry to bother. Have you found a solution / the cause of the problem? I am currently doing a school project where I have to make a small backend for a blog app. I am getting the exact same error as you. During the drop phase at startup, it crashes. There are amyn posts about this problem but I haven't found one explaining the solution.

[mavina]

Hey, I'm sorry, I did not find a solution. I ended up going back to Spring and giving up Quarkus unfortunately. Best regards Le jeu. 22 août 2024 à 12:44, Luca Dario Bützberger < ***@***.***> a écrit :

…

Hey, sorry to bother. Have you found a solution / the cause of the problem? I am currently doing a school project where I have to make a small backend for a blog app. I am getting the exact same error as you. During the drop phase at startup, it crashes. There are amyn posts about this problem but I haven't found one explaining the solution. — Reply to this email directly, view it on GitHub <#39713 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB2PJNVHUDVQHZOTUFZEB4DZSW6J3AVCNFSM6AAAAABFJJFWPKVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTANBRG43TQMY> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[TukaWarrior]

Thank you for the quick reply. I have not found a solution yet but I think I bodged together a workaround. Though I have no idea what complications may arise because I am just starting to learn how to code.

In the applications properties, I set the generation type of the database to create.

quarkus.hibernate-orm.database.generation=create

Then I created an import.sql file here: /src/main/resources/import.sql
In said file, I wrote these lines.

DROP TABLE IF EXISTS blog_post CASCADE;
DROP TABLE IF EXISTS account CASCADE;
DROP TABLE IF EXISTS comment CASCADE;
```

[mavina]

Hey, the problem with that approach is that you actually have to maintain this file each time you create a table, or modify one. The whole point of this was not to have to handle database creation / modification. It'll work for your school project, but in a real situation, you want to avoid having such manual, easy to forget files to maintain