[Logo] JForum - Powering Communities
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Top Downloads] Top Downloads   [Groups] Back to home page 
[Register] Register /  [Login] Login 


JForum 2.8.2 is out with various fixes and improvements. Read all about it here

Problem after update RSS feed
Forum Index » User Forum
Author Message
jdev1


Joined: 2021/3/22
Messages: 22
Online
Hi! I just updated my jforum from 2.1.9 to 2.4.1 and when i try to open forum with more then 1000 of topics i've got error.

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

<!-- BEGIN ERROR STACK TRACE
net.jforum.exceptions.ForumException: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at net.jforum.Command.process(Command.java:114)
at net.jforum.JForum.processCommand(JForum.java:255)
at net.jforum.JForum.service(JForum.java:237)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:765)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at net.jforum.csrf.CsrfFilter.doFilter(CsrfFilter.java:97)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:7smilie
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:367)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:639)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:885)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:168smilie
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.reflect.InvocationTargetException
... 35 more
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:479smilie
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
at net.jforum.dao.generic.GenericTopicDAO.fillTopicsData(GenericTopicDAO.java:925)
at net.jforum.dao.generic.GenericTopicDAO.selectAllByForumByLimit(GenericTopicDAO.java:467)
at net.jforum.dao.oracle.OracleTopicDAO.selectAllByForumByLimit(OracleTopicDAO.java:62)
at net.jforum.dao.generic.GenericTopicDAO.selectAllByForum(GenericTopicDAO.java:44smilie
at net.jforum.view.forum.common.TopicsCommon.topicsByForum(TopicsCommon.java:10smilie
at net.jforum.view.forum.ForumAction.show(ForumAction.java:179)
... 35 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

... 56 more

URL is: /forum/forums/show/415.page
END ERROR STACK TRACE-->
udittmer


Joined: 2013/2/21
Messages: 393
Online
JForum has no such limit in general, at least not currently - I have a test forum with 4.5M topics, and it works fine. Might be an Oracle limitation as discussed in https://community.oracle.com/tech/developers/discussion/958612/ora-01795-maximum-number-of-expressions-in-a-list-is-1000-error

That's going to be hard to replicate or fix, since we have no Oracle DB available, and 2.4.1 is such an old version.

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
I can fix it by myself but i need to know what queries executes when i try to open any forum? Is it TopicModel in generic_queries.sql?
udittmer


Joined: 2013/2/21
Messages: 393
Online
No, it should be TopicModel.selectAllByForumByLimit in oracle.sql

I don't recall when selectAllByForum is executed, and when selectAllByForumByLimit, but only the latter one is overridden for Oracle.

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
I looked at the history of sql queries in Oracle and saw that when executing the query Topic Model.selectAllByForumByLimit variables for the block "WHERE LINENUM >= ? AND LINENUM < ?" 0 and 2147483647 are passed.
When executing the request, the full list of forum topics is pulled, not for one page.
Then I checked the history of sql queries in the old version of my forum, variables are passed correctly (0 and 20).
I tried installing a later version (2.6.2), but the problem is the same.
Could there be a problem with variables due some settings in SystemGlobal.properties or in jforum-custom.conf are incorrect?
udittmer


Joined: 2013/2/21
Messages: 393
Online
jdev1 wrote:I looked at the history of sql queries in Oracle and saw that when executing the query Topic Model.selectAllByForumByLimit variables for the block "WHERE LINENUM >= ? AND LINENUM < ?" 0 and 2147483647 are passed.

The value 2147483647 interesting, that sounds suspiciously like "-1", as used in GenericTopicDAO.selectAllByForum, and should cause TopicModel.selectAllByForum to be used rather than TopicModel.selectAllByForumByLimit. That is an optimization which apparently was never fixed for the Oracle code.

I think this is what should be in OracleTopicDAO:


@Override public List<Topic> selectAllByForumByLimit (final int forumId, final int startFrom, final int count)
{
if (count < 0)
return super.selectAllByForumByLimit(forumId, startFrom, count);
else
return super.selectAllByForumByLimit(forumId, startFrom, startFrom + count);
}

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
I found reason of my problem it's "topic.cache.enabled" in SystemGlobal.properties. When it's "true" it uses selectAllByForum with MAX.integer as argument that's why it was 2147483647 as value.


And I have successfully upgraded to version 2.8.2.
But now i have another problem at Admin panel. At config list when i click button "Sent test email" i've got JS console error:

VM1843:2 Uncaught SyntaxError: Unexpected token ':'
at b (VM1839 jquery-3.6.3.min.js:2:866)
at Function.globalEval (VM1839 jquery-3.6.3.min.js:2:2905)
at text script (VM1839 jquery-3.6.3.min.js:2:83507)
at VM1839 jquery-3.6.3.min.js:2:79897
at l (VM1839 jquery-3.6.3.min.js:2:80014)
at XMLHttpRequest.<anonymous> (VM1839 jquery-3.6.3.min.js:2:82782)
b @ VM1839 jquery-3.6.3.min.js:2
globalEval @ VM1839 jquery-3.6.3.min.js:2
text script @ VM1839 jquery-3.6.3.min.js:2
(anonymous) @ VM1839 jquery-3.6.3.min.js:2
l @ VM1839 jquery-3.6.3.min.js:2
(anonymous) @ VM1839 jquery-3.6.3.min.js:2
load (async)
send @ VM1839 jquery-3.6.3.min.js:2
ajax @ VM1839 jquery-3.6.3.min.js:2
testEmail @ list.page:60
onclick @ list.page:700



And error in source code:
<!-- BEGIN ERROR STACK TRACE
net.jforum.exceptions.ForumException: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at net.jforum.Command.process(Command.java:122)
at net.jforum.JForum.processCommand(JForum.java:269)
at net.jforum.JForum.service(JForum.java:251)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at net.jforum.csrf.CsrfFilter.doFilter(CsrfFilter.java:104)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:7smilie
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.reflect.InvocationTargetException
... 35 more
Caused by: java.lang.NullPointerException
at java.util.Hashtable.put(Unknown Source)
at net.jforum.util.preferences.SystemGlobals.setValue(SystemGlobals.java:160)
at net.jforum.view.forum.AjaxAction.sendTestMail(AjaxAction.java:134)
... 35 more

URL is: /forum/ajax/sendTestMail.page
END ERROR STACK TRACE-->



Also when i click on save button it page redirects to forum list page and save nothing.
Same thing at Lucene page. When i want to check is message indexed i've got error. When i want to start indexing, page redirects to forum list page.
udittmer


Joined: 2013/2/21
Messages: 393
Online
AN NPE sounds like some value is missing, so make sure all fields required for sending emails (basically, all fields above "Send a test email") have correct values in them.

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
It looks very strange. No AJAX POST request works. When I changed the type to GET, everything worked.

$.ajax({
type:"POST",
url:"${JForumContext.encodeURL("/ajax/sendTestMail")}",
data:params,
dataType:"script",
global:false
});
udittmer


Joined: 2013/2/21
Messages: 393
Online
jdev1 wrote:I found reason of my problem it's "topic.cache.enabled" in SystemGlobal.properties. When it's "true" it uses selectAllByForum with MAX.integer as argument that's why it was 2147483647 as value.

That addresses the symptom, not the problem. If I provide you with a version that includes the fix I posted above, would you be willing to test that?

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
udittmer wrote:
jdev1 wrote:I found reason of my problem it's "topic.cache.enabled" in SystemGlobal.properties. When it's "true" it uses selectAllByForum with MAX.integer as argument that's why it was 2147483647 as value.

That addresses the symptom, not the problem. If I provide you with a version that includes the fix I posted above, would you be willing to test that?


Yes i can test it.
jdev1


Joined: 2021/3/22
Messages: 22
Online
Sorry to bother you but I have one last question. How can i change format of user registration date at user profile? Now it looks like this Jul 6, 2011
At SystemGlobals.properties date format looks like this dateTime.format=dd.MM.yyyy HH\:mm\:ss
Also topic list and forum list shows date in format dd.MM.yyyy HH\:mm\:ss
Thank you
udittmer


Joined: 2013/2/21
Messages: 393
Online
In the file templates/default/user_profile.htm, replace registrationDay with registrationDate - that will use the format you selected. Note that in the topic view, the format will still use the short version "Jul 6, 2011", because there space is at a premium.

Attached is a new JForum jar file that has the fix for Oracle. If you replace the one you have with this one, it should work regardless of the cache setting. Let me know how it works out for you.
 Filename jforum-2.8.2.jar [Disk] Download
 Description No description given
 Filesize 689 Kbytes
 Downloaded:  11 time(s)


Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
jdev1


Joined: 2021/3/22
Messages: 22
Online
udittmer wrote:In the file templates/default/user_profile.htm, replace registrationDay with registrationDate - that will use the format you selected. Note that in the topic view, the format will still use the short version "Jul 6, 2011", because there space is at a premium.

Attached is a new JForum jar file that has the fix for Oracle. If you replace the one you have with this one, it should work regardless of the cache setting. Let me know how it works out for you.


No, it's the same error
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
 
Forum Index » User Forum
Go to:   
Mobile view
Powered by JForum 2.8.2 © 2022 JForum Team • Maintained by Andowson Chang and Ulf Dittmer