DEV Community: Michael Brizic

JaVers: Periodic Maintenance of the Audit Tables

Michael Brizic — Thu, 04 Apr 2024 13:08:04 +0000

This article is about using JaVers for versioning your data object's within your Java application and performing maintenance on the JaVers audit tables that are used to track the changes.

JaVers is meant for storing an audit trail of changes made to an application data model. It's a very nice software product and is very simple to integrate into a Java Spring Boot application. Read here for further.

One day I discovered that the development and testing environments' databases had grown super huge. It turns out that the JaVers audit tables were contributing the most to the overall size.

However, I did not find any out-of-the-box feature that allows one to perform periodic maintenance of the audit tables. As far as I know, the audit tables simply grow and grow, along with all the changes to the data model, ad infinitum. I also did not find any documentation on how to do this.

I opened a GitHub question but it wasn't answered as quickly as I hoped. The development and testing environments' databases were so large that I was suddenly running out of disk space. I thought it might be nice to clean up the audit tables that JaVers uses rather than simply add more disk space since after all, the environments were merely for development and testing.

After studying the source code and the tables I realized that there are a total of four tables that JaVers creates and uses. I also figured out their relationships to one another and how cleaning up the data would be executed.

In my particular case, the database is MySQL and runs inside of a Docker container. So my solution includes interacting with the database via the Docker container.

The first part that I'll share below are the tables and the commands needed to reduce the size of data that they store. It requires that you choose a data for which all data from that point going backwards will be removed. The data provided should be given as yyyy-MM-dd.

The above would be saved to a file named cleanup-javers.sql. Then, the commands of the SQL file will be executed according to the following convenience script:

For convenience, I also added the above to files and hosted them in my GitHub located here.

Let me know if some corrections need to be made or enhancements. Thanks!

Java 17 Upgrade with Spring Boot, Hibernate, MySQL and H2

Michael Brizic — Sun, 08 Jan 2023 13:15:00 +0000

Preface

This article considers a web application based on the following software versions:

Spring Boot version 2.6.8
- Hibernate 5.6.9.FINAL (as a managed dependency of Spring Boot)
Java 11
MySQL 5.7 (for server/application use)
H2 (for integration tests)

The goal: To upgrade the web application to Java 17 (the new LTS version).

Problem

Upgrading a Java 11 based Spring Boot web app to 17 was fairly smooth until I opened a PR and the code was built using CI hosted by Bitbucket Pipelines.

Prior to opening the PR I had built and ran all unit and integration tests locally on my MacBook Pro. I also ran the web app and did some user acceptance testing ensuring the app actually works.

So what was the problem when building and running the integration tests on the CI machines?

The Spring Boot JPA was throwing OptimisticLockingFailureExceptions but the cause was the internal Hibernate StaleObjectStateException.

I was struck by the oddity of this outcome since locally all tests passed. And, I ran the application as well and everything worked just fine. So why were the integration tests failing in the CI build (Bitbucket Pipeline)?

Guessing At the Answer

You would be right if you guessed that the problem might be related to the environment. I too realized that my MacBook laptop is not the same as running the build in the CI environment. But I was surprised to learn exactly what about the different environments was actually the source of the problem.

I observed that integration tests which were performing inserts and updates to rows in our H2 database (used for integration tests) were indeed failing the @version check performed by Hibernate. But why? I decided to Google and found the below post on a Hibernate forum:

Java 17 @version and nanoseconds truncation

Here is the bug that was opened and fixed by Hibernate

https://hibernate.atlassian.net/browse/HHH-15135

OK. So the forum and bug ticket seemed very promising as to the source of the problem, but why did it work on my machine, and not the CI? Why did the web app work when deployed too?

Further Research and Understanding

I thought I had read all the cool new features that were releases as part of Java 17 LTS. I don’t recall reading anything about nanosecond precision! Apparently, the Java team introduced nanosecond precision to their system clock in JDK 15. Of course, Java 17 includes these changes.

(See below the links to the original bug/ticket, the pull request with source changes, and the email detailing the enhancement)

Enhance the system clock to nanosecond precision

jdk/jdk: f1f88e6fad02

os::javaTimeSystemUTC to call nanosecond precision OS API, so Clock.systemUTC() can give nanosecond precision UTC

Very interesting, yes?

Making Sense of It All

An example entity had the following @version property:

@Version
@Column(columnDefinition = "DATETIME(6)")
private Instant version;

This entity property indicates that the field named version is a Java Instant and will be used for versioning database rows. The @column annotation with the columnDefinition property is going to use DDL to create this table column as a datetime with precision of 6. But didn’t we learn that Java ≥ 15 now uses nanosecond precision for time? I immediately checked docs for MySQL and H2. MySQL datetime columns only allow a maximum precision of 6. But, H2 allows a maximum of 9! Furthermore, H2 doesn’t have a data type known as datetime, so properties with this type will be coerced into timestamps. It would seem like all this information is beginning to make sense, but still, how did the integration tests pass on my MacBook but not CI?

The answer to that has to do with Java supporting the time precision that is allowed by the OS libraries it has access to. So, if you are running Java on an Apple Mac, the highest precision will be 6. But running Java on a Linux based machine will support a time precision of nanoseconds.

Let’s recap:

Java 15 and onward, uses the precision supported by the OS. I didn’t have any errors on my Apple MacBook Pro because it doesn’t support nanosecond precision. Linux however, DOES support nanosecond precision.
MySQL only stores datetimes with microsecond precision.
H2 coerces datetime types into timestamps and fully supports up to 9 digits of precision i.e. nanoseconds.
Datetime objects created with Java and the underlying OS with nanosecond precision will be chopped off to microseconds and thus any @version comparisons within Hibernate will result in OptimisticLockExceptions.

But ***WHY*** does it work with MySQL (server/application use) and not H2 (integration tests only on OS that support nanosecond precision)?

The answer has to do with how each of the databases differs in the handling of the datetime data types with respect to Hibernate. It should be obvious that if the system, and thus Java, support nanoseconds, but the database data column is defined with datetime(6), then rounding must occur. Apparently, Hibernate in the generation of the SQL queries will rely on the specific database implementation for handling the data types. Read here for more details:

Explain why datetime types for @Version get OLE using >= Java 15

H2 is handling the rounding of nanosecond Instants and what they are compared to within Hibernate for the SQL update query differently than MySQL and thus using a Java Instant with nanosecond precision stored as an @version property with a lower precision of microseconds does not work.

The Solution

Make the columnDefintion datetime precision equal to nanoseconds.

@Version
@Column(columnDefinition = "DATETIME(9)")
private Instant version;

This solves the problem with H2 when running integration tests on JVMs running on OSs that support that level of precision. Furthermore, the highest level of precision allowed on MySQL is microseconds so this setting will result in a datetime with the default precision of 6.

Alternate Solutions (considered but not feasible)

Upgrade Hibernate

You will have noticed that the issue of handling precision with Java datetime objects was resolved if you read the link to the Hibernate bug ticket earlier. However, the bug was fixed in version 6. So why not upgrade to the Hibernate library version that contains this bug fix? Well, in Spring Boot 2.6.8, Hibernate 6 is prohibited. This has to do with an even larger change that occurred within Jakarata, the newest Java EE specification. The namespaces were changed and thus running an application on Spring Boot 2.6.8 does not allow using Hibernate 6.
Upgrade Spring Boot

Another approach considered was to update Spring Boot itself in order to get Hibernate 6. This proved unfeasible because I would have to update the web app to Spring Boot 3 and this in itself will be a rather non-simple migration.
Don’t use H2 for integration tests

Yet another approach was to NOT use H2 for the integration tests. This too was not ideal (at the time) since the integration test is large and varied. H2 provides speed as it is in-memory (we don’t need to save the data). Our original goal was to avoid starting up separate database processes and extending the time that it would take to run tests as part of our continuous integration pipeline.

Since solving this problem I have discovered something known as testcontainers and will be exploring that as an alternative to using H2 in-memory. My work on testcontainers will likely result in another article once I figure it out and get it working.

https://www.testcontainers.org/

Solving Problems By Guessing

Michael Brizic — Mon, 12 Sep 2022 10:22:18 +0000

I think that we shall have to get accustomed to the idea that we must not look upon science as a 'body of knowledge,' but rather as a system of hypotheses; that is to say, as a system of guesses or anticipations which in principle cannot be justified, but with which we work as long as they stand up to tests, and of which we are never justified in saying that we know they are 'true' or 'more or less certain' or even 'probable.'
-- Karl Popper

Karl's quote not only applies to explanatory knowledge but is equally as important regarding science that includes the solving of particular problems.

Software development is definitely a part of that science. Guessing is how I, and many software engineers, find a working solution to the various problems that arise. Indeed, almost anyone trying to solve a problem uses some form of guessing to some extent. There is absolutely nothing wrong with the educated guess. In fact, more often than not, I have no absolute answers at all and am guessing at almost every problem that arises.

