Customize Dashboard throws PSQLException: ERROR

[mohitashah]

Greetings,

I have installed Alfresco 3.4.5 on my Windows Machine with PostgreSQL as the DB.

As soon as my Alfresco Server is up and Running, I go to Alfresco Share and create a Site. Once I have created a new site I click on the 'Customize Dashboard' button on the left hand side corner. Immediately I see Exceptions in alfresco.log file that I have pasted below. I am simply not able to add any new Dashlets to my site.

[size=85]INFO: Server startup in 109239 ms
16:23:12,116  ERROR [extensions.webscripts.AbstractRuntime] Exception from executeScript - redirecting to status templat
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.parameter_IdPatternMap.
— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLExc
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.parameter_IdPatternMap.
— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.parameter_IdPatternMap.
— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLExc
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.parameter_IdPatternMap.
— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.ja
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptio
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:293)
        at org.alfresco.repo.domain.avm.ibatis.AVMNodeLinksDAOImpl.getChildEntryEntitiesByParent(AVMNodeLinksDAOImpl.jav
        at org.alfresco.repo.domain.avm.AbstractAVMNodeLinksDAOImpl.getChildEntriesByParent(AbstractAVMNodeLinksDAOImpl.
        at org.alfresco.repo.avm.ibatis.ChildEntryDAOIbatis.getByParent(ChildEntryDAOIbatis.java:65)
        at org.alfresco.repo.avm.PlainDirectoryNodeImpl.getListing(PlainDirectoryNodeImpl.java:174)
        at org.alfresco.repo.avm.AVMRepository.getListing(AVMRepository.java:1266)
        at org.alfresco.repo.avm.AVMServiceImpl.getDirectoryListing(AVMServiceImpl.java:321)
        at org.alfresco.repo.avm.AVMServiceImpl.getDirectoryListing(AVMServiceImpl.java:312)
        at org.alfresco.repo.avm.MultiTAVMService.getDirectoryListing(MultiTAVMService.java:318)
        at sun.reflect.GeneratedMethodAccessor626.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.alfresco.repo.transaction.CheckTransactionAdvice.invoke(CheckTransactionAdvice.java:47)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy6.getDirectoryListing(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor626.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.alfresco.repo.search.AVMSnapShotTriggeredIndexingMethodInterceptor.invoke(AVMSnapShotTriggeredIndexingMet
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.alfresco.repo.transaction.CheckTransactionAdvice.invoke(CheckTransactionAdvice.java:47)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.alfresco.repo.transaction.RetryingTransactionAdvice$1.execute(RetryingTransactionAdvice.java:64)
        at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:381)
        at org.alfresco.repo.transaction.RetryingTransactionAdvice.invoke(RetryingTransactionAdvice.java:67)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy6.getDirectoryListing(Unknown Source)
        at org.alfresco.repo.web.scripts.bean.AVMRemoteStore.listDocuments(AVMRemoteStore.java:517)
        at org.alfresco.repo.web.scripts.bean.BaseRemoteStore.execute(BaseRemoteStore.java:278)
        at org.alfresco.repo.web.scripts.RepositoryContainer$2.execute(RepositoryContainer.java:400)
        at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:381)
        at org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:453)
        at org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:491)
        at org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:275)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:352)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:189)
        at org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:118)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.alfresco.web.app.servlet.GlobalLocalizationFilter.doFilter(GlobalLocalizationFilter.java:58)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:861)
        at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
        at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1584)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml.
— The error occurred while applying a parameter map.
— Check the alfresco.avm.parameter_IdPatternMap.
— Check the statement (query failed).
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
        at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:295)
        at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:1)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
        … 61 more
Caused by: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:229)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
        … 68 more[/size]

[mward]

Assuming that you are using PostgreSQL 9.1.x - this is fixed ready for 4.0.1. There is also a workaround - to configure PostgreSQL to use the 'old' string handling:

standard_conforming_strings = off

See http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.1#Backward_compatibility_issues

[kirit]

I did following with my postgreSQL 9.1 , (I am using alfresco enterprise 3.4.6.)
1) login as user postgre
2) set standard_conforming_strings = off

Still I am getting the same issue of not seeing dashlets inside columns (where i suppose to see currently configured dashlets)
And same error is coming in backgournd

ERROR [extensions.webscripts.AbstractRuntime] Exception from executeScript - redirecting to status template error: SqlMapClient operation; SQL [];  
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml. 
— The error occurred while applying a parameter map. 
— Check the alfresco.avm.parameter_IdPatternMap. 
— Check the statement (query failed). 
— Cause: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  
— The error occurred in alfresco/ibatis/#resource.dialect#/avm-common-SqlMap.xml. 
Caused by: org.postgresql.util.PSQLException: ERROR: invalid escape string
  Hint: Escape string must be empty or one character.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

Please suggest.