The latest from the MySQL community

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

Sometimes replication halts with an error like:

Slave I/O: Got fatal error 1236 from master when reading data from binary log
Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master;

If it's the SQL thread instead of the I/O thread, it might complain about 'Event too big'. The error could also be the other direction, complaining of 'Event too small'.

I rarely see this error actually have anything to do with max_allowed_packet. You can check the largest event in the master's binary log file. Even though the binary log records the length in each entry, mysqlbinlog doesn't expose it, so we have to do the math ourselves:

mysqlbinlog mysql-bin.00XXX | gawk "/^# at / { diff = $3 - prev; prev = $3; } (diff > max) { max = diff } END {print max}" -

If the result is larger than max_allowed_packet, then the problem and solution are …

This time it is nothing like a linkt to another Blog:  Galera on Mesos

Ok as a fact I was kinda involved. Even the work is done by Stefan all alone. We meat for a day in a coworking space and discussed about Galera and Mesos.
In the end Stefan produced this incredible blogpost and pushed Mesos forward.
Whats the fun about this post?
We already now Galera is already the standard in a lot of architectures. For example OpenStack. Doing consultant work for Docker also I encourage to use Galera for all this infrastructures Docker runs on. 
Mesos is about to run easy on 1000 nodes. It has a nice abstraction of nodes and framework. Companies like Airbnb, Paypal, eBay, Groupon use Mesos. Having a Galera poc for Mesos is going to make it likely to …

New version of JSON UDF functions has been just released. This version introduces two new features.

1. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::'
2. Second feature is much bigger. Now JSON functions accessing elements by a key (JSON_CONTAINS_KEY, JSON_EXTRACT, JSON_REPLACE, JSON_REMOVE, JSON_APPEND, JSON_SET, JSON_COUNT) can use alternate keypath syntax: $.key1.key2[arr_index] where $ is root element, .key is key name, [arr_index] is array index. JSON_SEARCH can also return path in this format with escaped $, . and [] symbols.

Improving the performance of a web system involves knowledge of how the entire technology stack operates and interacts. There are many simple and common tips that can provide immediate improvements for a website. Some examples include:

• Using a CDN for assets
• Compressing content
• Making fewer requests (web, cache, database)
• Asynchronous management
• Optimizing your SQL statements
• Have more memory
• Using SSD’s for database servers
• Updating your software versions
• Adding more servers
• Configuring your software correctly
• … And the general checklist goes on

Understanding where to invest your energy first, knowing what the return on investment can be, and most importantly the measurement and verification of every change made is the difference between blind trial and error and a solid plan and process. …

Have you ever had a case where you needed to find a process which sent a HUP/KILL/TERM or other signal to your database? Let me rephrase. Did you ever have to find which process messed up your night? If so, you might want to read on. I’m going to tell you how you can find it.

Granted, on small and/or meticulously managed systems tracking down the culprit is probably not a big deal. You can likely identify your process simply by checking what processes have enough privileges to send mysqld a HUP/KILL/TERM signal. However, frequently we see cases where this may not work or the elimination process would be too tedious to execute.

We recently had a case where a process was frequently sending SIGHUPs to mysqld and the customer asked us to see if we could get rid of his annoyance. This blog is the direct result of a discussion I had with my colleague …

From time to time we detect query patterns that are not well fitted to the BTree+ structures provided by InnoDB. One such situation is when you need to traverse a hierarchy (tree) or graph structure with many nodes. Specialist databases exist for this such as Neo4J. However there exists a simple solution in the form of  OQGraph which is distributed with MariaDB and is documented here.

The OQGRAPH engine is based on an original idea by Open Query founder Arjen Lentz, and was developed in-house with Antony Curtis at Open Query.

A …

MariaDB 10.1.3 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.3/

This is the 1st beta, and 4th overall, release of MariaDB 10.1, so there are a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 420 – 117 in 10.1.2 & 637 in 10.1.1, fwiw).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

• Table and Tablespace Encryption.
• …

MySQL 5.6.23 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 3 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1j to version 1.0.1k. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
2. Functionality Changed: Support for the SSL 2.0 and SSL 3.0 protocols has been disabled because they provide weak encryption. (Bug #19820550, Bug #19921150)
3. Functionality Changed: yaSSL was upgraded to version …

MariaDB 5.5.42 was recently released (it is the latest MariaDB 5.5), and is available for download here:

https://downloads.mariadb.org/mariadb/5.5.42/

This is a maintenance release, and so there were not too many changes at all and only 3 changes (enhancements) I felt noteworthy:

• The new version of the Audit Plugin is 1.2 is included with the following new features:
  • In the audit log passwords are now masked, i.e. the password characters are replaced with asterisks.
  • It’s now possible to filter logging to include only DDL (CREATE, ALTER, etc.) or DML (INSERT, UPDATE, etc.) statements.
  • For more information please refer to the About the MariaDB Audit Plugin page. The plugin is …