Skip to main content

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

  • April 15, 2014
  • 18 replies
  • 36 views
Santiago_Molina
Forum|alt.badge.img
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

    18 replies

    Prasanta_Pal
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • April 15, 2014
    Before trying to connect vertica through Java program, how did test JDBC connection is working fine?
    Santiago_Molina
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • April 15, 2014
    yes, the jdbc connection is working fine
    Prasanta_Pal
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • April 16, 2014
    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
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • April 25, 2014

    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
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • April 25, 2014
    Can you try by setting ResultBufferSize the below ?
    ((VerticaConnection)dbconn).setProperty("ResultBufferSize", "0" );

    Prasanta_Pal
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • April 25, 2014
    Also check
    If there any idle connection timeout configured at firewall/application level?

    Santiago_Molina
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • April 26, 2014

    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
    Forum|alt.badge.img
    • Prasanta_Pal
    • Participating Frequently
    • April 26, 2014
    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
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • April 26, 2014
    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?
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 1, 2014
    Can you get the output of the following?

    select * from nodes;


    Santiago_Molina
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • May 5, 2014
    yes, I can. I can see 4 nodes up and running.
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 5, 2014
    can you post the output for the same query ?
    Santiago_Molina
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • May 6, 2014
    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
    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 6, 2014
    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
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • May 7, 2014
    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;



    Forum|alt.badge.img+2
    • Anonymous
    • Participating Frequently
    • May 7, 2014
    Can you post the query and the error ?

    Santiago_Molina
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • May 7, 2014
    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
    Forum|alt.badge.img
    • Santiago_Molina
    • Author
    • Participating Frequently
    • May 8, 2014
    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?
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement