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:7
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:168
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:479
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:44
at net.jforum.view.forum.common.TopicsCommon.topicsByForum(TopicsCommon.java:10
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-->

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.

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?

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.

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?

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);

	}

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:7
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.

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.

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
});

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?

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.

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

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.

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

Hi!
I have a problem at forums list and topics list.
If new message added forum and topic marks as "New Messages" only if user is online. If user is offline, after log in no forum or topic is marked as "New Messages" even if new posts have been added to them.
How can i solve that problem? Maybe some setting at config list?
Thank you!