The MariaDB Blog The Official Blog of the MariaDB Project

Tag Archives: mysql

Today we upgraded the PCRE library bundled with MariaDB-10.0 to PCRE-8.34. This PCRE release includes some improvements, fixes for better stability and performance, and gives more compatibility with the Perl regular expressions.

I’d like to give details on the PCRE changes that especially affected MariaDB.

PCRE now includes support for [[:<:]] and [[:>:]] as used in the BSD POSIX library (written by Henry Spencer) to mean “start of word” and “end of word“, respectively. This is a good news for those project (like MariaDB) migrating from the Henry Spencer’s library to PCRE, as this non-standard syntax seemed to be used quite widely. Many thanks to Philip Hazel and the PCRE team who kindly added this extension into PCRE and who gave us a patch before the final 8.34 release, so we were able to fix an incompatibility with MySQL RLIKE earlier (see “MDEV-5357 REGEXP word boundaries don’t work“, fixed in Maria-10.0.7).

PCRE-8.33 has also fixed a crash caused by stack overrun in pcre_compile() in cases when the pattern contains a very deep level of nested parenthesis. PCRE now has a compile-time limit (250 by default) on the depth of nesting of parentheses. This works perfectly fine with programs using the OS default stack size settings, and instead of crashing, pcre_compile() now returns an error safely. However, unfortunately, this new limit did not help us, because MariaDB uses a smaller individual thread stack size, needed to handle dozen thousands concurrent connections and controlled by the @@thread_stack MariaDB system variable with the default value of 288Kb (versus the default Posix thread stack size of 8Mb). With the default @@thread_stack=288Kb, MariaDB still would crash with the verbatim copy of PCRE-8.34 on a query like this:

1

SELECT'a'RLIKEREPEAT('(',1000);

The exact number that would hit the crash might vary on different operating systems. It was about 210 during my tests on a Fedora box, which a little bit smaller than the new PCRE default limit

To prevent the crash we had to keep our patch that adds a callback function into PCRE. See pcre/mariadb-patches/pcre_stack_guard.diff for details. pcre_compile() calls this callback function every time when a parenthesis is met in the regular expression pattern, before going into the next recursion level. If the thread stack size gets dangerously small, mysqld indicates this by the callback function result, pcre_compile() returns with an error, and the entire SQL query display the error message:

1

2

3

4

mysql>SELECT'a'RLIKEREPEAT('(',1000);

ERROR1436(HY000):Threadstackoverrun:

263672bytesusedofa294912bytestack,and32000bytesneeded.

Use'mysqld --thread_stack=#'tospecifyabiggerstack

If you give more stack size by starting mysqld with say --thread-stack=589824, threads don’t run out of stack too early, so the compiled PCRE limit is hit instead, which is indicated by a different error message:

We’ll be watching if the future versions of PCRE add some built-in means to control the used stack size. In the meanwhile, we’ll have to keep compiling the bundled patched version even though if PCRE is installed in the system.

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.35. This is a Stable (GA) release. See the Release Notes and Changelog for detailed information on this release and the What is MariaDB 5.5? page in the MariaDB Knowledge Base for general information about the MariaDB 5.5 series.

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

The source code for my application can be found on Github: https://github.com/rasmushoj/nodejs-gpx-mariadbI’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 MariaDB. Remember 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:

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:

1

2

varcommon=require('./routes/common');

vardata=require('./data.js');

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:

1

app.use(express.json());

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:

1

2

3

4

5

6

7

8

app.get('/upload',common.fileForm);

app.post('/upload',common.fileUpload);

app.get('/readfile',common.readFile);

app.get('/parsegpx',common.parseGPX);

app.get('/track',common.listPoints);

app.get('/showmap',function(req,res){

res.sendfile(__dirname+'/views/maps.html');

});

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.

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:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

<trkpt lon="24.8374462127686009"lat="60.1847839355469034">

<ele>4.4632568359375</ele>

<time>2013-09-21T12:00:11.000Z</time>

<extensions>

<gpxtpx:TrackPointExtension>

<gpxtpx:hr>137</gpxtpx:hr>

</gpxtpx:TrackPointExtension>

</extensions>

</trkpt>

<trkpt lon="24.8374595642089986"lat="60.1846923828125000">

<ele>4.4632568359375</ele>

<time>2013-09-21T12:00:14.000Z</time>

<extensions>

<gpxtpx:TrackPointExtension>

<gpxtpx:hr>146</gpxtpx:hr>

</gpxtpx:TrackPointExtension>

</extensions>

</trkpt>

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:

1

2

3

4

5

for(variinjson.gpx.trk[0].trkseg[0].trkpt){

lon=json.gpx.trk[0].trkseg[0].trkpt[i].$.lon;

lat=json.gpx.trk[0].trkseg[0].trkpt[i].$.lat;

data.insertPoint(lon,lat);

}

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

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:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

exports.getTrackPoints=function(trackId,httpRes){

varxys=[];

client.query('SELECT X(GeomFromText(AsText(gpsPoint))) AS X, Y(GeomFromText(AsText(gpsPoint))) AS Y FROM trackpoints WHERE trackId = :pTrackId',{pTrackId:trackId})

.on('result',function(res){

res.on('row',function(row){

xys.push(row);

})

.on('error',function(err){

console.log('Result error: '+inspect(err));

})

.on('end',function(info){

console.log('Result finished successfully');

});

})

.on('end',function(){

httpRes.send(xys);

});

}

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

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.