ivanstoykov

I needed to test the new SkySQL server audit plug-in 1.1.7 on latest MariaDB 10.0.10
More info about that handy plug in you may find here : http://www.skysql.com/downloads/mariadb-audit-plugin
Take a moment to read the info, along with that nice blog : http://www.skysql.com/blogs/ralf-gebhardt/activating-auditing-mariadb-an...
Then I decided to test the CONNECT engine install as I have a friend who needs to import unknown DB data exported into XML.
And I was interested to import the Skype sqlite3 database into MySQL... :)
More about that MariaDB engine you may find here : https://mariadb.com/kb/en/connect/

So.
The test was done on CentOS 6.5 minimal install.
I will go step by step from the start.

1. Configure the MariaDB repository for CentOS 6.5, the best way to do it the the MariaDB repository configuration tool:
https://downloads.mariadb.org/mariadb/repositories/
Check the CentOS link, then choose the release, in my case CentOS 6 (64 bit), then the MariaDB version : 10.0
As a direct link :
https://downloads.mariadb.org/mariadb/repositories/#mirror=nucleus&distr...

In short: the content of the MariaDB.repo should be as follows:

cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2014-05-05 10:31 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

1. Install the MariaDB 10.0.10:

root@centos-6.5-minimal:[Mon May 05 12:49:52][~]$ yum search Mariadb
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: sunsite.rediris.es
 * extras: sunsite.rediris.es
 * updates: sunsite.rediris.es
================================= N/S Matched: Mariadb ==============================
MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-oqgraph-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server

  Name and summary matches only, use "search all" for everything.

root@centos-6.5-minimal:[Mon May 05 12:51:17][~]$ yum install MariaDB-server MariaDB*engine MariaDB-client 
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.atosworldline.com
 * extras: sunsite.rediris.es
 * updates: sunsite.rediris.es
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-cassandra-engine.x86_64 0:10.0.10-1.el6 will be installed
---> Package MariaDB-client.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.0.10-1.el6.x86_64
---> Package MariaDB-connect-engine.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: libodbc.so.2()(64bit) for package: MariaDB-connect-engine-10.0.10-1.el6.x86_64
---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64
---> Package MariaDB-server.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: perl(DBI) for package: MariaDB-server-10.0.10-1.el6.x86_64
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.0.10-1.el6.x86_64
---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
---> Package unixODBC.x86_64 0:2.2.14-12.el6_3 will be installed
--> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-2.2.14-12.el6_3.x86_64
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.0.10-1.el6 will be obsoleting
---> Package MariaDB-oqgraph-engine.x86_64 0:10.0.10-1.el6 will be installed
--> Processing Dependency: libJudy.so.1()(64bit) for package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64
---> Package libtool-ltdl.x86_64 0:2.2.6-15.5.el6 will be installed
---> Package mysql-libs.x86_64 0:5.1.73-3.el6_5 will be obsoleted
--> Finished Dependency Resolution
Error: Package: MariaDB-oqgraph-engine-10.0.10-1.el6.x86_64 (mariadb)
           Requires: libJudy.so.1()(64bit)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

root@centos-6.5-minimal:[Mon May 05 12:53:42][~]$ yum whatprovides *libJudy
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.ircam.fr
 * extras: distrib-coffee.ipsl.jussieu.fr
 * updates: mir01.syntis.net
base/filelists_db                                                                                                                                                             | 5.9 MB     00:11     
extras/filelists_db                                                                                                                                                           |  11 kB     00:00     
mariadb/filelists_db                                                                                                                                                          |  56 kB     00:00     
updates/filelists_db                                                                                                                                                          | 1.7 MB     00:03     
No Matches found
root@centos-6.5-minimal:[Mon May 05 12:54:20][~]$

So, it seems the MariaDB-oqgraph-engine-10.0.10-1 needs the libJudy which is missing at CentOS minimal install defaut repos.
At https://mariadb.atlassian.net/browse/MDEV-5664 there is detailed explanation, in short you have to set the epel repo.
How to set the Epel CentOS 6.5 repo:

root@centos-6.5-minimal:[Mon May 05 13:01:59][/etc/yum.repos.d]$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
root@centos-6.5-minimal:[Mon May 05 13:02:14][/etc/yum.repos.d]$ rpm -Uvh epel-release-6-8.noarch.rpm
root@centos-6.5-minimal:[Mon May 05 13:02:29][/etc/yum.repos.d]$ yum repolist
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/metalink                                                                                                                                                                 |  25 kB     00:00     
 * base: sunsite.rediris.es
 * epel: ftp.cica.es
 * extras: ftp.rezopole.net
 * updates: centos.quelquesmots.fr
epel                                                                                                                                                                          | 4.4 kB     00:00     
epel/primary_db                                                                                                                                                               | 6.1 MB     00:09     
repo id                                                                     repo name                                                                                                          status
base                                                                        CentOS-6 - Base                                                                                                     6,367
epel                                                                        Extra Packages for Enterprise Linux 6 - x86_64                                                                     10,762
extras                                                                      CentOS-6 - Extras                                                                                                      14
mariadb                                                                     MariaDB                                                                                                                12
updates                                                                     CentOS-6 - Updates 

Now, install libJudy:

root@centos-6.5-minimal:[Mon May 05 13:05:44][/etc/yum.repos.d]$ yum install Judy

Then install the MariaDB server, client and the available engines:

root@centos-6.5-minimal:[Mon May 05 13:06:12][/etc/yum.repos.d]$ yum install MariaDB-server MariaDB*engine MariaDB-client 

Start the MariaDB 10.0.10 MySQL service, check the installed engines and the configured plug-ins directory:

root@centos-6.5-minimal:[Mon May 05 13:13:53][/etc/yum.repos.d]$ mysql 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2 "No such file or directory")
root@centos-6.5-minimal:[Mon May 05 13:15:38][/etc/yum.repos.d]$ service mysql 
Usage: mysql  {start|stop|restart|reload|force-reload|status|configtest}  [ MySQL server options ]
root@centos-6.5-minimal:[Mon May 05 13:15:47][/etc/yum.repos.d]$ service mysql  status
 ERROR! MySQL is not running
