Hi Srinivas,
exception "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure" is very generic and basically means that there was something wrong with connecting to the MySQL. Could you share your entire execution log that was generated with --verbose flag?

Blindly shooting - it's suspicious that your issue appears only when certain amount of jobs is generated. MySQL is allowing limiting maximal number of concurrent connections. I would recommend checking defined connection limit on MySQL side (by SQL query show variables like "max_connections";) and compare it to number of created map tasks. In case that configured maximal value for connections is lower, then you need to either reconfigure mysql or sqoop (for example limit number of used mappers with --num-mappers argument or do not import all tables at once).

Jarcec

On Sat, May 26, 2012 at 09:40:47AM -0400, Srinivas wrote:
>
> > I have couple of problems that I am running into:
> >
> > 1. I was able to initiate only 5 sqoop jobs parallel which is = number of datanode's.
> > 2. If i try to invoke more jobs parallel then only 5 jobs are picked by and the rest of them throwing an error saying "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure"
> >
> > Do you have any idea why this is happening.
> >
> > Also, I have made sure that the mysql allows parallel connections from datanode's.
> >
> >
> >

Hi Srinivas,

In addition to Jarcec's excellent advice, perhaps you might find
http://archive.cloudera.com/cdh/3/sqoop/SqoopUserGuide.html#_mysql_connection_failurehelpful?

Regards,
Kathleen

On Sat, May 26, 2012 at 6:54 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]>wrote:

> Hi Srinivas,
> exception "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
> Communications link failure" is very generic and basically means that there
> was something wrong with connecting to the MySQL. Could you share your
> entire execution log that was generated with --verbose flag?
>
> Blindly shooting - it's suspicious that your issue appears only when
> certain amount of jobs is generated. MySQL is allowing limiting maximal
> number of concurrent connections. I would recommend checking defined
> connection limit on MySQL side (by SQL query show variables like
> "max_connections";) and compare it to number of created map tasks. In case
> that configured maximal value for connections is lower, then you need to
> either reconfigure mysql or sqoop (for example limit number of used mappers
> with --num-mappers argument or do not import all tables at once).
>
> Jarcec
>
> On Sat, May 26, 2012 at 09:40:47AM -0400, Srinivas wrote:
> >
> > > I have couple of problems that I am running into:
> > >
> > >     1. I was able to initiate only 5 sqoop jobs parallel which is > number of datanode's.
> > >     2. If i try to invoke more jobs parallel then only 5 jobs are
> picked by and the rest of them throwing an error saying
> "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications
> link failure"
> > >
> > >     Do you have any idea why this is happening.
> > >
> > > Also, I have made sure that the mysql allows parallel connections from
> datanode's.
> > >
> > >
> > >
>

Hello Jerec/Kathleen,

Thanks for your replies..

Below is  sqoop log with verbose enabled. I am able to run only 5
number of jobs which is equal to my datanodes count. Also maxnumber of
connections I can make to MySql database is 1500. I have  run more
than 5 jobs with only one mapper for each job and still I get the same
error. Please adivise..

below is the sqoop log.
12/05/26 13:07:29 DEBUG tool.BaseSqoopTool: Enabled debug logging.
12/05/26 13:07:29 WARN tool.BaseSqoopTool: Setting your password on
the command-line is insecure. Consider using -P instead.
12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
12/05/26 13:07:29 DEBUG manager.DefaultManagerFactory: Trying with
scheme: jdbc:mysql:
12/05/26 13:07:29 INFO manager.MySQLManager: Preparing to use a MySQL
streaming resultset.
12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.cloudera.sqoop.manager.DirectMySQLManager@1cb8deef
12/05/26 13:07:29 INFO tool.CodeGenTool: Beginning code generation
12/05/26 13:07:29 DEBUG manager.SqlManager: No connection paramenters
specified. Using regular API for making connection.
12/05/26 13:07:36 DEBUG manager.SqlManager: Using fetchSize for next
query: -2147483648
12/05/26 13:07:36 INFO manager.SqlManager: Executing SQL statement:
SELECT t.* FROM `db` AS t LIMIT 1
12/05/26 13:07:36 ERROR manager.SqlManager: Error executing statement:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

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