Recently, I have had the pleasure of upgrading a Java web server to compile and run on version 17. The original version upon which it was based is 11. This type of work usually requires a significant amount of time due to the longer feedback loop of having to not only pass compilation, but also all test suites, including running the app.

I manage to get through the aforementioned gates on my local machine, a MacBook Pro. I open a pull request for the team to review and let the CI pipeline do its thing. It fails. Many of the integration tests are causing the build to fail. There's a problem, and it has to be solved. Am I instantly conjuring up stored knowledge that succinctly solves the problem? No.

Immediately, the guesswork begins. Questions come to mind in the form of hows and whys. All a very natural part of the process. Much of it in the beginning is in the form of wild finger-pointing, mostly at oneself. Did I forget X? Is something wrong in the way I merged and kicked off the build? Bad parameters? But how did everything work locally? Must be an environmental issue. Is my local environment off? How is my local environment different from the CI pipeline's environment?

As one reflects, and questions the source of the problem, some questions will appear more likely than others. Selection among the candidates is the logical next step. Which of the thoughts, the questions, the guesses does one pursuit?

This is exactly how many problems are solved: creative guessing followed by selection and testing. Realize there is a problem, start wondering at how or why it is a problem, select among the guesses as to which one it might be, and then test whether the guess is valid and leads one towards identifying the problem. It is very rare that I draw upon some stored knowledge whereby I'm able to immediately and definitively answer or solve a problem in an instant.

Of course some guesses are going to be better than others, and have a higher probability at determining the source of the problem and how to solve it. This is essentially the meaning behind the oft-repeated apothegm of take an educated guess. So it's not just that one is guessing but that one is using prior knowledge and experience to inform the guess. One may even be using instincts (but perhaps informed by past experience). Frankly, there are times when one's guess is informed by others. This too, is an educated guess, just not necessarily educated by oneself. Other people, or knowledge sources, don't have 100% of the problem context but can provide insight with some level of surety that perhaps their knowledge and experience will identify the problem source and a path to a solution, having studied or solved something similar in the past.

Usually wild and ridiculous guessing is not really fruitful. Certainly there are times when spit-balling is useful and necessary such as when one doesn't even know where to begin or has had no prior experience or knowledge within a given problem context. And, random, uneducated guessing has proven itself able to solve problems, since this approach can potentially lead to unorthodox thinking and discovery. Rather, the educated guess, guessing with human creativity, instinct, or intuition, is going to be more efficient and effective. And thus one tries to use all previous knowledge one has acquired including any and all experiences to build a set of potential, testable guesses. Before even trying one of the guesses it's worthwhile to think them through. The so-called thought experiment is useful during the process. One might test the guess inside one's mind before even trying it in reality. Absolutely put the guess to a test in reality if thought experiments don't yield progress due to challenges of the problem context. This must be done eventually anyway. Thought experiments may be helpful in reducing the amount of time one spends selecting from among the set of guesses.

Testing cannot be overemphasized enough. It is without exception, necessary. For making educated guesses will always remain open to error correction. And this is the portion related to the practice of science that underscores the rarity of it being 100% absolutely correct for all of time. Guessing is the way, but fallibilism in the guesses must be accepted, and tolerated. Even after one has tested one's most cherished guesses, there could be any number of other factors that come along at some point and prove the guesses incomplete, or simply wrong.

First you guess. Don’t laugh, this is the most important step. Then you compute the consequences. Compare the consequences to experience. If it disagrees with experience, the guess is wrong. In that simple statement is the key to science. It doesn’t matter how beautiful your guess is or how smart you are or what your name is. If it disagrees with experience, it’s wrong. That’s all there is to it. -- Richard Feynman

In summary, guessing is an important part of science, notwithstanding, in solving problems. It's the educated guesses that usually yield more efficient and effective success although uneducated guessing sometimes works by revealing fresh ideas. Creative guessing is harder and requires more thinking to narrow the applicability to the problem. And guessing is not the complete process as one must also think with knowledge in order to select among the set of guesses which will likely be correct. Lastly, all guesses must be tested. The testing is sometimes done in one's mind playing out scenarios against outcomes. However, subjecting one's guesses to physical testing is required. In many cases, continual testing must be done to ensure that one's guess hasn't missed some part of the picture, or correct various errors that aren't always apparent initially.