Open Source Database

MySQL 5.7.4 Overview and Highlights

Chris on MySQL - April 15, 2014 - 2:47pm GMT

MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

In Memoriam:

“This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

As for the fixes, there are quite a few, which is to be expected in such an early milestone release.

The main highlights for me were:

  1. The Performance Schema now instruments prepared statements (for both the binary and text protocols). Info is available in the prepared_statements_instances table, along with performance_schema_max_prepared_statements_instances system variable, and Performance_schema_prepared_statements_lost status variable.
  2. Incompatible Change: MySQL deployments installed using RPM packages now are secure by default (single root account, ‘root’@'localhost’, no anonymous-user accounts, no test database).
  3. Incompatible Change: MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.
  4. Performance; InnoDB: InnoDB now supports multiple page_cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page_cleaner threads.
  5. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument
  6. InnoDB: InnoDB now supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.

Of course, there were many, many more fixes/updates (InnoDB being #1, Replication #2, and Partitioning #3 with most fixed bugs), so be sure to read through the full changelog. And if you are running a previous version of *5.7*, then definitely plan on upgrading to this latest 5.7.4.

Hope this helps.


MySQL 5.6.17 Overview and Highlights

Chris on MySQL - April 11, 2014 - 12:01am GMT

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

For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  1. Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  2. Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES).
  3. InnoDB: MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations: OPTIMIZE TABLE, ALTER TABLE … FORCE, and ALTER TABLE … ENGINE=INNODB
  4. On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.
  5. mysql_install_db provides a more informative diagnostic message when required Perl modules are missing.
  6. The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL. ALTER IGNORE TABLE causes problems for replication, prevents online ALTER TABLE for unique index creation, and causes problems with foreign keys (rows removed in the parent table).
  7. Incompatible Change: Old clients (older than MySQL 5.5.7) failed to parse authentication data correctly if the server was started with the –default-authentication-plugin=sha256_password option.

In addition to those, there were 60 other bug fixes:

  • 21 InnoDB
  • 13 Replication
  • 01 Partitioning
  • 25 Miscellaneous

There were 2 regression bugs fixed, so check if they might have affected you, and if so, then you’ll want to consider upgrading (*especially* note the 1st since it’s InnoDB and performance degradation)

  1. InnoDB: A regression introduced by Bug #14329288 (Oracle-internal) would result in a performance degradation when a compressed table does not fit into memory. (Bug #71436)
  2. Building MySQL from source on Windows using Visual Studio 2008 would fail with an identifier not found error due to a regression introduced by the patch for Bug #16249481 and Bug #18057449 (both Oracle-internal bugs).


  1. If you use AES_ENCRYPT() and AES_DECRYPT(), I’d definitely investigate the changes, and plan for an upgrade (but test/check first and make and necessary changes first).
  2. Since there were 21 InnoDB bugs fixed, including 1 regression bug, I would recommend upgrading if you’re running a previous 5.6 version.
  3. Similarly, since there were 13 replication bugs, if you have a replication setup, I’d also plan to upgrade to take advantage of the fixes (and not be affected by the bugs).

The full changelogs can be viewed here (which has more details about all of the bugs listed above):

Hope this helps.


MariaDB 10.0.10 Overview and Highlights

Chris on MySQL - April 8, 2014 - 9:17pm GMT

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

This is the first GA (“Generally Availability“, aka “recommended for production systems”) release of MariaDB 10.0, and 11th overall release of MariaDB 10.0.

Since this is the initial 10.0 GA release, this is primarily a bug-fix and polishing release.

Here are the main items of note:

  1. The Audit Plugin is now included in MariaDB (MDEV-5584)
  2. Improved XtraDB performance by fixing incorrect calculation of flushed pages (MDEV-5949)
  3. Fix for GTID duplicate key multi-master corruption bug (MDEV-5804)
  4. Default TokuDB compression is now TOKUDB_ZLIB (instead of TOKUDB_UNCOMPRESSED)
  5. Various algorithm improvements for engine-independent table statistics (EITS) (MDEV-5901, MDEV-5917, MDEV-5950, MDEV-5962, MDEV-5926)

You can read more about the 10.0.10 release here:

And if interested, you can review the full list of changes in 10.0.10 (changelogs) here:

Hope this helps.


MySQL 5.5.37 Overview and Highlights

Chris on MySQL - April 3, 2014 - 2:20am GMT

MySQL 5.5.37 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

This release, similar to the last 5.5 release, is mostly uneventful.

There was one new feature added (Solaris-specific + obscure), and only 21 bugs fixed.

The new feature is this:

  • On Solaris, mysql_config –libs now includes -R/path/to/library so that libraries can be found at runtime.

Out of the 21 bugs, most were benign, but there was one definitely worth mentioning (because it is a regression bug with performance degradation):

  • A regression introduced by Bug #14329288 would result in a performance degradation when a compressed table does not fit into memory. (Bug #18124788, Bug #71436)

The bug that causes this regression, bug #14329288 (Oracle-internal), was introduced in 5.5.30, thus versions 5.5.30 through 5.5.36 are affected by this. There are some more details, including some benchmarks on the performance degradation, here.

For reference, the full 5.5.37 changelog can be viewed here:

Hope this helps.


Improve your Stored Procedure Error Handling with GET DIAGNOSTICS

Chris on MySQL - April 2, 2014 - 11:37pm GMT

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written.

It’s common to see exit handler code in the following form:


Where the SELECT outputs something not very useful in many cases.

With GET DIAGNOSTICS, you can get all of the error information, and you should, if not already.

If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

However, since we want the information from within the stored procedure, we must put this within the exit handler code. So the above exit handler code, now becomes (and I added 1 more line for formatting):

 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;

Which results in:

mysql> call my_proc('abc');
| @full_error                                      |
| ERROR 1146 (42S02): Table 'db1.t1' doesn't exist |
1 row in set (0.01 sec)

For more details on GET DIAGNOSTICS, I would recommend the following 2 pages:

Hope this helps.


Quickly Debugging Stored Procedures, Functions, Triggers, and Events with RESIGNAL

Chris on MySQL - April 2, 2014 - 10:59pm GMT

I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

It had a standard exit handler catch-all for SQLEXCEPTION, which was:


When there was an error, it didn’t really output anything useful.

As of MySQL 5.5, there is RESIGNAL:

“RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”

There is also some good information about it here as well:

It is very simple to use, just add it (though it is much more robust that just this – see above link):


By just adding RESIGNAL, in addition to what was output by the SELECT, we now see the actual error printed immediately after, for instance:

mysql> call my_proc('abc');
| STATUS  | Records | Query |
| ....... | NULL    | NULL  |
1 row in set (0.01 sec)

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

Notice the last line is what you are looking for (when troubleshooting).

As I mentioned, this is available as of MySQL and MariaDB 5.5.

If you are using MySQL 5.6 or MariaDB 10.0, I would recommend skipping RESIGNAL and using GET DIAGNOSTICS within your exit handler to obtain the exact error code, SQL state, and error message *all* of the time. I cover using GET DIAGNOSTICS here, if interested.

Hope this helps.


InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN

Chris on MySQL - March 26, 2014 - 8:19pm GMT

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)