root@centos-6.5-minimal:[Mon May 05 13:15:53][/etc/yum.repos.d]$ service mysql  start
Starting MySQL. SUCCESS! 
root@centos-6.5-minimal:[Mon May 05 13:16:06][/etc/yum.repos.d]$ mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.10-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql root@centos-6.5-minimal:[Mon May  5 13:16:08 2014][(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
| OQGRAPH            | YES     | Open Query Graph Computation Engine (http://openquery.com/graph)           | NO           | NO   | NO         |
| CASSANDRA          | YES     | Cassandra storage engine                                                   | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
13 rows in set (0.00 sec)

mysql root@centos-6.5-minimal:[Mon May  5 13:16:30 2014][(none)]> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.02 sec)

As you can see above, the OQGRAPH and CONNECT engines are available now.

So, how to test the CONNECT engine sqlite3 db handler?
First, take a look at https://mariadb.com/kb/en/connect-table-types-odbc-table-type-accessing-... page
In short, you need to setup the unixODBC driver for the CONNECT engine sqlite3 handler.

How to setup sqliute3 unixODBC driver on CentOS 6.5 minimal install:
1. Check the existing odbc setup :

root@centos-6.5-minimal:[Mon May 05 13:29:16][~]$ cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description	= ODBC for PostgreSQL
Driver		= /usr/lib/psqlodbc.so
Setup		= /usr/lib/libodbcpsqlS.so
Driver64	= /usr/lib64/psqlodbc.so
Setup64		= /usr/lib64/libodbcpsqlS.so
FileUsage	= 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc5.so
Setup		= /usr/lib/libodbcmyS.so
Driver64	= /usr/lib64/libmyodbc5.so
Setup64		= /usr/lib64/libodbcmyS.so
FileUsage	= 1
root@centos-6.5-minimal:[Mon May 05 13:29:22][~]$ odbcinst -q -d
[PostgreSQL]
[MySQL]

It seems the sqlite3 unixODBC is missing:

root@centos-6.5-minimal:[Mon May 05 13:30:38][~]$ locate libsqlite
/usr/lib64/libsqlite3.so.0
/usr/lib64/libsqlite3.so.0.8.6
root@centos-6.5-minimal:[Mon May 05 13:32:46][~]$ yum search sqlite | grep -i odbc

We find nothing.

So the good old way /my preferred one/ to do it is to compile the sqlite3 unixODBC driver as explained at http://www.ch-werner.de/sqliteodbc/ :

root@centos-6.5-minimal:[Mon May 05 13:33:37][/opt/installs]$ wget http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.996.tar.gz
root@centos-6.5-minimal:[Mon May 05 13:33:41][/opt/installs]$ tar xvf sqliteodbc-0.996.tar.gz
root@centos-6.5-minimal:[Mon May 05 13:33:50][/opt/installs]$ cd sqliteodbc-0.996
root@centos-6.5-minimal:[Mon May 05 13:34:22][/opt/installs/sqliteodbc-0.996]$ ./configure 
checking build system type... x86_64-redhat-linux-gnu
checking host system type... x86_64-redhat-linux-gnu
checking for gcc... no
checking for cc... no
checking for cc... no
checking for cl... no
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details.

As expected, the minimal install of the CentOS 6.5 does not have the needed gcc make etc dev tools.
How to install the development environment:

root@centos-6.5-minimal:[Mon May 05 13:34:35][/opt/installs/sqliteodbc-0.996]$ yum groupinstall 'Development Tools'
..
...
Install     104 Package(s)

Total download size: 93 M
Installed size: 272 M
Is this ok [y/N]: y
Complete! 
root@centos-6.5-minimal:[Mon May 05 13:54:57][/opt/installs/sqliteodbc-0.996]$ ./configure 
.
..
checking for sqlite3_close_v2 in -lsqlite3... no
no
configure: WARNING: SQLite4 header file and source not found
configure: error: No usable SQLite header/library on this system

Here the issue is the missing sqlite3 devel headers. This can be fixed by installing the sqlite-devel package with yum:

root@centos-6.5-minimal:[Mon May 05 13:55:33][/opt/installs/sqliteodbc-0.996]$ yum install sqlite-devel
Installed:
  sqlite-devel.x86_64 0:3.6.20-1.el6                                                                                                                                                                 

Complete!
root@centos-6.5-minimal:[Mon May 05 13:56:53][/opt/installs/sqliteodbc-0.996]$ ./configure 
.
..
checking for ODBC headers and libraries... no
configure: error: ODBC header files and/or libraries not found

New issue : ODBC header files are missing. Again, fix that by installing unixODBC-devel package:

root@centos-6.5-minimal:[Mon May 05 13:57:11][/opt/installs/sqliteodbc-0.996]$ yum install unixODBC-devel
root@centos-6.5-minimal:[Mon May 05 13:58:11][/opt/installs/sqliteodbc-0.996]$ ./configure 
..
checking for gmtime_r... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating sqliteodbc.spec
config.status: creating debian/changelog
root@centos-6.5-minimal:[Mon May 05 13:58:29][/opt/installs/sqliteodbc-0.996]$ make -j3 

gcc -shared  .libs/sqlite3odbc.o  -lsqlite3 -ldl  -Wl,-soname -Wl,libsqlite3odbc-0.996.so -o .libs/libsqlite3odbc-0.996.so
(cd .libs && rm -f libsqlite3odbc.so && ln -s libsqlite3odbc-0.996.so libsqlite3odbc.so)
ar cru .libs/libsqlite3odbc.a  sqlite3odbc.o
ranlib .libs/libsqlite3odbc.a
creating libsqlite3odbc.la
(cd .libs && rm -f libsqlite3odbc.la && ln -s ../libsqlite3odbc.la libsqlite3odbc.la)
root@centos-6.5-minimal:[Mon May 05 13:59:24][/opt/installs/sqliteodbc-0.996]$ make -j3  install

Check the UnixUDBC recent setup:

root@centos-6.5-minimal:[Mon May 05 13:59:59][/opt/installs/sqliteodbc-0.996]$ odbcinst -q -d
[PostgreSQL]
[MySQL]

Add the SQLite driver:

root@centos-6.5-minimal:[Mon May 05 14:00:27][/opt/installs/sqliteodbc-0.996]$ nano /etc/odbcinst.ini
root@centos-6.5-minimal:[Mon May 05 14:00:53][/opt/installs/sqliteodbc-0.996]$  odbcinst -q -d
[PostgreSQL]
[MySQL]
[SQLite]
root@centos-6.5-minimal:[Mon May 05 14:01:52][/opt/installs/sqliteodbc-0.996]$ cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description	= ODBC for PostgreSQL
Driver		= /usr/lib/psqlodbc.so
Setup		= /usr/lib/libodbcpsqlS.so
Driver64	= /usr/lib64/psqlodbc.so
Setup64		= /usr/lib64/libodbcpsqlS.so
FileUsage	= 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc5.so
Setup		= /usr/lib/libodbcmyS.so
Driver64	= /usr/lib64/libmyodbc5.so
Setup64		= /usr/lib64/libodbcmyS.so
FileUsage	= 1

# added for MySQL Connect engine 
[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/local/lib/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so
Threading=2

Check the SELinux status:

root@centos-6.5-minimal:[Mon May 05 14:02:48][/opt/installs/sqliteodbc-0.996]$ sestatus 
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted
Keep in mind that at the moment SELinux is enabled!

Create a custom directory that will host the external db files handled by the MariaDB 10.0.10 CONNECT engine

root@centos-6.5-minimal:[Mon May 05 14:04:22][/var/lib/mysql]$ mkdir /var/lib/mysql.connnect.db
root@centos-6.5-minimal:[Mon May 05 14:04:45][/var/lib/mysql]$ chown -R mysql:mysql /var/lib/mysql.connnect.db
root@centos-6.5-minimal:[Mon May 05 14:44:01][/var/lib/mysql]$ cd /var/lib/mysql.connnect.db

Create the test sqlite3 database:

root@centos-6.5-minimal:[Mon May 05 14:44:01][/var/lib/mysql.connnect.db]$  sqlite3 maria-sqlite3.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE DEPARTMENT(
   ...>    ID INT PRIMARY KEY      NOT NULL,
   ...>    DEPT           CHAR(50) NOT NULL,
   ...>    EMP_ID         INT      NOT NULL
   ...> );
sqlite> 
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(1,'sales',1);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(2,'sales',2);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(3,'sales',3);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(4,'marketing',4);
sqlite> insert into DEPARTMENT (ID,DEPT,EMP_ID) values(5,'marketing',5);
sqlite> . tables
DEPARTMENT
sqlite> select * from DEPARTMENT;
1|sales|1
2|sales|2
3|sales|3
4|marketing|4
5|marketing|5

Set the proper file permissions for that sqlite3 database:

root@centos-6.5-minimal:[Mon May 05 14:46:29][/var/lib/mysql.connnect.db]$ ls -lrth
total 4.0K
-rw-r--r--. 1 root root 3.0K May  5 14:45 maria-sqlite3.db
root@centos-6.5-minimal:[Mon May 05 14:46:31][/var/lib/mysql.connnect.db]$ chown -R mysql:mysql maria-sqlite3.db 
root@centos-6.5-minimal:[Mon May 05 14:46:42][/var/lib/mysql.connnect.db]$ ls -lrth
total 4.0K
-rw-r--r--. 1 mysql mysql 3.0K May  5 14:45 maria-sqlite3.db

Create the MariaDB 10.0.10 CONNECT engine table to handle that sqlite3 database:

root@centos-6.5-minimal:[Mon May 05 14:46:43][/var/lib/mysql.connnect.db]$ mysql 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.10-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql root@centos-6.5-minimal:[Mon May  5 14:47:26 2014][(none)]>  create database sqlite3;
Query OK, 1 row affected (0.01 sec)

mysql root@centos-6.5-minimal:[Mon May  5 14:47:28 2014][(none)]> use sqlite3;
Database changed
mysql root@centos-6.5-minimal:[Mon May  5 14:47:34 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';
ERROR 1105 (HY000): [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libsqliteodbc.so' : file not found

Now, it seems we have non working setup of the UnixUDBC sqlite3 driver, the MariaDB 10.0.10 error is explanatory enough.
How to fix that:

root@centos-6.5-minimal:[Mon May 05 14:49:49][/var/lib/mysql.connnect.db]$ locate libsqlite | grep -i odbc | grep -v install
/usr/local/lib/libsqlite3odbc-0.996.so
/usr/local/lib/libsqlite3odbc.a
/usr/local/lib/libsqlite3odbc.la
/usr/local/lib/libsqlite3odbc.so

So it seems the name of the sqlite3 unixODBC file is not set right.
'/usr/local/lib/libsqliteodbc.so' is missing, '/usr/local/lib/libsqlite3odbc.so' is available

 
root@centos-6.5-minimal:[Mon May 05 14:48:40][/var/lib/mysql.connnect.db]$ fgrep libsqliteodbc.so /etc/odbcinst.ini 
Driver=/usr/local/lib/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so

Change the /etc/odbcinst.ini configuration file:

root@centos-6.5-minimal:[Mon May 05 14:50:29][/var/lib/mysql.connnect.db]$ nano /etc/odbcinst.ini
root@centos-6.5-minimal:[Mon May 05 14:50:57][/var/lib/mysql.connnect.db]$  odbcinst -q -d
[PostgreSQL]
[MySQL]
[SQLite]
root@centos-6.5-minimal:[Mon May 05 14:51:06][/var/lib/mysql.connnect.db]$ cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description	= ODBC for PostgreSQL
Driver		= /usr/lib/psqlodbc.so
Setup		= /usr/lib/libodbcpsqlS.so
Driver64	= /usr/lib64/psqlodbc.so
Setup64		= /usr/lib64/libodbcpsqlS.so
FileUsage	= 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description	= ODBC for MySQL
Driver		= /usr/lib/libmyodbc5.so
Setup		= /usr/lib/libodbcmyS.so
Driver64	= /usr/lib64/libmyodbc5.so
Setup64		= /usr/lib64/libodbcmyS.so
FileUsage	= 1

[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/local/lib/libsqlite3odbc.so
Setup=/usr/local/lib/libsqlite3odbc.so
Threading=2

Reload the MariaDB 10.0.10 MySQL service:

root@centos-6.5-minimal:[Mon May 05 14:51:44][/var/lib/mysql.connnect.db]$ service mysql reload
 SUCCESS! Reloading service MySQL

Try to create again the sqlite3 CONNECT ENGINE table at MariaDB 10.0.10 MySQL:

mysql root@centos-6.5-minimal:[Mon May  5 14:52:16 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';
ERROR 1105 (HY000): [unixODBC][SQLite]connect failed

ERROR again, this time a different one.
In general this is due to the SELinux restrictions on the mysql accessing other directories:
One work around is to put the sqlite3 db file at the mysql data directory, it will work.

Example:

root@centos-6.5-minimal:[Mon May 05 15:04:07][/var/lib/mysql.connnect.db]$ cp -p /var/lib/mysql.connnect.db/maria-sqlite3.db /var/lib/mysql/maria-sqlite3.db
mysql root@centos-6.5-minimal:[Mon May  5 15:04:24 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.24 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:05:06 2014][sqlite3]> show create table my_dept \G
*************************** 1. row ***************************
       Table: my_dept
Create Table: CREATE TABLE `my_dept` (
  `ID` int(9) NOT NULL,
  `DEPT` varchar(50) NOT NULL,
  `EMP_ID` int(9) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='Driver=SQLite;Database=/var/lib/mysql/maria-sqlite3.db;version=3;' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENT'
1 row in set (0.01 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:07:35 2014][sqlite3]> select * from my_dept;
+----+-----------+--------+
| ID | DEPT      | EMP_ID |
+----+-----------+--------+
|  1 | sales     |      1 |
|  2 | sales     |      2 |
|  3 | sales     |      3 |
|  4 | marketing |      4 |
|  5 | marketing |      5 |
+----+-----------+--------+
5 rows in set (0.00 sec)

The other way is to disable the SELinux:
Example:

root@centos-6.5-minimal:[Mon May 05 15:07:51][/var/lib/mysql.connnect.db]$ echo 0 >/selinux/enforce

mysql root@centos-6.5-minimal:[Mon May  5 15:09:16 2014][sqlite3]> create table se_my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/var/lib/mysql.connnect.db/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.18 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:09:27 2014][sqlite3]> insert into se_my_dept values(6,'dev',6);
Query OK, 1 row affected (0.18 sec)

Now you may access the sqlite3 db and to check the data for the inserted new row:

root@centos-6.5-minimal:[Mon May 05 15:16:18][/var/lib/mysql.connnect.db]$ sqlite3 maria-sqlite3.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
DEPARTMENT
sqlite> select * from DEPARTMENT;
1|sales|1
2|sales|2
3|sales|3
4|marketing|4
5|marketing|5
6|dev|6
So again, keep in mind the SELinux restrictions when you use the MariaDB CONNECT ENGINE with local db files.

Now, lets take a look at the MariaDB-Server-Audit plugin installation process:
http://www.skysql.com/blogs/ralf-gebhardt/activating-auditing-mariadb-an... has everything explained for you.
In short:

mysql root@centos-6.5-minimal:[Mon May  5 15:21:52 2014][sqlite3]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.00 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:21:56 2014][sqlite3]> SELECT * from information_schema.plugins where plugin_name='server_audit';
+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+
| PLUGIN_NAME  | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY  | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR             | PLUGIN_DESCRIPTION         | PLUGIN_LICENSE | LOAD_OPTION | PLUGIN_MATURITY | PLUGIN_AUTH_VERSION |
+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+
| SERVER_AUDIT | 1.1            | ACTIVE        | AUDIT       | 3.2                 | server_audit.so | 1.8                    | Alexey Botchkov (MariaDB) | Audit the server activity. | GPL            | ON          | Beta            | 1.1.5               |
+--------------+----------------+---------------+-------------+---------------------+-----------------+------------------------+---------------------------+----------------------------+----------------+-------------+-----------------+---------------------+
1 row in set (0.01 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:22:13 2014][sqlite3]> SELECT * from information_schema.plugins where plugin_name='server_audit' \G
*************************** 1. row ***************************
           PLUGIN_NAME: SERVER_AUDIT
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUDIT
   PLUGIN_TYPE_VERSION: 3.2
        PLUGIN_LIBRARY: server_audit.so
PLUGIN_LIBRARY_VERSION: 1.8
         PLUGIN_AUTHOR: Alexey Botchkov (MariaDB)
    PLUGIN_DESCRIPTION: Audit the server activity.
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Beta
   PLUGIN_AUTH_VERSION: 1.1.5
1 row in set (0.06 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:22:18 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.01 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:22:42 2014][sqlite3]> SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:22:56 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:22:58 2014][sqlite3]> SET GLOBAL server_audit_logging=ON;
Query OK, 0 rows affected (0.00 sec)

mysql root@centos-6.5-minimal:[Mon May  5 15:23:32 2014][sqlite3]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | ON                    |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

At the MariaDB 10.0.10 MySQL error log you see the audit plugin info:

root@centos-6.5-minimal:[Mon May 05 15:23:53][/var/lib/mysql.connnect.db]$ tail -f /var/lib/mysql/centos-6.5-minimal.err /var/lib/mysql/server_audit.log 
==> /var/lib/mysql/centos-6.5-minimal.err  /var/lib/mysql/server_audit.log 

So, we want to upgrade to the latest MariaDB-Audit-Plugin which is 1.1.7:
Download location: https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/
Install the plugin at the MariaDB MySQL plugins directory:

root@centos-6.5-minimal:[Mon May 05 15:15:55][/var/lib/mysql.connnect.db]$ cd /usr/lib64/mysql/plugin/
root@centos-6.5-minimal:[Mon May 05 15:19:56][/usr/lib64/mysql/plugin]$ wget https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/server_audit-1.1.7/linux-64/server_audit.so 
--2014-05-05 15:20:05--  https://downloads.skysql.com/enterprise/MariaDB-Audit-Plugin/server_audit-1.1.7/linux-64/server_audit.so
Resolving downloads.skysql.com... 46.105.96.115, 2001:41d0:2:cf73::1
Connecting to downloads.skysql.com|46.105.96.115|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 166618 (163K) [application/octet-stream]
Saving to: “server_audit.so.1”

Note the name of the new file: "server_audit.so.1"

root@centos-6.5-minimal:[Mon May 05 15:21:03][/usr/lib64/mysql/plugin]$ ls -lrth
total 64M
-rwxr-xr-x. 1 root root 5.4M Mar 30 23:37 ha_connect.so
-rwxr-xr-x. 1 root root 9.6M Mar 30 23:38 ha_cassandra.so
-rwxr-xr-x. 1 root root 2.6M Mar 30 23:38 ha_oqgraph.so
-rwxr-xr-x. 1 root root  20M Mar 30 23:41 ha_innodb.so
-rwxr-xr-x. 1 root root 6.2M Mar 30 23:43 ha_spider.so
-rwxr-xr-x. 1 root root  15M Mar 30 23:49 ha_tokudb.so
-rwxr-xr-x. 1 root root 253K Mar 30 23:54 ha_sequence.so
-rwxr-xr-x. 1 root root 819K Mar 30 23:54 ha_sphinx.so
-rwxr-xr-x. 1 root root  19K Mar 30 23:59 auth_pam.so
-rwxr-xr-x. 1 root root 159K Mar 30 23:59 locales.so
-rwxr-xr-x. 1 root root 2.6M Mar 30 23:59 handlersocket.so
-rwxr-xr-x. 1 root root 269K Mar 30 23:59 metadata_lock_info.so
-rwxr-xr-x. 1 root root 664K Mar 30 23:59 semisync_master.so
-rwxr-xr-x. 1 root root  20K Mar 30 23:59 sql_errlog.so
-rwxr-xr-x. 1 root root 562K Mar 30 23:59 semisync_slave.so
-rwxr-xr-x. 1 root root 206K Mar 30 23:59 server_audit.so
-rwxr-xr-x. 1 root root  13K Mar 31 00:00 auth_socket.so
-rwxr-xr-x. 1 root root 346K Mar 31 00:00 query_response_time.so
-rwxr-xr-x. 1 root root 273K Mar 31 00:00 query_cache_info.so
-rw-r--r--. 1 root root 163K Apr 17 09:44 server_audit.so.1

Uninstall the old plugin:

mysql root@centos-6.5-minimal:[Tue May  6 13:43:23 2014][(none)]> uninstall plugin server_audit;

At the error logs and the audit logs you will see messages like these ones:

20140506 13:43:23,centos-6.5-minimal,root,localhost,7,0,CONNECT,,,0
20140506 13:43:23,centos-6.5-minimal,root,localhost,7,32,QUERY,,'select @@version_comment limit 1',0
20140506 13:43:23,centos-6.5-minimal,root,localhost,7,33,QUERY,,'select USER()',0
20140506 13:43:28,centos-6.5-minimal,root,localhost,7,34,WRITE,mysql,plugin,
20140506 13:43:28,centos-6.5-minimal,root,localhost,7,34,QUERY,mysql,'uninstall plugin server_audit',0
==> /var/lib/mysql/centos-6.5-minimal.err 

Now, replace the old one with the new server_audit.so.1 and reload the MariaDB 10.0.10 MySQL service:

root@centos-6.5-minimal:[Tue May 06 13:26:55][/usr/lib64/mysql/plugin]$ mv server_audit.so server_audit.so.1.1.5
root@centos-6.5-minimal:[Tue May 06 13:47:05][/usr/lib64/mysql/plugin]$ mv server_audit.so.1 server_audit.so
root@centos-6.5-minimal:[Tue May 06 13:47:12][/usr/lib64/mysql/plugin]$ service mysql reload
 SUCCESS! Reloading service MySQL

Install the server_audit plugin again:

mysql root@centos-6.5-minimal:[Tue May  6 13:44:09 2014][(none)]> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.01 sec)
mysql root@centos-6.5-minimal:[Tue May  6 13:48:17 2014][(none)]>  SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.00 sec)

You will see at the error log that the latest MariaDB 10.0.10 Audit Plugin version 1.1.7 was loaded :

==> /var/lib/mysql/centos-6.5-minimal.err  SET GLOBAL server_audit_events='CONNECT,QUERY,TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql root@centos-6.5-minimal:[Tue May  6 13:51:56 2014][(none)]> SET GLOBAL server_audit_logging=ON;
Query OK, 0 rows affected (0.00 sec)

mysql root@centos-6.5-minimal:[Tue May  6 13:52:07 2014][(none)]>  SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | CONNECT,QUERY,TABLE   |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_logging          | ON                    |
| server_audit_mode             | 0                     |
| server_audit_output_type      | file                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
14 rows in set (0.01 sec)

You will notice at the /var/lib/mysql/server_audit.log the new records:

==> /var/lib/mysql/centos-6.5-minimal.err  /var/lib/mysql/server_audit.log 

So, that was the test, with MariaDB 10.0.10 and its CONNECT ENGINE audited by the MariaDB Audit Plugin version 1.1.7.

About the Author

ivanstoykov's picture

Ivan Stoykov is Support Engineer for MariaDB and MySQL.

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: