question Re: Sqoop import in Archives of Support Questions (Read Only)

Sqoop import

arunpoy — Tue, 21 Jun 2016 13:39:27 GMT

I am having a small cluster with 2 master nodes and 3 slave nodes (data nodes/ node managers). Suppose i want to import the data from a remote mysql db to HDFS/HBASE, do i need to provide permissions for the ip addresses of the datanodes at the database server . Some sort of grant privileges for the ip addresses of the datanode machines on the database server machine.

Since sqoop import is the submission of map jobs to the DB. I see these errors in the individual datanode logs

java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago.

IS there any standard procedure for sqoop jobs to be adopted across the cluster.

Re: Sqoop import

rajkumar_singh — Tue, 21 Jun 2016 13:42:37 GMT

java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago

it seems you have connectivity issue from the node to the mysql server. can you check whether you are able to run simple jdbc program to connect to your mysql server.

Re: Sqoop import

ssubhas — Tue, 21 Jun 2016 13:43:52 GMT

@ARUNKUMAR RAMASAMY

The communication between the datanodes and mysql needs to be open. Make sure telnet <mysql_server> <port> works on all the nodes in the cluster. Also, need to verify the bind address at mysql end to verify the connectivity. You can refer to below link for more debugging at mysql end:

http://stackoverflow.com/questions/2121829/com-mysql-jdbc-exceptions-jdbc4-communicationsexceptioncommunications-link-fail

Hope this helps.

Thanks and Regards,

Sindhu

Re: Sqoop import

arunpoy — Tue, 21 Jun 2016 14:11:05 GMT

@Sindhu, I made sure that the ports are open

I am able to run the simple sqoop list command(shown below) from each of the nodes and it works fine, but the import is not working.

sqoop list-tables --connect jdbc:mysql://hostname/sqoop_demo --username root -P

Re: Sqoop import

ssubhas — Tue, 21 Jun 2016 14:16:26 GMT

@ARUNKUMAR RAMASAMY

MySQL might be rejecting connections to the extract the data from the tables from remote host. We need to grant privileges for the IP's of the data nodes at the database end as below:

GRANTALL PRIVILEGES ON*.*TO'user'@'ipadress'

Thanks and Regards,

Sindhu

Re: Sqoop import

arunpoy — Tue, 21 Jun 2016 14:35:24 GMT

Thanks @Sindhu, i gave the permissions, but i didnt flush the privileges;

Re: Sqoop import

rchaman — Sat, 18 Feb 2017 04:04:18 GMT

Check if you can listen to the mysql port.

[root@node1 ~]# lsof -i :3306 #replace the port no as applicable

If there is no output that means mysql is running in "skip_networking" mode.

Disable the mode by commenting out below line in /etc/my.cnf and restart the db

# Don't listen on a TCP/IP port at all.

#skip-networking

Now check the port again . It should show similar output

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysqld 18730 mysql 10u IPv4 120579417 0t0 TCP *:mysql (LISTEN)

mysqld 18730 mysql 29u IPv4 120579424 0t0 TCP localhost:mysql->localhost:41107 (ESTABLISHED)

Now try to run sqoop job.