SQLSyntaxErrorException with APS 1.7 and mysql

[muhmadtabrez]

Hi,

I am trying to run the spring boot project by pointing to local APS 1.7 db(mysql) and i am getting the following exceptions.I am running elastic search 6.4.2. Do i need to create any index. i can see that index template is getting created but i am unable to see any index created.

1. Related to water mark
   org.activiti.engine.ActivitiException: error while executing http request GET http://127.0.0.1:9200/watermarklog/watermarkevent/0 with status code: 404
   at com.alfresco.activiti.analytics.elastic.ElasticHTTPClient.executeHttpRequest(ElasticHTTPClient.java:103)
   at com.alfresco.activiti.analytics.elastic.ElasticHTTPClient.execute(ElasticHTTPClient.java:64)
   at com.alfresco.activiti.analytics.elastic.CustomElasticAnalyticsEndpoint.fetchWaterMark(CustomElasticAnalyticsEndpoint.java:106)
   at com.alfresco.activiti.analytics.processing.Watermark.fetchWatermark(Watermark.java:23)
   at com.alfresco.activiti.analytics.processing.ScheduledAnalyticsProcessing.analyticsProcessing(ScheduledAnalyticsProcessing.java:65)

2. Related to mysql native query
   org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:488) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.11.4.RELEASE.jar:na]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) ~[spring-data-commons-1.13.4.RELEASE.jar:na]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at com.sun.proxy.$Proxy84.getMaxTimestamp(Unknown Source) ~[na:na]
   at com.alfresco.activiti.analytics.processing.ProcessBatchPreparation.getMostRecentTimestamp(ProcessBatchPreparation.java:110) ~[classes/:na]
   at com.alfresco.activiti.analytics.processing.ProcessBatchPreparation.getBatchMetadata(ProcessBatchPreparation.java:53) ~[classes/:na]
   at com.alfresco.activiti.analytics.processing.ScheduledAnalyticsProcessing.analyticsProcessing(ScheduledAnalyticsProcessing.java:67) ~[classes/:na]
   at sun.reflect.GeneratedMethodAccessor86.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
   at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
   at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65) ~[spring-context-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81) [spring-context-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_121]
   at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_121]
   at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_121]
   at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.8.0_121]
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_121]
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_121]
   at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]
   Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
   at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
   at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:529) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
   at sun.reflect.GeneratedMethodAccessor87.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
   at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
   at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:372) ~[spring-orm-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at com.sun.proxy.$Proxy95.getSingleResult(Unknown Source) ~[na:na]
   at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:206) ~[spring-data-jpa-1.11.4.RELEASE.jar:na]
   at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85) ~[spring-data-jpa-1.11.4.RELEASE.jar:na]
   at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) ~[spring-data-jpa-1.11.4.RELEASE.jar:na]
   at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) ~[spring-data-jpa-1.11.4.RELEASE.jar:na]
   at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483) ~[spring-data-commons-1.13.4.RELEASE.jar:na]
   at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461) ~[spring-data-commons-1.13.4.RELEASE.jar:na]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56) ~[spring-data-commons-1.13.4.RELEASE.jar:na]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.9.RELEASE.jar:4.3.9.RELEASE]
   ... 25 common frames omitted
   Caused by: java.sql.SQLSyntaxErrorException: Every derived table must have its own alias
   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at com.mysql.cj.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1923) ~[mysql-connector-java-6.0.6.jar:6.0.6]
   at sun.reflect.GeneratedMethodAccessor90.invoke(Unknown Source) ~[na:na]
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
   at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
   at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.15.jar:na]
   at com.sun.proxy.$Proxy80.executeQuery(Unknown Source) ~[na:na]
   at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
   ... 59 common frames omitted

The above exception is thrown when calling this finction. ProcessBatchPreparation.getBatchMetadata.

[cijujoseph]

The first error will go away one the process runs atleast once successfully. You don't have to create the index manually. The second error is most likely due to the native DB query in Event Log Repository JpaRepository class (findUniqueProcessList() in either ActivitiEventLogRepository or ProcessedActivitiEventsRepository depending on your configuration). You may need to adjust that query to suite MySQL. I am not sure if I tested the most recent changes with MySQL..

[muhmadtabrez]

Above comments solved the problem.

[cijujoseph]

do you mind sharing the changes you had to make to get it working with MySQL? I'll add it to the README

[muhmadtabrez]

Hi Ciju,

I have done the following changes to make it working with mysql but this is a workaround as we should change this query to Hibernate query 

1. In activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/repository/ActivitiEventLogRepository.java 
   We need to change the query and method signature as follows. Since queryBatchSize is should be integer for any database(oracle,mysql,postgres). 
  
  	@Query(nativeQuery=true, value="select max(TIME_STAMP_) as TO_TIMESTAMP from (select TIME_STAMP_ from ACT_EVT_LOG where  "
		+ "PROC_DEF_ID_ is not null and TIME_STAMP_ > :to_timestamp "
			+ "and  PROC_DEF_ID_ not in :excludedProcessDefinitionIdList "
			+ "group by TIME_STAMP_  order by TIME_STAMP_ asc) as NEW_TIME_STAMP LIMIT :queryBatchSize" )
  String getMaxTimestamp(@Param("to_timestamp") Date  to_timestamp, 
			@Param("queryBatchSize") Integer  queryBatchSize, @Param("excludedProcessDefinitionIdList") List<String> excludedProcessDefinitionIdList);
			
  
Note: If you are using Alfresco APS enterprise do the above changes in activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/repository/ProcessedActivitiEventsRepository.java

2. In activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/processing/ProcessBatchPreparation.java
   Parse queryBatchSize value to integer as following .
   
   return processedActivitiEventsRepository.getMaxTimestamp(df.parse(lastUpdatedTimestamp), Integer.parseInt(queryBatchSize),
				getExcludedProcessIds());

I hope this will helpful.......

[cijujoseph]

Hi Ciju,

I have done the following changes to make it working with mysql but this is a workaround as we should change this query to Hibernate query 

1. In activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/repository/ActivitiEventLogRepository.java 
   We need to change the query and method signature as follows. Since queryBatchSize is should be integer for any database(oracle,mysql,postgres). 
  
  	@Query(nativeQuery=true, value="select max(TIME_STAMP_) as TO_TIMESTAMP from (select TIME_STAMP_ from ACT_EVT_LOG where  "
		+ "PROC_DEF_ID_ is not null and TIME_STAMP_ > :to_timestamp "
			+ "and  PROC_DEF_ID_ not in :excludedProcessDefinitionIdList "
			+ "group by TIME_STAMP_  order by TIME_STAMP_ asc) as NEW_TIME_STAMP LIMIT :queryBatchSize" )
  String getMaxTimestamp(@Param("to_timestamp") Date  to_timestamp, 
			@Param("queryBatchSize") Integer  queryBatchSize, @Param("excludedProcessDefinitionIdList") List<String> excludedProcessDefinitionIdList);
			
  
Note: If you are using Alfresco APS enterprise do the above changes in activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/repository/ProcessedActivitiEventsRepository.java

2. In activiti-analytics-spring-boot/src/main/java/com/alfresco/activiti/analytics/processing/ProcessBatchPreparation.java
   Parse queryBatchSize value to integer as following .
   
   return processedActivitiEventsRepository.getMaxTimestamp(df.parse(lastUpdatedTimestamp), Integer.parseInt(queryBatchSize),
				getExcludedProcessIds());

I hope this will helpful.......

Thanks!