Connect MySQL to sqlite3 database or to flat txt file via CONNECT engine of MariaDB 10.0.7 on Fedora/CentOS

You might don’t know that, but the MariaDB engines are amazing ..
More info you can find here:
https://mariadb.com/kb/en/mariadb-storage-engines/

I was teased by CONNECT engine : https://mariadb.com/kb/en/connect/

What is good for ? Well, I leave that to your imagination, as you could have MariaDB as a spider connected and managing InnoDB tables, flat files, MS ACCESS databases, MS Excel files … all that in same time.
So how to use it?
By the documentation :
1. https://mariadb.com/kb/en/loading-the-connect-handler/
check if there is CONNECT already installed, if not, install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:14 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         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | 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         |
| CASSANDRA          | YES     | Cassandra storage engine                                       | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Its not installed by default for MariaDB 10.0.7
So lets install it:

mysql root@mariadb-10.0.7:[Wed Jan 29 09:36:19 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
ERROR 1126 (HY000): Can't open shared library '/usr/lib64/mysql/plugin/ha_connect.so' (errno: 2, cannot open shared object file: No such file or directory)

My first install failed and I had to install MariaDB-connect-engine package from MariaDB

$ yum info MariaDB-connect-engine
Name        : MariaDB-connect-engine
Arch        : x86_64
Version     : 10.0.7
Release     : 1
Size        : 4.3 M
Repo        : installed
From repo   : mariadb
Summary     : MariaDB: a very fast and robust SQL database server
URL         : http://mariadb.org
License     : GPL
Description : MariaDB: a very fast and robust SQL database server
            : 
            : It is GPL v2 licensed, which means you can use the it free of charge under the
            : conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).
            : 
            : MariaDB documentation can be found at http://kb.askmonty.org/
            : MariaDB bug reports should be submitted through https://mariadb.atlassian.net/


$ cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/yum/10.0/centos6-amd64
#baseurl = ftp://ftp.ulak.net.tr/pub/MariaDB/mariadb-10.0.6/yum/centos6-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


then again

mysql root@mariadb-10.0.7:[Wed Jan 29 09:40:45 2014][(none)]> INSTALL PLUGIN CONNECT SONAME 'ha_connect';
Query OK, 0 rows affected (0.00 sec)
mysql root@mariadb-10.0.7:[Wed Jan 29 09:44:56 2014][(none)]> pager grep CONNECT
PAGER set to 'grep CONNECT'
mysql root@mariadb-10.0.7:[Wed Jan 29 09:45:24 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         |
| MyISAM             | YES     | MyISAM storage engine                                              | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)     | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables          | NO           | NO   | NO         |
| CONNECT            | YES     | Management of External Data (SQL/MED), including many file formats | NO           | NO   | NO         |
| InnoDB             | DEFAULT | 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         |
| CASSANDRA          | YES     | Cassandra storage engine                                           | NO           | NO   | NO         |
+--------------------+---------+--------------------------------------------------------------------+--------------+------+------------+
12 rows in set (0.00 sec)

Now, sqlite3
UnixODBC was installed along with the MariaDB-connect-engine package, but I was not a able to find SQLite UnixODBC driver installed. Neither I was able to get if from the Fedora repos.
Checking for the http://www.unixodbc.org/drivers.html I ended up with compiling the SQLite ODBC Driver from http://www.ch-werner.de/sqliteodbc/

The instructions are good enough for me:
1. get the source from

$ wget http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.996.tar.gz

2. Untar it :

$ tar xvf sqliteodbc-0.996.tar.gz

3. go to the source directory:

$ cd sqliteodbc-0.996

4. compile it

$ ./configure
..
...
configure: WARNING: SQLite4 header file and source not found
configure: error: No usable SQLite header/library on this system

4.1 In case you get the No usable SQLite header/library error, installs the sqlite-devel package

$ yum install sqlite-devel
sqlite-devel.x86_64 0:3.6.20-1.el6 will be installed

4.2 run configure again

$ ./configure
..
...
checking for ODBC headers and libraries... no
configure: error: ODBC header files and/or libraries not found

4.3 In case you get error: ODBC header files and/or libraries not found, install the unixODBC-devel package:

$ yum install unixODBC-devel
Package unixODBC-devel.x86_64 0:2.2.14-12.el6_3 will be installed

4.4 run configure again:

$ ./configure
make 
make install

5. Check the installed ODBC drivers:

$ odbcinst -q -d
[PostgreSQL]
[MySQL]
$ 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

6. Install the sqlite3 odbc driver:

$ nano /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/libsqliteodbc.so
Setup=/usr/local/lib/libsqliteodbc.so
Threading=2
 
$ odbcinst -q -d
[PostgreSQL]
[MySQL]
[SQLite]

So the SQLite ODBC driver was installed.

In a view to trace the CONNECT engine , its good to create connect.ini file in the mysql directory

$ cat /var/lib/mysql/connect.ini 
[CONNECT]
Trace=2

The CONNECT engine errors will be in the mysql error.log, in my case mariadb-10.0.7.err

How to create a MySQL table using sqlite3 database?
create the sqlite3 table :

