"COPY FROM VERTICA" or "EXPORT" produces "receipt failed" or "socket error"
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
Problem Description:
------------------------------
Using the copy from/export feature, the CONNECT command works fine but when I execute the copy from or export I get one of the two following errors:
ERROR: Receive: Message receipt failed: v_targetdbname_node0001
ROLLBACK: One or more nodes did not open a data connection to this node. This may indicate a network configuration problem. Check that the private interfaces used for communication among the cluster hosts reside in the same subnet and are returned first by host address lookup.
ERROR: [sourcedbname] Statement is canceled
ERROR: [sourcedbname] NetworkSend: failed to open connection to node v_targetdbname_node0001 (socket error: Resource temporarily unavailable)
Solution:-
Using the copy from/export feature, the CONNECT command works fine but when I execute the copy from or export I get one of the two following errors:
ERROR: Receive: Message receipt failed: v_targetdbname_node0001
ROLLBACK: One or more nodes did not open a data connection to this node. This may indicate a network configuration problem. Check that the private interfaces used for communication among the cluster hosts reside in the same subnet and are returned first by host address lookup.
ERROR: [sourcedbname] Statement is canceled
ERROR: [sourcedbname] NetworkSend: failed to open connection to node v_targetdbname_node0001 (socket error: Resource temporarily unavailable)
Solution:-
---------------
The copy from and export commands need to be able to establish connections on high ports (e.g. 40000 to 50000 range) using the ip address reported by the OS when doing ping of the hostname. There are a few tools that can help diagnose these problems.
at OS level:
- /bin/hostname -f returns the hostname of the machine
- ping hostname of the returned above and record the IP address returned
- ifconfig and verify the IP from ping is assigned to the ethernet (eth0, eth1, etc.) being used
- cat /etc/hosts and confirm the public and private ip/name pairs are correct
at Vertica level:
- in vsql do "select * from nodes;" and confirm the node_address column IP matches that from the OS tests. It should not be loopback 127.0.0.1
- in admintools, Configuration Menu, View Database, select your database. The Hosts line item should match the IP from the OS tests, not 127.0.0.1
If either of the vertica tests report 127.0.0.1 it means that when the single node cluster was set up the install_vertica was run without passing the -s hostname, or passed -s localhost, so it defaulted to localhost and the 127.0.0.1 loopback IP. As noted in the docs this restricts you from being to add any more nodes. It also causes the copy from/export to fail.
To resolve:
shut down the database
change all references in the /opt/vertica/config/admintools.conf file of 127.0.0.1 to the 10.xx.xxx.xx IP reported by ping
restart the database
Other items to check would be if there are any firewalls blocking use of the higher ports. You can test this using the OS netcat command.
# using net cat (nc) to test port comms between two nodes
# on listening node -> nc -l port#
nc -l 40000
# and it will go into listen mode and wait for remote input across the port
# on sending node -> nc listeningnode port#
nc myothernode 40000
# and it will go into send more waiting for you to input keystrokes. Note you can use the ip instead of the machine name for the listeningnode.
Entering text and hitting return should push the text across the port and display on the other machine.
CTRL C gets you out of them
A doc bug was entered to enhance the Installation and SQL Reference guides to note the need for high ports and limitation can't use loopback/localhost.
The copy from and export commands need to be able to establish connections on high ports (e.g. 40000 to 50000 range) using the ip address reported by the OS when doing ping of the hostname. There are a few tools that can help diagnose these problems.
at OS level:
- /bin/hostname -f returns the hostname of the machine
- ping hostname of the returned above and record the IP address returned
- ifconfig and verify the IP from ping is assigned to the ethernet (eth0, eth1, etc.) being used
- cat /etc/hosts and confirm the public and private ip/name pairs are correct
at Vertica level:
- in vsql do "select * from nodes;" and confirm the node_address column IP matches that from the OS tests. It should not be loopback 127.0.0.1
- in admintools, Configuration Menu, View Database, select your database. The Hosts line item should match the IP from the OS tests, not 127.0.0.1
If either of the vertica tests report 127.0.0.1 it means that when the single node cluster was set up the install_vertica was run without passing the -s hostname, or passed -s localhost, so it defaulted to localhost and the 127.0.0.1 loopback IP. As noted in the docs this restricts you from being to add any more nodes. It also causes the copy from/export to fail.
To resolve:
shut down the database
change all references in the /opt/vertica/config/admintools.conf file of 127.0.0.1 to the 10.xx.xxx.xx IP reported by ping
restart the database
Other items to check would be if there are any firewalls blocking use of the higher ports. You can test this using the OS netcat command.
# using net cat (nc) to test port comms between two nodes
# on listening node -> nc -l port#
nc -l 40000
# and it will go into listen mode and wait for remote input across the port
# on sending node -> nc listeningnode port#
nc myothernode 40000
# and it will go into send more waiting for you to input keystrokes. Note you can use the ip instead of the machine name for the listeningnode.
Entering text and hitting return should push the text across the port and display on the other machine.
CTRL C gets you out of them
A doc bug was entered to enhance the Installation and SQL Reference guides to note the need for high ports and limitation can't use loopback/localhost.
0