Screenshot 3: The GPX track shown on Google Maps

The availability of the node.js binding for MariaDB’s non-blocking client library together with the GIS capabilities of MariaDB inspired me to make an example of using node.js and MariaDB to import so-called GPX tracks to a MariaDB database and then show them on a map. GPX tracks are what are stored by many GPS devices including running watches and smartphones.

My project makes use of MariaDB’s non-blocking client library together with the node.js platform and on top of that uses the GIS functionality found in MariaDB 5.5 and 10.0.

To start with let’s go through the software and components I’m using:

  • Node.js – The popular Node.js platform built on Chrome’s JavaScript runtime. An event-driven and non-blocking architecture
  • Express.js – a web application framework for node.js (similar to Sinatra in Ruby). Puts a nice structure on the application and makes configuration easy.
  • Jade – A node.js template engine for outputting in most cases HTML, but can also be used for other XML structured languages
  • node-mariasql – A node.js binding to MariaDB’s non-blocking (MySQL-compatible) client library created by Brian White
  • xml2js – Node module for parsing XML
  • Google Maps – To show the end result on a map

The source code for my application can be found on Github:
I’ve licensed it under the MIT license, so feel free to grab it, copy it, reuse it or distribute it. If you build on top of it, it would of course be nice to know and it would appreciated if you dropped an email to me or added a comment here.

Installing the platform

I chose to use Ubuntu 13.10 x64 as the OS for the application. Below is outlined the steps for installing each of the components needed for this application. Before I begin going through them it’s always good to create a new OS user and not run as root. adduser is handy for the purpose. Remember also to add the user to the sudo group if you’re going to make the installation with this new user. In my case I chose to “sudo adduser rasmus sudo”.

Install MariaDB 10.0 by following the instructions found on the downloads repository for MariaDBRemember to choose the right Ubuntu release on that page, which in my case is 13.10 “saucy”. Then follow the instructions on the page. While doing this tutorial the version of MariaDB getting installed was 10.0.7. The application uses MariaDB’s root user with password password1 so you might want to do the same to get this example working without code changes. Create a database named running with the following commands:

In the GitHub repository for the application you’ll find running.sql in the root. Run that with the following command on the server to create the schema for the running -database:

Prepare the environment:
Update the apt repository: sudo apt-get update
Install a compiler: sudo apt-get install build-essential
Install curl which will be needed when installing node.js: sudo apt-get install curl

Install node.js and Node Package Manager (NPM):
sudo apt-get install nodejs
sudo apt-get install nodejs-legacy
sudo apt-get install npm

Install Express.js and Jade:
npm install express
npm install jade

Install the MariaDB non-blocking client:
npm install mariasql
(there might be a few compiler warnings, I at least had a few)

Finally install the xml2js module which we will need:
npm install xml2js

With that done, the platform is set up and the next step is to create an Express.js web application by running the command express nodegpxmariadb, where nodegpxmariadb is the name of the web application. I did it in the home directory of my user. Go to the nodegpxmariadb -directory and make sure all required modules are installed by running npm install.

Configuring the application

In app.js a few different things are specified.

All module dependencies are listed. For my application the two additions to the normal modules set up by express.js is a data.js and a common.js module in which I’ve put all the database handling. The module dependency is defined by adding these rows:

Now the common and data modules are available throughout the application. Common follows the principles of the express.js framework and is done as a so-called route which include the logic for url path mappings mentioned below.

The default  express.js environment configurations are in use like the server running on port 3000. Make sure JSON support is on. It should be there by default:

Another configuration needed is to tell express.js where to store uploaded files:

In addition app.js is the place where all the URL path mappings are defined. The following are needed:

As you can see most of the url path mappings point to the common module, where it’s defined what to do when there is an incoming request for the url path. For /showmap there is no server side logic needed so an html file can just be read and sent to the client.

Start the application

With the configuration in place the application can be started:

You should see a “Welcome to Express” -message if you open up the http://[server ip]:3000 in a browser.

Upload GPX file

Let’s take a closer look at what actually is done in the common.js module. Let’s start with the upload part. Exports.fileForm and exports.fileUpload are handling the displaying of an upload form and respectively actually doing the upload of the file chosen by the user.

Screenshot 1: File upload

Parse GPX file

Once a file has been uploaded it gets more interesting. This is when the parsing of a GPX file starts. GPX files are full of tracking points with a certain interval. For example:

As seen above each track point is its own XML node, which holds the longitude and latitude coordinates, elevation, time and some other interesting information such as heart rate. An easy way to handle XML of this type (or in general) is to make use of the node.js library xml2js which parses XML and outputs JSON. It’s very straightforward. I make use of it in in the parseGPX –function.

When we have the GPX content in JSON it’s time to extract the track points and insert them into a MariaDB database. We start by calling the connect –method of the data –module to get a connection to MariaDB. After that we iterate over the JSON contents:

