[opencms-dev] OpenCms SQL Statement error (PostgreSQL 8.3.3)

[Roman Uhlig]

Thanks for the fast reply.

We already considered upgrading from OpenCms 6.2.3 to 7, but we could not
manage to get the database pooling running with MySQL 5.0 (we use both
PostgreSQL and MySQL).
Using Tomcat 6, OpenCms 7.0.4 and MySQL 5.0 we always get the following
error in our testing environment (after some time of inactivity):

org.apache.catalina.core.ApplicationDispatcher invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.sql.SQLException: Already closed.
        at
org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
        at
org.apache.commons.dbcp.PoolingDriver$PoolGuardConnectionWrapper.close(PoolingDriver.java:269)
        at
org.apache.jsp.WEB_002dINF.jsp.online.system.modules.de_opencms7test.templates.tpl_005fglobal_jsp._jspService(tpl_005fglobal_jsp.java:137)
        at org.apache.jasper.runtime.HttpJspBase.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(Unknown Source)
        at org.apache.jasper.servlet.JspServletWrapper.service(Unknown
Source)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(Unknown
Source)
        at org.apache.jasper.servlet.JspServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(Unknown Source)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
Source)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
Source)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(Unknown
Source)
        at org.apache.catalina.core.ApplicationDispatcher.doInclude(Unknown
Source)
        at org.apache.catalina.core.ApplicationDispatcher.include(Unknown
Source)
        at
org.opencms.flex.CmsFlexRequestDispatcher.includeExternal(CmsFlexRequestDispatcher.java:194)
        at
org.opencms.flex.CmsFlexRequestDispatcher.include(CmsFlexRequestDispatcher.java:169)
        at org.opencms.loader.CmsJspLoader.service(CmsJspLoader.java:1172)
        at
org.opencms.flex.CmsFlexRequestDispatcher.includeInternalWithCache(CmsFlexRequestDispatcher.java:423)
        at
org.opencms.flex.CmsFlexRequestDispatcher.include(CmsFlexRequestDispatcher.java:173)
        at
org.opencms.loader.CmsJspLoader.dispatchJsp(CmsJspLoader.java:1206)
        at org.opencms.loader.CmsJspLoader.load(CmsJspLoader.java:1150)
        at
org.opencms.loader.A_CmsXmlDocumentLoader.load(A_CmsXmlDocumentLoader.java:232)
        at
org.opencms.loader.CmsResourceManager.loadResource(CmsResourceManager.java:964)
        at org.opencms.main.OpenCmsCore.showResource(OpenCmsCore.java:1494)
        at org.opencms.main.OpenCmsServlet.doGet(OpenCmsServlet.java:152)
        at javax.servlet.http.HttpServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(Unknown Source)
        at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Unknown
Source)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(Unknown
Source)
        at org.apache.catalina.core.StandardWrapperValve.invoke(Unknown
Source)
        at org.apache.catalina.core.StandardContextValve.invoke(Unknown
Source)
        at org.apache.catalina.core.StandardHostValve.invoke(Unknown Source)
        at org.apache.catalina.valves.ErrorReportValve.invoke(Unknown
Source)
        at org.apache.catalina.core.StandardEngineValve.invoke(Unknown
Source)
        at org.apache.catalina.connector.CoyoteAdapter.service(Unknown
Source)
        at org.apache.jk.server.JkCoyoteHandler.invoke(Unknown Source)
        at org.apache.jk.common.HandlerRequest.invoke(Unknown Source)
        at org.apache.jk.common.ChannelSocket.invoke(Unknown Source)
        at org.apache.jk.common.ChannelSocket.processConnection(Unknown
Source)
        at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(Unknown
Source)
        at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(Unknown
Source)
        at java.lang.Thread.run(Thread.java:619)

We increased MySQL's "wait_timeout" config parameter, but that didn't help.
So far, we could not manage to get OpenCms 7 running stable with MySQL 5.0.
Thats why we didn't upgrade yet.

We would appreciate any hint on this.

Thanks in advance,
Roman



Roman Uhlig wrote:
> 
> After upgrading from PostgreSQL 8.2.7 to 8.3.3 we always get the following
> OpenCms SQL statement error when trying to publish resources:
> 
> Error reading the direct publish list for resource "/monitoring/".
> Reason: An SQL error occurred when executing the following query: SELECT
> CMS_OFFLINE_STRUCTURE.STRUCTURE_ID,CMS_OFFLINE_STRUCTURE.RESOURCE_ID,CMS_OFFLINE_STRUCTURE.RESOURCE_PATH,CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE,CMS_OFFLINE_STRUCTURE.DATE_RELEASED,CMS_OFFLINE_STRUCTURE.DATE_EXPIRED,CMS_OFFLINE_RESOURCES.RESOURCE_ID,CMS_OFFLINE_RESOURCES.RESOURCE_TYPE,CMS_OFFLINE_RESOURCES.RESOURCE_FLAGS,CMS_OFFLINE_RESOURCES.RESOURCE_STATE,CMS_OFFLINE_RESOURCES.DATE_CREATED,CMS_OFFLINE_RESOURCES.DATE_LASTMODIFIED,CMS_OFFLINE_RESOURCES.USER_CREATED,CMS_OFFLINE_RESOURCES.USER_LASTMODIFIED,CMS_OFFLINE_RESOURCES.PROJECT_LASTMODIFIED
> AS
> LOCKED_IN_PROJECT,CMS_OFFLINE_RESOURCES.RESOURCE_SIZE,CMS_OFFLINE_RESOURCES.SIBLING_COUNT,CMS_OFFLINE_RESOURCES.PROJECT_LASTMODIFIED
> FROM CMS_OFFLINE_STRUCTURE INNER JOIN CMS_OFFLINE_RESOURCES ON
> CMS_OFFLINE_STRUCTURE.RESOURCE_ID=CMS_OFFLINE_RESOURCES.RESOURCE_ID AND
> (CMS_OFFLINE_RESOURCES.PROJECT_LASTMODIFIED=4) AND
> (CMS_OFFLINE_RESOURCES.RESOURCE_SIZE = -1) AND
> (CMS_OFFLINE_STRUCTURE.RESOURCE_PATH LIKE /sites/default/monitoring/%
> ESCAPE '|') AND NOT (((CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE=0 AND
> CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE >=
> CMS_OFFLINE_RESOURCES.RESOURCE_STATE) OR
> (CMS_OFFLINE_RESOURCES.RESOURCE_STATE=0 AND
> CMS_OFFLINE_RESOURCES.RESOURCE_STATE >=
> CMS_OFFLINE_STRUCTURE.STRUCTURE_STATE))) ORDER BY
> CMS_OFFLINE_STRUCTURE.RESOURCE_PATH.
> Reason: ERROR: operator does not exist: smallint = character varying
> 
> 
> Is there possibly any solution to this or do we have to downgrade to
> PostgreSQL 8.2.7 again?
> 
> Thanks in advance for any help,
> Roman
> 

-- 
View this message in context: http://www.nabble.com/OpenCms-SQL-Statement-error-%28PostgreSQL-8.3.3%29-tp18441163p18441742.html
Sent from the OpenCMS - Dev mailing list archive at Nabble.com.