How to Use SSL and MySQL Client Library in the Same Binary!

We plan to hide (not export) the symbols of the SSL library used by the MySQL client library. As step one in that effort, we plan to hide all of the non-documented symbols because we want to stop polluting the MySQL client program’s namespace.

Consider a requirement where there the OpenSSL and MySQL client library functions are both used directly to generate a binary. If the order of linking used is 1. MySQL client library (i.e libmysqlclient) and then 2. OpenSSL library (i.e libcrypto), then using the created binary will result in a crash.

The Reason why it will crash is as follows: The libmysqlclient library already has the built-in yaSSL library and has exported its symbols. The yaSSL and OpenSSL libraries share some of the same symbol names, so the executable which is prepared in the above said order, will resolve the OpenSSL symbols indirectly using the libmysqlclient library (yaSSL) rather than using the OpenSSL library directly. Hence, the binary will try to call functions defined in the libmysqlclient library instead of the desired OpenSSL function, thus resulting in a crash.

We plan on solving this and similar issues as follows: Hide (don’t export) all of the bundled SSL library symbols from libmysqlclient. We will also hide all of the undocumented MySQL symbols in libmysqlclient as well. The final list of symbols that will be exported can be found here.

That’s all for now. I really hope that this new feature makes users’ lives easier and improves their overall experience with MySQL! We also look forward to your feedback on this new feature! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.

As always, THANK YOU for using MySQL!

Relay Log Recovery when SQL Thread’s Position is Unavailable

This blog explains how relay log recovery happens in a scenario where an applier thread (SQL_Thread) is starting for the first time and its starting position is not available for relay log recovery operations. If you are using GTIDs with MASTER_AUTO_POSITION then the following is more or less irrelevant since we then employ a more resilient repositioning scheme. The potential issue described here will also not occur if you have employed crash-safe replication settings, including --sync_master_info=1. With those disclaimers out of the way, let’s proceed.

Background

A crash-safe slave in MySQL 5.6 guarantees that replication progress information is always in sync with what has actually been applied to the local database instance. But there can still potentially be a few cases where the synchronization is not 100% guaranteed, for example in the case of DDL statements (which are not yet transactional).

In order to achieve crash-safe replication you must do the following:

Crash safe replication ensures that the applier thread saves its relevant state information at transaction commit time. During recovery—when relay-log-recovery=1—any existing relay logs are discarded and new ones are created. The receiver thread (IO_Thread) then starts fetching events from the master’s binary log that the applier thread(s) (SQL_Thread) had previously applied on the slave. In other words, the receiver thread’s Read_Master_Log_Pos is set to applier thread’s Exec_Master_Log_Pos. (You can learn more about what those positions mean here). So in practice the receiver thread copies the applied position from the applier thread and starts pulling the master binary log events from that point forward. The applier thread’s Relay_Log_File and Relay_Log_Pos values are then set to new relay log file and position.

In MySQL 5.6.22, we made this procedure even smarter. In the case that an applier thread does not report any position at all (e.g., it had not been started yet when the crash happened), then the slave still tries to mine the relay logs for the first rotate event from the master and then sets the slave’s file and position info based on that event instead.

Problem

The relay log recovery process explained above is entirely dependent on an applier thread’s position. There could, however, be a scenario where an applier thread was never started whereas the receiver thread was. In that case a receiver thread may have download events from the master’s binary log and appended them to slave’s relay log(s). If a crash happens in this scenario and the server is restarted, then even when crash safe settings are enabled the automatic recovery could still fail. This is because the recovery process expects Relay_Master_Log_File to be set in order to start the recovery process. If that value is not set, then the recovery process will finish without doing any recovery work.
img1
Crash safe replication ensures that the applier thread’s state is saved at transaction commit time, whereas the same is not guaranteed for the receiver thread’s state. That means that the receiver thread’s position info is not updated with each event as they are pulled from the master. Hence the slave_master_info table could potentially be out-of-date. This could then cause the events to be fetched once again and appended (for a second time) to the slave’s relay log(s). When the applier thread is then started post-recovery this could result in duplicate key errors on the slave. This issue was reported as Bug#73039 (fixed in MySQL 5.6.22).
Continue reading

When ONLY_FULL_GROUP_BY Won’t See the Query Is Deterministic…

