VGet: "Failed to get a connection to the cluster".

Santiago_Molina

I've installed the Vertica 7 VM image and I'm trying to use the new key/value feature. This is the program that I'm trying to run:

import java.sql.*;import com.vertica.jdbc.kv.*;

public class verticaKV2 {
public static void main(String[] args) {
com.vertica.jdbc.DataSource jdbcSettings 
= new com.vertica.jdbc.DataSource();
          jdbcSettings.setDatabase("VMART");
            jdbcSettings.setHost("192.168.204.140");
            jdbcSettings.setUserID("dbadmin");
            jdbcSettings.setPassword("password");
            jdbcSettings.setEnableRoutableQueries(true);
            jdbcSettings.setFailOnMultiNodePlans(false);
            jdbcSettings.setPort((short)5433);

VerticaRoutableConnection conn;
try {
conn = (VerticaRoutableConnection) 
jdbcSettings.getConnection();
   System.out.println("Connected.");
              VGet get = conn.prepareGet("public", "customer_dimension");
                get.addPredicate("customer_key", 5);
ResultSet rs = get.execute();
rs.next();
System.out.println("ID: " + 
rs.getString("customer_key"));
System.out.println("Username: " 
+ rs.getString("customer_name"));
System.out.println("Closing Connection.");
   conn.close();
} catch (SQLException e) {
System.out.println("Error! Stacktrace:");
   e.printStackTrace();
}
}
}

I'm getting an exception in the  VGet get = conn.prepareGet("public", "customer_dimension"); line: "Failed to get a connection to the cluster".

What might be the cause? I'm using the tables that are coming in the example database and I registered the copy with a community edition license.


thanks,
Santiago

Prasanta_Pal

Before trying to connect vertica through Java program, how did test JDBC connection is working fine?

Santiago_Molina

yes, the jdbc connection is working fine

Prasanta_Pal

