Extraction Error in Alflytics

[shabnash]

Hi Francesco,

I tried installing Alflytics with Alfresco community 5.2 . But Ended up in some error . I am new to the solution , request your help in identifying the error .

Alfresco Share 5.2
pentaho 7.1
Postgres DB
Installation Method : Manual

During the process of installation , there was no errors . However , we started having error from extraction process . please find the Extraction image , which show the details below .

I am also attaching the log file for reference .

Pentaho_20190421012018.log

Regards
Shabanash

[fcorti]

Hi Shabanash,

If you check at the Pentaho log files there are some errors.
Below the ones I found.

2019/04/21 09:22:57 - Abort.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Row nr 1 causing abort : [{"query":{"query": "(((TYPE:\"cm:content\") OR (TYPE:\"cm:folder\")) AND NOT (TYPE:\"cm:systemfolder\") AND (@cm:modified:[\"2001-01-01T00:00:00.000Z\" TO MAX]))","language":"afts"},"paging":{"maxItems":1,"skipCount":1000000000}}], [http://192.168.10.209:8080/alfresco/api/-default-/public/search/versions/1/search], [POST], [{"error":{"errorKey":"framework.exception.ApiDefault","statusCode":500,"briefSummary":"03210411 ","stackTrace":"For security reasons the stack trace is no longer displayed, but the property is kept for previous versions","descriptionURL":"https://api-explorer.alfresco.com","logId":"b500abe4-5c09-4f82-af53-de3aeb4faacf"}}], [500]
2019/04/21 09:22:57 - Abort.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Rest call failed.

2019/04/21 09:23:00 - Abort.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Row nr 1 causing abort : [{"query":{"query":"(TYPE:\"cm:category\")","language": "afts"},"sort":[{"type":"FIELD","field":"cm:modified","ascending": true},{"type":"FIELD","field":"sys:dbid","ascending":true}],"include":["properties"],"paging":{"maxItems": 5000,"skipCount":0}}], [http://192.168.10.209:8080/alfresco/api/-default-/public/search/versions/1/search], [POST], [{"error":{"errorKey":"framework.exception.ApiDefault","statusCode":500,"briefSummary":"03210415 ","stackTrace":"For security reasons the stack trace is no longer displayed, but the property is kept for previous versions","descriptionURL":"https://api-explorer.alfresco.com","logId":"f4479c71-74c4-40d0-9ed7-b4803e24f19e"}}], [500]
2019/04/21 09:23:00 - Abort.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Rest call failed.

2019/04/21 09:23:12 - Execute row SQL script.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Error in step, asking everyone to stop because of:
2019/04/21 09:23:12 - Execute row SQL script.0 - Couldn't execute SQL: INSERT INTO "OPE_ALF_WF_DEFINITIONS"("INSTANCE_ID","ID","DEPLOYMENT_ID","NAME","DESCRIPTION","VERSION","KEY") VALUES (1,'quickStartReviewPublish:1:35',33,'Web Quick Start: Review & Publish Web Assets',null,1,'quickStartReviewPublish')
2019/04/21 09:23:12 - Execute row SQL script.0 - 
2019/04/21 09:23:12 - Execute row SQL script.0 - ERROR: null value in column "DESCRIPTION" violates not-null constraint
2019/04/21 09:23:12 - Execute row SQL script.0 -   Detail: Failing row contains (1, quickStartReviewPublish:1:35, 33, Web Quick Start: Review & Publish Web Assets, null, 1, quickStartReviewPublish).

It seems an issue with the rest endpoints.
Please check them and try to extract again.

[howkymike]

Hello,
I seem to have very similar problem on Alfresco Community 6.1.1. I cannot extract data
The query:

{ "query":{
      "query":"(((TYPE:\"cm:content\") OR (TYPE:\"cm:folder\")) AND NOT (TYPE:\"cm:systemfolder\") AND (@cm:modified:[\"2001-01-01T00:00:00.000Z\" TO MAX]))",
      "language":"afts"
   },
   "paging":{
      "maxItems":1,
      "skipCount":1000000000
}}

returns error code 500.
There is something wrong with the cm:modified, because if I delete it, query is OK.

{ "query":{
    "query":"(((TYPE:\"cm:content\") OR (TYPE:\"cm:folder\")) AND NOT (TYPE:\"cm:systemfolder\"))",
    "language":"afts"
   },
   "paging":{
     "maxItems":1,
     "skipCount":1000000000
}}

This query is correct.

My error log on Pentaho side ( pentaho.log):

2020-04-19 11:30:16,559 ERROR [org.pentaho.di] 2020/04/19 11:30:16 - Abort.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Row nr 1 causing abort : [{"query":{"query": "(((TYPE:"cm:content") OR (TYPE:"cm:folder")) AND NOT (TYPE:"cm:systemfolder") AND (@cm:modified:["2001-01-01T00:00:00.000Z" TO MAX]))","language":"afts"},"paging":{"maxItems":1,"skipCount":1000000000}}], [http://localhost:80/alfresco/api/-default-/public/search/versions/1/search], [POST], [{"error":{"errorKey":"framework.exception.ApiDefault","statusCode":500,"briefSummary":"03190042 ","stackTrace":"For security reasons the stack trace is no longer displayed, but the property is kept for previous versions","descriptionURL":"https://api-explorer.alfresco.com","logId":"2e9h5399-ef93-44e9-a133-9d670481a05f"}}], [500]

My error log on Alfresco side:

my_project | 2020-04-19 10:47:18,834 ERROR [api.search.SearchApiWebscript] [http-nio-8080-exec-7] 2e9h5399-ef93-44e9-a133-9d670481a05f : [org.alfresco.repo.search.impl.solr.AbstractSolrQueryHTTPClient.postQuery(AbstractSolrQueryHTTPClient.java:79), org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient.postSolrQuery(SolrQueryHTTPClient.java:1116), org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient.executeQuery(SolrQueryHTTPClient.java:583), org.alfresco.repo.search.impl.solr.SolrQueryLanguage.executeQuery(SolrQueryLanguage.java:52), org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage.executeQuery(DbOrIndexSwitchingQueryLanguage.java:217), org.alfresco.repo.search.impl.solr.SolrSearchService.query(SolrSearchService.java:354), org.alfresco.repo.search.SearcherComponent.query(SearcherComponent.java:84), jdk.internal.reflect.GeneratedMethodAccessor533.invoke(Unknown Source), java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43), java.base/java.lang.reflect.Method.invoke(Method.java:566), org.alfresco.repo.management.subsystems.SubsystemProxyFactory$1.invoke(SubsystemProxyFactory.java:79), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212), com.sun.proxy.$Proxy42.query(Unknown Source), org.alfresco.repo.search.impl.SearchServiceSubSystemDelegator.query(SearchServiceSubSystemDelegator.java:133), jdk.internal.reflect.GeneratedMethodAccessor533.invoke(Unknown Source), java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43), java.base/java.lang.reflect.Method.invoke(Method.java:566), org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343), org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163), net.sf.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:80), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:53), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:166), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294), org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212), com.sun.proxy.$Proxy42.query(Unknown Source), org.alfresco.rest.api.search.SearchApiWebscript.execute(SearchApiWebscript.java:100), org.alfresco.repo.web.scripts.RepositoryContainer$3.execute(RepositoryContainer.java:519), org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:450), org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:587), org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:656), org.alfresco.repo.web.scripts.RepositoryContainer.executeScriptInternal(RepositoryContainer.java:428), org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:308), org.alfresco.rest.api.PublicApiRepositoryContainer.access$001(PublicApiRepositoryContainer.java:47), org.alfresco.rest.api.PublicApiRepositoryContainer$1.doWork(PublicApiRepositoryContainer.java:84), org.alfresco.repo.tenant.TenantUtil.runAsWork(TenantUtil.java:126), org.alfresco.repo.tenant.TenantUtil.runAsTenant(TenantUtil.java:95), org.alfresco.rest.api.PublicApiRepositoryContainer.executeScript(PublicApiRepositoryContainer.java:80), org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:399), org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:210), org.alfresco.repo.web.scripts.TenantWebScriptServlet.service(TenantWebScriptServlet.java:82), javax.servlet.http.HttpServlet.service(HttpServlet.java:742), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.alfresco.module.aosmodule.service.ContextRootFilter.doFilter(ContextRootFilter.java:93), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:75), jdk.internal.reflect.GeneratedMethodAccessor522.invoke(Unknown Source), java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43), java.base/java.lang.reflect.Method.invoke(Method.java:566), org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:132), org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186), org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212), com.sun.proxy.$Proxy173.doFilter(Unknown Source), org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:89), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.alfresco.web.app.servlet.GlobalLocalizationFilter.doFilter(GlobalLocalizationFilter.java:68), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.alfresco.web.app.servlet.ClearSecurityContextFilter.doFilter(ClearSecurityContextFilter.java:53), org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193), org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166), org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198), org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96), org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493), org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140), org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81), org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650), org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87), org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342), org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800), org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66), org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806), org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498), org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49), java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128), java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628), org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61), java.base/java.lang.Thread.run(Thread.java:834)]

[fcorti]

Hi @howkymike,

If you execute the full query using the ACS REST endpoint, is it working?

[howkymike]

Well.. I executed it via Alfresco API Explorer (the one based on Swagger) and it's not working.

[fcorti]

Which version of ACS are you using?

[howkymike]

Alfresco Community 6.1.1. Is the Alflytics compatible only with the paid version of Alfresco?

[fcorti]

Oh sorry, you wrote it also above in the thread.

Ok, it could be the problem because I did not test it with this version.
If you make it work using the REST API, you can change the file containing the query and it should work.