EntityListIterator issue with msyql8

I am trying to run moqui with mysql8, facing some issue with cursor fetch,
Iterator throwing exception if entity-find returns empty list.

Here is the refernce code

<entity-find entity-name="moqui.security.UserAccount" list="userAccounts" limit="10">
    <econdition field-name="username" value="TEST"/>
    <use-iterator/>
</entity-find>
<iterate list="userAccounts" entry="userAccount">
    <log message="====UserName===${userAccount.username}="/>
  </iterate>

04:30:34.184 ERROR 936550492-17                  o.moqui.i.a.XmlAction Error running groovy script (java.lang.NullPointerException: Cannot get property 'username' on null object): 
1 : import static org.moqui.util.ObjectUtilities.*
2 : import static org.moqui.util.CollectionUtilities.*
3 : import static org.moqui.util.StringUtilities.*
4 : import java.sql.Timestamp
5 : // these are in the context by default: ExecutionContext ec, Map<String, Object> context, Map<String, Object> result
6 :     userAccounts_xafind = ec.entity.find("moqui.security.UserAccount").limit(10)
7 :             .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("username", "equals", username, "TEST", null, false, false, false, "false"))
8 :             userAccounts = userAccounts_xafind.iterator()
9 :         userAccount_index = 0
10 :         _userAccountIterator = userAccounts.iterator()
11 :         while (_userAccountIterator.hasNext()) {
12 :             userAccount = _userAccountIterator.next()
13 :             userAccount_has_next = _userAccountIterator.hasNext()
14 :     ec.logger.log("info", """====UserName===${userAccount.username}=""", null)
15 :             userAccount_index++
16 :         }
17 :         if (userAccounts instanceof org.moqui.entity.EntityListIterator) userAccounts.close()
18 :     
19 : // make sure the last statement is not considered the return value
20 : return;

I am using the mysql8 database coffig where useCursorFetch=“true”, if useCursorFetch set as false its working fine.

I suspect EntityListIterator.hasNext() method returning true if list is empty, Either we need to fix ELI.hasNext() or update code reference to check if eli.next() is null.

Anyone else tried mysql8 or cursor based fetch ?

You mentioned it throws an exception, what is the exception and stack trace? Without that it’s hard to say how MySQL might be behaving differently in a way that would cause an issue.

The docs about useCursorFetch=true just mention that it tells the JDBC driver to fetch X records at a time (which is also configurable in Moqui). The strange thing is that usually the fetch size (number of records fetched per round trip to the DB) does NOT usually change any behavior, it’s just a performance tuning parameter that rarely needs to be touched.

Enabling cursor fetch in a JDBC driver should not change behavior, but if an exception is being thrown because that is enabled then something is behaving differently, and the error message + stack trace would be a good place to start to try to figure out what that is… and then we can talk about what to do about it.

Here is the complete stack trace

> :run
23:27:55.966 ERROR 887699190-17                  o.moqui.i.a.XmlAction Error running groovy script (java.lang.NullPointerException: Cannot get property 'partyId' on null object): 
1 : import static org.moqui.util.ObjectUtilities.*
2 : import static org.moqui.util.CollectionUtilities.*
3 : import static org.moqui.util.StringUtilities.*
4 : import java.sql.Timestamp
5 : // these are in the context by default: ExecutionContext ec, Map<String, Object> context, Map<String, Object> result
6 :     lists_xafind = ec.entity.find("org.apache.ofbiz.party.party.Person").limit(10)
7 :             .condition((org.moqui.entity.EntityCondition) ec.entity.conditionFactory.makeActionConditionDirect("partyId", "equals", partyId, "TEST", null, false, false, false, "false"))
8 :             lists = lists_xafind.iterator()
9 :         userAccount_index = 0
10 :         _userAccountIterator = lists.iterator()
11 :         while (_userAccountIterator.hasNext()) {
12 :             userAccount = _userAccountIterator.next()
13 :             userAccount_has_next = _userAccountIterator.hasNext()
14 :     ec.logger.log("info", """====UserName===${userAccount.partyId}=""", null)
15 :             userAccount_index++
16 :         }
17 :         if (lists instanceof org.moqui.entity.EntityListIterator) lists.close()
18 :     
19 : // make sure the last statement is not considered the return value
20 : return;


