AnsweredAssumed Answered

Alfresco, Hibernate, and ORA-01795 on Production

Question asked by jack.jin on Jun 11, 2009
Latest reply on May 28, 2010 by fracat71
Hello,

We are currently using alfresco 3.0c running on a dedicated linux server.  As we load more content with alfresco's wcm system, we encountered an error didn't exist before.  While using alfresco's native UI, we now encounter an error doing the following:

1) Login to alfresco as admin (admin is a content manager in our web project)
2) GO to the web project on the left nav
3) By trying to expand modified items in a heavily used sandbox, we have to wait 20 minutes ish, then we get the transaction is rolling back error.

Looking at the logs, there's a SQL error underneath that's being caused by WCMWorkflowEvaluator.  After turning on hibernate logging, we've found the following error while alfresco's trying to expand modified items

16:54:13,615 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1795, SQLState: 42000
16:54:13,615 ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-01795: maximum number of expressions in a list is 1000

After digging deeper into logs by enabling debug statements from org.hibernate.SQL, I found the following SQL statement right before the above error
16:54:13,600 DEBUG [org.hibernate.SQL] select this_.ID_ as ID1_70_1_, this_.VERSION_ as VERSION3_70_1_, this_.NAME_ as NAME4_70_1_, this_.DESCRIPTION_ as DESCRIPT5_70_1_, this_.ACTORID_ as ACTORID6_70_1_, this_.CREATE_ as CREATE7_70_1_, this_.START_ as START8_70_1_, this_.END_ as END9_70_1_, this_.DUEDATE_ as DUEDATE10_70_1_, this_.PRIORITY_ as PRIORITY11_70_1_, this_.ISCANCELLED_ as ISCANCE12_70_1_, this_.ISSUSPENDED_ as ISSUSPE13_70_1_, this_.ISOPEN_ as ISOPEN14_70_1_, this_.ISSIGNALLING_ as ISSIGNA15_70_1_, this_.ISBLOCKING_ as ISBLOCKING16_70_1_, this_.TASK_ as TASK17_70_1_, this_.TOKEN_ as TOKEN18_70_1_, this_.PROCINST_ as PROCINST19_70_1_, this_.SWIMLANINSTANCE_ as SWIMLAN20_70_1_, this_.TASKMGMTINSTANCE_ as TASKMGM21_70_1_, this_.CLASS_ as CLASS2_70_1_, processins1_.ID_ as ID1_63_0_, processins1_.VERSION_ as VERSION2_63_0_, processins1_.KEY_ as KEY3_63_0_, processins1_.START_ as START4_63_0_, processins1_.END_ as END5_63_0_, processins1_.ISSUSPENDED_ as ISSUSPEN6_63_0_, processins1_.PROCESSDEFINITION_ as PROCESSD7_63_0_, processins1_.ROOTTOKEN_ as ROOTTOKEN8_63_0_, processins1_.SUPERPROCESSTOKEN_ as SUPERPRO9_63_0_ from JBPM_TASKINSTANCE this_ inner join JBPM_PROCESSINSTANCE processins1_ on this_.PROCINST_=processins1_.ID_ where this_.ISOPEN_=? and this_.END_ is null and processins1_.END_ is null and processins1_.ID_ in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Looking at the SQL, we have about 100 expressions, but for some reason, we have over 1000 ?s.  Is this intentional?  It seems to be directly related to ORA-01795.  Oracle thinks we're executing a SQL query with over 1000 expressions, because we have over ?s.

This is happening on our production system and our website is scheduled to go live tomorrow.  If anyone have any advice on how to avoid or solve this issue, please let me know.

Thanks for your time,
Jack

Outcomes