Showing posts with label identity. Show all posts

Thursday, 30 January 2020

MariaDB issues

I ran into some issues, and I thought I'd document them here.

Not able to detect platform for vendor name [MariaDB1010.3.17-MariaDB]. Defaulting to [org.eclipse.persistence.platform.database.DatabasePlatform]. The database dialect used may not match with the database you are using. Please explicitly provide a platform using property "eclipselink.target-database".]]

So changed my persistence.xml and added:

Also:

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=16) Table 'mmud.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

As the vendor name was not detected, eclipselink switched to a default implementation. The default implementation requires SEQUENCES for the IDENTITY definition.

It turns out the MariaDB implementation of Sequences is not compatible with the standard SQL way of creating sequences.

So I had to add the following property to the JDBC Connection pool:

useMysqlMetadata = true

References

JIRA MariaDB : since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns: https://jira.mariadb.org/browse/CONJ-702
Java Persistence API (JPA) Extensions Reference for EclipseLink, Release 2.4 : target-database: https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_target_database.htm
Eclipse JIRA : Bug 462196 - Add support for MariaDB: https://bugs.eclipse.org/bugs/show_bug.cgi?id=462196

Wednesday, 4 March 2015

Sequence Generators and Hibernate

Hibernate has a Hi-Lo Sequence Generator that maps to a Database Sequence (for example Oracle Sequence) with a multiplication.

What this means is, when you retrieve the next value from the Database Sequence (which returns 1), Hibernate will multiply that with a "allocationSize", the default for which is 50.

Now Hibernate can (stored at application level) use it to create PK numbers from 1 to 50.

The next time a Database Sequence value is retrieved (for example 2), Hibernate gets to create PK numbers 51 to 100.

Conclusion

This seems to be a valuable strategy, if your database sees a lot of inserted records and high loads and the sequence starts to be a bottleneck.

This can be a valid issue, for example when you run Batch jobs that cause a lot of inserts for every transaction.

Caveat Emptor

The reason for this blog is that a Colleague of mine at work, got a customer on the phone who was spooked, as his new PKs suddenly were a noticable factor bigger.

And, of course, it is a living hell to change this sequence schema afterwards to something more simple, as you run the risk of duplicate PKs.

Quote

“Only think of database sequences as unique - nothing more, nothing less.”

This means that the one task of a sequence generator is to provide you with values that have not been used yet, not will they be ever again.

It is an error, to assume that there is any kind of ordering in the numbers issued, or that there are no 'holes' in the sequence.

References

Vlad Mihalcea's Blog - The hi/lo algorithm: http://vladmihalcea.com/2014/06/23/the-hilo-algorithm/
JavaDocs 7 - SequenceGenerator: http://docs.oracle.com/javaee/7/api/javax/persistence/SequenceGenerator.html
StackOverflow - Hibernate, @SequenceGenerator and allocationSize: http://stackoverflow.com/questions/12745751/hibernate-sequencegenerator-and-allocationsize
Fixing JPA2 Sequence Generator Problem With Hibernate 3.5: http://www.petrikainulainen.net/programming/tips-and-tricks/fixing-jpa2-sequence-generator-problem-with-hibernate-3-5/