SingleStoreDB v8.1

JDBC Connector Setup Instructions With Optional GSSAPI

You can use GSSAPI authentication via the JDBC client libraries. This tutorial assumes that SingleStoreDB is already configured to use GSSAPI. See Kerberos Authentication for information on third-party authentication using 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). This example uses MariaDB Connector/J (JDBC) v2.4.0.The concepts of this installation and configuration can be applied elsewhere.

Download the MariaDB JDBC connector.

wget -O /tmp/mariadb-java-client.jar http://download.memsql.com/clients/mariadb-java-client-2.4.0.jar

Copy the following Java source code sample to /path/of/your/choice/Query.java file.

Note

The code is the same for regular and Kerberos connections since the connector is porting the C extensions. This is because the SingleStoreDB 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.

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);
        }
    }
}

In this example, we'll use /tmp/Query.java. 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 MariaDB client jar.
```
javac -cp ./mariadb-java-client.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 MariaDB client jar.
Run the java binary against the MariaDB client jar.
The classpath should include the path to Query.class and mariadb-java-client.jar. Substitute kerberos_principal_name with your Kerberos user.
```
java -cp ./mariadb-java-client.jar:. Query <kerberos_principal_name>
```

Here's a sample output:

[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 ~]$

In this section:

JDBC Connector Setup Instructions With Optional GSSAPI

Note

Search results