Sqoop job fails with YARN container error 143

[#1]

I have installed HDP 2.4 on a three node cluster. I have got Sqoop 1.4.6.2.4.3.0-227.

I am trying to execute a sqoop command to import data from mysql (Ver 14.14) to HDFS.

When I run a import command the MR job ends up in 2 errors.

 sqoop import --connect jdbc:mysql://hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net/employees --username anup --password-file /user/anup/.password --table employees --target-dir /data/landing/employees --delete-target-dir

1) It throws exception : com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
        at org.apache.sqoop.mapreduce.db.DBInputFormat.setConf(DBInputFormat.java:167)
        at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76)
        at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:749)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:168)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:162)
Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

2) It gives below error :

Container killed by the ApplicationMaster.
Container killed on request. Exit code is 143
Container exited with a non-zero exit code 143

The job ends as error and has message :

Job failed as tasks failed. failedMaps:1 failedReduces:0


17/08/21 05:59:15 INFO mapreduce.Job: Counters: 31
        File System Counters
                FILE: Number of bytes read=0
                FILE: Number of bytes written=469884
                FILE: Number of read operations=0
                FILE: Number of large read operations=0
                FILE: Number of write operations=0
                HDFS: Number of bytes read=347
                HDFS: Number of bytes written=9184962
                HDFS: Number of read operations=12
                HDFS: Number of large read operations=0
                HDFS: Number of write operations=6
        Job Counters
                Failed map tasks=6
                Launched map tasks=9
                Other local map tasks=9
                Total time spent by all maps in occupied slots (ms)=38059
                Total time spent by all reduces in occupied slots (ms)=0
                Total time spent by all map tasks (ms)=38059
                Total vcore-seconds taken by all map tasks=38059
                Total megabyte-seconds taken by all map tasks=58458624
        Map-Reduce Framework
                Map input records=200024
                Map output records=200024
                Input split bytes=347
                Spilled Records=0
                Failed Shuffles=0
                Merged Map outputs=0
                GC time elapsed (ms)=263
                CPU time spent (ms)=12740
                Physical memory (bytes) snapshot=807444480
                Virtual memory (bytes) snapshot=9736806400
                Total committed heap usage (bytes)=524812288
        File Input Format Counters
                Bytes Read=0
        File Output Format Counters
                Bytes Written=9184962
17/08/21 05:59:15 INFO mapreduce.ImportJobBase: Transferred 8.7595 MB in 32.0575 seconds (279.7996 KB/sec)
17/08/21 05:59:15 INFO mapreduce.ImportJobBase: Retrieved 200024 records.
17/08/21 05:59:15 ERROR tool.ImportTool: Error during import: Import job failed!

I am trying this for 2 mysql tables. For first table the data gets transferred despite of above errors . But for other no data is transferred.

I am also using Hive on same HDP and the MR jobs for Hive are running fine and doesn't give any error.

Please suggest a solution.

[#2]

@Geoffrey Shelton OkotCan you please help me with this

[#3]

@Anup Shirolkar

The main cause of this issue sems to be MySQL server is not running fine or having some N/W issue.

Causedby: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communications link failure

.

You will need to first check if the MySQL connectivity is fine.

- Try running a simple Java JDBC code to see if you are able to connect to MySQL server properly or not? You can use the code mentioned in the following link: https://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysq...

.

As we are reeading MySQL data first and then further processing will happen. So we can skip other Job Failures right now. First we will need to fix the DB issue. Once we are able to successfully communicate with MySQL server then further exceptions/errors can be investigated.

Also please check if the Following Connection URL (Do you need to specify the Port like 3306) , Username & Password is correct in the .password file and the user has right privileges to access the table and query?

--connect jdbc:mysql://hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net/employees
--username anup
--password-file /user/anup/.password

.

[#4]

@Jay SenSharma

Thanks for looking into this. I have tested the the Database connectivity and it is working fine.One thing I had to change was to use 'localhost' instead of the hostname 'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net'. There is no problem with password in /user/anup/.password
When I run the code at https://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysq...

it gives result:

[anup@hdp25-node2 ~]$ java -jar meta_artifact-1.0-SNAPSHOT-jar-with-dependencies.jar anup passwd jdbc:mysql://localhost/employees

Database connection established
Database Connection Terminated

With this result I tried the sqoop command with connection url 'jdbc:mysql://localhost/employees' but it gives same error.

For another trial I executed the sqoop command with 'root' user which has full privileges and doesn't require a password.

  sqoop import --connect jdbc:mysql://localhost/employees --username root --table salaries --target-dir /data/landing/salaries --delete-target-dir

The same exception are occurred with this.

Although we get 'connectException' here the data is transferred for one of the MySQL table; that means MySQL is infact connected.

        Map-Reduce Framework
                Map input records=1896925
                Map output records=1896925
                Input split bytes=232
                Spilled Records=0
                Failed Shuffles=0
                Merged Map outputs=0
                GC time elapsed (ms)=569
                CPU time spent (ms)=23890
                Physical memory (bytes) snapshot=560414720
                Virtual memory (bytes) snapshot=6498037760
                Total committed heap usage (bytes)=364380160
        File Input Format Counters
                Bytes Read=0
        File Output Format Counters
                Bytes Written=65600471

But the exception occurs every time. Change of user/url/table has no effect in the exception behaviour.

Please suggest.

[#5]

@Anup Shirolkar

can you please check the below:

1) You have he Sqoop client installed on all the Nodemanagers

2) Make sure you the MySql server connectivity is working from all the NodeManagers

Thanks

Venkat

[#6]

@Venkata Sudheer Kumar M

Yes there seems to be some issue with my connectivity.

When I run the same program with url having hostname or ip address it gives below error

[anup@hdp25-node2 ~]$ java -jar meta_artifact-1.0-SNAPSHOT-jar-with-dependencies.jar anup passwd jdbc:mysql://10.0.0.5:3306/employees
Cannot connect to database server
Access denied for user 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' (using password: YES)
java.sql.SQLException: Access denied for user 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' (using password: YES)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:943)
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4113)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1308)
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2336)
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2369)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2153)
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at Connect.main(Connect.java:15)<br>

I noticed one more thing the hostname is 'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net' but the error doesnt contain last word of it 'net'.

The MySQL I am using here is installed as Hive Metastore. So I haven't done any explicit setting for it.

Another thing to note here is my HDP installtion is based on Microsoft azure.

Please find contents of my /etc/hosts file, ifconfig and hostname :

[root@hdp25-node2 etc]# cat /etc/hosts
127.0.0.1   localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.0.0.4 hdp25-node1.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net
10.0.0.5 hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net
10.0.0.6 hdp25-node3.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net

[root@hdp25-node2 etc]# hostname -f
hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net

[root@hdp25-node2 etc]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.0.5  netmask 255.255.255.0  broadcast 10.0.0.255
        inet6 fe80::20d:3aff:fe13:eebe  prefixlen 64  scopeid 0x20<link>
        ether 00:0d:3a:13:ee:be  txqueuelen 1000  (Ethernet)
        RX packets 9127206  bytes 4061110056 (3.7 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 8154850  bytes 6846285406 (6.3 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0


lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1  (Local Loopback)
        RX packets 4100975  bytes 6067880532 (5.6 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 4100975  bytes 6067880532 (5.6 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0<br>

Please let know if you get any clue.

[#7]

@Anup Shirolkar

Looks like the user "anup" does not have grant while connecting with hostname.

mysql> grant all on *.* to 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp' identified by 'YOUR_PASSWORD_HERE';

mysql> flush privileges;

.

So please try running the following query and then try again.

.

Also please double check this:

mysql> SELECT * from mysql.user where User = 'anup';

.

[#8]

@Anup Shirolkar

Also looks like your hostnmae is following:

hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net

.

But in the error ".net" is missing, is that a typo error? Please check the hostname again if it should have ".net" or not?

Access denied for user 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' (using password: YES)
java.sql.SQLException: Access denied for user 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' (using password: YES)

.

[#9]

I think I got the cause behind this. When I tried to run grant command:

mysql> grant all on *.* to 'anup'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net';
ERROR 1470 (HY000): String 'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.net' is too long for host name (should be no longer than 60)

So that's the reason it is printing 'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' because the last part 'net' doesn't fits in 60 characters.

Also have a look at permissions table:

mysql> select * from information_schema.user_privileges;
+-----------------------------------------------------------------------+---------------+-------------------------+--------------+
| GRANTEE                                                               | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------------------------------------------------------------+---------------+-------------------------+--------------+
| 'root'@'localhost'                                                    | def           | SELECT                  | YES          |
| 'root'@'localhost'                                                    | def           | INSERT                  | YES          |
| 'root'@'localhost'                                                    | def           | UPDATE                  | YES          |
| 'root'@'localhost'                                                    | def           | DELETE                  | YES          |
| 'root'@'localhost'                                                    | def           | CREATE                  | YES          |
| 'root'@'localhost'                                                    | def           | DROP                    | YES          |
| 'root'@'localhost'                                                    | def           | RELOAD                  | YES          |
| 'root'@'localhost'                                                    | def           | SHUTDOWN                | YES          |
| 'root'@'localhost'                                                    | def           | PROCESS                 | YES          |
| 'root'@'localhost'                                                    | def           | FILE                    | YES          |
| 'root'@'localhost'                                                    | def           | REFERENCES              | YES          |
| 'root'@'localhost'                                                    | def           | INDEX                   | YES          |
| 'root'@'localhost'                                                    | def           | ALTER                   | YES          |
| 'root'@'localhost'                                                    | def           | SHOW DATABASES          | YES          |
| 'root'@'localhost'                                                    | def           | SUPER                   | YES          |
| 'root'@'localhost'                                                    | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'localhost'                                                    | def           | LOCK TABLES             | YES          |
| 'root'@'localhost'                                                    | def           | EXECUTE                 | YES          |
| 'root'@'localhost'                                                    | def           | REPLICATION SLAVE       | YES          |
| 'root'@'localhost'                                                    | def           | REPLICATION CLIENT      | YES          |
| 'root'@'localhost'                                                    | def           | CREATE VIEW             | YES          |
| 'root'@'localhost'                                                    | def           | SHOW VIEW               | YES          |
| 'root'@'localhost'                                                    | def           | CREATE ROUTINE          | YES          |
| 'root'@'localhost'                                                    | def           | ALTER ROUTINE           | YES          |
| 'root'@'localhost'                                                    | def           | CREATE USER             | YES          |
| 'root'@'localhost'                                                    | def           | EVENT                   | YES          |
| 'root'@'localhost'                                                    | def           | TRIGGER                 | YES          |
| 'root'@'localhost'                                                    | def           | CREATE TABLESPACE       | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | SELECT                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | INSERT                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | UPDATE                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | DELETE                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | DROP                    | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | RELOAD                  | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | SHUTDOWN                | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | PROCESS                 | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | FILE                    | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | REFERENCES              | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | INDEX                   | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | ALTER                   | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | SHOW DATABASES          | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | SUPER                   | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | LOCK TABLES             | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | EXECUTE                 | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | REPLICATION SLAVE       | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | REPLICATION CLIENT      | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE VIEW             | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | SHOW VIEW               | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE ROUTINE          | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | ALTER ROUTINE           | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE USER             | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | EVENT                   | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | TRIGGER                 | YES          |
| 'root'@'hdp25-node2.wulme4ci31tu3lwdofvykqwgkh.bx.internal.cloudapp.' | def           | CREATE TABLESPACE       | YES          |

Here also we can see the incomplete hostname.

But I am afraid if I change my hostname to fit in 60 characters that will affect various services on my cluster.

Please suggest a way to get around this. Or if anybody has experience changing hostname on oprational cluster please let know best practice to do it.

[#10 · Accepted solution]

@Anup Shirolkar

Please try using "%" wildcard for the granting.

Example:

grant all on *.* to 'anup'@'%.cloudapp.net';
OR
grant all on *.* to 'anup'@'%';

.

Please see the doc for more details: https://dev.mysql.com/doc/refman/5.7/en/grant.html

The_and%wildcards are permitted when specifying database names inGRANTstatements that grant privileges at the database level.