Portal Event Aggregation Fails When Search Term is Too Long

Description

We noticed that our portal (version 4.4.0-snapshot) no longer was aggregating portal events.

Looking in the portal-event.log the exception stack trace shown below indicated that a search term the portal was trying to aggregate was too long for the column

I solved the problem (I hope) by increasing the size of the search_term column in table UP_SEARCH_REQ_AGGR form 255 to 500.

After the change the portal started to aggregate events again.

ERROR [uP-TaskExec-891-aggregateRawEvents] o.j.p.e.a.PortalEventProcessingManagerImpl 2016-10-06 08:55:03,496 - aggregateRawEvents failed
org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [insert into UP_SEARCH_REQ_AGGR (AGGR_GROUP_ID, DATE_DIMENSION_ID, DURATION, AGGR_INTERVAL, TIME_DIMENSION_ID, STATS_COMPLETE, SEARCH_COUNT, SEARCH_TERM, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [72000]; error code [12899]; could not execute batch; nested exception is org.hibernate.exception.GenericJDBCException: could not execute batch
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:651) ~[spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:106) ~[spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) ~[spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:755) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:724) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:148) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.jasig.portal.events.aggr.PortalRawEventsAggregatorImpl.doAggregateRawEvents(PortalRawEventsAggregatorImpl.java:217) ~[classes/:4.4.0-SNAPSHOT]
at sun.reflect.GeneratedMethodAccessor1001.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at com.sun.proxy.$Proxy226.doAggregateRawEvents(Unknown Source) ~[na:na]
at org.jasig.portal.events.aggr.PortalEventProcessingManagerImpl$3.apply(PortalEventProcessingManagerImpl.java:204) ~[classes/:4.4.0-SNAPSHOT]
at org.jasig.portal.events.aggr.PortalEventProcessingManagerImpl$3.apply(PortalEventProcessingManagerImpl.java:201) ~[classes/:4.4.0-SNAPSHOT]
at org.jasig.portal.concurrency.locking.ClusterLockServiceImpl.doInTryLock(ClusterLockServiceImpl.java:200) ~[classes/:4.4.0-SNAPSHOT]
at org.jasig.portal.events.aggr.PortalEventProcessingManagerImpl.aggregateRawEvents(PortalEventProcessingManagerImpl.java:199) ~[classes/:4.4.0-SNAPSHOT]
at sun.reflect.GeneratedMethodAccessor835.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64) [spring-context-3.2.9.RELEASE.jar:3.2.9.RELEASE]
at org.jasig.portal.utils.threading.ThreadNamingRunnable.run(ThreadNamingRunnable.java:41) [classes/:4.4.0-SNAPSHOT]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_91]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_91]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_91]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_91]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_91]
Caused by: org.hibernate.exception.GenericJDBCException: could not execute batch
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:122) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:101) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:161) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.getBatch(JdbcCoordinatorImpl.java:196) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3057) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3521) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:88) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:395) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:387) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:303) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:349) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1195) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:404) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:175) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:75) ~[hibernate-entitymanager-4.2.19.Final.jar:4.2.19.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:513) ~[spring-orm-3.2.9.RELEASE.jar:3.2.9.RELEASE]
... 30 common frames omitted
Caused by: java.sql.BatchUpdateException: ORA-12899: value too large for column "UPORTAL43"."UP_SEARCH_REQ_AGGR"."SEARCH_TERM" (actual: 264, maximum: 255)

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:11190) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:244) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
at sun.reflect.GeneratedMethodAccessor589.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_91]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235) ~[tomcat-jdbc-7.0.37.jar:na]
at com.sun.proxy.$Proxy49.executeBatch(Unknown Source) ~[na:na]
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:113) ~[hibernate-core-4.2.19.Final.jar:4.2.19.Final]
... 47 common frames omitted

Environment

None

Assignee

Chris Beach

Reporter

Bruce Phillips

Labels

None

Estimated End Date

None

Fix versions

Affects versions

Priority

Major
Configure