UnexpectedAccessToTheDatabase error when updating (session.merge()) an existing entity with a ManyToMany relation, which uses a JoinTable with composite id.

[BertSuffys]

Allow me to clarify;

My system has Users and roles. A user can have many roles, and each role can belong to multiple users. A User has a list of roles, mapped as ManyToMany, fetched eagerly. It is also using an intermediary table with a composite id.

Consider the code below;

public void updateUser(RoutingContext routingContext) {
    Map<String, String> postBody = getRequestBody(routingContext);
    if (postBody != null) {
      String id = postBody.get("id");
      if (id != null) {
        userRepository.getUserById(Long.decode(id)).onComplete(cmsUserGet -> {
          if (cmsUserGet.succeeded()) {

            // language
            String urlLang = routingContext.request().getParam("lang");
            Lang lang = urlLang != null ? Lang.createLang(urlLang) : LangUtil.getConfigDefaultLang();
            CmsUser cmsUser = cmsUserGet.result();
            cmsUser.setLang(lang);

            this.roleRepository.getAllCmsRoles().onComplete(roles -> {

              // Retrieve fields
              String firstname = postBody.get("firstname");
              String lastname = postBody.get("lastname");
              String email = postBody.get("email");
              String title = postBody.get("title");
              String password = AuthenticationUtil.bCrypt(postBody.get("password"));
              List<Long> roleIds = Arrays.stream(postBody.get("roles").split(",")).map(Long::parseLong).toList();
              List<CmsRole> selectedRoles = roles.result().stream().filter(obj -> roleIds.contains(obj.getId())).toList();

              // Update fields
              cmsUser.getRoles().clear();
              cmsUser.getRoles().addAll(selectedRoles);
              cmsUser.setFirstname(firstname);
              cmsUser.setLastname(lastname);
              cmsUser.setEmail(email);
              cmsUser.setTitle(title);
              if(password != null){
                cmsUser.setPassword(password);
              }

              // Update
              userRepository.updateUser(cmsUser).onComplete(cmsUserUpdate -> {
                if (cmsUserUpdate.succeeded()) {
                  ResponseUtil.ok(routingContext, new CmsUserDTO(cmsUser, lang).serializeJson().encode());
                } else {
                  ResponseUtil.internal_server_error(routingContext, "Something went wrong updating the CMSRole");
                }
              });
            });
          } else {
            ResponseUtil.internal_server_error(routingContext, "Something went wrong retrieving the CmsRole by the provided ID.");
          }
        });
      } else {
        ResponseUtil.bad_request(routingContext, "No CmsRole id was provided.");
      }
    }
  }

The problem:

In the above code an existing user is updated and persisted. The user is retrieved by Id, all Roles are retrieved, the user is updated given the provided data & chosen roles, and persisted again. The problem occurs in userRepository.updateUser(cmsUser). I've been able to figure out that the issue must be related to the relationship between Users and Roles. If a user gets persisted, and has less roles coupled to it than it did before, i get no issues. As soon as an existing role that was not yet coupled to the user, does get coupled, i get the error on persisting. Below i will provide the specific error as well as some other classes. This is my first issue post, so hopefully it is formatted in a somewhat proper manner. Thanks alot in advance because this one is really puzzling me :o

org.hibernate.HibernateException: java.util.concurrent.CompletionException: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database

Repository methods:

  public Future<CmsUser> getUserById(Long id) {
    CompletionStage<CmsUser> cmsRoleCompletionStage = super.getSessionFactory().withTransaction((session, transaction) ->
      session.find(CmsUser.class, id)
    );
    return Future.fromCompletionStage(cmsRoleCompletionStage).map(role -> role); // Maps directly to null if not found
  }
  
 public Future<CmsUser> updateUser(CmsUser updatedCmsUser) {
    CompletionStage<CmsUser> updateStage = super.getSessionFactory().withTransaction((session, transaction) -> {
      return session.merge(updatedCmsUser);
    });
    return Future.fromCompletionStage(updateStage);
  }
  
  
public Future<List<CmsRole>> getAllCmsRoles() {
    CompletionStage<List<CmsRole>> cmsRolesCompletionStage = super.getSessionFactory().withTransaction((session, transaction) ->
      session.createQuery("FROM CmsRole", CmsRole.class).getResultList()
    );
    return Future.fromCompletionStage(cmsRolesCompletionStage).map(roles -> roles);
 }

CmsUser:

@Entity
@Table(name="cms_user")
public class CmsUser extends Translated {

  /* FIELDS */
  private Long id;
  private String email;
  private String password;
  private List<CmsRole> roles = new ArrayList<>();
  private String firstname;
  private String lastname;

  /* CONSTRUCTOR */
  public CmsUser(){
      super();
  }

  @Override
  public Translation getUntranslated(Lang lang) {
    CmsUserLang cmsUserLang = new CmsUserLang();
    cmsUserLang.setId(null); // signals non-db entity
    cmsUserLang.setTitle(ConfigHolder.project_config.getJsonObject("framework").getString("missing_translation_text"));
    cmsUserLang.setLang(lang);
    return cmsUserLang;
  }

  public CmsUser(String email, String password){
    this();
    setEmail(email);
    setPassword(password);
  }