The lon variable holds the longitude coordinate of the trackpoint and lat the latitude. Unfortunately MariaDB doesn’t yet support the 3rd coordinate in GIS, elevation, otherwise we could have added it also. The lon and lat are given to the data.insertPoint –method which stores them as a GIS Point in the database:

Make sure you check out the data.js file for how to connect and disconnect to MariaDB.

Screenshot 2: Parsing the GPX file
Screenshot 2: Parsing the GPX file

Show on map

The final thing I wanted to do was to see how easily I could plot the stored GIS points on a map. To start with we need to retrieve all the GIS points from the database, which is done is data.js in the following way:

From the above we can see that an array is built which is passed directly as an http response and of course by default in JSON format. This is very good for Google Maps which now can point to a URL, which in this case is /track and read the JSON formatted array of points from there and plot a so-called Polyline connecting each of the points to a nice track.

Screenshot 3: The GIS points shown as a polyline on Google Maps
Screenshot 3: The GIS points shown as a polyline on Google Maps

A lot more could of course be done when having the GIS points stored in the database, including:

  • distances could be counted
  • I could view which of my runs that intersect with each other (well this is actually a MariaDB 10.1 feature since INTERSECT will be introduced there)
  • count how many of my runs are inside a certain area
  • time could be added so that duration and pace could be measured

To sum it up, I would say that whenever you think about creating your next app, being it a web application or a smartphone app with the need of a back-end node.js + MariaDB is a great choice and all the pieces needed are available. You’ll have a full blown and proven RDBMS backing up your application.

Also location awareness and other geographical functionality seems to be almost mandatory in new development. MariaDB includes quite a nice (and properly implemented) set of spatial data types, GIS functions and other relevant functionality.

There has been a lot of discussion about MariaDB 10.0 throughout the whole year. When will it be released, what will it include, what is the focus on MariaDB going forward, etc? My feeling is that people have in the past few months started to understand the value of MariaDB 10.0. There is a good group of people and companies that have been trying out and using the MariaDB 10.0 alpha releases and providing us with excellent feedback.

MariaDB 10.0 is a massive release with loads of new features which ease several pain points that MariaDB and MySQL users have run into over the years.

The highlights of MariaDB 10.0 can be divided into the following areas:

  • Replication enhancements
  • Storage engines
  • Optimizer enhancements
  • Administration improvements
  • Other important new features
  • Merged features and functionality from MySQL 5.6

I’ll go through each of these areas briefly to give an overview on what they include.


Global Transaction Id – In replication, MariaDB 10.0 introduces a new building block, Global Transaction Id(GTID). The implementation is different from the corresponding functionality in MySQL 5.6 and the main goals of the implementation have been 1) make it easy to set up replication with GTID and easily provision new slaves and 2) support other replication features such as Multi-Source Replication (see below). The introduction of GTID also makes the slaves crash safe, which they haven’t been before.

Multi-Source Replication – With Multi-source replication, contributed by Lixun Peng and Taobao you can replicate data from many masters to one slave. This is very useful when you have sharded your data and want a complete copy of it on one server. For this very reason Tumblr, the microblogging site, nowadays part of Yahoo! is already using this feature. Check out the story.

Parallel Slave (sponsored by Google) – Finally, MariaDB 10.0 solves a long lasting replication challenge that even exists in MySQL 5.6. Until now, with enough writes (INSERT/UPDATE) happening on a master the slaves would not be able to keep up at the same pace and they would lag behind. With the parallel slave feature in 10.0 this challenge is now gone. The slaves will adapt to the speed of the master and apply binlog events in parallel. Transactions will be applied in parallel if they were executed in parallel on the master. Unlike MySQL 5.6, the transactions can concern the same database or even the same table.


Many storage engine developers have been actively engaging with the developers of MariaDB lately and as a result there are several new storage engines in MariaDB 10.0 (in alphabetical order):

  • Cassandra Storage Engine: Combine data from Cassandra with your data in MariaDB, read and write Cassandra data, and connect many MariaDB servers to one Cassandra ring, creating a high-availability cluster.
  • Connect Storage Engine: Use Connect to handle data in files or behind ODBC as if that data was part of the data stored in MariaDB. Developed and contributed by Olivier Bertrand.
  • Sequence Storage Engine: Create numerical sequences that are stored in memory and can be used in your queries. For some use case examples check this article.
  • Spider Storage Engine: A storage engine with built in sharding capabilities. Developed and contributed by Kentoku Shiba.
  • TokuDB: A storage engine designed for high performance write-intensive workloads. Developed and contributed by Tokutek.