The last packet successfully received from the server was 4,270
milliseconds ago.  The last packet sent successfully to the server was
12 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:3591)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3480)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4021)
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:2671)
at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1644)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2232)
at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:487)
at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:496)
at com.cloudera.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:195)
at com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:179)
at com.cloudera.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:116)
at com.cloudera.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1226)
at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1051)
at com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84)
at com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:370)
at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:456)
at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)
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:3041)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3491)
... 23 more
12/05/26 13:07:36 ERROR manager.CatalogQueryManager: Failed to
rollback transaction
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
No operations allowed after connection closed.
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.throwConnectionClosedException(ConnectionImpl.java:1245)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1237)
at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4717)
at com.cloudera.sqoop.manager.CatalogQueryManager.getColumnNames(CatalogQueryManager.java:157)
at com.cloudera.sqoop.orm.ClassWriter.getColumnNames(ClassWriter.java:1198)
at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1053)
at com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84)
at com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:370)
at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:4

Hi,

- check your mysql max connections
- verify if mysqld is started without the --skip-networking option

cheers
- Alex

--
Alexander Alten-Lorenz
http://mapredit.blogspot.com
German Hadoop LinkedIn Group: http://goo.gl/N8pCF

On May 29, 2012, at 1:22 AM, Srinivas Surasani wrote:

> Hello Jerec/Kathleen,
>
> Thanks for your replies..
>
> Below is  sqoop log with verbose enabled. I am able to run only 5
> number of jobs which is equal to my datanodes count. Also maxnumber of
> connections I can make to MySql database is 1500. I have  run more
> than 5 jobs with only one mapper for each job and still I get the same
> error. Please adivise..
>
> below is the sqoop log.
>
>
> 12/05/26 13:07:29 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/26 13:07:29 WARN tool.BaseSqoopTool: Setting your password on
> the command-line is insecure. Consider using -P instead.
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/26 13:07:29 DEBUG manager.DefaultManagerFactory: Trying with
> scheme: jdbc:mysql:
> 12/05/26 13:07:29 INFO manager.MySQLManager: Preparing to use a MySQL
> streaming resultset.
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.DirectMySQLManager@1cb8deef
> 12/05/26 13:07:29 INFO tool.CodeGenTool: Beginning code generation
> 12/05/26 13:07:29 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/05/26 13:07:36 DEBUG manager.SqlManager: Using fetchSize for next
> query: -2147483648
> 12/05/26 13:07:36 INFO manager.SqlManager: Executing SQL statement:
> SELECT t.* FROM `db` AS t LIMIT 1
> 12/05/26 13:07:36 ERROR manager.SqlManager: Error executing statement:
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
> Communications link failure
>
> The last packet successfully received from the server was 4,270
> milliseconds ago.  The last packet sent successfully to the server was
> 12 milliseconds ago.
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
> Communications link failure
>
> The last packet successfully received from the server was 4,270
> milliseconds ago.  The last packet sent successfully to the server was
> 12 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:3591)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3480)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4021)
> 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:2671)
> at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1644)
> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2232)
> at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:487)
> at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:496)
> at com.cloudera.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:195)
> at com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:179)
> at com.cloudera.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:116)
> at com.cloudera.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1226)
> at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1051)

Hi Srinivas,
log that you sent us is suggesting that sqoop is even not able to boostrap your import job. It fails before creating any mapreduce job.

You said that you're not able to create more then 5 jobs. Do you mean 5 concurrent jobs? So that sixth concurrent one will end up on that error? Or are you trying them sequentially?

Just after this error, are you able to connect to your MySQL server from the same box? (mysql -h $host -u $user -P $database)

Jarcec

On Mon, May 28, 2012 at 07:22:36PM -0400, Srinivas Surasani wrote:
> Hello Jerec/Kathleen,
>
> Thanks for your replies..
>
> Below is  sqoop log with verbose enabled. I am able to run only 5
> number of jobs which is equal to my datanodes count. Also maxnumber of
> connections I can make to MySql database is 1500. I have  run more
> than 5 jobs with only one mapper for each job and still I get the same
> error. Please adivise..
>
> below is the sqoop log.
>
>
> 12/05/26 13:07:29 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/26 13:07:29 WARN tool.BaseSqoopTool: Setting your password on
> the command-line is insecure. Consider using -P instead.
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/26 13:07:29 DEBUG manager.DefaultManagerFactory: Trying with
> scheme: jdbc:mysql:
> 12/05/26 13:07:29 INFO manager.MySQLManager: Preparing to use a MySQL
> streaming resultset.
> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.DirectMySQLManager@1cb8deef
> 12/05/26 13:07:29 INFO tool.CodeGenTool: Beginning code generation
> 12/05/26 13:07:29 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/05/26 13:07:36 DEBUG manager.SqlManager: Using fetchSize for next
> query: -2147483648
> 12/05/26 13:07:36 INFO manager.SqlManager: Executing SQL statement:
> SELECT t.* FROM `db` AS t LIMIT 1
> 12/05/26 13:07:36 ERROR manager.SqlManager: Error executing statement:
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
> Communications link failure
>
> The last packet successfully received from the server was 4,270
> milliseconds ago.  The last packet sent successfully to the server was
> 12 milliseconds ago.
> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
> Communications link failure
>
> The last packet successfully received from the server was 4,270
> milliseconds ago.  The last packet sent successfully to the server was
> 12 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:3591)
> at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3480)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4021)
> 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:2671)
> at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1644)
> at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2232)
> at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:487)
> at com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:496)
> at com.cloudera.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:195)
> at com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:179)

All,

Thanks for your inputs..Issue is resolved.. Reason: multiple
connections cannot be opened from same datanode.( this was set on
MySql ).

Thanks,
Srinivas Surasani

On Tue, May 29, 2012 at 2:20 AM, Jarek Jarcec Cecho <[EMAIL PROTECTED]> wrote:
> Hi Srinivas,
> log that you sent us is suggesting that sqoop is even not able to boostrap your import job. It fails before creating any mapreduce job.
>
> You said that you're not able to create more then 5 jobs. Do you mean 5 concurrent jobs? So that sixth concurrent one will end up on that error? Or are you trying them sequentially?
>
> Just after this error, are you able to connect to your MySQL server from the same box? (mysql -h $host -u $user -P $database)
>
> Jarcec
>
> On Mon, May 28, 2012 at 07:22:36PM -0400, Srinivas Surasani wrote:
>> Hello Jerec/Kathleen,
>>
>> Thanks for your replies..
>>
>> Below is  sqoop log with verbose enabled. I am able to run only 5
>> number of jobs which is equal to my datanodes count. Also maxnumber of
>> connections I can make to MySql database is 1500. I have  run more
>> than 5 jobs with only one mapper for each job and still I get the same
>> error. Please adivise..
>>
>> below is the sqoop log.
>>
>>
>> 12/05/26 13:07:29 DEBUG tool.BaseSqoopTool: Enabled debug logging.
>> 12/05/26 13:07:29 WARN tool.BaseSqoopTool: Setting your password on
>> the command-line is insecure. Consider using -P instead.
>> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Loaded manager factory:
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
>> com.cloudera.sqoop.manager.DefaultManagerFactory
>> 12/05/26 13:07:29 DEBUG manager.DefaultManagerFactory: Trying with
>> scheme: jdbc:mysql:
>> 12/05/26 13:07:29 INFO manager.MySQLManager: Preparing to use a MySQL
>> streaming resultset.
>> 12/05/26 13:07:29 DEBUG sqoop.ConnFactory: Instantiated ConnManager
>> com.cloudera.sqoop.manager.DirectMySQLManager@1cb8deef
>> 12/05/26 13:07:29 INFO tool.CodeGenTool: Beginning code generation
>> 12/05/26 13:07:29 DEBUG manager.SqlManager: No connection paramenters
>> specified. Using regular API for making connection.
>> 12/05/26 13:07:36 DEBUG manager.SqlManager: Using fetchSize for next
>> query: -2147483648
>> 12/05/26 13:07:36 INFO manager.SqlManager: Executing SQL statement:
>> SELECT t.* FROM `db` AS t LIMIT 1
>> 12/05/26 13:07:36 ERROR manager.SqlManager: Error executing statement:
>> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
>> Communications link failure
>>
>> The last packet successfully received from the server was 4,270
>> milliseconds ago.  The last packet sent successfully to the server was
>> 12 milliseconds ago.
>> com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
>> Communications link failure
>>
>> The last packet successfully received from the server was 4,270
>> milliseconds ago.  The last packet sent successfully to the server was
>> 12 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:3591)
>>       at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3480)
>>       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4021)
>>       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:2671)
>>       at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1644)

[EMAIL PROTECTED]