The query is:


This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index

EXPLAIN returns NULL for “possible_keys”. This means there was no relevant index (though we see one was used at some point):

“It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.”

So “possible_keys” returns NULL, but key returns `id2`.

Per the above, that means `id2` is a covering index.

This part is a bit strange, as it is not readily intuitive why this is the case.

I mean, the query asks for “id1″, yet EXPLAIN says “id2″ is the covering index.

How is this possible and why isn’t just the Primary Key chosen anyway?

This is possible because of the way InnoDB’s secondary indexes are stored. In InnoDB, secondary indexes are stored along with their corresponding primary key values, so by looking at the secondary index, you can see the primary key values.

As for why this is chosen instead of the primary key, it took some digging, but I found the exact comments as to why in the source code (sql/ in the find_shortest_key() function:

"As far as
1) clustered primary key entry data set is a set of all record
	fields (key fields and not key fields) and
2) secondary index entry data is a union of its key fields and
	primary key fields (at least InnoDB and its derivatives don't
	duplicate primary key fields there, even if the primary and
	the secondary keys have a common subset of key fields),
then secondary index entry data is always a subset of primary key entry.
Unfortunately, key_info[nr].key_length doesn't show the length
of key/pointer pair but a sum of key field lengths only, thus
we can't estimate index IO volume comparing only this key_length
value of secondary keys and clustered PK.
So, try secondary keys first, and choose PK only if there are no
usable secondary covering keys or found best secondary key include
all table fields (i.e. same as PK):"

So since secondary index entry data is always a subset of primary key entry, scanning the secondary index should generate slightly less IO than scanning the primary key.

Fwiw, this also explains why key_len is less if you specify “USE INDEX (Primary)” to force it to use the PK over the secondary index:

mysql> EXPLAIN SELECT id1 FROM t1 USE INDEX (Primary)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index

Note this reports key_len of 4, whereas the former reported 5.

Hope this helps.


MariaDB 10.0.9 Overview and Highlights

Chris on MySQL - March 14, 2014 - 11:22pm GMT

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

