Solr
  1. Solr
  2. SOLR-2045

DIH doesn't release jdbc connections in conjunction with DB2

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.4.1, 3.6, 4.0
    • Fix Version/s: 4.1, 5.0
    • Labels:
      None
    • Environment:

      DB2 SQLLIB 9.5, 9.7 jdbc Driver

      Description

      Using the JDBCDatasource in conjunction with the DB2 JDBC Drivers results in the following error when the DIH tries to close the the connection due to active transactions. As a consequence each delta im port or full import opens a new connection without closing it. So the maximum amount of connections will be reached soon. Setting the connection to readOnly or changing the transaction isolation level doesn't help neither.

      The JDBC Driver I used: "com.ibm.db2.jcc.DB2Driver" relieing in db2jcc4.jar shipped with DB2 Express 9.7 for example

      Here is the stack trace...

      14.08.2010 01:49:51 org.apache.solr.handler.dataimport.JdbcDataSource closeConnection
      FATAL: Ignoring Error when closing connection
      com.ibm.db2.jcc.am.SqlException: [jcc][10251][10308][4.8.87] java.sql.Connection.close() requested while a transaction is in progress on the connection.The transaction remains active, and the connection cannot be closed. ERRORCODE=-4471, SQLSTATE=null
      at com.ibm.db2.jcc.am.gd.a(gd.java:660)
      at com.ibm.db2.jcc.am.gd.a(gd.java:60)
      at com.ibm.db2.jcc.am.gd.a(gd.java:120)
      at com.ibm.db2.jcc.am.lb.u(lb.java:1202)
      at com.ibm.db2.jcc.am.lb.x(lb.java:1225)
      at com.ibm.db2.jcc.am.lb.v(lb.java:1211)
      at com.ibm.db2.jcc.am.lb.close(lb.java:1195)
      at com.ibm.db2.jcc.uw.UWConnection.close(UWConnection.java:838)
      at org.apache.solr.handler.dataimport.JdbcDataSource.closeConnection(JdbcDataSource.java:399)
      at org.apache.solr.handler.dataimport.JdbcDataSource.close(JdbcDataSource.java:390)
      at org.apache.solr.handler.dataimport.DataConfig$Entity.clearCache(DataConfig.java:173)
      at org.apache.solr.handler.dataimport.DataConfig.clearCaches(DataConfig.java:331)
      at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:339)
      at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:389)
      at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:370)

      Well the issue can be solved by invoking a commit or rollback directly before the connection.close() statement. Here is the code snipped of changes I made in JdbcDatasource.java

      private void closeConnection() {
      try {
      if (conn != null) {

      if (conn.isReadOnly())

      { LOG.info("connection is readonly, therefore rollback"); conn.rollback(); }

      else

      { LOG.info("connection is not readonly, therefore commit"); conn.commit(); }

      conn.close();
      }
      } catch (Exception e)

      { LOG.error("Ignoring Error when closing connection", e); }

      }

      1. SOLR-2045.patch
        42 kB
        James Dyer
      2. SOLR-2045.patch
        33 kB
        James Dyer

        Issue Links

          Activity

          Hide
          Lance Norskog added a comment -

          Mysql has the same problem: incremental updates do not reuse a connection and therefore fail.

          Show
          Lance Norskog added a comment - Mysql has the same problem: incremental updates do not reuse a connection and therefore fail.
          Hide
          Kjetil Ødegaard added a comment -

          We see the same issue on Oracle (11g).

          Show
          Kjetil Ødegaard added a comment - We see the same issue on Oracle (11g).
          Hide
          Alexey Serba added a comment - - edited

          I encountered the same problem with Derby database. The reason is that you need to issue commit or rollback before releasing jdbc connection (that's ridiculous requirement but it is what it is). So this patch fixing this problem.

          But there's even more easy workaround that doesn't require patching Solr sources. You can set data source autoCommit property to true.

          Setting the connection to readOnly or changing the transaction isolation level doesn't help neither.

          readOnly property should help as well, but there's a bug in parameters parsing logic. Documentation claims that readOnly parameter causes setting autoCommit property to true, but that's not true as the next conditional statement resets this property back to false.

          "JDBCDataSource.createConnectionFactory:164"
          if (Boolean.parseBoolean(initProps.getProperty("readOnly"))) {
            c.setReadOnly(true);
            // Add other sane defaults
            c.setAutoCommit(true);
            c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
          }
          if (!Boolean.parseBoolean(initProps.getProperty("autoCommit"))) {
            c.setAutoCommit(false);
          }
          
          Show
          Alexey Serba added a comment - - edited I encountered the same problem with Derby database. The reason is that you need to issue commit or rollback before releasing jdbc connection (that's ridiculous requirement but it is what it is). So this patch fixing this problem. But there's even more easy workaround that doesn't require patching Solr sources. You can set data source autoCommit property to true. Setting the connection to readOnly or changing the transaction isolation level doesn't help neither. readOnly property should help as well, but there's a bug in parameters parsing logic. Documentation claims that readOnly parameter causes setting autoCommit property to true, but that's not true as the next conditional statement resets this property back to false. "JDBCDataSource.createConnectionFactory:164" if ( Boolean .parseBoolean(initProps.getProperty( "readOnly" ))) { c.setReadOnly( true ); // Add other sane defaults c.setAutoCommit( true ); c.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED); c.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT); } if (! Boolean .parseBoolean(initProps.getProperty( "autoCommit" ))) { c.setAutoCommit( false ); }
          Hide
          James Dyer added a comment -

          For this issue I will add Derby alongside HSQLDB as a random option for testing SqlEntityProcessor, building on the new tests added with SOLR-3856. The bug described here fails with Derby but not HSQLDB, so it is good to have at least RDBMS to test with.

          Show
          James Dyer added a comment - For this issue I will add Derby alongside HSQLDB as a random option for testing SqlEntityProcessor, building on the new tests added with SOLR-3856 . The bug described here fails with Derby but not HSQLDB, so it is good to have at least RDBMS to test with.
          Hide
          James Dyer added a comment -

          This patch fixes the problem by issuing a "commit" before closing the connection, as suggested by Fenior.

          I added Derby as a randomly-selected test db to have coverage for this bug. As derby is only needed for testing, I configured Ivy to locate the derby jar in the same directory as the hsqldb jar, under the dih example.

          I also added the 2 db jars to the Eclipse dot.classpath and to the Idea config files so that you can easily run these tests from either ide. (this is my first exposure to Idea but with all the good words I've heard on this mailing list I thought this a good time to try it out...)

          I plan on committing this patch tomorrow.

          Show
          James Dyer added a comment - This patch fixes the problem by issuing a "commit" before closing the connection, as suggested by Fenior. I added Derby as a randomly-selected test db to have coverage for this bug. As derby is only needed for testing, I configured Ivy to locate the derby jar in the same directory as the hsqldb jar, under the dih example. I also added the 2 db jars to the Eclipse dot.classpath and to the Idea config files so that you can easily run these tests from either ide. (this is my first exposure to Idea but with all the good words I've heard on this mailing list I thought this a good time to try it out...) I plan on committing this patch tomorrow.
          Hide
          James Dyer added a comment -

          new patch to commit.

          Show
          James Dyer added a comment - new patch to commit.
          Hide
          James Dyer added a comment -

          committed...
          Trunk: r1408364/r1408368 (CHANGES.txt)
          4x: r1408370

          Thanks, Fenlor. Sorry it took 2+ years for such an easy change.

          Show
          James Dyer added a comment - committed... Trunk: r1408364/r1408368 (CHANGES.txt) 4x: r1408370 Thanks, Fenlor. Sorry it took 2+ years for such an easy change.
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1412266

          SOLR-2045: workaround for Locales not supported by Derby

          Show
          Commit Tag Bot added a comment - [branch_4x commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1412266 SOLR-2045 : workaround for Locales not supported by Derby
          Hide
          Commit Tag Bot added a comment -

          [trunk commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1412262

          SOLR-2045: workaround for Locales not supported by Derby

          Show
          Commit Tag Bot added a comment - [trunk commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1412262 SOLR-2045 : workaround for Locales not supported by Derby
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1412266

          SOLR-2045: workaround for Locales not supported by Derby

          Show
          Commit Tag Bot added a comment - [branch_4x commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1412266 SOLR-2045 : workaround for Locales not supported by Derby
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] Steven Rowe
          http://svn.apache.org/viewvc?view=revision&revision=1408688

          SOLR-2045: Maven configuration: add derby test dependency to DIH (merge trunk r1408685)

          Show
          Commit Tag Bot added a comment - [branch_4x commit] Steven Rowe http://svn.apache.org/viewvc?view=revision&revision=1408688 SOLR-2045 : Maven configuration: add derby test dependency to DIH (merge trunk r1408685)
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1408389

          SOLR-2045: fix svn:eol-style for sha1 file

          Show
          Commit Tag Bot added a comment - [branch_4x commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1408389 SOLR-2045 : fix svn:eol-style for sha1 file
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1408380

          SOLR-2045: suppress creation of derby.log during test

          Show
          Commit Tag Bot added a comment - [branch_4x commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1408380 SOLR-2045 : suppress creation of derby.log during test
          Hide
          Commit Tag Bot added a comment -

          [branch_4x commit] James Dyer
          http://svn.apache.org/viewvc?view=revision&revision=1408370

          SOLR-2045: DIH doesn't release jdbc connections for some databases

          Show
          Commit Tag Bot added a comment - [branch_4x commit] James Dyer http://svn.apache.org/viewvc?view=revision&revision=1408370 SOLR-2045 : DIH doesn't release jdbc connections for some databases

            People

            • Assignee:
              James Dyer
              Reporter:
              Fenlor Sebastia
            • Votes:
              6 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development