MySQL 5.7 labs and the HTTP Plugin – inserting, updating and deleting records in MySQL via HTTP

In the MySQL Labs version of MySQL version 5.7, there is a new HTTP plugin. The HTTP plugin documentation from the labs site provides this information (from MySQL Labs):

The HTTP Plugin for MySQL adds HTTP(S) interfaces to MySQL. Clients can use the HTTP respectively HTTPS (SSL) protocol to query data stored in MySQL. The query language is SQL but other, simpler interfaces exist. All data is serialized as JSON. This version of MySQL Server HTTP Plugin is a Labs release, which means it’s at an early development stage. It contains several known bugs and limitation, and is meant primarily to give you a rough idea how this plugin will look some day. Likewise, the user API is anything but finalized. Be aware it will change in many respects.

In other words, with a simple HTTP URL, you can access and modify your data stored in MySQL. Here is an overview from the documentation:


The HTTP Plugin for MySQL is a proof-of concept of a HTTP(S) interface for MySQL 5.7.

The plugin adds a new protocol to the list of protocols understood by the server. It adds the HTTP respectively HTTPS (SSL) protocol to the list of protocols that can be used to issue SQL commands. Clients can now connect to MySQL either using the MySQL Client Server protocol and programming language-dependent drivers, the MySQL Connectors, or using an arbitrary HTTP client.
Results for SQL commands are returned using the JSON format.

The server plugin is most useful in environments where protocols other than HTTP are blocked:
• JavaScript code run in a browser
• an application server behind a firewall and restricted to HTTP access
• a web services oriented environment

In such environments the plugin can be used instead of a self developed proxy which translates HTTP requests into MySQL requests. Compared to a user-developed proxy, the plugin means less latency, lower complexity and the benefit of using a MySQL product. Please note, for very large deployments an architecture using a proxy not integrated into MySQL may be a better solution to clearly separate software layers and physical hardware used for the different layers.

The HTTP plugin implements multiple HTTP interfaces, for example:
• plain SQL access including meta data
• a CRUD (Create-Read-Update-Delete) interface to relational tables
• an interface for storing JSON documents in relational tables

Some of the interfaces follow Representational State Transfer (REST) ideas, some don’t. See below for a description of the various interfaces.

The plugin maps all HTTP accesses to SQL statements internally. Using SQL greatly simplifies the development of the public HTTP interface. Please note, at this early stage of development performance is not a primary goal. For example, it is possible to develop a similar plugin that uses lower level APIs of the MySQL server to overcome SQL parsing and query planning overhead.


In this post, I will show you how to install the plugin and use HTTP commands to retrieve data. The documentation also provides other examples. We aren’t going to explain everything about the plugin, as you will need to download the documentation.

First, you will need to download the MySQL Labs 5.7 version which includes the plugin. This download is available from the MySQL Labs web site.

After MySQL 5.7 is installed, you will want to add these lines to your my.cnf/my.ini file under the [mysqld] section:

#
# Default database, if no database given in URL
#
myhttp_default_db = httptest
#
# Non-SSL default MySQL SQL user
#
myhttp_default_mysql_user_name = http_sql_user
myhttp_default_mysql_user_passwd = sql_secret
myhttp_default_mysql_user_host = 127.0.0.1

There are other options for the plugin, but we will skip them for this post.

# Change only, if need be to run the examples!
#
# General settings
# 
# myhttp_http_enabled = 1
# myhttp_http_port = 8080
# myhttp_crud_url_prefix = /crud/
# myhttp_document_url_prefix = /doc/
# myhttp_sql_url_prefix = /sql/
# 
# 
# 
# Non-SSL HTTP basic authentication
# 
# myhttp_basic_auth_user_name = basic_auth_user
# myhttp_basic_auth_user_passwd = basic_auth_passwd

# 
# SSL
# 
# myhttp_https_enabled = 1
# myhttp_https_port = 8081
# myhttp_https_ssl_key = /path/to/mysql/lib/plugin/myhttp_sslkey.pem

After modifying the my.cnf/my.ini file, restart mysql and then install the plugin from a mysql prompt. Before proceeding, be sure to also check to make sure the plugin is installed:

mysql> INSTALL PLUGIN myhttp SONAME 'libmyhttp.so';
Query OK, 0 rows affected (0.09 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='myhttp'\G
*************************** 1. row ***************************
           PLUGIN_NAME: myhttp
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: DAEMON
   PLUGIN_TYPE_VERSION: 50705.0
        PLUGIN_LIBRARY: libmyhttp.so
PLUGIN_LIBRARY_VERSION: 1.5
         PLUGIN_AUTHOR: Andrey Hristov, Ulf Wendel
    PLUGIN_DESCRIPTION: HTTP Plugin for MySQL
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
1 row in set (0.03 sec)

We will need to create the user for accessing our database, and grant permissions:

mysql> CREATE USER 'http_sql_user'@'127.0.0.1' IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (1.89 sec)

mysql> SET old_passwords = 0;
Query OK, 0 rows affected (0.05 sec)

mysql> SET PASSWORD FOR 'http_sql_user'@'127.0.0.1' = PASSWORD('sql_secret');
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT ALL ON myhttp.* TO
    -> 'http_sql_user'@'127.0.0.1';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.58 sec)

We will need to create a table for our example. The table will be a very simple table with three fields – ID, first and last names:

mysql> CREATE TABLE `names` (
    ->   `id` int(11) NOT NULL DEFAULT '1000',
    ->   `name_first` varchar(40) DEFAULT NULL,
    ->   `name_last` varchar(40) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

We need to insert some data into the table:

INSERT INTO `names` (name_first, name_last) VALUES ('Clark','Kent');
INSERT INTO `names` (name_first, name_last) VALUES ('Bruce','Wayne');
INSERT INTO `names` (name_first, name_last) VALUES ('Hal','Jordan');
INSERT INTO `names` (name_first, name_last) VALUES ('Barry','Allen');
INSERT INTO `names` (name_first, name_last) VALUES ('Diana','Prince');
INSERT INTO `names` (name_first, name_last) VALUES ('Arthur','Curry');
INSERT INTO `names` (name_first, name_last) VALUES ('Oliver','Queen');
INSERT INTO `names` (name_first, name_last) VALUES ('Ray','Palmer');
INSERT INTO `names` (name_first, name_last) VALUES ('Carter','Hall');
Query OK, 9 rows affected (0.01 sec)

Now that we have our table and table data, we can test a select statement with an HTTP URL. You may use a browser for this, but since I like to work with command line tools, I am going to use curl, a command line tool for doing all sorts of URL manipulations and transfers. Here is a simple select statement via curl. Use the plus sign (+) for spaces.

Select all of the names in the table:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names"
[
{
"meta":[
	{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
	{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[ 
	["Clark","Kent"],
	["Bruce","Wayne"],
	["Hal","Jordan"],
	["Barry","Allen"],
	["Diana","Prince"],
	["Arthur","Curry"],
	["Oliver","Queen"],
	["Ray","Palmer"],
	["Carter","Hall"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]

If you want to use a browser, you might have to authenticate the connection (enter the user name and password):

And here is the output from submitting the URL in a browser:

URL:  http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names

Selecting a single name:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+name_first,+name_last+FROM+names+where+name_first+=+'Clark'"
[
{
"meta":[
	{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_first","org_column":"name_first","charset":33,"length":120,"flags":0,"decimals":0},
	{"type":253,"catalog":"def","database":"myhttp","table":"names","org_table":"names","column":"name_last","org_column":"name_last","charset":33,"length":120,"flags":0,"decimals":0}
],
"data":[ 
	["Clark","Kent"]
],
"status":[{"server_status":34,"warning_count":0}]
}
]

Deleting a row:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/delete+from+names+where+name_first+=+'Hal'"
{"server_status":34,"warning_count":0,"affected_rows":1,"last_insert_id":0}

Inserting a row:

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/INSERT+INTO+names+(name_first,+name_last)+VALUES+('Hal','Jordan');"
{"server_status":2,"warning_count":0,"affected_rows":1,"last_insert_id":1018}

In a future post, I will show you how to use Perl to connect via HTTP and then parse the results.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.

Advertisements

Using Perl to send tweets stored in a MySQL database to twitter

Twitter is not my favorite social media site. Using twitter is like driving downtown, screaming what you want to say out the window, and hoping someone hears you. There might be tens of thousands of people downtown, but your message will only be heard by a few. Your best bet is to repeat your message as often as possible.

However, twitter is free and if you want to reach as many people (theoretically) as possible, you might as well use it. But sending tweets on a scheduled basis can be a pain. There are client programs available which allow you to schedule your tweets (Hootsuite is one I have used in the past). You can load your tweets in the morning, and have the application tweet for you all day long. But you still have to load the application with your tweets – one by one.

A friend of mine asked me if there was a way to send the same 200 tweets over and over again, spaced out every 20 minutes or so. He has a consulting business, and just wants to build up a list of twitter followers by tweeting inspirational quotes. If he tweets for twenty hours a day, and sends three quotes an hour, it will take him a little more than three days to burn through his 200 quotes. And he can always add more quotes or space out the tweets as necessary. I decided to write a Perl script to do this for him.

To start, we will need a MySQL database to store the tweets. I use MySQL’s Workbench product as my client application for connecting to MySQL. From within Workbench, I can create my tweet database:

CREATE DATABASE 'tweets' /*!40100 DEFAULT CHARACTER SET latin1 */

I will then need a table inside my database to store my tweets.

CREATE TABLE 'tweets' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'tweet' char(140) DEFAULT NULL,
  'last_tweet_date' datetime NOT NULL DEFAULT '2015-01-01 00:00:00',
  'tweet_count' int(5) DEFAULT NULL,
  'tweet_length' int(3) DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1

The tweet messages will be stored in the tweet column, and the last date the tweet was sent will have a time stamp in the last_tweet_date column. When we perform a search to find the next tweet, we will simply sort our search by the last_tweet_date and then id, and limit our output to one tweet. After we send the tweet, we will update the last_tweet_date column and send that tweet to the end of the line. The script will also incrementally change the tweet_count value (number of times the tweet has been sent), and record the length of the tweet in tweet_length. I do not do any error checking in the script to make sure the tweet was sent, but errors are printed.

We now need to insert some tweets into our table. Since my friend is going to be sending inspirational quotes, I found a few I can import. In order to make it easier for importing, all single quote marks () will be replaced by the carat symbol (^). I can then swap these symbols inside the Perl script. You could use the backslash (\) before the single quote, but I prefer a single character substitution so I know how long the tweet will be.

I will also use the tilde (~) as a way to designate a carriage return in my tweet. The Perl script will replace the tilde with a carriage return (\n). Two tildes give me two carriage returns and a blank line.

insert into tweets (tweet) VALUES('I^m not afraid. -Luke~~You will be. -Yoda~~http://SomeWebSiteHere.com');
insert into tweets (tweet) VALUES('Do or do not.  There is no try.~~-Yoda~~http://SomeWebSiteHere.com');
insert into tweets (tweet) VALUES('No, I am your father.~~-Darth~~http://SomeWebSiteHere.com');

I also create a history table to store the tweet identification numbers. Each tweet is assigned a unique number by twitter, and this is how you can access this tweet. I save this information so I can delete the tweets later using this number. I have included a short script for deleting tweets near the end of this post.

CREATE TABLE 'history' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'tweet' char(140) DEFAULT NULL,
  'tweet_id' varchar(30) DEFAULT NULL,
  'tweet_update' datetime DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=1000032 DEFAULT CHARSET=latin1

You will need to register your application with twitter via apps.twitter.com, and obtain the following:

consumer_key
consumer_secret
access_token
access_token_secret

You will also need to register your mobile phone in order to link your twitter account to your application. I have not figured out how to use this script with someone else’s account, as the instructions for scripting Perl for use with twitter are not very thorough. I will try to add this at a later date.

Now that you have your application information and all of your tables created with data inserted, here is the Perl script for sending tweets. You will need to install the necessary Perl modules that are used.


#!/usr/bin/perl
 
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;

my $Database = "tweets";

# ----------------------------------------------------------------------------------
# this has to be near the top - as other parts of the script rely on these figures
# ----------------------------------------------------------------------------------

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time;

$year = $year + 1900;
$mon = $mon + 1;

# add a zero if the value is less than 10

if ($sec < 10) { $sec = "0$sec"; }
if ($min < 10) { $min = "0$min"; }
if ($hour < 10) { $hour = "0$hour"; }
if ($mday < 10) { $mday = "0$mday"; }
if ($mon < 10) { $mon = "0$mon"; }
if ($year < 10) { $year = "0$year"; }
if ($wday < 10) { $wday = "0$wday"; }
if ($yday < 10) { $yday = "0$yday"; }
if ($isdst < 10) { $isdst = "0$isdst"; }

$DateTime = "$year-$mon-$mday $hour:$min:$sec";

# ----------------------------------------------------------------------------------
# retrieve tweet from database
# ----------------------------------------------------------------------------------

     $dbh = ConnectToMySql($Database);
     $query = "select id, tweet, last_tweet_date, tweet_count FROM tweets order by last_tweet_date, id limit 1";	
     $sth = $dbh->prepare($query);
     $sth->execute();
    
          while (@data = $sth->fetchrow_array()) {
            
		 $id = $data[0];
		 $tweet = $data[1];
		 $last_tweet_date = $data[2];
		 $tweet_count = $data[3];
	}

$tweet_original = $tweet;

# ----------------------------------------------------------------------------
# find tildes ~ and substitute for carriage return
# find carats and substitue for single quote
# ----------------------------------------------------------------------------

$tweet =~ s/~/\n/g;
$tweet =~ s/\^/\'/g;

# ----------------------------------------------------------------------------------
# check length of tweet
# ----------------------------------------------------------------------------------

$tweet_length = length($tweet);

if (length($tweet) > 140)

{
	print "Error - tweet is longer than 140 characters\n";
	exit;
}

# add to the tweet count
$tweet_count++;

# ----------------------------------------------------------------------------
# send tweet
# ----------------------------------------------------------------------------

my $nt = Net::Twitter::Lite::WithAPIv1_1->new(
      traits              => [qw/API::RESTv1_1/],
      consumer_key        => "$consumer_key",
      consumer_secret     => "$consumer_secret",
      access_token        => "$access_token",
      access_token_secret => "$access_token_secret",
      ssl                 => 1
);

my $results = eval { $nt->update("$tweet") };
 
  if ( my $err = $@ ) {
      die $@ unless blessed $err && $err->isa('Net::Twitter::Lite::Error');

      warn "HTTP Response Code: ", $err->code, "\n",
           "HTTP Message......: ", $err->message, "\n",
           "Twitter error.....: ", $err->error, "\n";
  } 

# ----------------------------------------------------------------------------
# update mysql with new date for last_tweet date/time
# ----------------------------------------------------------------------------

$dbh = ConnectToMySql($Database);
$query = "UPDATE tweets SET last_tweet_date = '$DateTime' , tweet_count = '$tweet_count' , tweet_length = '$tweet_length' where id = '$id'";
$sth = $dbh->prepare($query);
$sth->execute();

# ----------------------------------------------------------------------------
# get the status id of the last tweet
# ----------------------------------------------------------------------------

my $statuses = $nt->user_timeline({ user => "2044_The_Book", count=> 1 });

for my $status ( @$statuses ) {
	$tweet_id = "$status->{id}";
}

if ( my $err = $@ ) {
      die $@ unless blessed $err && $err->isa('Net::Twitter::Lite::Error');

      warn "HTTP Response Code: ", $err->code, "\n",
           "HTTP Message......: ", $err->message, "\n",
           "Twitter error.....: ", $err->error, "\n";
} 

# ----------------------------------------------------------------------------
# replace special characters
# ----------------------------------------------------------------------------

$tweet =~ s/\\\n/~/g;
$tweet =~ s/\'/^/g;

# update mysql with new date for last_tweet date/time

$dbh = ConnectToMySql($Database);	
$query = "insert into history (tweet,tweet_id,tweet_update) values ('$tweet_original','$tweet_id','$DateTime')";
$sth = $dbh->prepare($query);
$sth->execute();

#----------------------------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

   my ($db) = @_;

   open(PW, "<..\/accessTweets") || die "Can't access login credentials";
   my $db= <PW>;
   my $host= <PW>;
   my $userid= <PW>;
   my $passwd= <PW>;

   chomp($db);
   chomp($host);
   chomp($userid);
   chomp($passwd);
   
   my $connectionInfo="dbi:mysql:$db;$host:3306";
   close(PW);

   # make connection to database
   my $l_dbh = DBI->connect($connectionInfo,$userid,$passwd);
   return $l_dbh;

}



In the subroutine ConnectToMySql, I store the MySQL login credentials in a text file one directory below where my Perl script is located. This file contains this information:

database_name
hostname or IP
MySQL user name
password

You can include your information inside the file if you prefer.

If you want to delete your tweets, you can create a script to access the tweets in your history table, and then delete them one at a time. Here is an example without the database connections:


#!/usr/bin/perl
 
use Net::Twitter::Lite::WithAPIv1_1;
use DBI;
use DBD::mysql;

# ----------------------------------------------------------------------------
# delete tweet
# ----------------------------------------------------------------------------

# replace the values for $consumer_key $consumer_secret $access_token $access_token_secret
# with your values for your application

my $nt = Net::Twitter::Lite::WithAPIv1_1->new(
      consumer_key        => "$consumer_key",
      consumer_secret     => "$consumer_secret",
      access_token        => "$access_token",
      access_token_secret => "$access_token_secret",
      ssl                 => 1,
	 );

my $statuses = $nt->destroy_status({ id => "$tweet_id" });

exit;

Be sure to replace the value of $tweet_id with the value from the tweet you want to delete.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.