JDBC Connector Setup Instructions With Optional GSSAPI

Prerequisites

Configure SingleStore to use GSSAPI. Refer to Kerberos Authentication for information on third-party authentication using GSSAPI.
Install Java and Java SDK (java and javac binaries are available).

Configure Authentication

The following instructions are for a Red Hat Distribution. The concepts of this installation and configuration can be applied elsewhere.

Download the latest version of the SingleStore JDBC Driver .jar from GitHub.

Add the following Java source code sample to a file named Query.java.

Note

The code is the same for regular and Kerberos connections since the connector is porting the C extensions. This is because the SingleStore server checks grants to confirm if the user is authenticated using GSSAPI and the client C extensions have an attached handler for GSSAPI that runs independent of the third-party connector.

Java

import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

class Query {
    // Arguments:
    // args[0]: the kerberos principal name with which to connect to memsqld
    public static void main (String[] args) {
        if (args.length != 1) {
            System.err.println("Wrong number of arguments."); System.exit(1);
        }
        try {
            StringBuilder url = new StringBuilder("jdbc:singlestore://127.0.0.1:3306/information_schema?user=");
            url.append(URLEncoder.encode(args[0], "UTF-8"));

            String url_string = url.toString();
            System.err.println("Connection url " + url_string);
            Connection conn = DriverManager.getConnection(url_string);
            Statement stmt = conn.createStatement();
            ResultSet rs;

            rs = stmt.executeQuery("select query_text from mv_queries");
            while (rs.next()) {
                String qt = rs.getString("query_text");
                System.out.println("query text: " + qt);
            }

            conn.close();
            System.exit(0);
        } catch (Exception e) {
            System.err.println("Got an exception!");
            System.err.println(e.getMessage());
            e.printStackTrace(System.out);
            System.exit(1);
        }
    }
}

This example uses the /tmp/Query.java file. To test against something other than localhost, change 127.0.0.1 (in the code above) to the IP of your choice.

Compile the source against the SingleStore JDBC Driver jar. Replace x.x.x in the following command with the SingleStore JDBC Driver version.
Shell
```
javac -cp ./singlestore-jdbc-client-x.x.x.jar /Query.java
```
This command generates a file named Query.class. The -cp flag in the command specifies the classpath. It should be the path to the SingleStore JDBC Driver jar.
Run the java binary against the SingleStore JDBC Driver jar.

The classpath should include the path to Query.class and singlestore-jdbc-client-x.x.x.jar. Substitute kerberos_principal_name with your Kerberos user and x.x.x with the SingleStore JDBC Driver version in the following command.
Shell
```
java -cp ./singlestore-jdbc-client-x.x.x.jar:. Query <kerberos_principal_name>
```

Here's a sample output:

Shell

[ec2-user ~]$ java -cp "/tmp/singlestore-jdbc-client-x.x.x.jar:/tmp" Query "tron"

Connection url jdbc:singlestore://127.0.0.1:3306/information_schema?user=tron
Debug is true storeKey false useTicketCache true useKeyTab false doNotPrompt true ticketCache is null isInitiator true KeyTab is null refreshKrb5Config is false principal is null tryFirstPass is false useFirstPass is false storePass is false clearPass is false
Acquire TGT from Cache
Principal is tron@LOCALHOST
Commit Succeeded

SELECT @@max_allowed_packet,@@system_time_zone,@@time_zone,@@auto_increment_increment SELECT @@memsql_id
SELECT @@memsql_version
SELECT ACTIVITY_NAME, QUERY_TEXT, PLAN_WARNINGS, PLAN_INFO FROM INFORMATION_SCHEMA.LMV_QUERIES
SELECT WITH(binary_serialization=1, binary_serialization_internal=1) `_WM_AGG_TABLE`.`AGGREGATOR_ACTIVITY_NAME` AS `aggregator_activity_name`, SUM(`_WM_AGG_TABLE`.`ELAPSED_TIME_MS`) AS `elapsed_time_ms`, SUM(`_WM_AGG_TABLE`.`SUCCESS_COUNT`) AS `numRuns` FROM `_WM_AGG_TABLE` as `_WM_AGG_TABLE` GROUP BY 1 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=LLVM) SELECT query_text FROM mv_queries

select query_text from mv_queries

SELECT HEARTBEAT_NO_LOGGING agg.AGGREGATOR_ACTIVITY_NAME, coalesce(sum(leaves.memory_bs)*1000/agg.elapsed_time_ms/0x400/0x400, 0):>bigint,coalesce(agg.elapsed_time_ms/agg.numRuns, @):>bigint as avg_runtime, coalesce(sum(leaves.cpu_time_ms)/agg.numRuns, @):>bigint as avg_cpu_time FROM (select aggregator_activity_name, sum(elapsed_time_ms) as elapsed_time_ms, sum(success_count) as numRuns from information_schema._WM_AGG_TABLE group by 1) agg join information_schema._WM_LEAF_TABLE leaves on agg.aggregator_activity_name = leaves.aggregator_activity_name group by agg.AGGREGATOR_ACTIVITY_NAME

select @@version_comment limit @

SELECT @@memsql_id
SELECT @@memsql_version
SELECT AVAILABILITY_GROUP FROM information_schema.LEAVES
JOIN information_schema.LMV_NODES
ON information_schema.LEAVES.NODE_ID=information_schema.LMV_NODES.NODE_ID
SELECT WITH(binary_serialization=1, binary_serialization_internal=1)

`leaves_0`.`MEMORY_BS` AS `MEMORY_BS`, `leaves_0`.`CPU_TIME_MS` AS `CPU_TIME_MS`, `leaves_0`.`AGGREGATOR_ACTIVITY_NAME` AS `AGGREGATOR_ACTIVITY_NAME` FROM `_WM_LEAF_TABLE` as `leaves_0` OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=LLVM) SELECT SQL_NO_LOGGING 1
SELECT SQL_NO_LOGGING @@maximum_table_memory, @@maximum_memory

[ec2-user ~]$

To enable debug logging for Kerberos authentication, add the -Dsun.security.krb5.debug=true -Dsun.security.jgss.debug=true JVM argument to the command. Here’s an example,