This is a follow-up post to my recent announcement of only_full_group_by improvements in 5.7, where I’d like to address some tricky corner cases where GROUP BY contains non-column expressions (functions).

In the SQL standard, GROUP BY should contain only table columns and never expressions. So this example query where we want to count how many people have the same name is illegal in the SQL2011 standard because the GROUP BY clause contains a function:

But MySQL does allow expressions in a GROUP BY clause, and it’s a very convenient addition to the standard. The logic of the only_full_group_by SQL mode notices that the selected expression is equal to the grouping expression, so it concludes that the result is not random and thus accepts the query.

However, let’s try this:

As you’ll see, it returns:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.people.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
Indeed, the selected expression is not equal to the grouping one anymore. A human brain instantly recognizes that it’s still deterministic, as it’s simply the UPPER() value of the grouping expression, but for MySQL to recognize it too it should analyze the selected expression’s parse tree, trying to spot the grouping expression at all possible places in that tree. As you can imagine, the selected expression could be rather complex. For example:

MySQL would have to:

  • Start with UPPER:
    • See if the UPPER value is the CONCAT of GROUP BY — no it’s not
    • So go down to arguments of UPPER
    • There it finds CONCAT, which is equal to the GROUP BY one — OK!
  • Now check SUBSTR:
    • It’s not CONCAT
    • So go down to the arguments
    • CONCAT is there in the first argument — fine
    • The second argument is a multiplication, which is not CONCAT
      • So go down to its arguments
      • We have 0.6, which is not CONCAT but rather a constant and deterministic
      • Then LENGTH, which is not CONCAT
      • So let’s check its arguments
        • CONCAT is there, good. Finally done!

All these comparisons of functions have a runtime cost and an implementation cost, so we decided not to go down this path. After all, only_full_group_by had always rejected such a query—which is a non-Standard one—so we chose not to address these specific cases.

Thus, MySQL simply observes that the selected expression is not the grouping one and then it collects columns referenced by the selected expression to see if they’re equal to, or functionally dependent on, some grouping columns. But if there’s no grouping column — bail out!

One could take a step back and realize that grouping on first_name and last_name (the columns) is more natural than grouping on CONCAT, and it also yields a Standard-compliant query:

But what if you do have a query which really must group on an expression (likely something less trivial than my examples)? We saw that UPPER and GROUP BY CONCAT don’t fit together. A first idea is thus to group on a column; for that, the Standard-compliant solution is to use a derived table:

It makes CONCAT’s output a column of the derived table and thus the grouping happens on this column and finally UPPER uses this column value.

However, the derived table will have as many rows as people; as it will be materialized internally it will form a big temporary table, which creates space and speed issues. It would be better if the derived table contained only the groups. So we’re set for grouping on CONCAT, keeping UPPER separated from the grouping query:

Another potential solution, albeit a less safe one, is to add ANY_VALUE around UPPER() (or even turn only_full_group_by off!):

One case I have seen recently, brought up by Roland Bouman, is when one wants to sort the output in a particular order, for example one wants NULLs first, so adds ISNULL in ORDER BY:

This time, the selected expression is equal to that of GROUP BY, so it has no problem; but the ISNULL expression is the new problem! To ensure a deterministic order of results, only_full_group_by has to validate this ISNULL, and, just like for the previous UPPER call, it will not see that the argument of the ISNULL call is the same as the grouping expression. Here, because the problem is in the ORDER BY clause, and because ORDER BY clauses are allowed to reference expressions of the select list by aliases in MySQL, the simplest solution is to simply use an alias:

MySQL will see that ISNULL’s argument is an alias (CONC) and that it matches the same alias in the GROUP BY clause — the alias is then treated as a “grouping pseudo-column”. Matching aliases is much simpler than matching expressions, so we have been able to implement that in the only_full_group_by logic. Thus the above query will pass the only_full_group_by restrictions. It’s also much more readable with aliases than without, in my humble opinion; it might even execute slightly faster, as the value of CONC, computed in GROUP BY, is now reused by ISNULL, leading to less calls to the CONCAT function. The only potential drawback is that using aliases in GROUP BY clauses is not Standard-compliant, so it may cause issues if you need to support various DBMS options with your application.