root@mariadb-10.0.7:[Wed Jan 29 10:57:39][/usr/lib64/mysql]$ 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> .tables
DEPARTMENT
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);

note,  its in /usr/lib64/mysql
its good to place the database in a place where the mysql user can read/write it.
root@mariadb-10.0.7:[Wed Jan 29 11:04:14][/usr/lib64/mysql]$ mv maria-sqlite3.db /tmp/
root@mariadb-10.0.7:[Wed Jan 29 11:04:20][/usr/lib64/mysql]$ chmod 0777 /tmp/maria-sqlite3.db
root@mariadb-10.0.7:[Wed Jan 29 11:07:26][/usr/lib64/mysql]$ mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.7-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:27 2014][(none)]> create database sqlite3;
Query OK, 1 row affected (0.00 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:39 2014][(none)]> use sqlite3                                                                                                                            Database changed
mysql root@mariadb-10.0.7:[Wed Jan 29 11:07:57 2014][sqlite3]> create table my_dept engine=CONNECT table_type=ODBC tabname='DEPARTMENT' Connection='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;';
Query OK, 0 rows affected (0.13 sec)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:03 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)

mysql root@mariadb-10.0.7:[Wed Jan 29 11:08:41 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=/tmp/maria-sqlite3.db;version=3;' `TABLE_TYPE`='ODBC' `TABNAME`='DEPARTMENT'
1 row in set (0.00 sec)

In case you have the CONNECT engine trace set to 2, you will see in the mysql error log something like:

 DBMS: SQLite, Version: 3.6.20, rc=0
ODBCColumns: max=4096 len=0,0,0
Getting col results ncol=12
Columns: NBCOL=12 NBLIN=3
s_init: CREATE TABLE whatever (`ID` INT(9) NOT NULL,`DEPT` VARCHAR(50) NOT NULL,`EMP_ID` INT(9) NOT NULL) TABLE_TYPE='ODBC' TABNAME='DEPARTMENT' CONNECTION='Driver=SQLite;Database=/tmp/maria-sqlite3.db;version=3;'
ColDB: am=100 colname=ID tabname=my_dept num=0
cdp(1).Name=ID cp=(nil)
 making new ODBCCOL C1 ID at 0x7f1259400420
colp=0x7f1259400420
ColDB: am=100 colname=DEPT tabname=my_dept num=0
cdp(2).Name=DEPT cp=(nil)
 making new ODBCCOL C2 DEPT at 0x7f12594004b8
colp=0x7f12594004b8
ColDB: am=100 colname=EMP_ID tabname=my_dept num=0
cdp(3).Name=EMP_ID cp=(nil)
 making new ODBCCOL C3 EMP_ID at 0x7f1259400550
colp=0x7f1259400550
....
DBMS: SQLite, Version: 3.6.20, rc=0
Prepare hstmt=0x7f12ebc59f00 INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (?,?,?)
 setting INTEGER to: 1
 setting INTEGER to: 1
S1000: [unixODBC][SQLite]attempt to write a readonly database (8), Native=8
ODBC CloseDB: closing my_dept

In case you get read only table :

mysql root@mariadb-10.0.7:[Wed Jan 29 11:09:51 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values(6,'IT-DBA',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT

It seems you have your SQLite db in a folder where your database user /mysql/ must have write permissions,but does not.
Most probably you have selinux activated.
How to fix that without disabling the selinux? The best way is move the SQLite db file to the mysql data directory : /var/lib/mysql/, or to create a separated directory where mysql shell user has write permissions.

mysql root@mariadb-10.0.6:[Fri Jan 31 12:06:54 2014][sqlite3]> insert into my_dept (ID,DEPT,EMP_ID) values (6,'mysql',6);
ERROR 1296 (HY000): Got error 122 '[unixODBC][SQLite]attempt to write a readonly database (8)' from CONNECT
mysql root@mariadb-10.0.6:[Fri Jan 31 12:07:27 2014][sqlite3]> drop table my_dept;
Query OK, 0 rows affected (0.00 sec)

Move the db file to MySQL data directory :

root@mariadb-10.0.6:[Fri Jan 31 12:09:15][/var/lib/mysql]$ cp -rp /tmp/maria-sqlite3.db  ./

mysql root@mariadb-10.0.6:[Fri Jan 31 12:08:17 2014][sqlite3]> create table my_dept_new  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.11 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:09:57 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
+----+------------+--------+
6 rows in set (0.01 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:02 2014][sqlite3]> insert into my_dept_new (ID,DEPT,EMP_ID) values (6,'mysql',6);                                                                        Query OK, 1 row affected (0.08 sec)

mysql root@mariadb-10.0.6:[Fri Jan 31 12:10:07 2014][sqlite3]> select * from my_dept_new;
+----+------------+--------+
| ID | DEPT       | EMP_ID |
+----+------------+--------+
|  1 | sales      |      1 |
|  2 | sales      |      2 |
|  3 | sales      |      3 |
|  4 | marketing  |      4 |
|  5 | marketing  |      5 |
|  7 | IT-SUPPORT |      7 |
|  6 | mysql      |      6 |
+----+------------+--------+
7 rows in set (0.01 sec)

So now we have R/W sqlite3 table managed by MariaDB 10.0.7

Advertisements