Export to vertica failing

[gdba]

Hi,

I'm using the vsql client to export data from a production database to a development database. However, the export is failing. Below, is output from my vsql session:

CONNECT TO VERTICA dev_server USER joe PASSWORD '*******' ON 'dev.vertica.node01', 5433;
CONNECT
EXPORT TO VERTICA dev_server.foo AS SELECT * FROM foo WHERE ydate > '2012-12-05';
ERROR 4572: [dev_server] RemoteSend: Open failed on node
[v_prod_reporting_node0007] (socket error: Connection timed out)

I checked privileges. User joe has the following privileges:
- SELECT privileges on the source table
- USAGE privilege on source table schema
- INSERT privileges for the destination table in target database
- USAGE privilege on destination table schema

Additionally, network connectivity is good between the prod and dev servers. The databases are on separate physical servers. The CONNECT attempt is successful.

Dev version is v6.0.0-1. Prod is v6.0.1-0.

What could be causing error 4572?

Thanks.

[JimKnicely]

Hi!

Welcome to the forums!

Is dev_server the name of the database or the schema on the dev.vertica.node01 server?

After you connect, can you run the SELECT statement by itself successfully?

Also, according to the Administrator's Guide doc:

Exporting data fails if either side of the connection is a single-node cluster installed to localhost or you do not specify a host name or IP address.

[gdba]

Hi Jim,

dev_server is the name of the database.

After connecting, the SELECT statement runs successfully by itself.

Both sides of the connection are multi-node clusters.

Any other ideas?

Thanks.

[JimKnicely]

Hi,

From your original post I noticed that your DEV version of Vertica is older than the PROD version of Vertica:

Dev version is v6.0.0-1. Prod is v6.0.1-0.

To determine if that's an issue have you tried exporting data in the other direction? That is, from DEV to PROD?

Although, I have no issues exporting from v6.1.0-0 to v6.0.0-3 and from v6.0.0-3 to v6.1.0-0.

Have you checked the vertica.log file for the events that are reported at the time you run the EXPORT command?

Have you tried using the COPY command? I'm curious to see if it works.

After connecting to your prod server on your DEV server you'd run something like:

Code: Select all

COPY foo FROM VERTICA dev_server.foo DIRECT;

[gdba]

Hi Jim,

I won't be able to test from DEV to PROD due to internal restrictions on testing in PROD environments.

After issuing the connect on PROD, netstat displays the connection on the DEV box:
tcp 0 0 dev.vertica.node01:5433 vertica.prod:50915 ESTABLISHED

The EXPORT runs on the PROD box for about 20 seconds before timing out. While the EXPORT is running, netstat displays the following on the DEV box:
tcp 0 0 dev.vertica.node01:5433 vertica.prod:50915 ESTABLISHED
tcp 0 1 dev.vertica.node01:49181 vertica.prod:47235 SYN_SENT

After the timeout occurs on PROD, the SYN_SENT entry disappears from netstat output on DEV, but the ESTABLISHED connection persists until I exit the vsql session on PROD.

Also, I ran strace against my vsql session on the PROD box, but I don't see anything interesting, other than the same errors reported by the vsql client.

Here are the entries from the vertica.log. v_reporting_node0018 is the logical name of the PROD node where I am running the EXPORT from vsql.

PROD log

2013-01-17 15:49:18.685 Init Session:0x7fe8f800e340 <ERROR> @v_reporting_node0018: V1001/4572: [dev_server] RemoteSend: Open failed on node [v_reporting_node0018] (socket error: Connection timed out)
LOCATION: /scratch_a/release/vbuild/vertica/EE/Operators/RemoteSend.cpp:40
2013-01-17 15:49:18.887 Init Session:0x7fe8f800e340 [PROTOCOL] <INFO> Num Errors: 1, Error Rollup: rootError 0, commError 0, ignoreCancel 0
2013-01-17 15:49:18.951 Init Session:0x7fe8f800e340 <ERROR> @v_reporting_node0018: V1001/4572: [dev_server] RemoteSend: Open failed on node [v_reporting_node0018] (socket error: Connection timed out)
LOCATION: /scratch_a/release/vbuild/vertica/EE/Operators/RemoteSend.cpp:40

DEV log

2013-01-17 15:49:18.622 Poll dispatch:0x1ae844d0 [Dist] <WARNING> Messenger::connectcb: socket error 110: Connection timed out (on port 38944)
2013-01-17 15:49:18.622 Poll dispatch:0x1ae844d0 [Dist] <WARNING> send_ready called with unprepared send handle
2013-01-17 15:49:18.622 Init Session:0x2aad08081c00 [EE] <WARNING> RemoteSend: open failed on node: v_reporting_node0018 (Detail: socket error: Connection timed out) (tag: 1001 plan: 51548769412)
2013-01-17 15:49:18.630 Init Session:0x2aad08081c00 <ERROR> @v_dev_node0001: V1001/4572: RemoteSend: Open failed on node [v_reporting_node0018] (socket error: Connection timed out)
LOCATION: initializeExecution, /scratch_a/release/vbuild/vertica/EE/Operators/RemoteSend.cpp:40

[JimKnicely]

Hi,

Is there a firewall up between the two servers? Maybe the port Vertica is attempting to use is being blocked? Or maybe the server TCP configuration is preventing access to some TCP ports on remote hosts.

Your error here indicates Vertica is trying to use port 38944.

2013-01-17 15:49:18.622 Poll dispatch:0x1ae844d0 [Dist] <WARNING> Messenger::connectcb: socket error 110: Connection timed out (on port 38944)

Try using the netcat command on your DEV server. Note that netcat is a simple unix utility that reads and writes data across network connections, using TCP or UDP protocol.

• nc -z <host> <port>; echo $?

So in your case, you could try:

• nc -z <<Your Prod Server Name>> 38944; echo $?

I'd also try running the command on your prod server if you can

• nc -z dev_server 38944; echo $?

[JimKnicely]

Hi,

Maybe you can try to use vsql to copy data between two Vertica databases. Something like the following which you can run on the DEV server:

Code: Select all

vsql -U username -w passwd -h prod_server -d dbname -At -c “SELECT * from foo” \
| vsql -U username -w passwd -d dbname -c “COPY foo FROM STDIN DELIMITER ‘|’;”