search

NCEAS / vegbank

VegBank vegetation plot and classification database
GNU General Public License v2.0
0 stars 1 forks source link

Removing a single dataset item removes all dataset items #1

Closed amoeba closed 4 years ago

amoeba commented 4 years ago

Michael Lee reported this one:

Steps to reproduce:

  1. Add more than one item to a dataset
  2. Remove one item
  3. Observe all are removed

Expected: Only one is removed.

amoeba commented 4 years ago

I confirmed Michael's report, and could reproduce the bug on both vegbank.org and vegbankdev. I tested locally and could not reproduce it which I find really odd since my Java and PostgreSQL versions are the same as on our VMs.

I found what looks like the culprit in vegbank.log:

org.postgresql.util.PSQLException: Operation requires a scrollable
ResultSet, but this ResultSet is FORWARD_ONLY
Expand for full log output incld. stacktrace ``` DEBUG utility.DatasetUtility: getting datacart from session: 199168 ERROR utility.Utility: Problem building bean from DB results. org.postgresql.util.PSQLException: Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY. at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkScrollable(AbstractJdbc2ResultSet.java:190) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.first(AbstractJdbc2ResultSet.java:275) at org.vegbank.common.utility.Utility.buildBean(Utility.java:389) at org.vegbank.common.utility.DatasetUtility.retrieveDatasetFromDB(DatasetUtility.java:770) at org.vegbank.common.utility.DatasetUtility.getDataset(DatasetUtility.java:711) at org.vegbank.common.utility.DatasetUtility.getDatacart(DatasetUtility.java:794) at org.vegbank.ui.struts.taglib.VegbankDatacartTag.doStartTag(VegbankDatacartTag.java:137) at org.apache.jsp.ajax.get_005fdatacart_005fcount_ajax_jsp._jspx_meth_vegbank_005fdatacart_005f0(get_005fdatacart_005fcount_ajax_jsp.java:284) at org.apache.jsp.ajax.get_005fdatacart_005fcount_ajax_jsp._jspService(get_005fdatacart_005fcount_ajax_jsp.java:250) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:476) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) 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:53) 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:200) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:668) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:394) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:834) ERROR utility.Utility: Problem building bean from DB results. org.postgresql.util.PSQLException: Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY. at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkScrollable(AbstractJdbc2ResultSet.java:190) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.first(AbstractJdbc2ResultSet.java:275) at org.vegbank.common.utility.Utility.buildBean(Utility.java:389) at org.vegbank.common.utility.DatasetUtility.retrieveDatasetFromDB(DatasetUtility.java:770) at org.vegbank.common.utility.DatasetUtility.getDatacartByUser(DatasetUtility.java:744) at org.vegbank.common.utility.DatasetUtility.getOrCreateDatacart(DatasetUtility.java:832) at org.vegbank.ui.struts.taglib.VegbankDatacartTag.doStartTag(VegbankDatacartTag.java:142) at org.apache.jsp.ajax.get_005fdatacart_005fcount_ajax_jsp._jspx_meth_vegbank_005fdatacart_005f0(get_005fdatacart_005fcount_ajax_jsp.java:284) at org.apache.jsp.ajax.get_005fdatacart_005fcount_ajax_jsp._jspService(get_005fdatacart_005fcount_ajax_jsp.java:250) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:476) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329) at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) 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:53) 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:200) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:668) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:394) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:834) DEBUG utility.DatasetUtility: + + + creating new empty datacart + + + usr_id: 780 ```

The offending line of code is:

https://github.com/NCEAS/vegbank/blob/20d05a9ff843c1c08679b746d152f33eedca60ee/src/java/org/vegbank/common/utility/Utility.java#L389

which appears to require a scrollable result set.

Vegbank performs all of its database operations with a lightweight helper on top of various java.sql classes and issuing a select looks like:

https://github.com/NCEAS/vegbank/blob/20d05a9ff843c1c08679b746d152f33eedca60ee/src/java/org/vegbank/common/utility/DatabaseAccess.java#L54

So I refactored issueSelect(String) to be polymorphic by adding a issueSelect(String, int, int) form which allows flags to be passed like:

da.issueSelect(DatabaseUtility.removeSemicolons(query.toString()), 
               ResultSet.CONCUR_READ_ONLY, 
               ResultSet.TYPE_SCROLL_INSENSITIVE);

issueSelect(String) simply calls issueSelect(String, int, int) with the default flags (read only, non-scrollable) and is used in 51 places in the codebase. issueSelect(String, int, int) is only directly needed for this single usage in the dataset/cart feature.

I deployed updated code to vegbankdev and will coordinate with Michael Lee to help me verify the fix.

Things left to do: