Uploaded image for project: 'Sqoop (Retired)'
  1. Sqoop (Retired)
  2. SQOOP-509

MySql wait_timeout too low can cause failures when importing into hive

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 1.4.1-incubating
    • None
    • hive-integration
    • None

    • Hive version 0.8.1.1, hadoop 0.20.205

    Description

      The mysql setting wait_timeout causes connections to close when they are idle for too long. Since sqoop re-uses the same connection on the client if the sqoop import takes longer than the wait_timeout time the job can fail.

      I believe that this is only an issue with the hive import command. The hive import job runs after the map reduce job finishes and queries mysql for schema information again. At this point the connection is not longer open and the job fails with the following output.

      12/06/25 13:37:29 ERROR manager.SqlManager: Error executing statement: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,892 milliseconds ago. The last packet sent successfully to the server was 107 milliseconds ago.
      com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,892 milliseconds ago. The last packet sent successfully to the server was 107 milliseconds ago.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677)
      at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1643)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2242)
      at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:487)
      at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:496)
      at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:194)
      at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:178)
      at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:126)
      at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:191)
      at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:392)
      at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:453)
      at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
      at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
      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)
      Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489)
      ... 22 more
      12/06/25 13:37:29 ERROR manager.CatalogQueryManager: Failed to rollback transaction
      com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.Util.getInstance(Util.java:386)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
      at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4763)
      at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:156)
      at org.apache.sqoop.hive.TableDefWriter.getColumnNames(TableDefWriter.java:107)
      at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:132)
      at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:191)
      at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:392)
      at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:453)
      at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
      at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
      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)
      12/06/25 13:37:29 ERROR manager.CatalogQueryManager: Failed to list columns
      com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,898 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2627)
      at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1556)
      at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:148)
      at org.apache.sqoop.hive.TableDefWriter.getColumnNames(TableDefWriter.java:107)
      at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:132)
      at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:191)
      at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:392)
      at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:453)
      at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
      at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
      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)
      Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489)
      ... 20 more
      12/06/25 13:37:29 WARN tool.BaseSqoopTool: Error while closing connection: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
      12/06/25 13:37:29 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,898 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
      java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,898 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
      at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:162)
      at org.apache.sqoop.hive.TableDefWriter.getColumnNames(TableDefWriter.java:107)
      at org.apache.sqoop.hive.TableDefWriter.getCreateTableStmt(TableDefWriter.java:132)
      at org.apache.sqoop.hive.HiveImport.importTable(HiveImport.java:191)
      at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:392)
      at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:453)
      at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
      at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
      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)
      Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 749,898 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3589)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3478)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4019)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2677)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2627)
      at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1556)
      at org.apache.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:148)
      ... 12 more
      Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
      at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3039)
      at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3489)
      ... 20 more

      The workaround that I am using is to set interactiveClient=true in the JDBC connection string. Which uses an alternative timeout period.

      Attachments

        Activity

          People

            Unassigned Unassigned
            hansmire Max Hansmire
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: