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

[Michael Moossen]

Hi Roman!

this is a more general issue with no easy solution.
i would first check that the mysql jdbc driver is the latest.
if you are using the form generator module, update to the latest version.
if you access the db over the network, check that no firewall is closing 
the connections.

HTH

Kind regards,
Michael

-------------------

Alkacon Software GmbH  - The OpenCms Experts
http://www.alkacon.com - http://www.opencms.org


Roman Uhlig wrote:
> 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
>>
>