This is the second RC (“Release Candidate”) release of MariaDB 10.0, and 10th overall release of 10.0. All features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.9:

  1. InnoDB upgraded to version 5.6.15
  2. Extended-keys optimization is now enabled by default.
  3. MariaDB can be compiled to use the system’s PCRE library.
  4. Added MASTER_GTID_WAIT() and @@last_gtid.
  5. When a TIME value is casted to a DATETIME, the date part will be the CURRENT_DATE, not 0000-00-00. This is compatible with the SQL standard and MySQL-5.6. One can use @@old_mode=ZERO_DATE_TIME_CAST to revert to the old behavior.
  6. XtraDB is now the default InnoDB implementation, Oracle InnoDB is a plugin that can be dynamically loaded if desired.
  7. Builds for Debian Sid and Ubuntu Trusty are being made available for the first time in the MariaDB repositories. For this release the Trusty packages are considered as alpha releases. The Sid packages will likely always be considered as such. Both were made as part of normal MariaDB development, and we’re making them available for those that want to test or try them out.

Also, if you read my MariaDB 10.0.8 Overview post, you will have noticed there was a build issue with Percona’s XtraDB that was preventing it from being the default in MariaDB 10.0. I’m glad I posted about it, as the MariaDB and Percona Devs began working together immediately, and resolved the issue in days, and now XtraDB+ is back the default InnoDB in MariaDB 10.0. (This is mentioned in the bullet point #6 above, but I wanted to explicitly mention it as well, so it did not go unnoticed.)

You can read more about the 10.0.9 release here:

And if interested, you can review the full list of changes in 10.0.9 (changelogs) here:

Hope this helps.


MariaDB 5.5.36 Overview and Highlights

Chris on MySQL - March 14, 2014 - 10:36pm GMT

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

This is a maintenance release, and so there are not too many big changes of note, just a number of normal bug fixes. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.36
  2. TokuDB is now included in RPM packages for CentOS 6 on x86-64

Note this release did not contain the bugfixes from XtraDB 5.5.36 yet. There is one bug fix in XtraDB 5.5.36 that is fairly serious, so if you are a *heavy* XtraDB+ user, I might wait for MariaDB 5.5.37 to be released, so you get this fix. I should note the bug was partially fixed in 5.5.35, and I did not see reports of the same issues in 5.5.35, so perhaps that covered it, or at least the majority. 5.5.34 had the worst of this bug, so if you are running 5.5.34, then I *would* definitely recommend upgrading.

Additionally, if you are using MySQL’s InnoDB in MariaDB, then I would definitely recommend upgrading to MariaDB 5.5.36 as well.

If interested, there is more about the 5.5.36 release here:

And the full list of fixed bugs and changes in MariaDB 5.5.36 can be found here:

Hope this helps.


MyQuery 3.5.4 Released - Now with Client side dynamic columns!

Karlsson on databases and stuff - March 3, 2014 - 3:50pm GMT
It was quite a while ago since I released a new version of MyQuery, my Windows based query tool for MariaDB and MySQL. I did build a 3.5.3 version, but I decided not to do a public release of that. But now we have 3.5.4 ready, and there is some new shiny features in it. The main feature is that the dialog that you can use to inspect individual fields with, which pops up when you doubleclick (or now you can Right-click and select Show Menu) in a field, has been expanded.

To being with, you can use this to navigate the fields, so that you don't have to close this dialog and open it in the next field, instead there are navigational buttons in the Dialog.

But there is also some additions to the dialog itself. If you are using MariaDB and the Dynamic Columns feature, which is mostly used with the Cassandra Storage Engine but can be used with any blob data in any engine, then you can view these in the ShowData dialog, either as a tree or as JSON.

Another addition is that I have upgraded help links to MySQL 5.6 and MariaDB 10. Also, a lot of MariaDB features and now syntax coloured as appropriate. Another usability feature is the ability to copy a single column from a query result to the clipboard.

Download MySQL 3.5.4 from sourceforge and enjoy!


The soul of a new machine

Karlsson on databases and stuff - March 3, 2014 - 11:39am GMT
I re-read "The Soul of a new Machine" (Swedish translation "En Dators Födelse") by Tracy Kidder the other day and it still quite a marvelous book. It was a long time ago since I read it last and I have to admit this really made me feel old. And I admit: Yes, I worked with VAXen (yes, that is what pluralis of a VAX is, among us hackers from the days when being a "hacker" was a good thing. I used to be called Anders "Hackin'" Karlsson back then) and I worked with Data General machines and I have also worked as a Hardware Engineering back when working with a CPU was not necessarily something done in software and when if you didn't have enough bits in a register in a register in the CPU, you could add them yourself with some TTL chips piggy-backed onto the ones on the main board itself (in this case the "register board") and then attached at appropriate points with hand-soldered wires and some paths on the board appropriately cut.

Piggy-backing was used to get power to the added chips, and to mount them somewhere convenient, instead of having the just floating around in the wires, it looked somewhat like this:
But instead of connection all pins you just connected the power and grounds ones (on the upper right and lower left corners respectively) and the other pins were bent to point straight out, and then you soldered the wires to these pins and then attaching the other end of these.

And before you ask, before going on to do this, this had to be designed, somehow, and I did that too. In addition, you had to understand the original design of this, but that I figured out without too much work, What I never did do was to write even a single piece of code for this monster machine. How I got involved with this was interesting. I was working as a sysadmin for the development system machine for this box, which was a PABX based on Meridian but modified by Swedish Televerket (not Telia). When this localized version needed upgrading (as machines requiring higher capacity had been sold), it was assumed that the newer Nortel Meridian parts could be used. But this rurned out not to be that case, Nortel and developed the Meriad further since the Localized version was released, and the local version had also changed, but in a different direction, the parts actually wouldn't even physically fit!

Disaster! What do we do NOW? The customer has already bough this thing and now we can't deliver? And in Sweden, there wasn't much hardware enginering resources for this puppy anymore. So someone the manager of the development of this box thinks a bit and then realized that there is this weirdo young sysadmin, who by the way knows absolutely nothing about Meridian (but a fair amount about the development system for it, which was running Unix, Interactive Unix, based on Unix Version 6, no less! running on a PDP/11 70). This sysadmin boy had at some point said something that he was building his own home computer (which I was) so maybe he could have a look at this? Now, I was not the least qualified for. Not at all, so I obviously said no... Not! I think many of you were like that in your twenties also: You knew just about EVERYTHING on computers, and if someone asked you to do something, you just said yes. In this case I was asked to extend the page bank register in the SL/1 (which was the technical name for the Meridin and was a 16-bit machine with multiple 64k banks of memory).

Did I get this to work? Yes, I did. Don't ask me how, though. Today, some 30 years later, I still don't understand how I managed to do this, and if I was asked today, stuffed to the rim with training, knowledge and experience, to modify the CPU of some machine, I would assume the person asking had used excessive amounts of paint remover, but not with the intent of removing paint but with the quite successful intent of removing whatever small portion that is left of common sense from his/her brain.

This was my last job at Telia / Televerket (not because what I did didn't work. As far as I know, the CPU I designed and then built, was put in beta test at the Telia office and when I later called an old colleague there and asked what happened to my Heath Robinson style PABX I was told that it was in daily use and that I was, in fact, using it right as we spoke).  After this I joined another telco operator and then I went on to work for Oracle, where I at first was the local VAX guy and later the local Unix guy (also, I was the local Mac guy as I was the only one there who had even used on, and I had used it to run Pagemaker for editing a fanzine that I was working on at that time).

OK, time to wake up. Maybe I should port MySQL to the SL/1 or something today? But I need an extra bit in the pagebank register for that I guess? Well that can be fixed, is there some young, unsuspecting sysadmin around?

Also, read "The Soul of a new Machine" if you haven't done so. It does say a lot about how people in the IT indistry work and how we look at ourselves. And it's a probably a good read for a spouse or girlfriend/boyfriend who is not in the IT-industry, as this is not really a technical book (except to a very small extent).


Introduction to Job Queue daemon plugin

varokism - February 26, 2014 - 7:20am GMT
Dr. Adrian Partl is working in the E-Science group of the Leibniz Institute for Astrophysics Potsdam (AIP), where the key topics are cosmic magnetic fields and extragalactic, astrophysics is the branch of astronomy concerned with objects outside our own Milky Way galaxy
Why did you decided to create a Job Queue plugin, what issues does it solve?
A: Basically our MySQL databases hold astronomic simulations and observations content, the datasets are in multi Terra Bytes size and queries can take long time, astronomers can definitely wait for data acquisition, but jump on the data as soon as they are available.  Job Queue offer a protection from too many parallel query executions and prevent our servers to be spammed. Multiple queues are here to give us priority between users, today queries are executed as soon as a slot is available. Some timeouts per group can be define and queries will be killed passing that delay.
Would you like telling us more about your personal background?
A: I studied astronomy and have a PHD in astrophysics. For my PHD I focused on high performance computing by parallelizing a radiation transport simulation code to enable running it in large computational cluster. Now a day i'm more specialized in programming and managing big dataset. I stop doing scientists tasks, but i enjoy helping in making those publications happen by providing all the IT infrastructure for doing the job.
How did you came to MySQL ?
A: In the past we used SQL Server but we rapidly rich the performance limits of a single box, we found out that it can be very expensive to expend it for sharding.
We moved to MySQL and mostly MyISAM storage engine.  We are also using Spider storage engine since 3 years, for creating the shards. We needed true parallel queries, to do so we created PAQU a fork of Shard Query to better integrate with Spider, The map-reduce tasks in PaQu are all done by submitting multiple subsequent "direct background queries" to the Spider engine and we shortcut Gearman in shard-query. With this in place it is possible to manage map-reduce tasks using our Job Queue plugin.
S: Spider is now integrated in MariaDB10 and it is making fast improvements regarding map-reduce jobs, using UDF functions with multiple channels on partitions and for some simple aggregation query plans. Are you using advanced DBT3 big queries algorithms like BKA joins and MRR? Did you explore new engines like TokuDB that could bring massive compression, and disk IO saving to your dataset.
A: I will definitely have look at this. In the past we have experimented column stores, but it's not really adapted to what we do. Scientists extract all columns despite they don't use all of them. Better getting more, then to re extract :)       
When did you start working on Job Queue and how much time did it take? Did you found enough informations during the task of developing a plugin ? What was useful to you?
A: I took me one and a half year, i started by reading MySQL source code. Some books helped me, MySQL Internals from Sacha Pachev at Percona and MySQL plugins development from Sergei Golubchick at SkySQL and Andrew Hutchings at HP. Reading the source code of handler_socket plugin from Yoshinori Matsunobu definitely put me on faster track.
S: Yes we all miss Yoshinori but he is now more social than ever:), did you also search help from our public freenode IRC MariaDB channel.
A: Not at all, but i will visit knowing now about it.
How is the feedback from the community so far?
It did not yet pickup, but i ported the PgSphere API from PostgreSQL. The project is call mysql_sphere, it's still lacking indexes but it is fully functional and that project get so far very good feedback.
Any wishes to the core ?   
A: GiST index API like in PostgreSQL would be very nice to have, i have recently started a proxying storage engine to support multi dimensional R-Tree, but i would really like to add indexing on top of the existing storage engine.
S: ConnectDB made by Olivier Bertrand share the same requirements, to create  indexing proxy you still need to create a full engine for this, we support R-tree in InnoDB and MyISAM but this a valid point, we do not have functional indexes API like GiST. This has been already discuss internally but never been implemented.  
The results of the job execution are materialized in tables, can you force a storage engine for a job result ?  
A: This is not yet possible at the moment but easy to implement.
What OS and Forks are known to be working with Jog Queue?  
A: It’s not very deep tested because we mostly use it internally on linux and MySQL 5.5 and we have tested it on MariaDB recently, i don't see any reason why it would not work for other OS. Feedback are of course very welcome!
Do you plan to add features in upcoming release?
A: We don't really need additional features now a day, but we are open to any user requests.
S: Run some query on a scheduler ?
A: Can be done. I could allocate time if it make sense for users.   Job Queue is part of a bigger project Daiquiri, using Gearmand can you elaborate?  
A: Yes Daiquiri is our PHP web framework for publication of datasets.This is manage by Dr. Jochen Klar and control dataset permissions and roles independently of the grants of MySQL. Job Queue is an optional component on top of it, for submitting jobs to multiple predefine dataset. We allow our users to enter free queries. Daiquiri is our front office for Paqu and Job Queue plugin. We are using Gearman in Daiquiri to dump user requests to CSV or into specialized data formats.
S: We have recently implemented Roles in MariaDB 10, you may enjoy this as well but for sure it may not feet all specific custom requirements.
Where can we learn more about Job Queue?  
Job Queue Pluginmail:
S: Transporting MySQL and MariaDB to the space last frontier, there are few days like that one when i discovered your work making me proud to work for an Open Source company. Many thanks Adrian for your contributions!
S: If you found this plugin useful and would like to use it, tell it to our engineer team by voting to this Public Jira Task. If your share the same needs to have GiST like indexing API please vote for this Public Jira Task.  

