"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?

Wapentake

We face a similar problem and can't cope with it. We've had 3 queries that stopped "working" and were translated into a failing where 1=1 and 1=1 sql query. Not at the same time ! At diffferent moment after a server restart, after a release. Code that worked perfectly before this moment, untounched for months, and which worked again after a restart... Weird, freaky, and not solved. We've had a big problem three days ago with a query that was supposed to delete only a fraction of a collection of entities, but deleted the whole collection. That was backup reload day... Grails 2.4.4,Hibernate 4 – Kries

Same issue here with where queries in grails 2.4.4. Query which supposed to cancel expired members, managed to cancel our whole userbase! It is very nondeterministic and depends how the app starts. – Salk

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

Turkoman

Well, finally a logical explanation of this problem :). But one thing makes me wonder why the problem occurs only for some people? It is impossible that this is a a common problem Grails 2.3 / 2.4. I found one more regularity, the problem does not occur on a virtual machine... – Wapentake

The AbstractHibernateCriterionAdapter is populated the first time a criteria request is executed. So it may depends on the application first criteria requests. If two requests are executed synchronously at the application startup (Bootstrap.groovy?), the problem may occur, otherwise there is no possibility this problem can happen. It can explains why only some applications are impacted. – Turkoman

Do you need any further information to accept this response? – Turkoman

Oh, I'm sorry. Thanks for your help – Wapentake

Answer 2

It's a weird way to get all instances of User in that way by the id.

Have you tried this?

User.findAllById(userid)

Trustbuster

It was just an example. The main problem lies in the fact that all query in the project creates an incorrect SQL. – Wapentake

If you are doing a join the method is using the criterion join inside a withcriteria closure. – Trustbuster