OpenCms SQL Statement error (PostgreSQL 8.3.3)

[Roman Uhlig]

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

[Michael Moossen]

Hi Roman!

update to OpenCms 7.0.5, since this has been fixed in OpenCms 7.0.4

Kind regards,
Michael

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

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


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

_______________________________________________
This mail is sent to you from the opencms-dev mailing list
To change your list options, or to unsubscribe from the list, please visit
http://lists.opencms.org/mailman/listinfo/opencms-dev

[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

[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
>>
>

_______________________________________________
This mail is sent to you from the opencms-dev mailing list
To change your list options, or to unsubscribe from the list, please visit
http://lists.opencms.org/mailman/listinfo/opencms-dev

[Roman Uhlig]

Thanks for the hint, Michael. And you've been right: After upgrading from MySQL/J connector 5.0.7 to 5.1.6, the DB pooler is keeping up the database connection correctly.

Maybe this package should be changed in the 7.0.5 installation?

Thanks,
Roman