Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-1329

JDBC connection to Oracle timeout after data import but before hive metadata import

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.4.4
    • Fix Version/s: 1.4.5
    • Component/s: connectors/oracle
    • Labels:
    • Environment:

      Red Hat Enterprise Linux Server release 6.5

      Description

      When I'm importing a table from Oracle which takes more than 1 hour to extract, I'm getting following error message at the stage when Sqoop tries to import data from temporary HDFS location to Hive:

      14/05/27 13:05:51 INFO mapreduce.ImportJobBase: Transferred 47.2606 GB in 6,389.4644 seconds (6.7206 MB/sec)
      14/05/27 13:05:51 INFO mapreduce.ImportJobBase: Retrieved 98235461 records.
      14/05/27 13:05:51 DEBUG util.ClassLoaderStack: Restoring classloader: sun.misc.Launcher$AppClassLoader@566d0085
      14/05/27 13:05:51 DEBUG hive.HiveImport: Hive.inputTable: WAREHOUSE.MY_BIG_TABLE
      14/05/27 13:05:51 DEBUG hive.HiveImport: Hive.outputTable: WAREHOUSE.MY_BIG_TABLE
      14/05/27 13:05:51 DEBUG manager.OracleManager: Using column names query: SELECT t.* FROM WAREHOUSE.MY_BIG_TABLE t WHERE 1=0
      14/05/27 13:05:51 DEBUG manager.SqlManager: Execute getColumnTypesRawQuery : SELECT t.* FROM WAREHOUSE.MY_BIG_TABLE t WHERE 1=0
      14/05/27 13:05:51 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again

      java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)

      With small tables (under 1 hour) everything is fine.

      I'm using Sqoop v1.4.4

      14/05/27 13:49:14 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4-cdh5.0.0
      Sqoop 1.4.4-cdh5.0.0
      git commit id 8e266e052e423af592871e2dfe09d54c03f6a0e8

      This problems looks exactly as described in (SQOOP-934) issue.

      1. SQOOP-1329.patch
        0.8 kB
        Andrey Dmitriev

        Activity

        Hide
        biosetup Andrey Dmitriev added a comment -

        Catches expired connection and resets it.

        Show
        biosetup Andrey Dmitriev added a comment - Catches expired connection and resets it.
        Hide
        biosetup Andrey Dmitriev added a comment -

        Catch if cached connection has expired and reset it.

        Show
        biosetup Andrey Dmitriev added a comment - Catch if cached connection has expired and reset it.
        Hide
        gwenshap Gwen Shapira added a comment -

        Andrey,

        Is it tested?
        In first glance, I can't see why a connection that got timed-out by the server will be null.
        I thought fixing the issue will require implementing discardConnection() for OracleManager, which will close and null all connections in the cache.

        If its tested and resolves the issue, then never mind

        Show
        gwenshap Gwen Shapira added a comment - Andrey, Is it tested? In first glance, I can't see why a connection that got timed-out by the server will be null. I thought fixing the issue will require implementing discardConnection() for OracleManager, which will close and null all connections in the cache. If its tested and resolves the issue, then never mind
        Hide
        biosetup Andrey Dmitriev added a comment -

        Hi Gwen,

        Yes it is tested. Maybe this is not the best solution, but it works and solves the problem and it has minimal impact to the existing code.
        I set the connection to null, to make sure that it will be passed to the next step where new connection will be established.
        This is my first time, so please let me know if I'm doing something wrong.

        Thank you,
        Andrey

        Show
        biosetup Andrey Dmitriev added a comment - Hi Gwen, Yes it is tested. Maybe this is not the best solution, but it works and solves the problem and it has minimal impact to the existing code. I set the connection to null, to make sure that it will be passed to the next step where new connection will be established. This is my first time, so please let me know if I'm doing something wrong. Thank you, Andrey
        Hide
        gwenshap Gwen Shapira added a comment -

        Thanks a million for the patch, Andrey.

        I support the patch, but since I'm not a committer, my +1 doesn't do much.
        Lets wait for one of the committers to review and comment.

        Show
        gwenshap Gwen Shapira added a comment - Thanks a million for the patch, Andrey. I support the patch, but since I'm not a committer, my +1 doesn't do much. Lets wait for one of the committers to review and comment.
        Hide
        jarcec Jarek Jarcec Cecho added a comment -

        On the contrary, your +1 do count Gwen Shapira! I don't have further comments, so I'll commit it momentarily.

        Show
        jarcec Jarek Jarcec Cecho added a comment - On the contrary, your +1 do count Gwen Shapira ! I don't have further comments, so I'll commit it momentarily.
        Hide
        jira-bot ASF subversion and git services added a comment -

        Commit d902d2449f60584de58a101fd128626944d6cd53 in sqoop's branch refs/heads/trunk from Jarek Jarcec Cecho
        [ https://git-wip-us.apache.org/repos/asf?p=sqoop.git;h=d902d24 ]

        SQOOP-1329: JDBC connection to Oracle timeout after data import but before hive metadata import

        (Andrey Dmitriev via Jarek Jarcec Cecho)

        Show
        jira-bot ASF subversion and git services added a comment - Commit d902d2449f60584de58a101fd128626944d6cd53 in sqoop's branch refs/heads/trunk from Jarek Jarcec Cecho [ https://git-wip-us.apache.org/repos/asf?p=sqoop.git;h=d902d24 ] SQOOP-1329 : JDBC connection to Oracle timeout after data import but before hive metadata import (Andrey Dmitriev via Jarek Jarcec Cecho)
        Hide
        jarcec Jarek Jarcec Cecho added a comment -

        Thank you for your contribution Andrey Dmitriev and Gwen Shapira for review!

        Show
        jarcec Jarek Jarcec Cecho added a comment - Thank you for your contribution Andrey Dmitriev and Gwen Shapira for review!
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop200 #891 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/891/)
        SQOOP-1329: JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53)

        • src/java/org/apache/sqoop/manager/OracleManager.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop200 #891 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/891/ ) SQOOP-1329 : JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53 ) src/java/org/apache/sqoop/manager/OracleManager.java
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop20 #885 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/885/)
        SQOOP-1329: JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53)

        • src/java/org/apache/sqoop/manager/OracleManager.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop20 #885 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/885/ ) SQOOP-1329 : JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53 ) src/java/org/apache/sqoop/manager/OracleManager.java
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop23 #1088 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/1088/)
        SQOOP-1329: JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53)

        • src/java/org/apache/sqoop/manager/OracleManager.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop23 #1088 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/1088/ ) SQOOP-1329 : JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53 ) src/java/org/apache/sqoop/manager/OracleManager.java
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop100 #850 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/850/)
        SQOOP-1329: JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53)

        • src/java/org/apache/sqoop/manager/OracleManager.java
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Sqoop-ant-jdk-1.6-hadoop100 #850 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/850/ ) SQOOP-1329 : JDBC connection to Oracle timeout after data import but before hive metadata import (jarcec: https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=d902d2449f60584de58a101fd128626944d6cd53 ) src/java/org/apache/sqoop/manager/OracleManager.java

          People

          • Assignee:
            biosetup Andrey Dmitriev
            Reporter:
            biosetup Andrey Dmitriev
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development