Hibernate 6 sequece maybe not working as expected

Hibernate ORM
1

Few days ago I finished project migration from Hibernate 5 + Hibernate Search 5 to Hibernate 6 + Hibernate Search 6.

Underlying database is MySQL and there are 19 tables that stores entity data.
With Hibernate 5 I had only one sequence file hibernate_sequence.
To see what Hibernate 6 wants, I did first app run without any table and with option spring.jpa.hibernate.ddl-auto set to create. Then I notieced each table got its _seq table.
After some analysis the idea was to upgrade existing production database by dropping hibernate_sequence and manually creating sequence for each entity table.

Here is the SQL for single entity/table, one of 19 I have:

DROP TABLE hibernate_sequence;

CREATE TABLE article_seq ( next_val BIGINT(20) );
INSERT INTO article_seq ( next_val ) VALUES ( 1 );
UPDATE article_seq SET next_val = (
    SELECT MAX(id) + 1
    FROM article
);

Right after database upgrade the status is:

mysql> select max(id) from search_history_item;
+---------+
| max(id) |
+---------+
|   12131 |
+---------+
1 row in set (0.00 sec)

mysql> select * from search_history_item_seq;
+----------+
| next_val |
+----------+
|    12132 |
+----------+
1 row in set (0.00 sec)

Then I start application and reads are working as expected - pages are rendered, pagination works as expected.

But after first write attempt I get an exception:

SQL Error: 1062, SQLState: 23000
Duplicate entry ‘12084’ for key ‘search_history_item.PRIMARY’
DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [search_history_item.PRIMARY]
DataIntegrityViolationException: could not execute statement;

New status is the same as old status - last ID unchanged and next_val unchanged:

mysql> select max(id) from search_history_item;
+---------+
| max(id) |
+---------+
|   12131 |
+---------+
1 row in set (0.00 sec)

mysql> select * from search_history_item_seq;
+----------+
| next_val |
+----------+
|    12132 |
+----------+
1 row in set (0.00 sec)

After this second attempt to write I get almost the same exception with only difference in ID mentioned - now this problematic ID is 12085 (previously it was 12084).

Java part:

class SearchHistoryItem {
	@Id  @GeneratedValue( strategy = GenerationType.SEQUENCE )
	private Integer id;
	// ...
}

What am I doing wrong?

2

Pretty sure that the value in the sequence table is the “high” value i.e. you have to adjust the update statement to e.g.

UPDATE article_seq SET next_val = (
    SELECT MAX(id) + <increment> + 1
    FROM article
);

where <increment> is what you specified for your @SequenceGenerator(allocationSize), which defaults to 50 if you don’t specify the annotation. It might be worth specifying the annotation to adapt the allocation size though.
Alternatively, you can also switch back to the old behavior if you want that. See hibernate-orm/migration-guide.adoc at 6.0 ¡ hibernate/hibernate-orm ¡ GitHub

3

Switching back to old behaviour is not an option because I want to learn and become friends with new Hibernate and upgrade my project to be up to date.

Now I tried this:

@Id
@GeneratedValue(
	strategy = GenerationType.SEQUENCE,
	generator = "sequence-generator"
)
@SequenceGenerator(
	name = "sequence-generator",
	allocationSize = 5
)
private Integer id;

But I get only one table in MySQL named sequence-generator (name is not the problem, just to mention, suppose I can change it with sequenceName parameter inside @SequenceGenerator).

Please can you tell me is the default Hibernate 6 behaviour to create sequence table for each entity table better than single table (which I guess it’s true - if not, it wouldn’t be default behaviour) and what is the right way to upgrade my entities and tables to comply with it.

P.S. Oh, yes, forgot to mention I have single abstract class BaseEntity wich defines ID and all other entities extend this class.

abstract class BaseEntity {
	@Id
	@GeneratedValue(
		strategy = GenerationType.SEQUENCE,
		generator = "sequence-generator"
	)
	@SequenceGenerator(
		name = "sequence-generator",
 		allocationSize = 5
	)
	private Integer id;

	private ZonedDateTime dateCreated;
	private ZonedDateTime dateLastUpdate;
}
4

Creating a sequence per entity by default happens to be a side effect of how we deduce certain object names based on the entity name. I guess that having a separate table could be beneficial though, as there might be some possible contention issues with having just a single table on certain databases that coarsen locks.

I found a discussion where we discussed the “hibernate_sequence” approach:

  • no real application is using that
  • apps that do, most likely don’t care
  • apps that do, and do care and just specify the sequence name

From my POV, you can continue using the “hibernate_sequence” approach or the table per sequence approach. Transactions on the table are very short, and the only database that possibly uses table based sequences is MySQL, which as far as I know, does not widen row locks to page locks, so you should be fine.

P.S. Oh, yes, forgot to mention I have single abstract class BaseEntity wich defines ID and all other entities extends this class.

This is why changing/specifying the name in the base entity leads to using the same table for all entities.

what is the right way to upgrade my entities and tables to comply with it.

Hard to say. If I were you, I would just continue using the “hibernate_sequence” approach.

1 Like
5

Those are steps which solved my issue:

  • exported original database from production environment to my local development machine
  • issued this SQL to update hibernate_sequence:
UPDATE hibernate_sequence h SET h.next_val = (h.next_val + 50 + 1) WHERE 1 = 1;

Without this update exception was always thrown and now I understand why. Current value in hibernate_sequence is some value taken from production environment while application was working.

When new Hibernate starts, I guess it takes in account the window of 50 entries calculated from current value in hibernate_sequence rounding to 50 and perhaps this value becomes less that actual value in hibernate_sequence.

After this update everything works fine.

Thanks!