SingleStore DB

Connect with Perl

This is a simple example of how to install and use Perl with SingleStore, including an example of using Perl and Kerberos.

Installing Drivers for Perl

SingleStore 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.

Here is a simple example install with the basic CPAN method:

$ 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 $pw = "";
#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!