23:27:55.967  WARN 887699190-17      o.moqui.i.c.TransactionFacadeImpl Transaction rollback. The rollback was originally caused by: Error running service co.hotwax.ofbiz.InventoryServices.test#Person (Throwable)
java.lang.NullPointerException: Cannot get property 'partyId' on null object
        at co_hotwax_ofbiz_InventoryServices_test_Person.run(co_hotwax_ofbiz_InventoryServices_test_Person:14) ~[?:?]
        at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:9) ~[?:?]
        at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:987) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[moqui_temp3740312692125626751WEB-INF_lib_javax.servlet-api-4.0.1.jar.:4.0.1]
        at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:96) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at java.lang.Thread.run(Thread.java:829) ~[?:?]
23:27:55.977  WARN 887699190-17      o.moqui.i.c.TransactionFacadeImpl Transaction rollback for [Error running service co.hotwax.ofbiz.InventoryServices.test#Person (Throwable)]. Here is the current location: 
org.moqui.BaseException: Rollback location
        at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:467) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.context.TransactionFacadeImpl.rollback(TransactionFacadeImpl.groovy:443) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:347) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:9) ~[?:?]
        at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:987) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[moqui_temp3740312692125626751WEB-INF_lib_javax.servlet-api-4.0.1.jar.:4.0.1]
        at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:96) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at java.lang.Thread.run(Thread.java:829) ~[?:?]
<============-> 98% EXECUTING [1m 29s]
23:27:56.479  WARN 887699190-17        o.moqui.i.s.ServiceCallSyncImpl Error running service co.hotwax.ofbiz.InventoryServices.test#Person (Throwable) Artifact stack: co.hotwax.ofbiz.InventoryServices.test#Person, component://tools/screen/Tools/Service/ServiceRun.xml/run, component://tools/screen/Tools/Service/ServiceRun.xml, component://tools/screen/Tools/Service.xml, component://tools/screen/Tools.xml, component://webroot/screen/webroot/apps.xml, component://webroot/screen/webroot.xml
java.lang.NullPointerException: Cannot get property 'partyId' on null object
        at co_hotwax_ofbiz_InventoryServices_test_Person.run(co_hotwax_ofbiz_InventoryServices_test_Person:14) ~[?:?]
        at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.runner.InlineServiceRunner.runService(InlineServiceRunner.java:59) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.callSingle(ServiceCallSyncImpl.java:322) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.service.ServiceCallSyncImpl.call(ServiceCallSyncImpl.java:125) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions.run(component___tools_screen_Tools_Service_ServiceRun_xml_transition_run_actions:9) ~[?:?]
        at org.moqui.impl.actions.XmlAction.run(XmlAction.java:67) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenDefinition$TransitionItem.run(ScreenDefinition.groovy:987) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:749) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.recursiveRunTransition(ScreenRenderImpl.groovy:745) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.internalRender(ScreenRenderImpl.groovy:454) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.screen.ScreenRenderImpl.render(ScreenRenderImpl.groovy:170) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at org.moqui.impl.webapp.MoquiServlet.service(MoquiServlet.groovy:118) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:750) ~[moqui_temp3740312692125626751WEB-INF_lib_javax.servlet-api-4.0.1.jar.:4.0.1]
        at org.moqui.impl.webapp.ElasticRequestLogFilter.doFilter(ElasticRequestLogFilter.groovy:96) ~[moqui_temp8490129751187129696WEB-INF_lib_moqui-framework-3.0.0.jar.:3.0.0]
        at java.lang.Thread.run(Thread.java:829) ~[?:?]
23:27:56.481 ERROR 887699190-17          o.moqui.i.c.MessageFacadeImpl Cannot get property 'partyId' on null object
23:27:56.485  INFO 887699190-17           o.moqui.i.s.ScreenRenderImpl Transition apps/tools/Service/ServiceRun/run in 2138ms, JSON redirect to: /apps/tools/Service/ServiceRun?doingRun=Y&serviceName=co.hotwax.ofbiz.InventoryServices.test%23Person