Engine Independent Persistent Statistics – Statistics are now provided separately from storage engines. Before, statistics were supplied by the storage engines themselves and the quality of the statistics were usually quite poor. Also the storage engine interface puts a lot of restrictions on them. Statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Better statistics results in better execution plans and end users will experience faster results in general.

Histograms – To improve query optimization, MariaDB 10.0 introduces histograms. Histograms can be collected for both indexed and non indexed columns. Histogram data allows the query optimizer to make better query plan choices for complex multi-table queries.

Optimizations for EXISTS subqueries – the optimizer will convert EXISTS subqueries to IN, when possible. This conversion allows the usage of semi-join optimizations or Materialization optimization for certain classes of EXISTS subqueries.


10.0 includes a set of features that make the life of DBAs easier:

  • Roles – This is a big thing! Long awaited roles are introduced in MariaDB. DBAs can now create roles with certain permissions and assign users to the roles. Thanks to Google Summer of Code and developer Vicențiu Ciorbaru!
  • SHOW EXPLAIN – get the query plan of a running statement.
  • EXPLAIN in the Slow Query Log – write EXPLAIN output into the slow query log.
  • EXPLAIN INSERT/UPDATE/DELETE – EXPLAIN for INSERT/UPDATE/DELETE has been added in the same way as in MySQL 5.6, but the implementation is different.
  • SHOW PLUGIN SONAME – list all plugins despite them being installed or not.
  • SHUTDOWN – Finally use SQL to SHUTDOWN the server
  • KILL QUERY ID – Kill a specific query with its id, not the thread id (solves a nasty race condition).
  • Per-connection memory usage – List each connection with amount of memory used


There are also a bunch of new interesting features that don’t fall into the above categories:

New REGEXP – The switch to the modern regexp library PCRE together with the new SQL
functions REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR improve MariaDB text processing capabilities drastically. Developed during Google Summer of Code by Sudheera Palihakkara.

DELETE … RETURNING – Many users asked for the possibility for the DELETE statement to return the deleted rows in a resultset. It’s now possible by adding RETURNING at the end of the statement.

ALTER … IF [NOT] EXISTS – In your ALTER statements you can now add the IF or IF NOT keywords to e.g. add a column or create an index if it does or does not exist from before.

Named Dynamic Columns – In earlier versions of MariaDB Dynamic Columns, which allows for a different number of columns in each row, columns could only be referred to by number. Now the columns can be referred to by name.

Multiple GET_LOCK() – The use of many user level locks is now supported. Developed and contributed by Konstantin Osipov.


Selected pieces of MySQL 5.6 have been merged into MariaDB 10.0. The main ones are:

  • InnoDB – Most InnoDB enhancements are now in MariaDB 10.0.
  • Performance Schema – The full new performance schema with all the new event filtering, instrumentation, and other goodies.
  • Online ALTER – Do many of your schema changes without interrupting the system
  • Filesort optimization for queries using the ORDER BY … LIMIT optimization – A useful optimization for showing only a few rows of a bigger result set.
  • Character set extensions
  • TO_BASE64(), FROM_BASE64

Obviously there exist many smaller enhancements in MariaDB 10.0 and other things merged from 5.6, but the above overview of new features and merges includes the biggest ones.

As you can see, the community has been very active in contributing to 10.0 and it’s absolutely fantastic to be able to have all these new features pulled together into MariaDB and having them available to all MariaDB 10.0 users by default. A big thank you to all contributors!

Go give it a try and download the MariaDB 10.0.5 Beta!

Download MariaDB 10.0.5

Release Notes Changelog What is MariaDB 10.0?

If you’re excited about MariaDB 10.0 and its features or MariaDB in general and have a great MariaDB story to share, about some interesting use of MariaDB or some specific feature you love, please let us know.

PS. If you are attending Percona Live in London make sure you come and celebrate the launch of the MariaDB 10.0 beta with us over a drink or two on Mon 11th Nov. More info here.


First, congratulations Oracle on the GA of MySQL 5.6! Well done!

In this post I walkthrough the features of the first two alpha versions of MariaDB 10.0. The first, 10.0.0-alpha, which was made available in November, and 10.0.1-alpha that saw daylight yesterday. I will go through the features by placing them in the following categories:

  • MariaDB 10.0-only Features (features that aren’t in MySQL 5.6)
  • MariaDB 10.0 Merged Features (features merged from MySQL 5.6)
  • MariaDB 10.0 Reimplemented Features (features reimplemented from features in MySQL 5.6)
  • MariaDB 5.x Features now in MySQL 5.6 (features introduced in earlier MariaDB versions which have now been introduced in MySQL 5.6)
  • MariaDB 5.x Features Backported from MySQL 5.6 (features introduced in earlier MariaDB versions which were backports of features from MySQL 5.6 development versions)

Some of the features will have links to the MySQL manual for the documentation Oracle has made available on the feature.

