SingleStore DB

JDBC Connector Setup Instructions With Optional GSSAPI

The following instructions are for a Red Hat Distribution and assume Java and the Java SDK are installed (i.e., java and javac binaries are available). The concepts of this installation and configuration can be applied elsewhere.

Installation
  1. Download the MariaDB JDBC connector:

    wget -O /tmp/mariadb-java-client.jar http://download.memsql.com/clients/mariadb-java-client-2.4.0.jar
  2. Copy the Java source code sample below to /path/of/your/choice/Query.java.

    We'll use /tmp/Query.java for our instructions. If you wish to test against something other than localhost, change 127.0.0.1 to the IP of your choice.

  3. Compile the source against the MariaDB client jar.

    This should produce a file named Query.class. The -cp flag specifies the classpath. Here, it should be the path to the MariaDB client jar.

    javac -cp ./mariadb-java-client.jar /Query.java
  4. Run the java binary against the MariaDB client jar.

    The classpass should include the path to Query.class and mariadb-java-client.jar, and kerberos_principal_name should be substituted with your Kerberos user.

    java -cp ./mariadb-java-client.jar:. Query <kerberos_principal_name>
Code Sample for GSSAPI Authentication

The following section details the use of GSSAPI authentication via the JDBC client libraries. It is assumed that SingleStore DB is already configured to use GSSAPI. See: Kerberos Authentication.

Note that the code used to connect for regular connections and Kerberos connections should be the same since the connector is porting the C extensions. This is because the SingleStore DB server checks grants and sees that the user is authenticated via GSSAPI, and then the client C extensions have an attached handler for GSSAPI which runs independently of the third-party connector.

Query.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:mariadb://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);
        }
    }
}

Sample output from terminal:

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

Connection url jdbc:mariadb://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 ~]$