WEEK NINE

After trying multiple options for getting the right regexes for our filter, I finally got the correct regular expressions for concatenating an infinite number of strings. An equal regex was written to match the TOP N pattern of SQL Server syntax.
For string concatenation, I wrote and tested the following regular expression.

"[^"]*"(?: *\+ *"[^"]*")+

The above regex matches string concatenation and can be tested online HERE.
Now lets explain the regex:

"[^"]*"(?: *\+ *"[^"]*")+/g
  •  matches the characters  literally
  • [^"]* match a single character not present in the list below
    • Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]
    •  a single character in the list  literally (case sensitive)
  •  matches the characters  literally
  • (?: *\+ *"[^"]*")+ Non-capturing group
    • Quantifier: + Between one and unlimited times, as many times as possible, giving back as needed [greedy]
    •  * matches the character   literally
      • Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]
    • \+ matches the character + literally
    • * matches the character   literally
      • Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]
    •  matches the characters  literally
    • [^"]* match a single character not present in the list below
      • Quantifier: * Between zero and unlimited times, as many times as possible, giving back as needed [greedy]
      •  a single character in the list  literally (case sensitive)
    •  matches the characters  literally
  • g modifier: global. All matches (don’t return on first match)

For the TOP N regex, I wrote a simple regex
“top”\d

Now We will improve our program to use these new regular expressions to match and rewrite the queries accordingly.

For string concatenation, we save the matched string and substitute all plus (+) with commas (,). Then  next we wrap this new string in CONCAT(new_string).  This is further substituted in the original query to have our new syntax for string concatenation.

For the TOP N syntax a simple algorithm is match the pattern and save it in a variable. replace the matched position with a white space and substitute TOP with LIMIT. Our new string is appended to the end of the new query to get our new results filtering syntax.

WEEK 7 & 8

We wrote some expressions code for our string concatenation example.
We will limit the number of strings to just two for now just to get it working and will later improve to support multiple string concatenations.
In MS SQL Server string concatenation is done using the + operator on the strings being concatenated and the result is the first string being appended to the second.

Example

 
    SELECT    "string a" + "string b"; 

will result in strinastrinb.

The regex for concatenating two strings is

"([^"]+)" + "([^"]+)"

Our standalone program will compile this regex and match it against the input SQL query and replace the matched pattern with  MariaDB  CONCAT() function.
A snippet of the standalone program is iven below

#define QUERYLENGTH 100
#define PCRE2_CODE_UNIT_WIDTH 8
#include <my_global.h>
#include <mysql.h>
#include <string.h>
#include <pcre2.h>

void finish_with_error(MYSQL *con)
{
 fprintf(stderr, "%s\n", mysql_error(con));
 mysql_close(con);
 exit(1); 
}

void fetch_and_print_reselts(MYSQL *con){
 MYSQL_RES *result = mysql_store_result(con);
 
 if (result == NULL) 
 {
 finish_with_error(con);
 }

int num_fields = mysql_num_fields(result);

MYSQL_ROW row;
 
 while ((row = mysql_fetch_row(result))) 
 { 
 for(int i = 0; i < num_fields; i++) 
 { 
 printf("%s ", row[i] ? row[i] : "NULL"); 
 } 
 printf("\n"); 
 }
 }

void init_connection(MYSQL *con){
 if (con == NULL) 
 {
 fprintf(stderr, "%s\n", mysql_error(con));
 exit(1);
 }
 }

int main(int argc, char **argv)
{
 /* holds our sql query*/

char query[QUERYLENGTH];

MYSQL *con = mysql_init(NULL);

/* Initialise connection object*/
 init_connection(con);

if (mysql_real_connect(con, "localhost", "root", "root", 
 NULL, 0, NULL, 0) == NULL) 
 {
 fprintf(stderr, "%s\n", mysql_error(con));
 mysql_close(con);
 exit(1);
 }

while(fgets(query, QUERYLENGTH , stdin) != NULL)
 {
 if (mysql_query(con, query)) 
 {
 fprintf(stderr, "%s\n", mysql_error(con));
 mysql_close(con);
 exit(1);
 }
 printf("%s\n", query);
 
 fetch_and_print_reselts(con);
 }
 mysql_close(con);
 exit(0);
}

 

WEEK FIVE & SIX

During week 5 and 6, I did some research on Regular expression flavors with more focus on the PCRE2 library. We have been able to setup a functional filter and its time we start adding support for our regex patterns to the filter.  First our filter only has the string concatenation example and we need to make this better by hard coding it in the filter such that we won’t need any configurations. An approach is to write a stand alone C program that uses the PCRE2 library and accepts SQL queries from stdin and executes them against a MariaDB server. This program equally uses the PCRE2 library to match our concatenation regex  against the SQL query and does the necessary substitution. With this setup, we can subsequenty add support for other regex patterns that need to be matched as well. So our next milestone is to write this stand alone program which we will later modify to add into our filter queryRoute method.

WEEK THREE & FOUR

After creating  a filter skeleton for our filter, I took a look at the regex filter which I used to implement the methods we earlier defined for our filter skeleton. Much of the code was borrowed from the regex filter. The commits can be found on Github here The Ideas is to have a functional copy of the regex filter which we will modify in the next milestone  to parse SQL Server syntax and replace it with MariaDB variant.
However, I will be writing my end of semester Exams in the upcoming  days so I am not sure of any significant activity on this project during my Exams week.

I Also took a look at the pcre2  Library for regular expession functions. Maxscale rewrites some of these functions as wrappers in server/core/maxscale_pcre2.c. A sample function is :

mxs_pcre2_result_t mxs_pcre2_substitute(pcre2_code *re, const char *subject, const char *replace,
 char** dest, size_t* size)

which is a wrapper function to the invocation of pcre2_substitute for replacing all occurrences of a pattern with the provided replacement and places the end result into @c dest parameter.

It is always good idea to start writing tests early so I equally created a tests directory for our mssqlserverfilter and added a simple test configuration to it for the concatenation syntax.
The Sql server query  for concatenating strings was executed on our virtual machine instance before adding it t the test input file. More test cases will be covered subsequently.
the commits can be found here. and the complete branch here.

WEEK TWO

After Building Maxscale from source and setting up a MS SQL Server instance on a Windows server VM, I studied the filter API to understand the interface used to develop new filters. I equally took a look at how the API is used in implementing some of the filters that come packaged with Maxscale.
I have started work on our new filter where I have added an incantation of the filter in the source. This involves declaring the necessary structures for filter incantation and definition in the source tree. All the commits can be found here.

Methods like

static FILTER *createInstance(char **options, FILTER_PARAMETER **params);
static void *newSession(FILTER *instance, SESSION *session);
static void closeSession(FILTER *instance, void *session);
static void freeSession(FILTER *instance, void *session);
static void setDownstream(FILTER *instance, void *fsession, DOWNSTREAM *downstream);
static int routeQuery(FILTER *instance, void *fsession, GWBUF *queue);
static void diagnostic(FILTER *instance, void *fsession, DCB *dcb);

were declared and defined for our filter as specified by the filter API.

I also added the new filter to CMakeLists.txt to make sure it is build by adding the following code :

   add_library(mssqlserverfilter SHARED mssqlserverfilter.c)
   target_link_libraries(mssqlserverfilter maxscale-common)
   add_dependencies(mssqlserverfilter pcre2)
   set_target_properties(mssqlserverfilter PROPERTIES VERSION "1.0.0")
   install(TARGETS mssqlserverfilter DESTINATION ${MAXSCALE_LIBDIR})

This was build successfully locally and changes pushed to Github succeeded Travis build as well. Our next goal is to start implementing these methods  for the next milestone

Week One

Coding for Google Summer of Code 2016 has officially start and so lets start building our filter.
I have forked and cloned the Maxscale 1.4.3 branch which I will be basing my work on in creating the filter this summer. I have also created a new branch called MXS-1  to correspond with the jira task to which I will be comitting my changes.
But first Lets write down some test queries that won’t work on MariaDB/MySQL but run on MS Server to highlight the differences between the two syntaxes.

Basically, MS Server syntax is very similar to MariaDB syntax but allows from some additional clauses which MariaDB does not support. This allows for many possible queries with different syntax that can be written for MS SQL Server and which will not work on MariaDB.

=====SELECT====

The select Query has a very complex syntax but we will limit our scope for now to the basic select queries below which are guaranteed to fail on MariaDB but run on MS SQL Server.

             SELECT    TOP    10   *    FROM    TestTable    WHERE    id=12;

             SELECT (‘MS’ + ‘SQL’ + ‘Tips’)

             SELECT     NEWID();

             SELECT     *     from     [tablename];

             SELECT    *     FROM    FactResellerSales OPTION ( LABEL = ‘q17’ );

===INSERT===

             INSERT    top(5)    INTO    tTable1 SELECT   *   FROM tTable2;

===UPDATE===

 

===DELETE===

Also, we will need a MS SQL server instance running on a windows server so we can test our queries. Virtual machine images can be downloaded Here.

Installation

Extract the zip file downloaded to get the .ova file bundled in it.

Now start virtualbox,

go to File -> Import Appliance in the menu bar.

Click the “Open Appliance” button to select your OVA file. Once you have selected the file, click “Next”.

The next window will show you the configuration of the current virtual appliance. You can scroll through the configuration list and double click on any item (or check/uncheck the box) to make changes to it. Lastly, click “Import”.

Now we have our virtual machine up in virtualbox and we can go ahead and start it.

[Maxscale Filter] Community Bonding

During Community bonding with MariaDB as part of Google Sumer of Code 2016. I will be working on  setting up a working environment for the MaxScale filter project. The first step before starting work was to install and configure MaxScale. This post will give a general idea about the MaxScale configuration. Currently, Maxscale is not supported on OS X but most of the latest Linux distributions is just fine. I will be setting up Maxscale on CentOS7.

MariaDB MaxScale works with almost all versions of MariaDB and MYSQL.  I will be basing my work on MariaDB 10.1.

Installing MariaDB on CentOS7

Installing MariaDB on CentOS and most other rpm distributions is easy and straight forward,

     yum install mariadb-server mariadb-client

The above command will install both the client and server for you.

After Installation

After the installation completes, start MariaDB with:

sudo systemctl start mariadb

Then you can setup security to harden your installation by running the script

           mysql_secure_installation

This will assist in setting up a password for your installation.

Now we have MariaDB installed and server started. Lets go ahead and install Maxscale

Maxscale Installation from Source

In this post, i will describe how i build Maxscale from source on a fresh installation of Cent OS 7.

 

Now clone the GitHub project to your machine either via the web interface, your favorite graphical interface or the git command line

$ git clone https://github.com/mariadb-corporation/MaxScale
Cloning into 'MaxScale'...

This will create a Maxscale folder with the source code, now we are set to start building.

In order to build Maxscale , we will need a C compiler installed as well as some build tools and libraries. The following command should get all of them installed.

               sudo yum install mariadb-devel mariadb-embedded-devel libedit-                        devel gcc gcc-c++ ncurses-devel bison flex glibc-devel cmake libgcc                     perl make libtool openssl-devel libaio libaio-devel librabbitmq-devel                     libcurl-devel pcre-devel rpm-build

output:
Install 1 Package
Upgrade 2 Packages (+4 Dependent packages)

Total download size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
updates/7/x86_64/prestodelta | 500 kB 00:00:06
Delta RPMs reduced 899 k of updates to 101 k (88% saved)
(1/7): pcre-8.32-15.el7_8.32-15.el7_2.1.x86_64.drpm | 62 kB 00:00:01
(2/7): pcre-devel-8.32-15.el7_8.32-15.el7_2.1.x86_64.drpm | 39 kB 00:00:03
(3/7): glibc-devel-2.17-106.el7_2.6.x86_64.rpm | 1.0 MB 00:00:31
(4/7): libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm | 32 kB 00:00:18
(5/7): glibc-headers-2.17-106.el7_2.6.x86_64.rpm | 662 kB 00:00:20
(6/7): glibc-2.17-106.el7_2.6.x86_64.rpm | 3.6 MB 00:01:03
(7/7): glibc-common-2.17-106.el7_2.6.x86_64.rpm | 11 MB 00:05:05
——————————————————————————————————————————————————
Total 57 kB/s | 17 MB 00:05:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Updating : glibc-2.17-106.el7_2.6.x86_64 1/13
Updating : glibc-common-2.17-106.el7_2.6.x86_64 2/13
Updating : glibc-headers-2.17-106.el7_2.6.x86_64 3/13
Updating : pcre-8.32-15.el7_2.1.x86_64 4/13

Verifying : glibc-devel-2.17-106.el7_2.4.x86_64 12/13
Verifying : glibc-2.17-106.el7_2.4.x86_64 13/13

Installed:
libedit-devel.x86_64 0:3.0-12.20121213cvs.el7

Updated:
glibc-devel.x86_64 0:2.17-106.el7_2.6 pcre-devel.x86_64 0:8.32-15.el7_2.1

Dependency Updated:
glibc.x86_64 0:2.17-106.el7_2.6 glibc-common.x86_64 0:2.17-106.el7_2.6 glibc-headers.x86_64 0:2.17-106.el7_2.6 pcre.x86_64 0:8.32-15.el7_2.1

Complete!

 

The command should install the basic tools needed and in my case as seen on the output, I  have them installed so lets go ahead and try to build Maxscale.

cd Maxscale
   mkdir build
   cd build

Maxscale uses out of source build so

cmake ../

Output:

[maxscale@localhost build]$ cmake ../
— CMake version: 2.8.11
— The C compiler identification is GNU 4.8.5
— The CXX compiler identification is GNU 4.8.5
— Check for working C compiler: /usr/bin/cc
— Check for working C compiler: /usr/bin/cc — works
— Detecting C compiler ABI info
— Detecting C compiler ABI info – done
— Check for working CXX compiler: /usr/bin/c++
— Check for working CXX compiler: /usr/bin/c++ — works
— Detecting CXX compiler ABI info
— Detecting CXX compiler ABI info – done
— Looking for include file arpa/inet.h
— Looking for include file arpa/inet.h – found

……………………………………………………………………………
……………………………………………………………………………..

— MySQL provider: MariaDB
— Looking for pcre_stack_guard in MYSQL_EMBEDDED_LIBRARIES_STATIC-NOTFOUND
CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
MYSQL_EMBEDDED_LIBRARIES_STATIC
linked by target “cmTryCompileExec1784562683” in directory /home/maxscale/Desktop/MaxScale/build/CMakeFiles/CMakeTmp

CMake Error: Internal CMake error, TryCompile configure of cmake failed
— Looking for pcre_stack_guard in MYSQL_EMBEDDED_LIBRARIES_STATIC-NOTFOUND – not found
— PCRE libs: /usr/lib64/libpcre.so
— PCRE include directory: /usr/include
— Embedded mysqld does not have pcre_stack_guard, linking with system pcre.
CMake Error at cmake/FindMySQL.cmake:115 (message):
Library not found: libmysqld. If your install of MySQL is in a non-default
location, please provide the location with -DMYSQL_EMBEDDED_LIBRARIES=<path
to library>
Call Stack (most recent call first):
CMakeLists.txt:37 (find_package)
— Configuring incomplete, errors occurred!

 

As seen in the output above the build was unsuccessful because we could not link against libmysqld. So how do we fix this?

We will build the library from source and retry building Maxscale source and linking against our non default installation of library.

Lets  download the Library and extract it as follows:

1  wget –content-disposition https://downloads.mariadb.org/f/mariadb-                 10.0.22/bintar-linux-glibc_214-x86_64/mariadb-10.0.22-linux-glibc_214-x86_64.tar.gz/from/http%3A/mirror.netinch.com/pub/mariadb/?serve

2  tar -axf mariadb-10.0.22-linux-glibc_214-x86_64.tar.gz

Now with our library downloaded and extracted to our system, lets locate the library location and copy the path to it.

from the terminal, type

locate libmysqld

Output:

[maxscale@localhost mariadb-10.0.22-linux-x86_64]$ locate libmysqld
/home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a
/home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.so
/home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.so.18
/usr/lib64/mysql/libmysqld.so
/usr/lib64/mysql/libmysqld.so.18
[maxscale@localhost mariadb-10.0.22-linux-x86_64]$

As seen on the output, our library is located at  /home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a

This is the path we need to pass to our build rule so lets retry and build Maxscale.

First, lets clean the old build by

rm CMakeCache.txt   or by deleting the build directory and recreating it.

So
cd build,

cmake ../DMYSQL_EMBEDDED_LIBRARIES=/home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a

Output:
— CMake version: 2.8.11
— The C compiler identification is GNU 4.8.5
— The CXX compiler identification is GNU 4.8.5
— Check for working C compiler: /usr/bin/cc
— Check for working C compiler: /usr/bin/cc — works
— Detecting C compiler ABI info
— Detecting C compiler ABI info – done
— Check for working CXX compiler: /usr/bin/c++
— Check for working CXX compiler: /usr/bin/c++ — works
— Detecting CXX compiler ABI info
— Detecting CXX compiler ABI info – done
— Looking for include file arpa/inet.h

— Looking for include file sys/time.h – found
— Looking for include file sys/types.h
— Looking for include file sys/types.h – found
— Looking for include file sys/un.h
— Looking for include file sys/un.h – found
— Looking for include file time.h
— Looking for include file time.h – found
— Looking for include file unistd.h
— Looking for include file unistd.h – found
— RabbitMQ library not found.
— Found OpenSSL: /usr/lib64/libssl.so;/usr/lib64/libcrypto.so (found version “1.0.1e”)
— Valgrind found: /usr/bin/valgrind
— Found dynamic MySQL client library: /usr/lib64/mysql/libmysqlclient.so
— Static MySQL client library not found.
— Looking for LIBMARIADB
— Looking for LIBMARIADB – not found
— Found mysql_version.h: /usr/include/mysql/mysql_version.h
— MySQL version: 5.5.47
— MySQL provider: MariaDB
— Looking for pcre_stack_guard in /home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a
— Looking for pcre_stack_guard in /home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a – found
— Using embedded library: /home/maxscale/Desktop/mariadb-10.0.22-linux-x86_64/lib/libmysqld.a
— Using MySQL headers found at: /usr/include/mysql
— Using errmsg.sys found at: /usr/share/mysql/english/errmsg.sys
— Pandoc not found.
— Found libtcmalloc: /usr/lib64/libtcmalloc.so.4
— Could not find libjemalloc, using system default malloc instead.
— Found Git: /usr/bin/git (found version “1.8.3.1”)
— Found CURL: /usr/lib64/libcurl.so (found version “7.29.0”)
— RabbitMQ library not found.
— PCRE2 libs: /usr/local/lib/libpcre2-8.so
— PCRE2 include directory: /usr/local/include
— Building MariaDB Connector-C from source.
— Found git 1.8.3.1
— Commit ID: 4c025ccba9d90ec3f06e5fe3d0bd5ee685ec2cf5
— C Compiler supports: -Werror=format-security
— C Compiler supports: -Wno-unused-but-set-variable
— Using C99 standard
CMake Warning at server/modules/filter/CMakeLists.txt:9 (message):

— Found FLEX: /usr/bin/flex (found version “2.5.37”)
— Found BISON: /usr/bin/bison (found version “2.7”)
— Building MaxAdmin with editline: /usr/lib64/libedit.so
— Installing maxscale.conf to: /etc/ld.so.conf.d
— Installing startup scripts to: /etc/init.d
— Installing systemd service files to: /usr/lib/systemd/system
— Found Doxygen: /usr/bin/doxygen (found version “1.8.5”)
— Configuring done
— Generating done
— Build files have been written to: /home/maxscale/Desktop/MaxScale/build

 

Now our configure is successful and Maxscale is ready to be compiled.

Now run the following command to compile Maxscale.
make

Output

[ 1%] Performing download step (git clone) for ‘connector-c’
Cloning into ‘connector-c’…
remote: Counting objects: 5195, done.
remote: Total 5195 (delta 0), reused 0 (delta 0), pack-reused 5195
Receiving objects: 100% (5195/5195), 3.84 MiB | 88.00 KiB/s, done.
Resolving deltas: 100% (3797/3797), done.
Note: checking out ‘7fd72dfe3e5b889b974453b69f99c2e6fd4217c6’.

You are in ‘detached HEAD’ state. You can look around, make experimental
changes and commit them, and you can discard any commits you make in this
state without impacting any branches by performing another checkout.

If you want to create a new branch to retain commits you create, you may
do so (now or later) by using -b with the checkout command again. Example:

git checkout -b new_branch_name

HEAD is now at 7fd72df… Make sure that on windows we include iconv.h from win-iconv, not a system one
[ 1%] No patch step for ‘connector-c’
[ 2%] No update step for ‘connector-c’
[ 3%] Performing configure step for ‘connector-c’
— The C compiler identification is GNU 4.8.5
— Check for working C compiler: /usr/bin/cc
— Check for working C compiler: /usr/bin/cc — works

………………………………………………………………………………….
………………………………………………………………………………….
[ 93%] Built target topfilter
Scanning dependencies of target hintfilter
[ 94%] Building C object server/modules/filter/hint/CMakeFiles/hintfilter.dir/hintfilter.c.o
[ 95%] Building C object server/modules/filter/hint/CMakeFiles/hintfilter.dir/hintparser.c.o
Linking C shared library libhintfilter.so
[ 95%] Built target hintfilter
[ 96%] [FLEX][token] Building scanner with flex 2.5.37
[ 96%] [BISON][ruleparser] Building parser with bison 2.7
Scanning dependencies of target dbfwfilter
[ 97%] Building C object server/modules/filter/dbfwfilter/CMakeFiles/dbfwfilter.dir/dbfwfilter.c.o
[ 97%] [BISON][ruleparser] Building parser with bison 2.7
[ 98%] Building C object server/modules/filter/dbfwfilter/CMakeFiles/dbfwfilter.dir/ruleparser.c.o
[ 98%] Building C object server/modules/filter/dbfwfilter/CMakeFiles/dbfwfilter.dir/token.c.o
Linking C shared library libdbfwfilter.so
[ 98%] Built target dbfwfilter
Scanning dependencies of target maxadmin
[100%] Building C object client/CMakeFiles/maxadmin.dir/maxadmin.c.o
Linking C executable maxadmin
[100%] Built target maxadmin

Now our Maxscale build is successful yay!!!

Lets go ahead and install it,

make install