OHDSI Home | Forums | Wiki | Github

Different query on embedded tomcat vs. 7.0 Tomcat hosted webAPI

Developers
#1

Hi, All,
I’ve come across a strange behavior in my local environment for WebAPI. I have a JPA repository that I assign a ‘named entity graph’ that gives query hints about how the object should be retrieved from the db. Here’s the 2 queries that get built (the first being bad from Tomcat 7.0 hosted, other my local run as standalone app):

select protocol0_.id as id1_2_0_, cohortdefi1_.id as id1_3_1_, cohortdefi2_.id as id1_4_2_, simulation4_.id as id1_6_3_, protocol0_.created_by as created_2_2_0_, protocol0_.created_date as created_3_2_0_, protocol0_.description as descript4_2_0_, protocol0_.index_def_id as index_de8_2_0_, protocol0_.generate_info_id as generate9_2_0_, protocol0_.modified_by as modified5_2_0_, protocol0_.modified_date as modified6_2_0_, protocol0_.name as name7_2_0_, inclusionr3_.protocol_id as protocol1_2_0__, inclusionr3_.description as descript2_1_0__, inclusionr3_.expression as expressi3_1_0__, inclusionr3_.name as name4_1_0__, inclusionr3_.sequence as sequence5_0__, cohortdefi1_.created_by as created_2_3_1_, cohortdefi1_.created_date as created_3_3_1_, cohortdefi1_.description as descript4_3_1_, cohortdefi1_.expression_type as expressi5_3_1_, cohortdefi1_.modified_by as modified6_3_1_, cohortdefi1_.modified_date as modified7_3_1_, cohortdefi1_.name as name8_3_1_, inclusionr3_.protocol_id as protocol1_2_0__, inclusionr3_.description as descript2_1_0__, inclusionr3_.expression as expressi3_1_0__, inclusionr3_.name as name4_1_0__, inclusionr3_.sequence as sequence5_0__, cohortdefi2_.expression as expressi2_4_2_, inclusionr3_.protocol_id as protocol1_2_0__, inclusionr3_.description as descript2_1_0__, inclusionr3_.expression as expressi3_1_0__, inclusionr3_.name as name4_1_0__, inclusionr3_.sequence as sequence5_0__, simulation4_.execution_duration as executio2_6_3_, simulation4_.is_valid as is_valid3_6_3_, simulation4_.start_time as start_ti4_6_3_, simulation4_.status as status5_6_3_ 
from clinical_trial_protocol protocol0_ 
left outer join cohort_definition cohortdefi1_ on protocol0_.index_def_id=cohortdefi1_.id 
left outer join cohort_definition_details cohortdefi2_ on cohortdefi1_.id=cohortdefi2_.id 
cross join left outer join clinical_trial_inclusion inclusionr3_ on protocol0_.id=inclusionr3_.protocol_id left outer join simulation_generation_info simulation4_ on protocol0_.generate_info_id=simulation4_.id where protocol0_.id=17

select protocol0_.id as id1_2_0_, simulation2_.id as id1_6_1_, cohortdefi3_.id as id1_3_2_, cohortdefi4_.id as id1_4_3_, protocol0_.created_by as created_2_2_0_, protocol0_.created_date as created_3_2_0_, protocol0_.description as descript4_2_0_, protocol0_.index_def_id as index_de8_2_0_, protocol0_.generate_info_id as generate9_2_0_, protocol0_.modified_by as modified5_2_0_, protocol0_.modified_date as modified6_2_0_, protocol0_.name as name7_2_0_, inclusionr1_.protocol_id as protocol1_2_0__, inclusionr1_.description as descript2_1_0__, inclusionr1_.expression as expressi3_1_0__, inclusionr1_.name as name4_1_0__, inclusionr1_.sequence as sequence5_0__, simulation2_.execution_duration as executio2_6_1_, simulation2_.is_valid as is_valid3_6_1_, simulation2_.start_time as start_ti4_6_1_, simulation2_.status as status5_6_1_, cohortdefi3_.created_by as created_2_3_2_, cohortdefi3_.created_date as created_3_3_2_, cohortdefi3_.description as descript4_3_2_, cohortdefi3_.expression_type as expressi5_3_2_, cohortdefi3_.modified_by as modified6_3_2_, cohortdefi3_.modified_date as modified7_3_2_, cohortdefi3_.name as name8_3_2_, cohortdefi4_.expression as expressi2_4_3_ 
from clinical_trial_protocol protocol0_ 
left outer join clinical_trial_inclusion inclusionr1_ on protocol0_.id=inclusionr1_.protocol_id 
left outer join simulation_generation_info simulation2_ on protocol0_.generate_info_id=simulation2_.id 
left outer join cohort_definition cohortdefi3_ on protocol0_.index_def_id=cohortdefi3_.id 
left outer join cohort_definition_details cohortdefi4_ on cohortdefi3_.id=cohortdefi4_.id where protocol0_.id=17

