Skip to content

Last week at Percona Live Facebook has presented for the first time Docstore which is a native JSON implementation in MySQL. Oracle has also presented their MySQL 5.7 lab release that includes the implementation of a native JSON type.
This is an important move as MySQL was behind other other RDMS regarding JSON (PostgreSQL already had a nice and complete implementation). JSON being widely adopted in various area (JS dev, data exchange and database format, …) this move was awaited.
Keep on reading!

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!

MariaDB CONNECT Storage Engine allows to access a JSON file as if it was a local table.
The mapping between a hierarchical structure and a relational structure is not 'natural' and needs to be defined. To achieve that we need a specific syntax. We associate with each column a 'field_format' attribute. This 'field_format' column attribute defines what property in the JSON hierarchy we want to map to the MariaDB table column.
Keep on reading!

The MariaDB CONNECT storage engine now offers access to JSON file and allows you to see a external JSON file as a MariaDB table. JSON (JavaScript Object Notation) is a lightweight data-interchange format widely used on the Internet.
JSON like XML represents data hierarchically. The mapping from hierarchical data to tabular data needs to be specified.
Keep on reading!

When looking at Percona live Santa Clara 2015 agenda I saw two intriguing conferences :
- JSON support in MySQL 5.7
- Docstore: document database for MySQL at Facebook

Keep on reading!

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 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!

At FOSDEM 2014 ProxySQL and MaxScale were both presented. Both are proxy that can help build sophisticated MariaDB/MySQL architectures.
But currently what is the most used proxy with MySQL? It is HAproxy. HAproxy is a level 4 proxy that has no knowledge of the MySQL protocol. Being low level makes it very fast but it cannot accomplish any advanced proxy task. In many case this is enough for pure load balancing. But it cannot handle filtering, routing, query rewriting. This requires to understand the MySQL protocol and to look at the query passing through the proxy.
Keep on reading!

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!

When you pass a SQL command through MySQL it has to be compliant with MySQL syntax as it will have to go through the MySQL parser. Can we pass non MySQL syntax compatible selects to a target ODBC database ? MariaDB CONNECT Storage Engine allows through ODBC to access heterogeneous data sources like Oracle or Microsoft SQL Server.
In my previous post I showed you how to use the CONNECT Storage Engine ODBC table type to access an Oracle database. It was possible to run select commands against these remote ODBC tables. In many cases It would be very interesting to run the sql command directly on the target database.
The first reason would be for efficiency as this would avoid a lot of network roundtrips between the Oracle server and the MariaDB Server. The second reason would be to avoid the restriction on MySQL syntax compliance.

With the MySQL CONNECT storage Engine it is possible to define a remote ODBC table and to associate a SQL request to this table definition. This SQL request will be executed on the remote server. For example for a remote Oracle database it is possible to pass a request using proprietary syntax. In the case of Oracle suppose we want to use the Oracle proprietary "CONNECT BY" syntax that does not exists in MySQL, we will do it that way:

create table emp_hierarchy  
ENGINE=CONNECT 
TABLE_TYPE=ODBC tabname='EMP' CONNECTION='DSN=orcl;UID=scott;PWD=manager1' 
srcdef='SELECT empno, ename, mgr, LEVEL FROM emp CONNECT BY PRIOR empno = mgr;';

The table definition includes a srcdef argument that defines the SQL request that will be executed on the Oracle target database. When we request this table on MariaDB everything is in fact done on the Oracle server.

MariaDB [test]> select * from emp_hierarchy;
+-------+--------+------+-------+
| EMPNO | ENAME  | MGR  | LEVEL |
+-------+--------+------+-------+
|  7788 | SCOTT  | 7566 |     1 |
|  7876 | ADAMS  | 7788 |     2 |
…
|  7839 | KING   | NULL |     1 |
…
|  7521 | WARD   | 7698 |     3 |
+-------+--------+------+-------+
43 rows in set (2.54 sec)
MariaDB [test]>

Beside this basic example this open the door to the ability to execute any select on the target database. This allows for example to take benefit of Oracle's In-Database SQL Analytics which includes a lot of powerful functions(Ranking, Windowing, Reporting Aggregates, LAG/LEAD, FIRST/LAST, Inverse Percentile, Hypothetical Rank and Distribution, Pattern Matching, Modeling, Advanced aggregations..). The same idea applies to powerful spatial or full text capabilities.
And do not forget that with the MariaDB CONNECT Storage Engine you can aggregate many of these table located on different server to be seen as a single table. For that you use the CONNECT TBL table type. This also gives the benefit of parallel execution on these remote servers.

What would be great would be to have someone write a MySQL procedure wrapper that transparently create the CONNECT table to run the query against(something like "callRemoteSQL") ;-).