I hope that this blog post has given you enough safe and usable tips for those types of queries. If you know of other tricks, they are warmly welcome, and you might just drop them in a comment here (thanks in advance!).

As always, THANK YOU for using MySQL!

December London MySQL Meetup

bigbenThe London MySQL Meetup Group hosted a new evening of MySQL related talks on December 11, this time in Canonical’s offices in the Blue Fin Building. Conveniently, it was timed so that it coincided with the Debian/Ubuntu packaging sprint, which I also attended.

We started with pizza, beer and small talk. Since this was my first meetup in London, there were lots of new and interesting people to meet and talk to.

The topic of the evening was “MySQL distributions: in the cloud and on bare metal”, but before we got that far, James Page and Robie Basak presented the results so far of our ongoing packaging sprint. I’ve covered that in another blog post, and Akhil discusses it here as well.

The first on-topic talk was Ivan Zoratti’s talk on databases in the cloud in general, and on Amazon Aurora in particular. We currently don’t know very much about the inner workings of Aurora, except that it must be running MySQL 5.6 with some modifications on the lower layers. So it’s mostly speculations, but it was fun to discuss and talk about what they might have done to achieve what they have. Let’s call it well educated guesses.

George Lorch from Percona presented on OpenStack and Trove. For us that are not involved with OpenStack in our daily work, it was a nice introduction to what Trove is and isn’t.

Finally, I was also asked to give a short talk on new features in the MySQL 5.7.5 DMR. It was just a quick review of some of the features, based on Geir’s blog post on the same topic. I noted that there was some interest in what we’ve done to make InnoDB temporary tables faster.

I usually attend MySQL meetups in Trondheim, Norway, so it was fun to see how they do things in London. I guess there aren’t that many ways to do it when the biggest difference I could find was that the pizza is served before the talks in London while we do it afterwards in Trondheim. I just wish we had a pub nearby in Trondheim so that we could copy the tradition of going for a pint afterwards.

A big thank you to everyone that came, and to the organizers for inviting me!

New Option to Stop the Server If Binlogging Fails in MySQL 5.6

In this post I will try to explain the new MySQL binlog_error_action server option. This new option is available from MySQL 5.6.22 and later.

Background:
——————–
As part of MySQL replication all data changes that happen on the master server are recorded into a binary log so that they can be sent to slave and replayed there. If an error occurs that prevents mysqld from writing to the binary log (disk full, readonly file system, etc.) then the logs are simply disabled and operations continue on the master. This error mainly occurs during rotation of the binary log or while opening a binary log file.

This problem creates a serious potential for data loss within a replication group. When a master hits this failure in production, all downstream replication clients stop receiving replication events. The master will not store binlog events for committed transactions to its binary log and consequently there will be no new events in the binary log to send to replication clients. If that master then fails, then all the transactions the master received while binlogging was turned off are lost forever. This can lead to out of sync slaves and improper backups.

Error message when file system becomes readonly:
binlogging_impossible_error1As part of the bug fix for Bug#51014 the binlog_error_action server option was introduced. Using this option a user can choose to either ignore the error (still the default so as to avoid behavior changes in GA releases) or to abort the server. The IGNORE_ERROR option value refers to the default behavior (as described above) where binlogging will simply be disabled and the master will continue with its normal operations.

However, the ABORT_SERVER option value will cause the server to exit when binlogging operations fail. At the time of the resulting server exit a fatal error is pushed to clients and the server will shut down. The error details being:

Specifying the option:
———————————–
This option can be specified as a startup option (either on the command-line or in a config file) or dynamically in the running server using the SET command:
mysql> SET GLOBAL binlog_error_action=ABORT_SERVER;

Demonstration of the new option in the case of a read-only file system:
——————————————————————————————————————
Step 1: SET GLOBAL binlog_error_action= ABORT_SERVER;
Step 2: Make your file system readonly
Step 3: flush logs
abort_server_img

Summary:
——————
If an error occurs that prevents mysqld from writing to the binary log the existing behaviour is: binary logging is disabled and the server continues with its normal operations. Now with the new binlog_error_action server option the user can choose to either ignore the error (IGNORE_ERROR) or to abort the server (ABORT_SERVER) when binary logging failures occur. This optional behavior was first introduced in MySQL 5.6.20 using the binlogging_impossible_mode server option. That option name is now deprecated in MySQL 5.6.22 and the option is instead now referred to as binlog_error_action.

We look forward to your feedback on this new feature! If you have any questions or encounter any bugs, please do let us know by opening a support ticket or filing a bug. As always, THANK YOU for using MySQL!

MySQL Debian/Ubuntu packaging sprint

Debian/Ubuntu packaging sprint participants. From left: James Page, Norvald H. Ryeng, George Lorch, Akhil Mohan, Otto Kekäläinen, Robie Basak.
Debian/Ubuntu packaging sprint participants. From left: James Page, Norvald H. Ryeng, George Lorch, Akhil Mohan, Otto Kekäläinen, Robie Basak.

Last week, Canonical invited the MySQL packaging team in Debian to a packaging sprint in their London office, and most of us were able to participate. We’ve met online on IRC and UOSs before, but this was the first time we were all in the same room.

The results of our sprint will soon be available in a .deb near you. Since Debian Jessie is currently in feature freeze, most of it will hit Ubuntu first. The two main things we achieved on the MySQL side were to make MySQL 5.6 ready for Ubuntu Vivid (15.04) and to split MySQL, Percona and MariaDB configuration files. The configuration file split reduces coupling between MySQL and variant packages, and it is key to solving most of the challenges the team faces. We can now do packaging work on one variant without coordinating with all the others. That is a major achievement!

In addition to this, we also made smaller improvements: parallel builds and MTR test runs, defined and implemented a test plan in order to run the right tests at the right time (build vs. DEP8), and cleanup of the debian/rules file. Not all of this is visible to the end user, but to the package maintainers it’s a big deal.

Time flies when you’re having fun, so when the week was over, we still had more we’d like to do with the packages. The work will continue, but at least most of the heavy lifting is now done. I don’t think we ever would have got this far without meeting in person.

Let me end by extending my thanks to Canonical for organizing and hosting this sprint! It was really useful and fun! I think we all achieved and learned a lot. So once again, to Canonical and everybody that participated, thank you, and merry Christmas!

Some Notes on Index Statistics in InnoDB

In MySQL 5.6 we introduced a huge improvement in the way that index and table statistics are gathered by InnoDB and subsequently used by the Optimizer during query optimization: Persistent Statistics. Some aspects of the way that Persistent Statistics work could be improved further though, and we’d really like your input on that.

How much to sample?

The statistics are gathered by picking some pages semi-randomly, analyzing them, and deriving some conclusions about the entire table and/or index from those analyzed pages. The number of pages sampled can be specified on a per-table basis with the STATS_SAMPLE_PAGES clause. For example: ALTER TABLE t STATS_SAMPLE_PAGES=500;
This way you can request that more pages be sampled for larger tables in order to make the estimates more accurate. The more pages that are sampled, the better the estimates will be and thus the better the chance that the Optimizer will be able to choose optimal query plans, with the only downside being that the index statistics gathering operation (the sampling) itself becomes slower.

Being able to specify the number of pages to sample on a per table basis is flexible, but what if one can specify the value as a percentage of the table size, rather than an absolute number? Would that make sense? Setting such a global value would not meet every need and use case, because the given percentage may not be sufficient to get a good estimate on a small table and the same percentage may make the sampling process too slow for a very large table. Setting the percentage on a per table basis may also have little advantage over the existent functionality as one already knows if the table is huge or tiny and can set the appropriate value as an absolute number of pages to sample. What do you think? We would love to get your input on this! Do you see a lot of value in being able to set the number of pages to sample as a percentage of the table size or have it depend on the table size in some other way? Cast your vote:

Make it possible to set the amount of pages to ANALYZE as a % of the table size?

View Results

Loading ... Loading ...

How often to sample?

Currently, if 10% of the table has changed (rows updated, deleted, or inserted) since the previous statistics gathering operation then InnoDB will automatically trigger a recalculation of the statistics (it will analyze some leaf pages as discussed in the previous paragraph, reading them from disk if they are not already cached).

In MySQL 5.5 and older, where the auto recalculation threshold was set to 6.25%, it was a problem that the statistics were updated too often which caused too many execution plan changes. In general, we’ve found that 10% is a good overall threshold for this and we haven’t seen a great need to make this dynamic. But do you agree? Do you want to be able to change that value globally or even on a per table basis? Would you increment it or decrement it if you could? Cast your vote:

Make it possible to change the 10% threshold that triggers an automatic ANALYZE?

View Results

Loading ... Loading ...

We look forward to your feedback! And as always, THANK YOU for using MySQL!

MySQL 5.7: only_full_group_by Improved, Recognizing Functional Dependencies, Enabled by Default!

Here’s an account of recent work which has kept me busy and excited for a few months. For those unfamiliar with the only_full_group_by sql mode, let me provide some context. I’ll use the world database, which can be downloaded from this MySQL Documentation page. You can find details on how to install the database on the same page. We have a table of countries and a table of languages spoken in each country. It’s a 1-to-N relationship: a country can have many languages so CountryLanguage.CountryCode is a foreign key referencing Country.Code:

Here are two sample rows from the Country table:

And corresponding rows in CountryLanguage:

Consider this grouped  SELECT:

This SELECT groups by “Language”, so the first thing it has to do is “segregate” rows by language:

Then it must collapse (“aggregate”) all rows of the same group to form one row; this row should have, according to the columns listed in the SELECT, a value for “Language” and a value for “CountryCode”. For the “French” group, the value for “Language” is obviously “French”; but what is the value for “CountryCode”: “BEL” or “FRA” or something else? That’s non-deterministic, and that means the query is likely nonsensical.

The SQL-92 Standard solved this non-determinism by requiring that all selected columns be part of the GROUP BY clause. So it would then reject the example query above.

Historically, MySQL took a different route: it allowed the query above, and picked one value of “CountryCode” among the group. So you may obtain
(French, BEL), (Spanish, ABW)
or
(French, FRA), (Spanish, AND)
depending on MySQL’s implementation details, the access paths chosen to read the table, and subject to change in future versions; so in practice it was simply unpredictable.

In other words, users were allowed to execute non-standard SQL queries and it was up to them to think and decide whether the query was non-sensical or not. That made it far too easy for users to make mistakes that resulted in incorrect data presented to the user/application. Imagine that the user meant this reasonable query:

They wanted to know how many countries speak each language. Alas he/she used the wrong column in the COUNT() clause though and the query accidentally became the bad one which we saw earlier where we produce incorrect and unpredictable results. MySQL didn’t raise any error or warning here, which is not being helpful at all in this scenario.

Users wary of this “dangerous permissive behaviour” would prefer MySQL to complain here, that’s why the only_full_group_by flag was added to the sql_mode server variable years ago. When this sql_mode was set, then MySQL rejected the bad query by implementing the behaviour prescribed by the SQL-92 standard. Here’s a test to demonstrate this behavior in MySQL 5.6:

See: MySQL 5.6 requires that all SELECTed columns be in the GROUP BY clause; “CountryCode” is not, so an error message signals it.

Using this new safe GROUP BY mode was rightfully advertised in the manual, and by users such as http://mechanics.flite.com/blog/2013/02/12/why-i-use-only-full-group-by/
or http://codeascraft.etsy.com/2013/03/19/the-perils-of-sql_mode/.

Still, this safe mode wasn’t enabled by default. I wasn’t around at that time (as it was more than twelve years ago), but I can speculate that there were the following obstacles:

  1. Certain non-standard queries can be perfectly reasonable; for example this one, which wants a list of country names and how many languages are spoken in each country:

    In a group of rows of the same co.Code value, all rows have the same co.Name because co.Code is a primary key of the Country table. So co.Name is deterministic, but it’s not in the GROUP BY clause so SQL-92 and 5.6’s only_full_group_by mode rejected it.
  2. So enabling the mode would make some users’ life unduly difficult (the kind of user who “knows what they are doing”); they would have to put all selected columns in the GROUP BY clause, thus making this clause more  complex and thus increasing the chances that MySQL switches from an index scan to a table scan plus a filesort and a temporary table, thus making the query far slower.
  3. The Standard itself evolved to accomodate these reasonable queries! Starting from SQL-99, the concept of “functional dependency” was introduced; a column B is functionally dependent on columns {A1, A2} in a result set if all rows which share the same value of A1 and the same value of A2 necessarily share the same value of B. Then SQL-99 accordingly modifies the constraints on GROUP BY: by default it keeps the same rules as in SQL-92, but it allows a DBMS to optionally use a relaxed rule, specifically that all columns in the SELECT list merely need to be functionally dependent on the GROUP BY columns. That is known as “SQL optional feature T301″ and it guarantees that inside a group of rows, all selected columns have identical values, thus making the query’s result deterministic. That is exactly the case of the last query we showed where “co.Name” is determined by “co.Code”. Of course the old SQL-92 rule satisfies the relaxed rule: if all selected columns are part of the GROUP BY clause then they are functionally dependent on the GROUP BY columns. So we can say that the relaxed rule allows all SQL-92 legal queries, plus some new ones, while preserving determinism.

That was well explained by my former colleague Roland Bouman in
http://rpbouman.blogspot.co.uk/2007/05/debunking-group-by-myths.html .

For us then, it was a problem. The safe mode, only_full_group_by, was too restrictive and thus we could not make it the default. But that also meant that our innocent users were left prey to the potentially dangerous default mode. Once in a while a user would come to us with a strange result and we would have to explain, once again, that it’s a non-deterministic query and she/he should try only_full_group_by.
Another headache was that our QA team, who runs many thousands of automatically generated queries every day, was also experiencing this non-determinism; query result differences between, say, MySQL 5.5 and 5.6, would be flagged as regressions, though it was just a non-deterministic query with a GROUP BY. To avoid these false positives, QA would be running with only_full_group_by, which alas limited them to queries which were only a subset of what our users were allowed to do with the default permissive setting. So we were losing some testing depth, which was obviously not desirable.

Good news — those days are now over!

In MySQL 5.7.5, I have made only_full_group_by detect functional dependencies, which can be inferred not only from keys’ definitions as in the example above, but also from column equalities in the WHERE clause or [LEFT] JOIN ON clauses and from selected expressions in a view’s or derived table’s body. It was not an easy job, and it took a while to get it right, especially with outer joins and with views. Along the way, I even found a couple of small bugs in the relevant section of the SQL-2011 Standard, and thanks to the help from a colleague who has access to the Standard’s redaction committee, those bugs should be corrected in the next version of the SQL Standard.

The different types of functional dependencies are best explained with examples; so together with my colleague Paul DuBois we put them in a new section within the 5.7 manual. I really encourage you to read it as it shows well when columns are determined by other columns and when they are not, in spite of sometimes deceptive appearances! We used the world database there too, and our queries are understandable — so please check it out!

And, for a higher-level view, this is the master page which describes the new behaviour of only_full_group_by: http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html .

Because this mode is now smarter than before, there is very little reason not to use it. So I have made it a default in 5.7.5 and later. This means that some old queries might return an error when upgrading. Should this happen, please check them; quite possibly they are non-deterministic and should be corrected (remember the COUNT() example earlier in this post). If they are deterministic, and MySQL simply missed it, or if you really want non-determinism, a new function called ANY_VALUE has been added to force MySQL to accept the intentionally/knowingly non-deterministic query. Of course, you can also take the more brutal approach and turn only_full_group_by off in your session or globally.

Several prominent DMBSs that I know continue using the SQL-92 rule, while another one detects functional dependencies due to keys only. I think MySQL is now the DBMS with the most complete implementation, inferring functional dependencies from keys, equalities, and in views. We don’t have everything that’s described in SQL-2011, but we’re not far.

Implementing this feature was also an occasion to refactor the relevant code. It used to be scattered in several functions, accumulating pieces of information as resolution of the query progressed (in Item::fix_fields() calls), and drawing a conclusion in a final phase of resolution. Consolidating all this into one single entity of code, which looks only at the fully resolved query, made things clearer and automatically fixed an obscure bug with views. The main code file is sql/aggregate_check.h and it has an extensive Doxygen multi-page comment to explain the logic.

Here’s another eliminated annoyance: in only_full_group_by mode, using an alias of a SELECT list element in the HAVING clause was forbidden. That was known as http://bugs.mysql.com/bug.php?id=51058, which I have also now fixed as part of this work. You can now do this in only_full_group_by mode:

That’s far more readable than copying the full COUNT() expression into the HAVING clause and it’s also a bit faster to execute.

There was one last fix done as part of this Worklog. That is having to do with some rare non-grouped queries of the form SELECT DISTINCT ... ORDER BY also being non-deterministic, but this post is already long so I’ll discuss that in a subsequent post. Stay tuned!

That’s all for now. I really hope that this new feature makes users’ lives easier and improves their experience with MySQL! We also look forward to your feedback on this new feature! If you have any questions or encounter any bugs, please do let us know by opening a support ticket or filing a bug. As always, THANK YOU for using MySQL!

Write Yourself a Query Rewrite Plugin: Part 2

In my last post I covered how to use the query rewrite framework to write your own pre-parse plugin. The interface is simplistic: a string goes in, a string comes out, and the rest is up to your plugin’s internal workings. It doesn’t interact that much with the server. Today I am going to show you the other type of plugins that rewrite queries, post-parse query rewrite plugins. This type is, out of necessity, more tightly coupled with the server; it operates on the internal data structures that make up the query’s parse tree.

Creating the Plugin

Declaring the plugin is similar to declaring a pre-parse plugin: you declare the plugin in the usual way but with the addition of a specific plugin descriptor for the post-parse query rewrite plugin type. This is a struct, as usual:

As you can see there’s no function to free up memory, as we had with the pre-parse query rewrite plugin type. This is not likely needed in a post-parse scenario because you will be working much more on the data structures that the server offers and roll your own to a much smaller degree. Another thing to note is that you can ask the server to give you the statement digest along with the parse tree. Statement digests are essentially hashed values for queries which you can read all about in the manual. They are calculated from normalized statements (see the link) during parsing and you are likely to want them if you write a post-parse plugin. The provided Rewriter plugin (read about it in this post) makes heavy use of them.

The plugin’s entry point (I’ll call it the ‘rewrite function’ from here on) is called when a statement has been parsed. This happens either when a statement is sent over the wire the normal way, or when a prepared statement is prepared. This makes it possible for your plugin to operate so that prepared statements are only rewritten when they are prepared and after that the rewritten version is always run.

The rewrite function gets called with a Mysql_rewrite_post_parse_param struct, which gives you a flag that you can raise to tell the server that you rewrote the query, and a THD object. The THD object is key to accessing the internals of the server, and it allows you to examine the parse tree.

At Your Service

A plugin working on the parse tree is not that useful without the necessary primitives to operate on that parse tree. That’s where the parser service comes in. The MySQL 5.7 server offers certain services to plugins—read all about them here in the manual—and long with the query rewrite framework, the server now offers a parser service. This service lets you parse strings, manipulating the parse tree and the related functionality. The full documentation will end up in the manual in due course, but I will give you a quick walk-through today.

First Things First

MySQL has a habit of resolving the “current database” when parsing a string. This means that once you’ve parsed a query, there is no more dependency on the “current database” context. The “current database” is a session-dependent setting, so you may or may not want to use the “current database” from the session that triggered the plugin. If you wish to modify it, then you can use the mysql_parser_set_current_database(MYSQL_THD,</code> const MYSQL_LEX_STRING) interface to set the “current database” to whatever you want.

Let’s Parse

Alright, let’s parse something. The function to use for that is: int mysql_parser_parse(MYSQL_THD thd, const MYSQL_LEX_STRING query, unsigned char is_prepared, sql_condition_handler_function handle_condition, void *condition_handler_state)

This function lets you send in a query string for parsing. If you set is_prepared to a non-zero value then the string will be parsed as a prepared statement, i.e. you’re allowed to use the ? placeholder in lieu of literal values. There are interfaces that let you work with these placeholders (count, locate, etc.) later on.

A word of caution here: when you parse a string it will be the session’s new “current” parse tree, replacing the old one. There are ways around it which I may cover in a future post. But for now, let’s assume that parsing a string means replacing the “current” parse tree.

You may also want to handle parse errors, in which case you can send in a function pointer to a callback function handle_condition. If you want a state to be passed along to it, for instance to count the number of non-fatal warnings, then you would pass a pointer to it as condition_handler_state.

Take a Walk on the Server Side

The parse tree is walked using callbacks, so you will need to declare a parse_node_visit_function to handle them. This will be called for each literal in the parse tree, and it also lets you pass a state object around. The signature for this is: int (*parse_node_visit_function)(MYSQL_ITEM item, unsigned char* arg)

