MySQL Migration Activiti Errors

[clperkins]

I am in the process of migrating our Alfresco from it's default Postgres DB to MySQL. If I could avoid doing so I definitely would but our development team has standardized on MySQL. Here is a brief overview of my migration strategy:

1.Dump PG DB using –column-inserts –data-only options.
2.Create a new directory and change global config to use this new directory as dir.root.
3.Drop in the jdbc MySQL connectors and change global config to use MySQL
4.Start Tomcat
5.Verify DB schema was created and truncate all tables, turning off foreign key checks before truncate and back on after.
6.Convert PG data dump to MySQL using pg2mysql utility –>http://www.lightbox.ca/pg2mysql.php.
7.Import the converted .sql file into the empty MySQL Alfresco DB.
8.Start Tomcat

To my complete amazement, everything was going really well until I got this error:

11:38:46,637 ERROR [org.springframework.extensions.webscripts.AbstractRuntime] Exception from executeScript - redirecting to status template error: 07160002 Wrapped Exception (with status template): 07160015 Failed to retrieve tasks assigned to authority {0} in state {1}.‍

Here is the complete log entry related to that error:

11:38:46,637 ERROR [org.springframework.extensions.webscripts.AbstractRuntime] Exception from executeScript - redirecting to status template error: 07160002 Wrapped Exception (with status template): 07160015 Failed to retrieve tasks assigned to authority {0} in state {1}.
org.springframework.extensions.webscripts.WebScriptException: 07160002 Wrapped Exception (with status template): 07160015 Failed to retrieve tasks assigned to authority {0} in state {1}.
        at org.springframework.extensions.webscripts.AbstractWebScript.createStatusException(AbstractWebScript.java:970)
        at org.springframework.extensions.webscripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:171)
        at org.alfresco.repo.web.scripts.RepositoryContainer$2.execute(RepositoryContainer.java:393)
        at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:388)
        at org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:462)
        at org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:500)
        at org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:316)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:372)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:209)
        at org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:118)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.alfresco.web.app.servlet.GlobalLocalizationFilter.doFilter(GlobalLocalizationFilter.java:58)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
        at org.apache.tomcat.util.net.AprEndpoint$SocketWithOptionsProcessor.run(AprEndpoint.java:1763)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
        at java.lang.Thread.run(Thread.java:722)
Caused by: org.alfresco.service.cmr.workflow.WorkflowException: 07160015 Failed to retrieve tasks assigned to authority {0} in state {1}.
        at org.alfresco.repo.workflow.activiti.ActivitiWorkflowEngine.getAssignedTasks(ActivitiWorkflowEngine.java:1434)
        at org.alfresco.repo.workflow.WorkflowServiceImpl.getAssignedTasks(WorkflowServiceImpl.java:686)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.alfresco.repo.security.permissions.impl.AlwaysProceedMethodInterceptor.invoke(AlwaysProceedMethodInterceptor.java:34)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:46)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:147)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy73.getAssignedTasks(Unknown Source)
        at org.alfresco.repo.web.scripts.workflow.TaskInstancesGet.buildModel(TaskInstancesGet.java:123)
        at org.alfresco.repo.web.scripts.workflow.AbstractWorkflowWebscript.executeImpl(AbstractWorkflowWebscript.java:80)
        at org.springframework.extensions.webscripts.DeclarativeWebScript.execute(DeclarativeWebScript.java:64)
        … 28 more