Try alternate options ( change according to your DB name and host, may help to debug what wrong 
String connectionString = "jdbc:vertica://doc.verticacorp.com:5433/exampleDB?user=dbadmin&password=&EnableRoutableQueries=true";  VerticaRoutableConnection conn = (VerticaRoutableConnection) DriverManager.getConnection(connectionString);

Santiago_Molina

sorry, but I don't understand what you're asking me. This is the vertica's log:

2014-04-03 06:32:44.745 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/2705: Connection received: host=192.168.1.14 port=58485 (connCnt 3)

2014-04-03 06:32:44.749 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/4686: sendAuthRequest: user=dbadmin database=db_test host=192.168.1.14 authType=0

2014-04-03 06:32:44.749 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/2703: Connection authenticated: user=dbadmin database=db_test host=192.168.1.14

2014-04-03 06:32:44.749 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/2609: Client pid: 22123

2014-04-03 06:32:44.749 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/2608: Client label: jdbc-07.00.0000-93d24e5a-d5eb-44df-ba18-22d46b618dfe

2014-04-03 06:32:44.793 Init Session:0x2aab30013e60 [Session] <INFO> [Query] TX:0(vertica-4006:0x708b) set session autocommit to on

2014-04-03 06:32:44.814 Init Session:0x2aab30013e60 [Session] <INFO> [PQuery] TX:0(vertica-4006:0x708b) select node_name, node_port from node_resources

2014-04-03 06:32:44.814 Init Session:0x2aab30013e60 [Session] <INFO> [Query] TX:0(vertica-4006:0x708b) select node_name, node_port from node_resources

2014-04-03 06:32:44.815 Init Session:0x2aab30013e60-a0000000003af2 [Txn] <INFO> Begin Txn: a0000000003af2 'select node_name, node_port from node_resources'

2014-04-03 06:32:44.820 Init Session:0x2aab30013e60-a0000000003af2 [Txn] <INFO> Starting Commit: Txn: a0000000003af2 'select node_name, node_port from node_resources'

2014-04-03 06:32:44.821 Init Session:0x2aab30013e60 [Txn] <INFO> Commit Complete: Txn: a0000000003af2 at epoch 0x3c1

2014-04-03 06:32:44.858 Init Session:0x2aab30013e60 [Session] <INFO> [PQuery] TX:0(vertica-4006:0x708b) select node_name, node_address, export_address from nodes

2014-04-03 06:32:44.858 Init Session:0x2aab30013e60 [Session] <INFO> [Query] TX:0(vertica-4006:0x708b) select node_name, node_address, export_address from nodes

2014-04-03 06:32:44.859 Init Session:0x2aab30013e60-a0000000003af4 [Txn] <INFO> Begin Txn: a0000000003af4 'select node_name, node_address, export_address from nodes'

2014-04-03 06:32:44.870 Init Session:0x2aab30013e60-a0000000003af4 [Txn] <INFO> Starting Commit: Txn: a0000000003af4 'select node_name, node_address, export_address from nodes'

2014-04-03 06:32:44.870 Init Session:0x2aab30013e60 [Txn] <INFO> Commit Complete: Txn: a0000000003af4 at epoch 0x3c1

2014-04-03 06:32:45.239 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 08006/5167: Unexpected EOF on client connection

2014-04-03 06:32:45.239 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/4719: Session vertica-4006:0x708b ended; closing connection (connCnt 3)

Any clue from here?

thanks,

Santiago

Prasanta_Pal

Can you try by setting ResultBufferSize the below ?
((VerticaConnection)dbconn).setProperty("ResultBufferSize", "0" );

Prasanta_Pal

Also check
If there any idle connection timeout configured at firewall/application level?

Santiago_Molina

Same thing, isn't there anything meaningful in the log I copied before?

2014-04-03 06:32:44.870 Init Session:0x2aab30013e60-a0000000003af4 [Txn] <INFO> Starting Commit: Txn: a0000000003af4 'select node_name, node_address, export_address from nodes'

2014-04-03 06:32:44.870 Init Session:0x2aab30013e60 [Txn] <INFO> Commit Complete: Txn: a0000000003af4 at epoch 0x3c1

2014-04-03 06:32:45.239 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 08006/5167: Unexpected EOF on client connection

2014-04-03 06:32:45.239 Init Session:0x2aab30013e60 <LOG> @v_db_test_node0001: 00000/4719: Session vertica-4006:0x708b ended; closing connection (connCnt 3)

Prasanta_Pal

Yes, I could see this error
Please try using the below version of SDK
$ java -version
java version "1.6.0_24"
OpenJDK Runtime Environment (IcedTea6 1.11.8) (rhel-1.56.1.11.8.el6_3-x86_64)
OpenJDK 64-Bit Server VM (build 20.0-b12, mixed mode)

I could connect and get the data

java -cp vertica-jdbc-7.0.1-0.jar:. verticaKV2

what was your version?

Santiago_Molina

I tried also with 1.6.0_24 and I got the same error. I believe that's not a client side issue, it is a server side one, some missing configuration. Is this an stable feature? or just experimental?

[Deleted User]

Can you get the output of the following?

select * from nodes;

Santiago_Molina

yes, I can. I can see 4 nodes up and running.

[Deleted User]

can you post the output for the same query ?

Santiago_Molina

v_cswsaxdst_node0001 45035996273704980 UP 172.16.200.55 172.16.200.55 /data/vertica/cswsaxdst/v_cswsaxdst_node0001_catalog/Catalog 0v_cswsaxdst_node0002 45035996273719004 UP 172.16.200.56 172.16.200.56 /data/vertica/cswsaxdst/v_cswsaxdst_node0002_catalog/Catalog 0
v_cswsaxdst_node0003 45035996273719008 UP 172.16.200.57 172.16.200.57 /data/vertica/cswsaxdst/v_cswsaxdst_node0003_catalog/Catalog 0
v_cswsaxdst_node0004 45035996273719012 UP 172.16.200.58 172.16.200.58 /data/vertica/cswsaxdst/v_cswsaxdst_node0004_catalog/Catalog 0

[Deleted User]

Ok. Here is the issue. Looks like vertica is set up using 2 networks ( public and private) and your java code has native load balancing enabled which means all calls from clients get routed to private network by default (172.16.200.55) eventhough you specify 192.168.204.140 in your connection string. 

the following sql statements should resolve the issue assuming I have used the right ip addresses. 


CREATE NETWORK INTERFACE ni1 ON  v_cswsaxdst_node0001 WITH '192.168.204.140';


CREATE NETWORK INTERFACE ni2 ON  v_cswsaxdst_node0002 WITH '192.168.204.141';


CREATE NETWORK INTERFACE ni3 ON  v_cswsaxdst_node0003 WITH '192.168.204.142';


CREATE NETWORK INTERFACE ni4 ON  v_cswsaxdst_node0004 WITH '192.168.204.143';


ALTER NODE  v_cswsaxdst_node0001 EXPORT ON ni1;

ALTER NODE  v_cswsaxdst_node0002 EXPORT ON ni2;

ALTER NODE  v_cswsaxdst_node0003 EXPORT ON ni3;

ALTER NODE  v_cswsaxdst_node0004 EXPORT ON ni4;

Santiago_Molina

thanks, it does work now at least with the USERS table you're providing in the documentation, but I don't know why it is not working with another table I've created. I'm getting an "Attempt to run multi-node KV plan error" and the table definition is this one:
CREATE TABLE MY_TABLE
(
                    MSISDN INT NOT NULL,
        date_sid INT NOT NULL,   
    MEASURE1 NUMERIC,
    MEASURE2 NUMERIC
                                        
)
PARTITION BY DATE_SID;

CREATE PROJECTION MY_TABLE_p0
(
                    MSISDN,
        date_sid,   
    MEASURE1,
    MEASURE2
)
AS
SELECT
                    MSISDN,
        date_sid,   
     MEASURE1,
    MEASURE2
FROM MY_TABLE
ORDER BY MSISDN
SEGMENTED BY HASH(MSISDN) ALL NODES KSAFE;

[Deleted User]

Can you post the query and the error ?

Santiago_Molina

import java.sql.*;import com.vertica.jdbc.kv.*;

public class verticaKV2 {
public static void main(String[] args) {
com.vertica.jdbc.DataSource jdbcSettings 
= new com.vertica.jdbc.DataSource();
      jdbcSettings.setDatabase("my_db");
            jdbcSettings.setHost("myserver.com");
            jdbcSettings.setUserID("my_user");
            jdbcSettings.setPassword("password");
            jdbcSettings.setEnableRoutableQueries(true);
            jdbcSettings.setPort((short)5433);

VerticaRoutableConnection conn;
try {
conn = (VerticaRoutableConnection) 
jdbcSettings.getConnection();
    System.out.println("Connected.");
              VGet get = conn.prepareGet("my_db", "MY_TABLE");
                get.addPredicate("msisdn", 1);
ResultSet rs = get.execute();
rs.next();
System.out.println("ID: " + 
rs.getString("msisdn"));
System.out.println("Closing Connection.");
   conn.close();
} catch (SQLException e) {
System.out.println("Error! Stacktrace:");
   e.printStackTrace();
}
}
}


the error:


Error! Stacktrace:
java.sql.SQLNonTransientException: [Vertica][VJDBC](2242) ROLLBACK: Attempt to run multi-node KV plan
        at com.vertica.util.ServerErrorData.buildException(Unknown Source)
        at com.vertica.dataengine.VResultSet.fetchChunk(Unknown Source)
        at com.vertica.dataengine.VResultSet.initialize(Unknown Source)
        at com.vertica.dataengine.VQueryExecutor.readExecuteResponse(Unknown Sou
rce)
        at com.vertica.dataengine.VQueryExecutor.handleExecuteResponse(Unknown S
ource)
        at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source)
        at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source)
        at com.vertica.jdbc.common.SStatement.executeQuery(Unknown Source)
        at com.vertica.jdbc.kv.RoutableQuery.runQuery(Unknown Source)
        at com.vertica.jdbc.kv.RoutableQuery.execute(Unknown Source)
        at com.vertica.jdbc.kv.VGetImpl.execute(Unknown Source)
        at verticaKV2.main(verticaKV2.java:22)
Caused by: com.vertica.support.exceptions.NonTransientException: [Vertica][VJDBC
](2242) ROLLBACK: Attempt to run multi-node KV plan
        ... 12 more

Santiago_Molina

I could make it work but I don't understand why it is behaving like that. It seems silly but the only thing I did was to drop the table and recreate it, but this time I used lowercase for naming the MSISDN column (msisdn) and it started working. I tried this several times in order to confirm that this really making the difference. Any idea why this is working like that?