You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sqoop.apache.org by "Max Hansmire (JIRA)" <ji...@apache.org> on 2012/06/25 17:34:43 UTC
[jira] [Updated] (SQOOP-509) MySql wait_timeout too low can cause
failures when importing into hive
[ https://issues.apache.org/jira/browse/SQOOP-509?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Max Hansmire updated SQOOP-509:
-------------------------------
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.
{quote}
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
{quote}
The workaround that I am using is to set interactiveClient=true in the JDBC connection string. Which uses an alternative timeout period.
was:
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.
> MySql wait_timeout too low can cause failures when importing into hive
> ----------------------------------------------------------------------
>
> Key: SQOOP-509
> URL: https://issues.apache.org/jira/browse/SQOOP-509
> Project: Sqoop
> Issue Type: Bug
> Components: hive-integration
> Affects Versions: 1.4.1-incubating
> Environment: Hive version 0.8.1.1, hadoop 0.20.205
> Reporter: Max Hansmire
> Priority: Minor
>
> 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.
> {quote}
> 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
> {quote}
> The workaround that I am using is to set interactiveClient=true in the JDBC connection string. Which uses an alternative timeout period.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira