Pivotal Knowledge Base

Follow

Sqoop import fails to import oracle table with "Could not commit with auto-commit set on"

Environment

  • PHD 2.1.0.0

Symptom

You might see the following error when attempting to import a oracle table using sqoop

15/02/11 09:44:02 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
15/02/11 09:44:02 INFO manager.SqlManager: Using default fetchSize of 1000
15/02/11 09:44:02 INFO tool.CodeGenTool: Beginning code generation
15/02/11 09:44:05 INFO manager.OracleManager: Time zone has been set to GMT
15/02/11 09:44:05 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM H_CUSTOMER t WHERE 1=0
15/02/11 09:44:05 WARN manager.SqlManager: SQLException closing ResultSet: java.sql.SQLException: Could not commit with auto-commit set on
15/02/11 09:44:05 ERROR manager.OracleManager: Failed to rollback transaction
java.sql.SQLException: Could not rollback with auto-commit set on
        at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:2423)
        at org.apache.sqoop.manager.OracleManager.getColumnNames(OracleManager.java:767)
        at org.apache.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1207)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1062)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:57)
15/02/11 09:44:05 ERROR manager.OracleManager: Failed to list columns
java.sql.SQLException: Could not commit with auto-commit set on
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2356)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2403)
        at org.apache.sqoop.manager.OracleManager.getColumnNames(OracleManager.java:764)
        at org.apache.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1207)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1062)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:57)

Cause

There is a known issue SQOOP-163 where using the "--table" directive will result in the following error.

In this case user used the following sqoop command

import
-D oraoop.disabled=true 
--connect 
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XLDRED03-VIP)(PORT=1692))(ADDRESS=(PROTOCOL=TCP)(HOST=XLDRED04-VIP)(PORT=1692))(LOAD_BALANCE=YES)(FAILOVER=ON)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SHBED.SWACORP.COM)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
--username 
  USERABC
--password 
  password 
--table 
  CUSTOMER_INFO
--escaped-by
  '\\' 
--fields-terminated-by
  ',' 
--lines-terminated-by
  '\n'
--target-dir 
  /customer/data/sqoop/oracle/demo/CUSTOMER_INFO
--split-by
  CUSTOMER_KEY
--direct

Workaround

Use "--query select * from CUSTOMER_INFO" instead of "--table CUSTOMER_INFO"

import
-D oraoop.disabled=true 
--connect 
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XLDRED03-VIP)(PORT=1692))(ADDRESS=(PROTOCOL=TCP)(HOST=XLDRED04-VIP)(PORT=1692))(LOAD_BALANCE=YES)(FAILOVER=ON)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SHBED.SWACORP.COM)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))
--username 
  USERABC
--password 
  password 
--query 
  select * from CUSTOMER_INFO
--escaped-by
  '\\' 
--fields-terminated-by
  ',' 
--lines-terminated-by
  '\n'
--target-dir 
  /customer/data/sqoop/oracle/demo/CUSTOMER_INFO
--split-by
  CUSTOMER_KEY
--direct

Comments

Powered by Zendesk