Enhance properties with: hibernate-orm.query.mutation_strategy

[baltabog]

Description

I start to update of an project from 2.13.0.Final to 3.2.5.Final and now I have an issue caused by hibernate (they improved bulk mutation strategie and now for oracle require HTE_* tables, but this can't be implemented in my case and I need to revert this behave using hibernate.query.mutation_strategy = "org.hibernate.query.sqm.mutation.internal.inline.InlineMutationStrategy")

https://docs.jboss.org/hibernate/orm/6.1/userguide/html_single/Hibernate_User_Guide.html#batch-bulk-hql-strategies

Implementation ideas

No response

[quarkus-bot[bot]]

/cc @Sanne (hibernate-orm), @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

Thanks for reporting this. In the meantime you can try this:

quarkus.hibernate-orm.unsupported-properties."hibernate.query.mutation_strategy" = "org.hibernate.query.sqm.mutation.internal.inline.InlineMutationStrategy"

As for adding the setting to Quarkus proper... I'm not familiar with it, unfortunately. I'm a bit surprised that it's needed to migrate, though, since the relevant section of Hibernate ORM's migration guide says "The temporary table approach [...] works in a similar way to how 5.x already implemented it".

Hey @beikov, did this work differently in 5.x?

[baltabog]

thanks for prompt response. I will try to use this on monday morning and I will came back with a response if works or not. In 5.x version hibernate doesn't create this temporary global tables at all :-)

[baltabog]

I just checked using: hibernate-orm: database: generation: validate unsupported-properties: "hibernate.query.mutation_strategy": "org.hibernate.query.sqm.mutation.internal.inline.InlineMutationStrategy" properties but I have no changes on DB level: 2023-09-04 08:01:23,285 WARN [io.qua.hib.orm.run.FastBootHibernatePersistenceProvider] (JPA Startup Thread) Persistence-unit [<default>] sets unsupported properties. These properties may not work correctly, and even if they do, that may change when upgrading to a newer version of Quarkus (even just a micro/patch version). Consider using a supported configuration property before falling back to unsupported ones. If there is no supported equivalent, make sure to file a feature request so that a supported configuration property can be added to Quarkus, and more importantly so that the configuration property is tested regularly. Unsupported properties being set: [hibernate.query.mutation_strategy] Hibernate: create global temporary table HTE_T_****( rn_ number(10,0) not null, ID number(19,0), ***, primary key (rn_)) on commit delete rows

[beikov]

Hey @beikov, did this work differently in 5.x?

This works the same way as in 5. We just have a different property name now. In 5 it was hibernate.hql.bulk_id_strategy and in 6 it's hibernate.query.insert_strategy and hibernate.query.mutation_strategy.

You should maybe also set the insert_strategy property @baltabog, but overall I'd suggest you rather stick with the default strategy, and create/maintain the temporary tables as part of your schema management process. You can disable the creation/drop of these temporary tables on startup by setting hibernate.hql.bulk_id_strategy.global_temporary.create_tables and hibernate.hql.bulk_id_strategy.global_temporary.drop_tables to false.

@yrodiere: It might be worth adding these properties to the "supported Quarkus properties", as I think these might be common for Oracle users.

[yrodiere]

@yrodiere: It might be worth adding these properties to the "supported Quarkus properties", as I think these might be common for Oracle users.

Ok I'll have a look.

@beikov Can you confirm that the name of the properties hibernate.hql.bulk_id_strategy.global_temporary.create_tables and hibernate.hql.bulk_id_strategy.global_temporary.drop_tables no longer makes sense considering how hibernate.hql.bulk_id_strategy was renamed? Do you plan on renaming it, and if so how? I'll try to pick a name in Quarkus that mirrors the new one in ORM.

[yrodiere]

@beikov Actually there are a few more problems, I opened https://hibernate.atlassian.net/browse/HHH-17162

[yrodiere]

@baltabog your problem was probably caused by the insert strategy still trying to create tables on startup. Please try my suggestions below and tell us if they help?

The problem

To clarify, this whole thread is about a feature that allows HQL to perform insert/update/delete queries on entities spanning multiple tables. It's not about inserting/updating/deleting entities through EntityManager/Session methods such as persist/delete.

With Oracle specifically (and a few other databases not officially supported in Quarkus), this feature relies on a global temporary table named HTE_<root entity table name>, which contains all columns of all tables involved in the entity hierarchy.

Hibernate ORM creates those tables on startup if they don't exist by default, and does so outside of the normal schema management tools (see HHH-15525), which can lead to failures if the DB user doesn't have permission to create tables.

The solution

We're currently discussing solutions with @beikov , and there might be a solution that doesn't involve changing the schema with Oracle. We'll see where this leads us and depending on that, we'll consider adding quarkus.hibernate-orm.query.insert-strategy/quarkus.hibernate-orm.query.mutation-strategy (or not) in Quarkus.

The workaround

In the meantime, if your database user doesn't have permission to alter the schema, you should use this:

# Disable automatic creation of temporary tables: the DB user can't alter the schema.
quarkus.hibernate-orm.unsupported-properties."hibernate.hql.bulk_id_strategy.global_temporary.create_tables" = false
quarkus.hibernate-orm.unsupported-properties."hibernate.hql.bulk_id_strategy.global_temporary.drop_tables" = false

This will at least get rid of startup failures.

Now if you also need HQL update/delete on multi-table entities, you will need to either:

• alter your schema through a different user with more permissions, to add a global temporary table named HTE_<root entity table name>, which contains all columns of all tables involved in the entity hierarchy.

  This will allow insert, update and delete in HQL for multi-table entities.

• OR configure Hibernate ORM to use the (badly-performing) inline strategy for mutations:

  # Use a badly-performing strategy, but one that doesn't require altering our DB schema
  quarkus.hibernate-orm.unsupported-properties."hibernate.query.mutation_strategy" = inline

  There is no equivalent for insert, so:

  1. You still need the create_tables/drop_tables settings from the snippet above to tell the insert strategy not to try creating or dropping global temporary tables
  2. This will not allow insert in HQL for multi-table entities

  But at least this will allow update and delete in HQL for multi-table entities.

[baltabog]

Hi all, thanks for all this time you invest in this. In next days, I will try to change my properties as you mention.

[baltabog]

sugested workaround worked for me. Thanks once more!