"Where query" creates wrong SQL on production

Question

I had to update Spring from 1.2.7 to 2.0, as well Grails from 2.2.0 to 2.3.11. After solving a few typical update problems, I started to build a WAR to the testing server. Everything seemed ok. But after a few deployments I encountered a problem.

Sometimes (there is no rule) the server has 100% CPU usage -> OutOfMemeryError or application throws error 500. After debugging, I noticed that the reason for these problems is incorrect SQL query.

Where query:

UserRole.find{role.authority =~ "%${authTxt}" && user.id == currentUser.id}

creates such SQL:

select this_.id as id1_35_2_, this_.payment_enabled as payment_2_35_2_, this_.role_id as role_id3_35_2_, this_.user_id as user_id4_35_2_,
role_alias1_.id as id1_29_0_, role_alias1_.version as version2_29_0_, role_alias1_.authority as authorit3_29_0_, user_alias2_.id as id1_36_1_,
user_alias2_.version as version2_36_1_, user_alias2_.account_expired as account_3_36_1_, user_alias2_.account_locked as account_4_36_1_, 
user_alias2_.auto_password_flag as auto_pas5_36_1_, user_alias2_.email as email6_36_1_, user_alias2_.enabled as enabled7_36_1_, 
user_alias2_."password" as password8_36_1_, user_alias2_.password_expired as password9_36_1_, user_alias2_.user_info_id
as user_in10_36_1_, user_alias2_.username as usernam11_36_1_ from user_role this_ inner join role role_alias1_ on this_.role_id=role_alias1_.id inner join users user_alias2_ 
on this_.user_id=user_alias2_.id where (1=1 and 1=1)

and here the correct SQL:

... inner join role role_alias1_ on this_.role_id=role_alias1_.id inner join users user_alias2_ on this_.user_id=user_alias2_.id 
where ((role_alias1_.authority ilike ?) and (user_alias2_.id=?)) 

The obvious problem is that expression "(1 = 1 and 1 = 1)". In fact, such query:

User.findAll {id == userid} 

retrieve the entire table. Dynamic finders or criteria does not cause this problem.

I found two people who had a similar issue:

https://zenofchicken.wordpress.com/2016/01/06/freaky-grails-where-clauses-dont-work-anymore/

http://grails.1312388.n4.nabble.com/Finder-not-applying-criteria-td4655689.html

Plugins that I use:

• spring-security-core:2.0-RC5
• spring-security-oauth:2.0.2
• spring-security-oauth-facebook:0.1
• hibernate:3.6.10.16
• executor:0.3
• export:1.6
• csv:0.3.1
• database-migration:1.2.1
• quartz:1.0-RC7
• asset-pipeline:1.9.6
• grails-melody:1.57.0

Database: PostgreSQL 8.4.20

I've tried many solutions:

• I changed Grails version to 2.4
• I changed Hibernate3 to Hibernate4
• I changed the connector to Postgres from 9.0-801.jdbc3 to 9.1-901-1.jdbc4
• I removed the dependencies to see if any of them does not cause a problem
• I checked whether the extracted WAR does not differ in any file
• I changed Java version
• I changed Tomcat version
• I tested on another system

From what I read on a blog which link I gave above, Graeme Rocher wrote that the problem may be due to a lack of GORM in the application. However, the problem was there during the building of WAR - dependencies were wrongly packed(?). But here the problem arises when WAR is unpacking or when classes are loading. Looking at the source of the problem from blog entry, shows that the problem may be due to some ridiculous reason ...

Does anyone have an idea what might cause this problem or what can I debug to be able to determine the source of the error? And the question to grails dev: what has changed between version 2.2 and 2.3 that theoretically could cause such a problem?

Answer 1

The problem seems to come from AbstractHibernateCriterionAdapter in the grails-datastore-gorm-hibernate-core JAR.
The criterionAdaptors HashMap is not properly populated.

This map is a final static HashMap used to map GORM Criterions to Hibernate ones.

The problem randomly occurs when the application starts.
Sometime the HashMap is populated without any problem, sometimes problem occurs...
This Hashmap is static final and is kept corrupted for the rest of the application lifetime. That's why you sometime encounter this problem and everything goes well after a restart.

The HashMap can be corrupted when several threads are creating a AbstractHibernateCriterionAdapter object at the same time.
Each thread calls the constructor and the initialize() method.
This method is synchronized but the synchronized idiom is only effective for several threads accessing the same object (https://docs.oracle.com/javase/tutorial/essential/concurrency/syncmeth.html)

As a result, the synchronized doesn't lock properly the initialize() method and several threads can try to populate the criterionAdaptors HashMap at the same time.
HashMap is not thread safe, as a result the HashMap gets corrupted and some Criterions are missing.

If a GORM Criterion is not found in this HashMap, the Criterion is silently ignored... Which explains why it disapears from the generated request.

I've created a Github issue : https://github.com/grails/grails-data-mapping/issues/643