Generic JDBC Connector - Connections not properly closed?

[Claire Fautsch <cf...@goodgamestudios.com>]

Hello,

we are using Sqoop (v 1.99.3) to transfer data from our Vertica DB to HDFS.
To connect to the DB we use a slightly modified version of the Generic JDBC
Connector.
After using it for some time, we realized that we ended up with more and
more connections held by Sqoop in our DB. Somehow they were not properly
closed after the Sqoop jobs finished.

After having a look into the connector, I found two places where it seems
that connections are not properly closed.

I am wondering if this should be filed as bug, or if I am missing
something.

Here below the changes we made:

First, in the class GenericJdbcExportDestroyer, in the method
moveDataToDestination Table. A GenericJdbcExecutor is created, but it is
missing executor.close.

Our modified version now looks like


 private void moveDataToDestinationTable(ConnectionConfiguration
connectorConf,
    boolean success, String stageTableName, String tableName) {
    GenericJdbcExecutor executor =
      new GenericJdbcExecutor(connectorConf.connection.jdbcDriver,
        connectorConf.connection.connectionString,
        connectorConf.connection.username,
        connectorConf.connection.password);

    try {
        if (success) {
            LOG.info("Job completed, transferring data from stage table to
" +
                    "destination table.");
            executor.migrateData(stageTableName, tableName);
        } else {
            LOG.warn("Job failed, clearing stage table.");
            executor.deleteTableData(stageTableName);
        }
    } finally { //added part
        executor.close();
    }


  }

Second, in the class GenericJdbcImportInitializer, in the method getSchema.
By calling the method configureJdbcProperties, an executor is created, but
once again, never closed. We changed this method, to include executor.close
in the finally block

 finally {
        if(rs != null) {
            try {
              rs.close();
            } catch (SQLException e) {
              LOG.info("Ignoring exception while closing ResultSet", e);
            }
        }
        if (executor != null) { //added part
            executor.close();
        }
    }

   Thanks in advance for any feedback!

   Best regards
   Claire Fautsch

Re: Generic JDBC Connector - Connections not properly closed?

[Jarek Jarcec Cecho <ja...@apache.org>]

I would recommend filing bugs on our JIRA [1] - don’t hesitate and submit your patches, we’re more then happy to review and incorporate them!

Jarcec

Links:
1: https://issues.apache.org/jira/browse/SQOOP

On Aug 26, 2014, at 9:55 AM, Claire Fautsch <cf...@goodgamestudios.com> wrote:

> Hello, 
> 
> we are using Sqoop (v 1.99.3) to transfer data from our Vertica DB to HDFS. To connect to the DB we use a slightly modified version of the Generic JDBC Connector.  
> After using it for some time, we realized that we ended up with more and more connections held by Sqoop in our DB. Somehow they were not properly closed after the Sqoop jobs finished. 
> 
> After having a look into the connector, I found two places where it seems that connections are not properly closed.  
> 
> I am wondering if this should be filed as bug, or if I am missing something. 
> 
> Here below the changes we made: 
> 
> First, in the class GenericJdbcExportDestroyer, in the method moveDataToDestination Table. A GenericJdbcExecutor is created, but it is missing executor.close. 
> 
> Our modified version now looks like
> 
> 
>  private void moveDataToDestinationTable(ConnectionConfiguration connectorConf,
>     boolean success, String stageTableName, String tableName) {
>     GenericJdbcExecutor executor =
>       new GenericJdbcExecutor(connectorConf.connection.jdbcDriver,
>         connectorConf.connection.connectionString,
>         connectorConf.connection.username,
>         connectorConf.connection.password);
> 
>     try {
>         if (success) {
>             LOG.info("Job completed, transferring data from stage table to " +
>                     "destination table.");
>             executor.migrateData(stageTableName, tableName);
>         } else {
>             LOG.warn("Job failed, clearing stage table.");
>             executor.deleteTableData(stageTableName);
>         }
>     } finally { //added part
>         executor.close();
>     }
> 
> 
>   }
>   
> Second, in the class GenericJdbcImportInitializer, in the method getSchema. By calling the method configureJdbcProperties, an executor is created, but once again, never closed. We changed this method, to include executor.close in the finally block
>  
>  finally {
>         if(rs != null) {
>             try {
>               rs.close();
>             } catch (SQLException e) {
>               LOG.info("Ignoring exception while closing ResultSet", e);
>             }
>         }
>         if (executor != null) { //added part
>             executor.close();
>         }
>     }
>     
>    Thanks in advance for any feedback!
>     
>    Best regards
>    Claire Fautsch
> 
>                           
>