Environment:
Mysql server: mysql Ver 8.0.30 for macos12.4 on arm64 (Homebrew)
JDBC Driver version: mysql:mysql-connector-java:8.0.30

After initial debugging I found, with useCursorFetch=true eli.hasNext() returning true for empty result set, as rs.isBeforeFirst() returning true for empty result set.

Found old mysql ticket
https://bugs.mysql.com/bug.php?id=78321

Interesting, to restate: the difference in behavior is that when there are no results, and useCursorFetch is set to true, then EntityListIteratorImpl.hasNext() returns true when it should return false; the reason it does this is that the MySQL JDBC driver ResultSet.isBeforeFirst() returns true in this circumstance.

This would be a bug because the JDBC spec (from ResultSet javadoc) says that isBeforeFirst() returns: " true if the cursor is before the first row; false if the cursor is at any other position or the result set contains no rows".

The MySQL issue you linked to looks like a different issue, when isBeforeFirst() is false then getRow() returns different values based on the setting, but in this case the setting is making isBeforeFirst() return true when it should return false. It might be related, but is different.

What to do about this is another question, if the recommended pattern for EntityListIterator (calling next() only and not using hasNext() ) is used this won’t be a problem, ie:

https://moqui.org/javadoc/org/moqui/entity/EntityListIterator.html#hasNext()

Like:

EntityValue nextValue;
while ((nextValue = eli.next()) != null) { ... }

I just made some changes to the macro for the XML Actions iterator element to do this, ie look for nulls and don’t call hasNext():

On a side note, null checking can’t be used for all iteration, just for special cases where nulls are not expected, or really, not allowed… no null records in a SQL query result set. That would break things for other iterations of collections that include nulls and you want to iterate over those.

Anyway, going back to original issue… I’m surprised this came up because the useCursorFetch setting has been there as a default (in MoquiDefaultConf.xml) for a very long time. You mentioned you are using the mysql8 settings, which versions of the JDBC driver (MySQL Connector/J) and MySQL Server are you using?

1 Like

Earlier I was using mysql 5.7, recently I upgraded mysql server from 5.7 to 8.0
Here are the details
MySql server : mysql Ver 8.0.30 for macos12.4 on arm64 (Homebrew)
JDBC Driver Version: mysql connector j 8.0.30

Thanks David, After applying the fix, its working and not getting the mentioned error, but It seems eli not closed,

11:17:48.305 ERROR    Finalizer     o.moqui.i.e.EntityListIteratorImpl EntityListIterator not closed for entity [org.apache.ofbiz.party.party.Person], caught in finalize()
AT_ENTITY:org.apache.ofbiz.party.party.Person (AUTHZA_VIEW:iterator) 347ms TX 65
AT_SERVICE:co.hotwax.ofbiz.InventoryServices.test#Person (AUTHZA_ALL:inline) 348ms TX 65
AT_XML_SCREEN_TRANS:component://tools/screen/Tools/Service/ServiceRun.xml/run (AUTHZA_VIEW:text/html) 348ms TX 65
AT_XML_SCREEN:component://tools/screen/Tools/Service/ServiceRun.xml (AUTHZA_VIEW) 348ms TX 65
AT_XML_SCREEN:component://tools/screen/Tools/Service.xml (AUTHZA_VIEW) 348ms TX 65
AT_XML_SCREEN:component://tools/screen/Tools.xml (AUTHZA_VIEW) 348ms TX 65
AT_XML_SCREEN:component://webroot/screen/webroot/apps.xml (AUTHZA_VIEW) 348ms TX 65
AT_XML_SCREEN:component://webroot/screen/webroot.xml (AUTHZA_VIEW) 348ms TX 65

Thanks for reporting that, auto close of EntityListIterator restored in this commit:

I saw your code sample, did something very similar but with one small variation to use the ${.node[“@entry”]}Iterator field so it is based on the entry attribute instead of the list attribute, to better support the value of the list attribute being an expression without causing issues in the generated code.

1 Like

Thanks David,
Now every thing working fine.

I encountered another mysql8 issue while setting up new dev, I’ll start new thread for the same.