The whole article mainly talks about “what kind of Bug we encountered and how to locate and solve it”, which involves relatively simple knowledge points.
H2 and FitNesse need to be introduced, as they are the basis of the whole article.
Relax. It’s just a quick overview. Just know what they are.
The H2 database
What is the H2 database? Let’s take a look at GitHub’s introduction:
In my poor English, I can know the following characteristics:
Very fast, Open Source, Disk-based or memory-based
A quick summary:
- H2 is a Java SQL Database, which is an open source database that runs very fast.
- H2 is popular because it can be run as a standalone server or as a nested service, and it supports pure memory.
- The H2 JAR package is very small, only 2M in size, so it is ideal for embedded databases.
In Java development, databases are often used as unit tests because of their support for a pure memory form. Pre-run insert, since it is based on memory, is cleared directly after run.
OK, so now you have a general idea of what H2 is.
FitNesse
Testing is important, but covering test cases takes a lot of effort.
Especially for complex functions, it is unrealistic for developers to cover test cases.
And for some of the inner code (such as the Service layer code), the test students are not involved in the test.
So, is there a tool that allows developers to focus only on development and testers to test the inner code?
Some! FitNesse can do that.
FitNesse architecture diagram (source: https://www.testwo.com/blog/4805)
This is the Architecture diagram of FitNesse, and in a nutshell, the yellow parts need to be developed.
The Test Cases module can be seen as the Test Cases written by students in wiki. The Custom Fixtures and SUT modules on the right are a test interface developed by the students.
Think of it simply as an automated testing tool. Developers only need to provide a test interface, and then testers can write test cases by writing a wiki to keep testing and development separate.
If not, just know that FitNesse is a framework that can run test cases
practice
Taking our company as an example, we selected FitNesse interface for some businesses that needed to be tested, and the students who developed the FitNesse interface provided the test interface, so that the test students could write wiki to cover the test cases.
FitNesse supports running a single test case, as well as running multiple test cases at once, similar to JUnit.
After setting up the environment, the test students covered all the test cases, and they only needed to run these test cases regularly through scripts every day.
Testing is actually an input-output verification process, so in the case of high enough test coverage, all test cases of a function module can run, it can represent the function is not a problem.
The problem
To ensure that the direct data of each test case does not affect each other, we clear the cache before each test case runs and TRUNCATE all the tables.
TRUNCATE empties or truncates a table, clearing all rows in the table, but the table structure, constraints and indexes remain unchanged, resetting the self-increment of the table.
In theory, as long as you clear all the caches and reset all the tables, there should be no data interference.
In the real world, a problem arises:
While automating batch test cases in production, we found that some use cases did not run successfully, and the auto-increment primary key of some tables was not reset. These use cases can run alone.
To solve the process
Consider two points:
- Because of caching, it’s not like 1,000 test cases will fail.
- If the table is not cleared before each use case, it is more unlikely that only these use cases will fail.
So I’m going to rule out caching and clearing tables that aren’t clean. This “take for granted” for the back buried foreshadowing, very key, remember, this to test!
Simulate failed use cases
First, I’ll talk about abstracting test cases locally. Wrote a very simple code, simulated running two test cases at the same time, simulated the online use case error.
The TRUNCATE command can reset the primary key ID
As stated in the official document above, TRUNCATE command can reset the increment primary key.
Drop
DROP deletes all data in a table and deletes the table structure, constraints, and indexes.
The above experiment led me to point the finger at the cache, but after a careful examination of the cache, I was able to confirm that all the caches had been cleared.
Which makes me wonder if it’s the watch. As a result, I tried the stupid method of dropping all tables with the DROP command before each use case and then rebuilding the tables.
I reran the test cases with the idea of trying them out, but I didn’t expect all of them to work.
But because each test case was preceded by dropping all the tables (hundreds of them) and recreating them with the DROP command, the test case took a long time to run.
The cache
How to solve the problem of slow speed caused by DROP command?
My first idea was to swap space for time, and put the used tables into a container. Before each test case, I only need to traverse the container to delete the used tables and then rebuild the tables.
In a test environment with hundreds of tables, each test case may use a maximum of a dozen tables, which saves a lot of time on deleting tables and building tables. The extra memory consumed is almost negligible.
After this adjustment, running 1000 test cases took half as long as before, and the effect was significant.
Alter
Can’t it be faster?
I suddenly thought, why am I doing this? The primary key of the database is not initialized. Can’t I just initialize him with a command?
This is so stupid!
Since H2 has similar commands to Oracle, I add an ALTER TABLE {TABLE name} ALTER COLUMN {COLUMN name to be reset} RESTART WITH 1 command after TRUNCATE command to reset the primary key of each TABLE to 1.
The only good thing about this change is that you don’t need to maintain the used tables separately, and the code is much simpler.
So the matter rested for the time being.
The more I think about it, the worse it gets
After coming home from work, I washed an apple and ate it. As I was eating, I suddenly remembered the problem I had encountered during the day. The more I thought about it, the more I felt wrong.
Why is there a problem with just a few of the 1000 + use cases? And these are fixed? Why do I delete the table and then create the table again no problem?
Have I been duped?
TRUNCATE command does not clean tables.
Is there a Bug in the H2 database? Just that I didn’t find the trigger condition for the Bug?
Location problem
When I started Debug mode, I found that the H2 source was too difficult to trace, the layers were too deep, and it was too difficult to locate the problem.
Open Up GitHub and download the H2 source code locally.
After two hours of H2 source code analysis, found that H2 source code will be object-oriented reflected incisively and vividly.
A table is an object, an index is an object, and every command is an object.
Wait a minute! Every command is an object, right? Does TRUNCATE also correspond to a class?
Boy! TruncateTable class TruncateTable class
For those of you who know about databases, the above code is not too hard to read. The Sequence needs to be explained to those who are not familiar with Oracle.
The Sequence number is a Sequence of digits that the database system increments according to certain rules.
The Sequence functions in two aspects:
- Uniquely identified as a proxy primary key;
- The Sequence number is used to record the latest action in the database. If the statement has an action (I/U/D, etc.), the Sequence number will be updated.
- H2 does this: create a proxy primary key Sequence for the incremental Column of the created table.
Now that we know what Sequence is for, let’s go back and forth:
- Part 1 can be interpreted as cleaning tables,
- The second part can be understood as resetting the increment column, and the third part is to determine whether the increment column needs to be reset
A quick translation of the code in three parts: if the current value of the increment column is not equal to the minimum value, reset, otherwise it is not necessary.
At first glance, there is nothing wrong with this statement.
Ok, let’s run through the simulated test case above:
Insert two pieces of data, and then the TRUNCATE table looks at what is going on inside.
If the current value of the increment column is not equal to the minimum value, reset, otherwise it is not necessary.
Because currentValue = value-increment and value = 3, increment = 1, minValue = 1
So currentValue = 3 minus 1 = 2
So min! = currentValue
Therefore, you need to reset the value of the Sequence corresponding to the autoincrement column to min, which is also 1.
Is the logic perfect? No problem at all?
Follow the logic above:
Insert two data, TRUNCATE table, and reset value from 3 to 1
Insert 3 data, TRUNCATE table, reset value from 4 to 1
Insert 4 data, TRUNCATE table, reset value from 5 to 1
.
Is it possible to get:
Insert 1 data,TRUNCATETable, reset value from 2 to 1
But what about actually inserting a data and then TRUNCATE the table? Let’s Debug:
Because currentValue = value-increment and value = 2, increment = 1, minValue = 1
So currentValue = 2 minus 1 = 1
** so min == currentValue, **
Therefore, insert 1 data, TRUNCATE table, will not reset value from 2 to 1, the next time to value, it will be 2 instead of 1.
So the logic of judgment here is obviously flawed.
H2, something’s wrong with you! You don’t talk about martial virtue! To bury a Bug so deep!
subsequent
So I checked out the latest code on GitHub.
Boy! This issue has been fixed by removing the buggy judgment directly.
Something is wrong, our project is using the latest version of H2.
Go to MVnRepository and “Open MVnRepository and the release record has stopped in late fall of last year”. This is the H2 version of Grey Head.
The newest version was sent in October 19, it has been 20 years in December now, more than a year, still do not send a new version?
As a result, I went to GitHub and raised an issue to point out the problem, and hoped the author could release a new version soon.
The author wrote back to me the next day, basically saying that the issue had been fixed and it was no need to worry about it, then closed my issue and gave me a discussion address about the new release plan.
After a brief look at their discussion, the author concluded that the new version has a lot of compatibility processing not done well, so the recent release of the new version is not possible!
The address of the issue I mentioned: github.com/h2database/…
Interested partners can go to have a look.
conclusion
To sum up:
H2 Database usageTRUNCATECommand to reset a table if there is a column in the table, it will not reset the increment column.
This is a Bug in H2 that will be fixed in the next version, but right now it looks like the next version is far off.
It’s an interesting story, full of “suspense and suspicion”, and finally the “culprit”.
After reviewing my solution, I found that many places are not rigorous, there are a lot of assumptions.
Fortunately, when I finally ate the apple, I realized something was wrong and finally found the problem. It seems that I still need to eat more apples.
Write in the last
Although the writer is bad, but like to share, sometimes share technology, sometimes share life.
I’m CoderWang, a Java programmer.
See you next time!
If you can, please like and follow, thank you!
More exciting wechat public number search “CoderW”, we progress together!
The code involved in the article: github.com/xiaoyingzhi…
FitNesse website: www.fitnesse.org
The H2 official documentation: h2database.com/html/main.h…