MaxScale for the rest of us - Part 4

Karlsson on databases and stuff - February 24, 2014 - 5:02pm GMT
If you have followed this series of blogs (Introduction in part 1, Setting up the cluster in part 2 and Installing and configuring MaxScale in part 3), you should now have MaxScale up and running on a Cluster using MariaDB Replication. But as I said when I left off in Part 3, there is more to it.

To begin with, let's look at how the replication system works. Let's insert some more data through MaxScale, this should end up with the master and the replication system will handle making this available on the slaves. So on the Client machine (, logged in a root, do:
# mysql -h -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(2)"
# mysql -h -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(3)"
# mysql -h -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(4)"

We should now have 4 rows in the table t1, let's check:
# mysql -h -u maxuser -pmaxpwd test -e "SELECT c1 FROM t1"
| c1   |
|    1 |
|    2 |
|    3 |
|    4 |
Whoa!  That was cool. Or? Maybe not I guess. For example, where is this data coming from? Let's use something that we can use to tell us which server we are selecting from. Remember that the variable server_id is different in all servers (it has to be), it is 1 (on the master). 11 and 12 (on the two slaves respectively) so if we select that, we should see what server the data is coming from, right? Let's check it out:
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          11 |    1 |
|          11 |    2 |
|          11 |    3 |
|          11 |    4 |
So this was data coming from the first slave. We know that we are supposed to have load balancing in place here, so if we try the same thing again, data should come from the other slave. But don't take my word for it, let's try it:
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          12 |    1 |
|          12 |    2 |
|          12 |    3 |
|          12 |    4 |
Hey, load balancing seems to work! Coolness, dude! Our INSERTs above were clearly excuted on the master and replicated to the two slaves, right? Can we check that! Yeah, let's try again, but let's first remove all the rows in our test table:
# mysql -h -u maxuser -pmaxpwd test -e "DELETE FROM t1"
And now let's insert a row again, but use the server_id variable instead:
# mysql -h -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(@@server_id)"
Let's check what happened now:
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          12 |   12 |
What! Have we gone collectively nuts (or more so than usual at least), or? What happened here? Let's try it again:
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          11 |   11 |
Ouch! What's wrong! Well, what is going on here is that we are using statement based replication, which is the default, so on the master, c1 has the value 1, but executing INSERT INTO t1 VALUES(@@server_id) on the first slave will pick up the local value for @@server_id (which is 11) and insert that into the table. To fix this we have to use Row Based Replication (RBR). We can set this as a global variable, but to make it permanent, which is what we want to do, really, we have to set it in the appropriate config file on the master:
# vi /etc/my.cnf.d/server.cnf
and then adjust the mysqld section so it looks like this:
And then we restart the master server (this is also executed on the master, mind you):
# /etc/init.d/mysql restart
Now, let's try again, from the client:# mysql -h -u maxuser -pmaxpwd test -e "DELETE FROM t1"
# mysql -h -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(@@server_id)"
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          11 |    1 |
# mysql -h -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
| @@server_id | c1   |
|          12 |    1 |
That makes more sense, right. It got scary there for a while. And note that this isn't specific to MaxScale or anything, it's just how MariaDB Replication works.

With this we now have a Cluster of MariaDB servers set up where we use MaxScale as a Load Balancer with read-write split. In the next blog in this series, I plan to have a look at performance.


Correlating OS Thread IDs from SEMAPHORES Section to TRANSACTIONS Section

Chris on MySQL - February 14, 2014 - 9:58pm GMT

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do you match them up?

SELECT LOWER(HEX(1079654736));

This returns:


Now, examine the TRANSACTIONS section, and look at the value for “OS thread handle”.

Here is the corresponding entry in the TRANSACTIONS section (note “OS thread handle 0x405a3950″) for the semaphore wait:

---TRANSACTION 99FAB3092, ACTIVE 283 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 0x405a3950, query id 3546555576 update
REPLACE INTO `t1` (id,`date`,`col3`,`col4`,`col5`,`col6`,`col7`,col8)
VALUES (352937385,'2012-08-27 06:16:58','1056665','3755','0','0','0',101)
TABLE LOCK table `db1`.`t1` trx id 99FAB3092 lock mode IX
RECORD LOCKS space id 3680 page no 156295 n bits 456 index `PRIMARY` of table `db1`.`t1`
trx id 99FAB3092 lock_mode X locks rec but not gap

Since we see “0x405a3950″, we know this is the transaction waiting for the semaphore.

Hope this helps.


Getting Started with the Spider Storage Engine

Chris on MySQL - February 13, 2014 - 10:53am GMT

If you’re like me, you’ve probably heard of the Spider storage engine, but not used it yet.

While it has been available for some years now, I just simply haven’t used it before until now.

I suspect that has to do with ease of installation. Previously, one had to compile it with MySQL in order to use it, which excludes a lot of people. However, in MariaDB 10.0 (as of 10.0.4), it is very easy to add and use.

And with MariaDB 10.0.8 being declared RC, combined with Spider’s sheer usefulness, I only suspect its usage will become more and more widespread.

What is the Spider storage engine, and why will it be useful?

“The Spider storage engine is a storage engine with built-in sharding features. It supports partitioning and xa transactions, and allows tables of different MariaDB instances to be handled as if they were on the same instance. It refers to one possible implementation of ISO/IEC 9075-9:2008 SQL/MED. When a table is created with the Spider storage engine, the table links to the table on a remote server. The remote table can be of any storage engine. The table link is concretely achieved by the establishment of the connection from a local MariaDB server to a remote MariaDB server. When more than one Spider storage engine table can be used by the same connection, the connection is shared.”

This is quite flexible, and given that, you can achieve a lot with spider, including federated, sharding, high availability, and background search (the last expected to be fully implemented by the time MariaDB 10.0 is declared GA).

How to install spider?

This is quite simple as the files needed are now included with the MariaDB distribution. In the /share directory, you will see a file named: install_spider.sql

You just need to import this, and there you go:

mysql -uroot -p -P3315 < install_spider.sql

For those interested in the behind-the-scenes, this creates the following 6 system tables (in 'mysql' schema):


And it automatically loads the .so or .dll plugin (depending if you're running on Linux or Windows), thus afterward, SHOW ENGINES should report the following:

mysql> show engines;
| Engine | Support | Comment    | Transactions | XA   | Savepoints |
| SPIDER | YES     | Spider ... | YES          | YES  | NO         |

The most simple example is accessing a table on another instance.

Let's say you have this remote table you want to access:


Then on the local server, where Spider is enabled, issue:

COMMENT 'host "", user "chris", password "xxxxx", port "3314"';

Now you can SELECT from this table, or INSERT into it (which will write to the remote instance), etc.

INSERT INTO s(code) VALUES ('a');

This is very straight-forward, and really it's just the tip of the iceberg. The MariaDB KB article on the Spider storage engine has quite a bit of info and examples, so if you're interested, please see:

Hope this helps.


MariaDB 10.0.8 Overview and Highlights

Chris on MySQL - February 11, 2014 - 8:14pm GMT

MariaDB 10.0.8 was recently released as RC (“Release Candidate”), and is available for download here:

This is the first RC release of MariaDB 10.0. It is primarily a bug-fix and polishing release, and all features planned for MariaDB 10.0 GA are included in this release.

There were 6 notable changes in MariaDB 10.0.8:

  1. InnoDB upgraded to version 5.6.14
  2. FLUSH … FOR EXPORT works
  3. Added a new server variable, old_mode, to allow selectively restoring old behavior, in contrast to the old “all-or-nothing” approach of the –old command-line option. See OLD MODE for more.
  4. Added a new read-only server variable malloc_library
  5. Bundled PCRE library upgraded to version 8.34
  6. The CREATE OR REPLACE TABLE statement was added. It is used internally for replicating CREATE … SELECT statements (MDEV-5491), but it is also can be used by clients as any other SQL statement.

You can read more about the 10.0.8 release here:

And if interested, you can review the full list of changes in 10.0.8 (changelogs) here:

Hope this helps.


MySQL 5.6.16 Overview and Highlights

Chris on MySQL - February 11, 2014 - 7:52pm GMT

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

As opposed to the latest 5.5 release, this latest 5.6 release has quite a few more bug fixes, but that’s expected since 5.5 has been GA for much longer.

There were 2 minor functionality changes:

  • Previously, ALTER TABLE in MySQL 5.6 could alter a table such that the result had temporal columns in both 5.5 and 5.6 format. Now ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm, so specifying ALGORITHM=INPLACE in these cases results in an error. (Bug #17246318)
  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

I counted 80 bug fixes, 32 of which were InnoDB-related (and some of thoese also memcached-related), 2 partitioning-related, 8 replication-related, and the remaining covered a variety of areas.

I won’t go into all 80 bugs, but if you’re running memcached and/or InnoDB with MySQL 5.6, you should definitely consider upgrading to the latest 5.6.16. And if not, but you’re still running 5.6, I’d recommend reviewing the full changelogs, to see whether you should upgrade asap or not.

Lastly, there was one “known limitation” added, which is:

  • Building MySQL from source on Windows using Visual Studio 2008 fails with an identifier not found error. Later versions of Visual Studio are unaffected. The workaround is to set the CMake build option, INNODB_PAGE_ATOMIC_REF_COUNT, to OFF. This option is ON by default.

So this is very minor, and only related to those building MySQL on Windows using VS 2008. VS 2008 is quite old anyway, but there is the work-around if you’re still using t.

The full changelogs can be viewed here:

And in case you missed it, there is a great post from Morgan Tocker giving thanks to each community member involved in each bug reported/fixed in 5.6.16! What a nice combination of both old and new names and folks from all different companies! That sure shows how active and thriving the MySQL ecosystem really is!


MySQL 5.5.36 Overview and Highlights

Chris on MySQL - February 9, 2014 - 8:12pm GMT

MySQL 5.5.36 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

I was reading through the changelogs to review the changes and fixes, and to summarize, I must say this release is mostly uneventful.

There was one new feature added (for building, so not really applicable to everyone), and only 17 bugs fixed.

The new feature is this:

  • CMake now supports a -DTMPDIR=dir_name option to specify the default tmpdir value. If unspecified, the value defaults to P_tmpdir in . Thanks to Honza Horak for the patch. (Bug #68338, Bug #16316074)

Out of the 17 bugs, there was only 1 I thought worth mentioning (because it is a wrong results bug):

  • COUNT(DISTINCT) sometimes produced an incorrect result when the last read row contained a NULL value. (Bug #68749, Bug #16539979, Bug #71028, Bug #17867117)

Therefore, if you run COUNT(DISTINCT) and you allow NULL values, then you should consider upgrading so you’re not affected by this.

For reference, the full 5.5.36 changelog can be viewed here:

Hope this helps.


MariaDB 5.5.35 Overview and Highlights

Chris on MySQL - February 6, 2014 - 10:07pm GMT

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

This is a maintenance release, and so there are not too many big changes of note. However, there are a couple items worth mentioning:

  1. Includes all bugfixes and updates from: MySQL 5.5.35
  2. Includes all bugfixes and updates from: XtraDB from Percona-Server-5.5.35-rel33.0
  3. New SQL_MODE: OLD_MODE – to emulate behavior from old MySQL/MariaDB versions

You can read more about OLD_MODE here, if interested:

Also, there is more about the 5.5.35 release here:

And the full list of fixed bugs and changes in MariaDB 5.5.35 can be found here:

Hope this helps.


Exploring MySQL Metadata Lock Instrumentation in Closer Detail

Chris on MySQL - February 5, 2014 - 10:42pm GMT

I recently wrote a post on tracking metadata locks (MDL) in MySQL 5.7, and I wanted to take a moment to expand on it by explaining a couple of the associated variables in more detail.

First off, once you have enabled the performance_schema *and* the metadata lock instrumentation, you can verify it with:

mysql> SELECT * FROM performance_schema.setup_instruments
    -> WHERE NAME = 'wait/lock/metadata/sql/mdl';
| NAME                       | ENABLED | TIMED |
| wait/lock/metadata/sql/mdl | YES     | YES   |

“ENABLED” will report “YES” if it is enabled properly, and “NO” if not.

“TIMED” (referring to event timing) reports “YES” if it was enabled via the my.cnf or my.ini file, and it reports “NO” if it is enabled dynamically *and* you don’t explicitly set TIMED to YES. If you want to set it dynamically, *and* you want to enable event timing, then use this statement to enable the instrumentation:

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

As for variables:

mysql> show global variables like '%metadata%';
| Variable_name                         | Value |
| ...                                   | ...   |
| metadata_locks_cache_size             | 1024  |
| metadata_locks_hash_instances         | 8     |
| performance_schema_max_metadata_locks | 10000 |

The first two are metadata_locks_cache_size and metadata_locks_hash_instances. In looking up more about these two variables, I found out they are deprecated as of 5.7.4. So, they were only in 5.7.3. Therefore, no need to worry about these either. This sometimes happens with new features.

For both variables, the manual says:

“In MySQL 5.7.4, metadata locking implementation changes make this variable unnecessary, so it is deprecated and will be removed in a future MySQL release.”

That only leaves us with performance_schema_max_metadata_locks, which the manual says this:

“The maximum number of metadata lock instruments. This value controls the size of the metadata_locks table. If this maximum is exceeded such that a metadata lock cannot be instrumented, the Performance Schema increments the Performance_schema_metadata_lock_lost status variable.”

And here is the status variable the above refers to (incremented if performance_schema_max_metadata_locks is reached/exceeded), and note it is the only metadata-related status variable:

mysql> show global status like '%metadata%';
| Variable_name                         | Value |
| Performance_schema_metadata_lock_lost | 0     |

So if this is ever greater than 0, you’ll need to increase performance_schema_max_metadata_locks. However, the default is 10,000 which is quite high, so unless you have very high traffic loads, this should probably not ever be an issue (or perhaps it would indicate some larger issue at hand).

I searched the page for any possible new variables, and didn’t see any mention of any. However, with it being such a new feature, I’ll probably keep an eye out for any changes.

Lastly, the only other item I can think to cover here is the actual output from the metadata_locks table. Here is one full sample row (generated from a “BEGIN”):

mysql> select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
        OBJECT_SCHEMA: performance_schema
          OBJECT_NAME: metadata_locks
       OWNER_EVENT_ID: 14
1 row in set (0.00 sec)

And here is output from a metadata lock hang with several rows:

mysql> select * from metadata_locks;
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              21 |..|
| GLOBAL      |..| INTENTION_EXCLUSIVE | STATEMENT     |..|              22 |..|
| SCHEMA      |..| INTENTION_EXCLUSIVE | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_UPGRADABLE   | TRANSACTION   |..|              22 |..|
| TABLE       |..| EXCLUSIVE           | TRANSACTION   |..|              22 |..|
| TABLE       |..| SHARED_READ         | TRANSACTION   |..|              23 |..|

I think the rows are all described well, and self-explanatory, but I wanted to post the outputs for completeness. The second example above gives you an idea of the various LOCK_TYPES you might see, as well as LOCK_DURATIONS. I think it’s worth noting the OWNER_THREAD_ID corresponds to THREAD_ID in the performance_schema.threads table, in case you need to correlate the Process Id to the thread id listed here.

Hope this helps.


Syndicate content