MariaDB 10.0-only Features

Features in this section are unique to MariaDB 10.0 and aren’t found in MySQL 5.6.

Available since 10.0.0
  • Multi-source replication (MDEV-253)
    • Multi-source replication is a longtime wish of many users. In scenarios where you partition your data over many masters you can then replicate the data from all masters onto one slave. Typical use cases are:
      • Data partitioned over many masters can be pulled together onto one slave for analytical queries
      • Many masters can replicate to the same slave and a complete backup can be done on the slave
      • Newer hardware usually provides more performance. Usually all hardware isn’t upgraded at once and multi-source can be used for replicating many masters to a powerful new slave.
    • Original code from Taobao
  • Even faster Group Commit
    • Further enhancements have been made to group commit. A couple of blog posts about the improvements by the developer, Kristian Nielsen, can be found here.
    • Get the query plan of a running statement.
Now available in 10.0.1
  • Cassandra Storage Engine
    • An integration of the NoSQL database Apache Cassandra. Cassandra is seen as a storage engine to MariaDB. The integration enables:
      • Combining data from Cassandra and MariaDB
      • Reading and writing to Cassandra from MariaDB. SQL’s SELECT, INSERT, UPDATE and DELETE all work.
  • Engine independent statistics
    • Optimizer statistics is the collection of data that describe more details about the database and the objects in the database.
    • Statistics are now provided separately from storage engines. Before, statistics were supplied by the storage engines themselves and the quality of the statistics were usually quite poor. Also, since before this they were provided through the storage engine interface, a lot of restrictions were put on them.
    • These statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Better statistics results in better execution plans and end users will experience faster results in general.
    • Statistics are collected also for non-indexed columns. InnoDB’s statistics, for example, were previously only for indexes.
  • Improved Dynamic Columns
    • Dynamic Columns has been in MariaDB for a while already. This feature allows you to store a different set of columns for every row in a table. In that manner Dynamic Columns can be called NoSQL-like.
    • Since MariaDB introduced Dynamic Columns there has been user feedback and research going on to improve it further. Dynamic Columns has some new capabilities that now are in mainline MariaDB:
      • Database interoperability: It’s pretty rare that companies use only a single type of database and even critical business systems are often built on several different types of databases. Usually the data from those different databases is combined in an upper application level. MariaDB introduces the possibility of doing this at a low level inside the MariaDB database. The first implementation of this is integration with Cassandra. With Dynamic Columns and the Cassandra Storage Engine you can now combine data residing in Cassandra with data inside MariaDB and this is done through normal looking queries on the MariaDB side.
      • Data interchange: JSON has become a very popular standard for data interchange. In Dynamic Columns one can now request a row in JSON format.
  • Per thread memory usage (MDEV-4011)
    • Based on a patch by Taobao, INFORMATION_SCHEMA and SHOW STATUS enables the analysis of thread specific memory usage
  • Faster ALTER TABLE with UNIQUE key (MDEV-539)
    • Significant speed up of ALTER TABLE with unique keys (for Aria and MyISAM storage engines)

MariaDB 10.0 Merged Features

Features listed in this category have been directly merged from MySQL 5.6.

Already available since MariaDB 10.0.0
  • InnoDB and Performance Schema
    • Most InnoDB enhancements, but some, for example InnoDB’s fulltext capabilities, will come in an upcoming version of 10.0.
      MySQL Manual: InnoDB.
    • The full new performance schema with all the new event filtering, instrumentation, and other goodies.
      MySQL Manual: Performance Schema
  • ORDER BY … LIMIT -optimization
    • A useful optimization for showing only a few rows of a bigger resultset.
      MySQL Manual:Limit Optimization
Now added in 10.0.1
  • Plugin-load-add (MDEV-3860)
    • Used to avoid specifying a large set of plugins in a single long argument

MariaDB 10.0 Reimplemented Features

These features are re-implementations of the corresponding functionality in MySQL 5.6. In future versions of MariaDB 10.0 there will be a few more features in this category. I’ll cover them in a future blog post.

In 10.0.1
  • Add full support for auto-initialized/updated timestamp and datetime

MariaDB 5.x Features now in MySQL 5.6

Earlier MariaDB 5.x versions included features that have now been introduced in MySQL 5.6. It should be noted that the corresponding features in MySQL 5.6 haven’t been merged from MariaDB. Oracle has re-implemented them.

MariaDB 5.x Features Backported from MySQL 5.6

These features were merged from MySQL 5.6’s development trees to MariaDB, where they were then hardened for production use.

  • Binlog checksums, were introduced in MariaDB 5.3. It is backport of the corresponding feature in MySQL 5.6.

As you can see above there are quite many features in MariaDB 10.0 already, but more is coming. Stay tuned for an update on features going into MariaDB 10.0.2.