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, change127.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 theclasspath
. 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
andmariadb-java-client.jar
. Substitutekerberos_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 ~]$