Caused by: org.activiti.engine.ActivitiException: couldn't parse '17880af8-410e-478e-ab8e-c9a0dc988031bpmn20.xml': Content is not allowed in prolog.
        at org.activiti.engine.impl.util.xml.Parse.execute(Parse.java:136)
        at org.activiti.engine.impl.bpmn.parser.BpmnParse.execute(BpmnParse.java:186)
        at org.activiti.engine.impl.bpmn.deployer.BpmnDeployer.deploy(BpmnDeployer.java:81)
        at org.activiti.engine.impl.persistence.deploy.DeploymentCache.deploy(DeploymentCache.java:37)
        at org.activiti.engine.impl.persistence.deploy.DeploymentCache.resolveProcessDefinition(DeploymentCache.java:78)
        at org.activiti.engine.impl.persistence.deploy.DeploymentCache.findDeployedProcessDefinitionById(DeploymentCache.java:52)
        at org.activiti.engine.impl.cmd.GetStartFormCmd.execute(GetStartFormCmd.java:40)
        at org.activiti.engine.impl.cmd.GetStartFormCmd.execute(GetStartFormCmd.java:30)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.FormServiceImpl.getStartFormData(FormServiceImpl.java:52)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:136)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convertToInstanceAndSetVariables(ActivitiTypeConverter.java:561)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convertAndSetVariables(ActivitiTypeConverter.java:183)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:215)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:203)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:351)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:327)
        at org.alfresco.repo.workflow.activiti.ActivitiTypeConverter.convert(ActivitiTypeConverter.java:285)
        at org.alfresco.repo.workflow.activiti.ActivitiWorkflowEngine.getAssignedTasks(ActivitiWorkflowEngine.java:1421)
        … 49 more
Caused by: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 39; Content is not allowed in prolog.
        at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
        at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
        at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
        at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
        at org.activiti.engine.impl.util.xml.Parse.execute(Parse.java:133)
        … 72 more

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I got that when I logged into Share, probably from the My Tasks dashlet.

I can login to Alfresco and Share just fine, view docs, add docs. The only thing that isn't working right is workflow.

I tried switching out the MySQL jdbc driver to an earlier version but that didn't do anything. Looking through the SQL insert files and inspecting the PG DB vs. MySQL DB, I see that PGSQL uses bytea for the BYTES_ column in ACT_GE_BYTEARRAY where as MySQL uses LONGBLOB. That sounds right to me so what's the deal here? Here's a sample of the contents of '17880af8-410e-478e-ab8e-c9a0dc988031bpmn20.xml', referenced by the log. Are all of these values, which look like the alf_data directory structures, causing errors?

<?xml version="1.0" encoding="UTF-8"?>\012<definitions xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:activiti="http://activiti.org/bpmn" xmlns:bpmndi="http://www.omg.org/spec/BPMN/20100524/DI" xmlns:omgdc="http://www.omg.org/spec/DD/20100524/DC" xmlns:omgdi="http://www.omg.org/spec/DD/20100524/DI" typeLanguage="http://www.w3.org/2001/XMLSchema" expressionLanguage="http://www.w3.org/1999/XPath" targetNamespace="http://www.activiti.org/test">\012  <process id="change.management" name="Change Management">\012    <documentation>Place documentation for the 'change.management' process here.</documentation>\012    <startEvent id="startChangeManagement" name="Start" activiti:formKey="odesiecm:initiatorRequest" activiti:initiator="${initiator.properties.username}"></startEvent></definitions>‍

Would a complete re-index help?

[afaust]

Hello,

1) Index has nothing to do with Activiti process definition persistence, so a re-index would not help at all.
2) I don't see any values that look like alf_data directory structures.
3) I assume your migration has left the XML data somewhat mangled. Those "\012" look a bit suspicious and should probably not be there. I'd think they are the result of dumping the byte data from postgres into a text-based file.

Unfortunately, I am not familiar with Postgres to MySQL migrations, so I can't give any tips on this. But I would try to attempt another migration with proper care for the byte columns…

Regards
Axel

[clperkins]

Yeah, after thinking about what I said I realized I was reaching a bit on the re-indexing part.

I'll keep looking, thanks.

[deas0815]

Hm … this one looks familiar to me.

The short story is that pg2mysql does not properly deal with binary data. The fix is to replace '\x…' or '\\x…' with unhex(‘…’) for mysql. You can find more details at http://www.contentreich.de/migrating-alfresco-from-postgresql-to-mysql

regards
Andreas

[mrogers]

There's another thread somewhere in these forums with the same issue which relates to the character set uses for textual data.     Its important that the input and output match.     The good news is from what I remember of that issue it got fixed!