The JSON format includes the concept of array. A JSON object cant contain an attribute of array type. We have seen that we can use the MariaDB CONNECT Storage Engine provided UDFs (user defined functions) to implement dynamic columns.
Keep on reading!
Category: MariaDB
Using JSON as Dynamic Columns with MariaDB
MariaDB CONNECT storage engine handles access to JSON files through standard SQL. It comes with a set of UDFs (user defined functions) to manipulate the JSON format. This JSON content can be stored in a normal text column. This approach can be used to implement dynamic columns. The dynamic column concept was first introduced with MariaDB 5.3.
Keep on reading!
MariaDB CONNECT Storage Engine JSON Autodiscovery
The MariaDB CONNECT storage engine offers access to JSON file and allows you to see a external JSON file as a MariaDB table.
A nice feature of the CONNECT storage Engine is its capability to auto discover a table structure when the table correspond to external data. In our case the CONNECT storage engine will automatically define the columns based on the JSON file hierarchical structure.
This is possible thanks to a feature of MariaDB Storage Engine API. This auto discovery feature allows a create statement to delegate the discovery of the table structure to the chosen storage engine.
Keep on reading!
Select from a mariaDB table into JSON format ?
Can we output content of a mariaDB table into JSON using the CONNECT Storage Engine ?
MariaDB and MySQL are not currently JSON friendly databases. The usage of JSON in MySQL is almost inexistent with the excetion of the explain JSON output and of MariaDB dynamic column dump.
There also exist a few udf (in MySQL lab area) to help manipulate JSON strings(searching, modification …).
Keep on reading!
Since now on github is the place for MariaDB new dev !
Today while browsing through my emails I was very happy to read this email from Sergei Golubchik :
Hi! I'm happy to announce that MariaDB-10.1 tree has been completely migrated to github. Since now on we'll use github for the new development in MariaDB. It's https://github.com/MariaDB/server, go on, fork it, hack around, submit pull requests. Have fun! Older trees (10.0, 5.5, 5.3, 5.2, 5.1) are not on github - we do hope to migrate them too eventually, but at the moment they are still on launchpad. If you're a maria-captain on launchpad - for you to get write access to the mariadb repository on github you need to tell me your github account name. Regards, Sergei P.S.: Don't forget that 10.1 is still *pre-alpha*. You've been warned. _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
So if you want to embark the MariaDB adventure
it is here : https://github.com/MariaDB/server
Go on,
Fork it,
Hack around,
Submit pull requests.
Have fun!
MaxScale has now its own public irc channel
MaxScale is a Proxy for the MySQL protocol built with a modular architecture.
The underlying concept of modules allows to extend the MaxScale proxy services. The current version implements Read Write splitting and Connection Load Balancing. Internally MySQL queries go through a SQL parsing phase. This gives MaxScale great capabilities regarding queries routing.
Keep on reading!
MariaDB CONNECT Storage Engine as an ETL (or ELT) ?
The MariaDB CONNECT Storage Engine allows to access heterogeneous data sources. In my previous post I show you how to use the MariaDB CONNECT Storage Engine to access an Oracle database. This is quite easy through the CONNECT Storage Engine ODBC table type.
Keep on reading!
MariaDB CONNECT Storage Engine access to Oracle 11GR2
MariaDB CONNECT Storage Engine allows to access heterogeneous datasourses. This includes various file formats. But this also includes ODBC accessible datasources
The CONNECT Storage Engine ODBC table type allows to access SQLite, Excel, SQL Server or Oracle databases. Some nice features of the ODBC CONNECT table type are:
- Auto discovery of table structure. This means that you do not need to specify the columns of the target table.
- Condition push down. This is an optimization that push filtering conditions to the target database. This can avoid a lot of network roundtrips.
Let us have a look and test the Oracle access through the CONNECT Storage Engine. I downloaded, unzipped and installed Oracle 11gR2 in a virtual box.
Let us start Oracle 11gR2
[oracle@centos1 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 29 15:15:59 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect sys/manager1 as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 617975808 bytes Fixed Size 2215864 bytes Variable Size 377487432 bytes Database Buffers 230686720 bytes Redo Buffers 7585792 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Now let us start the oracle listener (this concept doest not exist in MySQL).
[oracle@centos1 ~]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-NOV-2013 20:08:20 Copyright (c) 1991, 2009, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/centos1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos1)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 29-NOV-2013 20:08:20 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/centos1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=centos1)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... Service "plsextproc" has 1 instance(s). Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@centos1 ~]$
On the machine with the MariaDB CONNECT Storage Engine we need to install the Oracle ODBC Driver. We obtain it through the installation of the Oracle instant client. Let us download and unzip the instant client. Installation is just unzip of the instant client zip files.
unzip instantclient-basic-linux.x64-12.1.0.1.0.zip unzip instantclient-odbc-linux.x64-12.1.0.1.0.zip unzip instantclient-sdk-linux.x64-12.1.0.1.0.zip
We need to add the path of the ODBC driver shared library. Let us 'vi .bash_profile' and add the required LD_LIBRARY_PATH.
CLIENT_HOME=/home/oraclec/instantclient_12_1 ; export CLIENT_HOME LD_LIBRARY_PATH=$CLIENT_HOME:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
On linux we also need to install the unixODBC open source ODBC driver manager :
yum install unixODBC yum install unixODBC-devel
The configuration of the unixODBC driver manager is simple and just requires 2 files updates:
/etc/odbcinst.ini wich points the driver to the installed shared library
[Oracle 12c ODBC driver] Description = Oracle ODBC driver for Oracle 12c Driver = /home/oraclec/instantclient_12_1/libsqora.so.12.1
/etc/odbc.ini wich defines here the DSN that will be used for the ODBC connection
[orcl] Driver = Oracle 12c ODBC driver ServerName = //centos1.localdomain:1521/oracle DSN = orcl UserName = scott Password = manager1
How to test that the unixODBC + oracle ODBC driver is correctly installed ?
The unixODBC includes an ODBC client tool 'isql'. Let us use it to test our installation and the DSN we just created.
root@centos1 ~]# isql orcl scott manager1 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from emp; +-------+-----------+----------+-------+--------------------+---------- | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL +-------+-----------+----------+-------+--------------------+---------- | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800 … +-------+-----------+----------+-------+--------------------+---------- SQLRowCount returns -1 15 rows fetched SQL>
Now let us create a CONNECT table of ODBC type. we will make this MariaDB ODBC table point to the Oracle 11g 'scott.emp' table.
[root@centos1 ~]# mysql -u root -pmanager1 -S /usr/local/mariadb10data/mariadb10.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.6-MariaDB-log 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. MariaDB [(none)]> MariaDB [test]> create table emp ENGINE=CONNECT TABLE_TYPE=ODBC tabname='emp' CONNECTION='DSN=orcl;UID=scott;PWD=manager1'; ERROR 1105 (HY000): Unsupported table type ODBC
Come on ! what happened ? I just installed the latest 10.0.6 MariaDB and the CONNECT storage Engine does not support the ODBC table type. That is true and that is an issue caused by old machine being used to build the binary the unnixODBC. This problem is not present in the MariaDB 10.0.6 rpm packages. But I have multiple tar based MariaDB version installed on my Centos box(in fact a virtual box vm) and I do not want any rpm based installation. Hopefully a storage engine is just a shared library.
Let us download MariaDB-10.0.6-centos6-x86_64-connect-engine.rpm and unpack the rpm without installing it :
[root@centos1 ~]# rpm2cpio ../MariaDB-10.0.6-centos6-x86_64-connect-engine.rpm | cpio --extract --make-directories 8216 blocs [root@centos1 plugin]# cp ./usr/lib64/mysql/plugin/ha_connect.so ha_connect.so /usr/local/mariadb10/lib/plugin/
We now have the CONNECT storage with the support of the ODBC table type. Our second try :
[root@centos1 local]# mysql -u root -pmanager1 -S /usr/local/mariadb10data/mariadb10.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.0.6-MariaDB-log 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. MariaDB [(none)]> use test Database changed MariaDB [test]> create table toto ENGINE=CONNECT TABLE_TYPE=ODBC tabname='emp' CONNECTION='DSN=orcl;UID=scott;PWD=manager1'; ERROR 1105 (HY000): [unixODBC][Driver Manager]Can't open lib '/home/oraclec/instantclient_12_1/libsqora.so.12.1' : file not found
?
I had forgotten that the mysql service is run under the mysql linux user. This user also needs to have access to the Oracle ODBC driver (libsqora.so.12.1 shared library provided by the Oracle instant client).
CLIENT_HOME=/home/oraclec/instantclient_12_1 ; export CLIENT_HOME LD_LIBRARY_PATH=$CLIENT_HOME:$LD_LIBRARY_PATH ; export LD_LIBRARY_PATH
Once done everything should goes better:
MariaDB [test]> create table toto ENGINE=CONNECT TABLE_TYPE=ODBC tabname='EMP' CONNECTION='DSN=orcl;UID=scott;PWD=manager1';Query OK, 0 rows affected (0.79 sec) MariaDB [test]> MariaDB [test]> select * from toto; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | … | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.09 sec) MariaDB [test]> insert into toto values ( 8000,'serge','peintre',7902,'1980-12-17',123,234,20); Query OK, 1 row affected (0.15 sec) MariaDB [test]> select * from toto;+-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 8000 | serge | peintre | 7902 | 1980-12-17 | 123.00 | 234.00 | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | … | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 15 rows in set (0.03 sec)
That looks nice. I have created an ODBC table pointing to a remote Oracle 11g table. That was quite simple as the auto discovery mechanism has automatically discovered the structure of the Oracle table and converted Oracle data types to compatible mysql column type.
Let us try to update Oracle data through MySQL :
MariaDB [test]> update toto set sal=999 where empno=8000; ERROR 1296 (HY000): Got error 174 'No DELETE/UPDATE of ODBC tablesd' from CONNECT
This is not supported ! That is right. This ODBC table update feature has been developed and is currently only available in the launchpad repository of the CONNECT Storage Engine.
CONNECT Storage Engine Sources on launchpad
MariaDB CONNECT Storage Engine replay & slides available
The slides and replay of yesterday's webinar on the MariaDB CONNECT storage engine have just been posted. First I want to thank the numerous attendees.
You have shown great interest on the parallel execution of query on distributed MySQL Servers. I agree this is cool.
The ODBC capabilities seems also to generate interest. This make it simple to access an ODBC datasource (SQLServer, Oracle ...) from plain MySQL syntax.
Here to view the replay
Here to get the slides
Let us know about your test. You just need to download MariaDB 10.0.5 which includes the CONNECT storage engine. Give us your feedback : positive, negative, improvement requests, bugs, doc pb, ...
Here to download the latest MariaDB 10.0.5 Beta release
Here to get the CONNECT Storage Engine Documentation
MySQL Fabric with MariaDB Galera Cluster ?
MySQL Fabric is a very promising sharding framework. If I take Ulf Wendel definition of MySQL Fabric :
MySQL Fabric is an administration tool to build large “farms” of MySQL servers. In its most basic form, a farm is a collection of MySQL Replication clusters. In its most advanced form, a farm is a collection of MySQL Replication clusters with sharding on top.
So MySQL Fabric takes care of two very orthogonal features :
- High availability of servers
- Sharding of data
Let us forget about sharding and look at the High availability infrastructure.
Servers are included in groups, called "High Availability Groups" when we talk about HA.
Each Server has an associated Status (or Role): primary secondary, spare
Each Server has also a mode : Offline, Read-only, and Read-Write.
The implementation has been made to allow various HA implementation patterns.
The most common HA pattern is the Master/Slave HA group ( in that case we should call it a "replica set" which is the terminology used in MongoDB or Facebook MySQL Pool Scanner (MPS).
Mats Kindahl in his blog post on MySQL Fabric High Availability Groups mentioned that other HA solutions are possible for an availability group :
- Shared Storage with SAN or NAS
- Replicated storage like DRBD
- MySQL Cluster shared nothing cluster
In the case of a HA group based on MySQL Cluster the group is self-managing regarding HA and MySQL Fabric does not handles the failover. With the "Shared Storage" and "Replicated storage" availability groups the secondary servers will be offline.
So one of my ideas that I hope is feasible would be to use MariaDB Galera Cluster as another HA solution with MySQL Fabric. The main advantage of this solution relates to the characteristics of MariaDB Galera Cluster. MariaDB Galera Cluster is an Active-active multi-master topology with synchronous replication. MariaDB Galera Cluster being innoDB based does not carry all the usage limitations associated with MySQL Cluster (main one being limited join capabilities).
Regarding to MySQL fabric the behavior of an availability group based on MariaDB Galera Cluster is identical to MySQL Cluster. It is a self-managing availability group.
MariaDB Galera Cluster
MariaDB Galera Cluster
Getting Started with MariaDB Galera Cluster
MySQL Fabric
MySQL Fabric: A new kid in the MySQL sharding world 2013-10-09 Serge Frezefond
MySQL Fabric: High Availability Groups 2013-10-21 Mats Kindahl
A Brief Introduction to MySQL Fabric 2013-09-21 Mats Kindahl
MySQL Fabric - Sharding - Introduction 2013-09-21 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Simple Example 2013-09-22 VN (Narayanan Venkateswaran)
MySQL Fabric - Sharding - Shard Maintenance 2013-09-27 VN
MySQL Fabric - Sharding - Migrating From an Unsharded to a Sharded Setup 2013-09-22 VN
Installing MySQL Fabric on Windows 2013-10-03 Todd Farmer
MySQL 5.7 Fabric: any good? 2013-09-23 Ulf Wendel
Writing a Fault-tolerant Database Application using MySQL Fabric 2013-09-21 Alfranio Junior
Sharding PHP with MySQL Fabric 2013-10-09 Johannes Schlüter
MySQL Fabric support in Connector/Python 2013-09-22 Geert Vanderkelen
MySQL Connector/J with Fabric Support 2013-09-21 Jess Balint