Background

Object-Relational Mapping (ORM) has become an indispensable tool to work with relational databases in Java applications. This topic, along with JPA and Hibernate, has been the subject of innumerable articles. Therefore, the present post will not enumerate again the pros and cons of using these tools but describe a real situation involving the misuse of ORM.

 

How good an abstraction is ORM

To some extent, ORM has proved to be a successful tool to hide the complexities of relational databases. Yet an abstraction is as good as its design to abstract implementation details away. When these details leak through the abstraction, there is likely to be problems (see the law of leaky abstractions for more details).

For instance, everybody familiar with JPA (Java Persistence API) knows about all those annotations scattered across the classes to indicate how the objects are to be represented in the underlying database, e.g. @ManyToOne, @OneToMany, @Id,  @JoinColumn.

Through those annotations, database concepts like relationship between entities, primary keys, foreign keys, join queries, etc., leak into the realm of object-oriented programming. Even worse, using an ORM efficiently requires some knowledge about how it works. For instance, when running a query you do not want to load in memory the content of the entire database, right? Yet that is what may happen if you do not configure your ORM properly to enable lazy loading (see this article for further explanation JPA lazy loading).

To compound the problem, let us introduce a new actor: Querydsl. As the name implies, Querydsl provides a Domain Specific Language to create queries based on Java objects. It can make you forget about the existence of the database, especially when compared to old  plain JDBC.

Querydsl example:

List<Person> persons = queryFactory.selectFrom(person)
  .where(
    person.firstName.eq("John"),
    person.lastName.eq("Doe"))
  .fetch();

Plain JDBC:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Person WHERE firstName = 'John' AND lastName = 'Doe'");

 

How to blow up a database with ORM

Now that all the actors are properly introduced, I can proceed to describe the issue I had to deal with and that motivated this post.

A MySQL database on Production was getting overloaded everyday at peak time and eventually would crash. There was some speculation about DoS attacks and/or the existence of a connection leak.

Anyway, I was asked to fix the problem. After examining the most common queries run on Production, I found the real culprit, namely, queries executing full table scans.

Here is a simplified version of the query:

select mytable.myid 
mytable.email 
mytable.first_name 
mytable.last_name 
mytable.title 
from mytable 
where lower(mytable.myid)='qqbwlz'

and this is the execution plan

{
 "query_block": {
 "select_id": 1,
 "table": {
 "table_name": "mytable",
 "access_type": "ALL",
 "rows": 1294267,
 "filtered": 100,
 "attached_condition": "(lcase(`mydatabase`.`mytable`.`myid`) = 'qqbwlz')"
 }
 }
}

 

As it can be seen, access_type=”ALL” and the number of scanned rows is 1,294,267, (all the rows on the table). And the big offender is this condition:

lower(mytable.myid)='qqbwlz'

 

Even though there is an index defined on the column ‘myid’, the fact of using a function to filter on that column prevents the query from making use of the index.

After manually removing the function “lower” and executing again the query, this was the new execution plan

{
 "query_block": {
 "select_id": 1,
 "table": {
 "table_name": "mytable",
 "access_type": "const",
 "possible_keys": [
 "PRIMARY"
 ],
 "key": "PRIMARY",
 "used_key_parts": [
 "myid"
 ],
 "key_length": "32",
 "ref": [
 "const"
 ],
 "rows": 1,
 "filtered": 100
 }
 }
}

 

Now, access_type is constant and number of scanned rows is 1, and  the query is making use of the index defined on the column ‘myid’.

What is more, the query execution time went from a few seconds to a few milliseconds.

After confirming the problem and the solution, it was time to find the way that query was being generated by the application. Below is the snippet that builds the “WHERE” clause taken from Github (the ‘-‘ line is the original code and the ‘+’ one is the code after the fix).

public final class Predicates {
 
 public static BooleanExpression hasMyId(String myId) {
 - return new Entity("myTable").myId.equalsIgnoreCase(myId);
 + return new Entity("myTable").myId.eq(myId.toUpperCase());
 }
}

 

From the above snippet is clear what happened: some developer, misguided by the apparent simplicity of the code, decided to play it safe and make a case-insensitive comparison of ‘myId’. Yet the developer failed to notice that the code would be translated by Querydsl into a SQL query with the ‘lcase’ function applied to ‘myId’.

It is interesting to think about what safeguards could be put in place to avoid this pitfall: no unit test nor integration test can detect the problem. Only load tests can help in this situation, but for that it is necessary to run enough iterations as to insert millions of rows in the database. And at the end of the day, someone will have to access the database and check the queries performance.

 

Conclusion

Although working with abstractions is convenient, ultimately it is necessary to know about what is going on behind the scenes.

In cases like the one described in this post, anyone could have made that mistake: a new developer unfamiliar with the project and completely unaware that such an innocent change could wreak havoc on the database. Especially when no alert in the form of failing tests would be triggered. The only possible solution would be to run load tests after every change, and that is time-consuming and also expensive (as you will need to set up servers on your cloud platform of choice and generate enough traffic to have the servers hammered). All this leads to load tests not being run as often as necessary.

Moreover, the irony is that thanks to ORM tools, there is less and less need for developers to learn about the underlying database and as a result programmers lack the knowledge to deal with this kind of situations.

In a similar vein, something similar might occur with application servers as microservices become more ubiquitous. Frameworks to develop microservices like Spring Boot hide the existence of application servers by embedding them into the application. I wonder if in the future developers will also forget about how application servers work!