The problem is this ‘cross left join outer join’ at the end of the first query. I"m not sure why I’m getting different results: the war file is bundled with the correct hibernate library…from all of the logs, it appears that the runtime is identical (hibernate finds the same dialects formt he same connection strings), the database is even the same!

So, I just wanted to put this out there for other people that may be seeing the same problem. Don’t trust your local environment, and always sanity check against an environment that mirrors your production environment.

-Chris

#2

Tomcat shouldn’t have anything to do with hibernate queries. What jars are on the hosted tomcat classpath (i.e. tomcat common lib)? Is the SQL server jar excluded from your war (provided scope)? Is the driver jar in tomcat lib the exact same as the one in your local repo?

#3

I did play around with the drivers in the lib folder to make sure that they were in sync. It’s hard to tell which driver was loaded (there’s an itds.jar file which is an open source implmentation of the protocol to talk to sql server). From the stack trace, however, the class that reported the error came from com.mincrosoft.XXX so (unless the ITDS crew made their driver class name ‘com.microsoft.XX’ it’s probaby from the microsoft driver).

I am kinda inclined to say that this is on the hibernate side. it’s not the jdbc driver that’s building the query, rather the hibernate ORM translator. And for some reason the one running locally is producing a different fetch SQL then the one hosted on the server. I’m wondering if there’s some kind of JPA infrastructure that comes with the JDK platform that the tomcat 7.0 instance is using that is different then the local netbeans IDE runtime.

For now i’ve used the Hibernate.initlaize() call to fetch all the lazy associations i need for a particular operation and that seems to be working for now, but ti’s less than ideal.

-Chris

#4

FWIW, I’m running WebApi in tomcat 7 deployed on a server, and haven’t had any issues. I can get you specs in the morning of the java version, but I think it’s tomcat 7 and java 7 - of course set up for Oracle though.

#5

thanks Charity. I’m going to play aroudn with specifying diret JPQL queries directly to see if I can give it the hint to fetch. I wouldn’t say i’m doing basic entity lookups, i have an entity which at the root has one 1:1 for cohort definition (the index rule), 1:* for inclusion rules, 1:(0|1) for a status record, and the underlyign cohort defintion has it’s own 1:(0|1) relationship…so i think when it tries to do ti all in one outer join query, it gets confused.

But what’s really really strange is same libraries being up a different query. I think that’s the key. I will try to dig into more of the server cofiguration (is ti running java 7/6/8) and maybe try to get some kinda log as to what classes are being loaded, but I need to get somethign done for the AMIA conference, so i’m doing it quick and dirty to get the job done.

#6

We have had cases where oracle does not give a consistent execution plan… Your note about hibernate getting confused is possible but I wouldn’t expect that to vary across environments, assuming they are comparably configured.
With that said, I would try changing your sql server driver jar dependency, in pom sql-server profile, to compile scope, so that it is included in your WAR, and then try deploying to the hosted tomcat. Tomcat should use the one in your war prior to considering $CATALINA_HOME/lib/*. I would be curious if you then get the same query as your local. I wouldn’t think there should be any hibernate/jpa jars in jdk or a tomcat lib, though I don’t know what else you may have added to the various classpaths (via tomcat installation, or tomcat/java service classpath options). If you have modified tomcat/system classpaths, you could always try a clean install. I would be surprised if this had anything to do with tomcat embedded vs stand alone.

#7

Ok, I’ll investigate some things and let you know if I turn anything up.

t