  public static CmsUser init(String firstname, String lastname, String email, String password, String title, List<CmsRole> roles, Lang lang){
    CmsUser cmsUser = new CmsUser();
    cmsUser.setFirstname(firstname);
    cmsUser.setLastname(lastname);
    cmsUser.setEmail(email);
    cmsUser.setRoles(roles);
    cmsUser.setPassword(AuthenticationUtil.bCrypt(password));
    // lang
    CmsUserLang cmsUserLang = new CmsUserLang();
    cmsUserLang.setTitle(title);
    cmsUserLang.setTranslated(cmsUser);
    cmsUserLang.setLang(lang);
    cmsUser.addTranslation(cmsUserLang);
    return cmsUser;
  }

  @Transient
  public void setLang(Lang lang) {
    try{
      super.setLang(lang, CmsUserLang.class, this);
    }catch (TranslationNotFoundException e){
      e.printStackTrace();
    }
  }

  /* GETTERS AND SETTERS */
  @OneToMany(mappedBy = "translated", fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, targetEntity = CmsUserLang.class)
  public List<Translation> getTranslations() {
    return super.getTranslations();
  }
  public void setTranslations(List<Translation> translations) {
    super.setTranslations(translations);
  }

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "cms_user_seq")
  @SequenceGenerator(name = "cms_user_seq", sequenceName = "cms_user_seq")
  public Long getId() {
    return id;
  }

  @ManyToMany(fetch = FetchType.EAGER)
  @JoinTable(
    name = "cms_user_role",
    joinColumns = @JoinColumn(name = "user_id"),
    inverseJoinColumns = @JoinColumn(name = "role_id")
  )
  public List<CmsRole> getRoles() {
    return roles;
  }

  @Transient
  public List<Long> getRoleIds() {
    List<Long> roleIds = new ArrayList<>();
    if (roles != null) {
      for (CmsRole role : roles) {
        roleIds.add(role.getId());
      }
    }
    return roleIds;
  }

  @Override
  public String toString(){
    return String.format("%s %s %s", this.firstname, this.lastname, this.email);
  }
  @Column(length = 500)
  public String getFirstname() {
    return firstname;
  }
  public void setFirstname(String firstname) {
    this.firstname = firstname;
  }
  @Column(length = 500)
  public String getLastname() {
    return lastname;
  }
  public void setLastname(String lastname) {
    this.lastname = lastname;
  }
  @Transient
  public String getTitle() {
    return ((CmsUserLang)super.getActiveTranslation()).getTitle();
  }
  public void setTitle(String title) {
     ((CmsUserLang)super.getActiveTranslation()).setTitle(title);
  }

  public void setId(Long id) {
    this.id = id;
  }

  public void setRoles( List<CmsRole> roles) {
    this.roles = roles;
  }
  public String getEmail() {
    return email;
  }

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

  public String getPassword() {
    return password;
  }

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

}

A CmsRole

@Entity
@Table(name="cms_role")
public class CmsRole extends Translated {

  /* FIELDS */
  private Long id;
  private String code;
  private int score;

  /* METHODS */
  @Transient
  public void setLang(Lang lang) {
    try{
      super.setLang(lang, CmsRoleLang.class, this);
    }catch (TranslationNotFoundException e){
      e.printStackTrace();
    }
  }

  /* CONSTRUCTOR */
  public CmsRole(){
  }

  @Override
  public CmsRoleLang getUntranslated(Lang lang) {
    CmsRoleLang cmsRoleLang = new CmsRoleLang();
    cmsRoleLang.setId(-1L); // signals non-db entity
    cmsRoleLang.setName(ConfigHolder.project_config.getJsonObject("framework").getString("missing_translation_text"));
    cmsRoleLang.setLang(lang);
    return cmsRoleLang;
  }

  public static CmsRole init(String code, String name, int score, Lang lang){
    CmsRole cmsRole = new CmsRole();
    cmsRole.setCode(code);
    cmsRole.setScore(score);
    CmsRoleLang cmsRoleLang = new CmsRoleLang();
    cmsRoleLang.setName(name);
    cmsRoleLang.setTranslated(cmsRole);
    cmsRoleLang.setLang(lang);
    cmsRole.addTranslation(cmsRoleLang);
    return cmsRole;
  }

  /* GETTER AND SETTER */
  @OneToMany(mappedBy = "translated", fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, targetEntity = CmsRoleLang.class)
  public List<Translation> getTranslations() {
    return super.getTranslations();
  }
  public void setTranslations(List<Translation> translations) {
    super.setTranslations(translations);
  }

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "cms_role_seq")
  @SequenceGenerator(name = "cms_role_seq", sequenceName = "cms_role_seq")
  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  @Override
  @Transient
  public String toString() {
    // Ex: {id:5, name:'client'}
    return String.format("{\"id\":%d, \"name\":\"%s\"}", this.id, this.getName());
  }
  @Transient
  public String getName() {
    return ((CmsRoleLang)super.getActiveTranslation()).getName();
  }
  public void setName(String name) {
    ((CmsRoleLang)super.getActiveTranslation()).setName(name);
  }

  @Column(length = 100)
  public String getCode() {
    return code;
  }

  public void setCode(String code) {
    this.code = code;
  }
  public int getScore() {
    return score;
  }
  public void setScore(int score) {
    this.score = score;
  }
}

[Vincentelias]

Have you tried turning it on and off again?