---

This section outlines several ways to connect to SingleStore DB using popular clients and libraries. To download supported client drivers, see Client Driver Downloads.

Python Client Library Setup Instructions

The following instructions are intended for a Red Hat Enterprise Linux Based Distribution, but the concepts may be applied elsewhere.

Installation

The first step is to install the Python client library. There are a few choices depending on your requirements.

**Note**: When using the GSSAPI authentication functionality, you must use a library that links against the MariaDB 10.1.11 or greater C bindings.

mysqlclient

mysqlclient-python

pip install mysqlclient

Recommended for SQLAlchemy users.

Code Sample for GSSAPI Authentication

The following section details the use of GSSAPI authentication via the Python client libraries. It is assumed that SingleStore DB is already configured to use GSSAPI. You can read about that here.

To validate, ensure that your MariaDB packages are version 10.1.11 or greater, and that you do not have conflicting MySQL packages:

RHEL MariaDB Version Check

[ec2-user ~]$ rpm -qa | egrep -i 'mysql|maria'

MariaDB-compat-10.2.23-1.el7.centos.x86_64
MariaDB-common-10.2.23-1.el7.centos.x86_64
MariaDB-client-10.2.23-1.el7.centos.x86_64
MariaDB-server-10.2.23-1.el7.centos.x86_64
MariaDB-common-debuginfo-10.2.23-1.el7.centos.x86_64
MariaDB-devel-10.2.23-1.el7.centos.x86_64

GSSAPI authentication has been validated with the “Python Connector,” mysqlclient-python/fork and mysqlclient-python/rhel client libraries. At the time of writing, there are no known pure python MySQL connector implementations which support the mariadb GSSAPI client-side protocol.

The following outlines an example Python program that will authenticate to SingleStore DB via GSSAPI using mysqlclient-python/fork.

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.

gssapi-test.py

import MySQLdb

# Substitute 'kerberos_principal_name' and change the host ip as required.
conn = MySQLdb.connect(
    user='root',
    host='127.0.0.1',
    database='information_schema')

if conn:
    print("Successfully connected via Kerberos authentication")

cursor = conn.cursor()

query = "SELECT query_text FROM mv_queries"

cursor.execute(query)
for qtext in cursor:
    print("{}".format(qtext))

cursor.close()
conn.close()

To use the same sample with the RHEL mysqlclient-python, change database= to db=.

This sample will also work with the MyQL Python Connector. Substitute MySQLdb with mysql.connector.

There is a known issue with the MySQL Python connector where it segfaults if there is not a valid ticket in the credentials cache.

The output should look like this:

[ec2-user ~]$ python gssapi-test.py

Successfully connected via Kerberos authentication
('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\nJOIN information_schema.LMV_NODES\nON 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 ~]$

PyMySQL

PyMySQL

Note: Because this is a pure python implementation, it will not work with GSSAPI / Kerberos.

pip install PyMySQL

MySQL Connector/Python

MySQL Connector/Python

yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-8.0.15-1.el7.x86_64.rpm yum install https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-cext-8.0.15-1.el7.x86_64.rpm

Note: The MySQL Connector/Python library is not recommended.

SingleStore recommends the PyMySQL and mysqlclient libraries.

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. You can read about that here.

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

Perl with SingleStore DB

This is a simple example of how to install and use Perl with SingleStore DB.

Installing Drivers for Perl

SingleStore DB works with the basic Perl drivers for MySQL using the standard DBI and DBM drivers. There are many methods to install and configure Perl, but having the MySQL driver is the key to getting Perl working with SingleStore DB.

Here is a simple example install with the basic CPAN method described here: https://dev.mysql.com/doc/refman/8.0/en/perl-installation.html

$ perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql

This installed the following version of DBI and DBD.

perl -MDBI -e 'print $DBI::VERSION."\n"'  # 1.642
perl -MDBD::mysql -e 'print $DBD::mysql::VERSION."\n"'  # 4.050

These drivers were tested with the following sample script.

The following script connects to a cluster and retrieves some basic information about the cluster.

#!/usr/bin/perl
use strict;
use DBI;

my $host = "127.0.0.1";
my $database = "information_schema"; my $port = 3306;
my $tablename = "mv_activities";
my $user = "root";
my $pw = "";
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host",$user, $pw) or die "Cannot connect to MySQL server\n";
my $sql = 'select @@memsql_version';
my $sth = $dbh->prepare($sql);
$sth->execute();

while (my @row = $sth->fetchrow_array) {
  print "MemSQL_Version: $row[0] \n";
}

my $sql = 'select count(*) from aggregators';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array) {
  print "Aggregators: $row[0] \n";
}

my $sql = 'select count(*) from leaves';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array) {
  print "Leaves $row[0] \n";
}

my $sql = 'select variable_name, variable_value from information_schema.global_status where variable_name = "uptime"';
my $sth = $dbh->prepare($sql);
$sth->execute();
while (my @row = $sth->fetchrow_array) {
#print "variable_name $row[0] variable_value $row[1]\n";
print "Cluster has been up for $row[1] seconds! \n";
}

Sample output:

$ ./show_cluster.pm
MemSQL_Version: 6.7.14 Aggregators: 1
Leaves 2
Cluster has been up ​for​ 16025 seconds!

Working with Kerberos

Getting our example Perl solution to work with Kerberos is simple. Since it is compiled locally with CPAN, it works out of the box. Remember to comment out the password line.

#my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host",$user, $pw) or die "Cannot connect to MySQL server\n";
my ​$dbh​ = DBI->connect(​"DBI:mysql:database=​$database​;host=​$host​"​,​$user​ ) or die ​"Cannot connect to MySQL server\n"​;

After doing this, it connects with Kerberos:

[ec2-user perl]$ klist
Ticket cache: KEYRING:persistent:1000:1000
Default principal: tron@LOCALHOST

Valid starting       Expires              Service principal
04/15/2019 23:47:37  04/16/2019 23:31:56  memsql/memsql.localhost@LOCALHOST
04/15/2019 23:31:56  04/16/2019 23:31:56  krbtgt/LOCALHOST@LOCALHOST

[ec2-user perl]$ ./show_cluster.pm
MemSQL_Version: 6.7.15
Aggregators: 1
Leaves 1
Cluster has been up ​for​ 1306 seconds!

MySQL Command-Line Client

SingleStore DB is wire-compliant with MySQL meaning connecting to SingleStore DB is as easy as connecting to the MySQL database. You may need the -u user, -h host, and -P port flags to ensure you are connecting to SingleStore DB properly. For example:

mysql -u root -h 127.0.0.1 -P 3306

Here we have -u root to specify login as user root, -h 127.0.0.1 to connect to the SingleStore DB host at localhost, and -P 3306 to connect to SingleStore DB on port 3306. This is the default port setting, which can be changed if needed.

You may also use the --prompt flag to change the prompt in the interactive session, simply to help distinguish SingleStore DB from MySQL:

mysql -u root -h 127.0.0.1 -P 3306 --prompt="singlestore> "

The output when you connect should look similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
singlestore>

These commands show more information about the connection and the server:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using  EditLine wrapper

Connection id:        6
Current database:
Current user:         root@127.0.0.1
SSL:                  Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:      ;
Server version:       5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Protocol version:     10
Connection:           127.0.0.1 via TCP/IP
Server characterset:  utf8
Db     characterset:  utf8
Client characterset:  utf8
Conn.  characterset:  utf8
TCP port:             3306
--------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| memsql             |
| sharding           |
+--------------------+
3 rows in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------------------------------------------------------+
| Variable_name           | Value                                                                        |
+-------------------------+------------------------------------------------------------------------------+
| memsql_version          | 4.0                                                                          |
| memsql_version_date     | Wed May 6 09:07:03 2015 -0700                                                |
| memsql_version_hash     | 0cba132fba0ba7650c1bc9aeec8c1fc381def395                                     |
| protocol_version        | 10                                                                           |
| version                 | 5.5.8                                                                        |
| version_comment         | MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial) |
| version_compile_machine | x86_64                                                                       |
| version_compile_os      | Linux                                                                        |
+-------------------------+------------------------------------------------------------------------------+
8 rows in set (0.03 sec)

mysql> show status like '%license%';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| License_key   | 5ca1ab1e000f40aaa6cb67b2ea3ee466 |
| License_type  | enterprise                       |
+---------------+----------------------------------+
2 rows in set (0.00 sec)

mysql> show status like '%cluster_capacity%';
+--------------------------+-------------+
| Variable_name            | Value       |
+--------------------------+-------------+
| Maximum_cluster_capacity | 10000000 MB |
| Used_cluster_capacity    | 107957 MB   |
+--------------------------+-------------+
2 rows in set (0.00 sec)

MySQL Command-Line Client Considerations

• If using the MySQL 8.x client, you must pass --default-auth=mysql_native_password in your connection string.

• If you also have MySQL installed, the MySQL client may connect to MySQL instead of SingleStore DB by default if you do not specify the appropriate host and port options. If you attempt to connect to SingleStore DB on localhost, even if you specify the correct port, the MySQL client may still attempt to connect to MySQL with default configuration. See this troubleshooting item on ERROR 2002.

For more documentation on the MySQL client, visit the MySQL documentation.

SQLAlchemy

Things to keep in mind when using SingleStore DB with the SQLAlchemy Python library:

• See the comprehensive SQLAlchemy documentation.
• See the SQLAlchemy tutorial.
• Use one of the recommended Python client libraries.
• Remember to use mysql:// in the create_engine statement.

Sequel Pro

Open the latest version of Sequel Pro. You can connect to SingleStore DB using Quick Connect:

Replace the host field with the IP address of your SingleStore DB instance. You can find this using the command boot2docker ip. Click Connect.

Select a database by clicking on the Choose Database… field in the top left corner. To create your own new database:

1. Select the Query tab.

2. Type the query into the field.

3. Press Run Previous on the right.

   

4. If this query was executed correctly, the same message should appear on the bottom of the window.

After running a CREATE TABLE query, the tables field on the left side updates:

SQL Workbench

Once you have SQL Workbench installed open the Connectors window. In the Driver drop-down menu select MySQL (com.mysql.jdbc.Driver). This Driver requires the MySQL Java connector. Once you have Driver selected, enter the URL jdbc:mysql://<host>:<port>. By default SingleStore DB has one user root with no password, so set username to root and leave password empty. Complete the other fields as desired.

Note: Some users have reported issues connecting with SQL Workbench v8 and have recommended using v6 instead.

Click OK and the SQL Workbench window will appear. Here you can issue queries in the top window and see results in the bottom window.

Ruby On Rails

Configuring SingleStore DB

Open config/database.yml and edit the development adapter to use MySQL. You will need to have the SingleStore DB socket which can be found using the query show variables like 'socket'.

$ mysql -u root -h 127.1 -P 3306 -e "show variables like 'socket'"

+---------------+---------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------------------------------------------------------+
| socket | /var/lib/memsql-ops/data/installs/MI85eaf8fca04849888be7f1a9a596210e/data/memsql.sock |
+---------------+---------------------------------------------------------------------------------------+

Querying Your Database

SingleStore DB can be queries using the mysql2 connector in controller code. Calling execute returns a mysql2 result which can be converted to an array and easily displayed:

class WelcomeController < ApplicationController
  def index
    render plain: ActiveRecord::Base.connection.execute('show databases').to_a.join(' ')
  end
end