The MYSQL_ITEM represents a literal which you examine by printing it with: MYSQL_LEX_STRING mysql_parser_item_string(MYSQL_ITEM item)

The string is allocated on the server side so it has to be freed by the server if you are to have any chance of your plugin running on OS’es that have separate heaps for dynamic libraries (yes, I’m looking at you, Windows). You do this by calling: void mysql_parser_free_string(MYSQL_LEX_STRING string)

Once you have a function to process literals in place—printing them out is a good start—then you’re ready to walk the parse tree. It is available from the MYSQL_THD, and we walk it by calling: int mysql_parser_visit_tree(MYSQL_THD thd, parse_node_visit_function processor, unsigned char* arg)

If this function returns a non-zero value then the server has probably knocked on your door already by calling your handle_condition() function, if you bothered to declare it. In other words, a non-zero return value means something went wrong.

Rewriting Something

Typically the first thing your plugin will want to do is look at the current query to see whether it should be rewritten. There are some different ways that you can do that. You can look at the digest—the normalized representation of the query—the query string as it looked when it came in, and the literals in the query. The first three have an increasing level of precision: the digest will accurately identify any normalized query, but there’s a slim chance of collisions, especially if the queries strings are very long, as the digest is only calculated on the first 1024 tokens (something we’d like to make configurable in future work). The normalized query format will group highly similar queries together by replacing constant expressions with ‘?’ symbols, while the exact query string is the way it looked prior to parsing. If you don’t want to use something like that, then you’re probably better off writing a pre-parse plugin.

The framework doesn’t let you make destructive changes to the parse tree, so you have to create a new one. How do we create a new parse tree, then? Anyone?

If you said “parse a string” out loud right now, you’re right. That’s what we do, and we use mysql_parser_parse() to do it. If you paid attention earlier, you know that the tree we parse will now be the new “current” parse tree.

Once you’re done you can raise the FLAG_REWRITE_PLUGIN_QUERY_REWRITTEN note in your rewrite function and return 0. Then you can look for the “Query rewritten” SQL Notes in your client applications.

I hope that this was helpful. As always, THANK YOU for using MySQL. Happy rewriting!

Write Yourself a Query Rewrite Plugin: Part 1

With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.

When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I cover in a separate post — is the efficiency, especially the lack of overhead for those queries that are actually rewritten. Typical cases where you may want to write a pre-parse plugin are:

  • When you want to remove certain specific clauses from queries. For example, perhaps you want to remove all ENGINE clauses from DDL statements.
  • When you want to replace all queries against table_foo with table_foo2.
  • When you want to add a hint, for example a USE INDEX hint, to all queries against tbl3.
  • Any general case where you might want to do a broad REGEXP style find and replace.

Example Plugin

There will be an example pre-parse plugin shipped with MySQL. It is not very useful, but you can take it as a starting point for developing your own plugins. What it does is rewrite all queries to lowercase. You would typically issue the following commands to install it and then to demonstrate its usage and effects:

As you can see, it really rewrites all queries, including the show warnings query that I just issued. I did that to show you that the server knows that the query was rewritten and lets you know by raising a note.

The Basics

The plugin is conceptually very simple: it’s a single C function. The input is a const char *, the output another const char *. If your plugin returns a new query string, that’s the new query and the server will forget about the old one. Only an SQL note remains as a testimony of the original query.

You really only need three files to create a plugin:

  • Your source code file
  • A plug.in file
  • A CMakeLists.txt file

Most of what goes into writing general plugins is already covered well in the manual, and http://dev.mysql.com/doc/refman/5.7/en/writing-plugins.html is a good starting point. What is new is the specific pre-parse query rewrite plugin type. You need to declare your specific plugin of this new type as shown below:

This should be fairly straightforward. The first argument is the version number of the interface that the plugin is using, the second is the function doing the rewrite, and the third is called by plugin framework after the rewrite is complete in order to free up any resources you might have been using—for example, in the rewrite_example plugin’s rewrite_lower function we allocate a new char[] that we return a pointer to, so we need to clean that up afterwards in the free_rewritten_query function.

As always, THANK YOU for using MySQL. Happy rewriting!