Logging with MySQL: Error-Logging to Syslog & EventLog

You’ve already read it in What’s new in 5.7 (So Far) — the MySQL server now has new-and-improved supported for syslog (on unix-like systems) and EventLog (on Windows). In the next few paragraphs, we’ll take a look at what they are, what has changed, and how they can make your life easier.

The MySQL server supplies information in two main ways:

  1. The client will receive a reply to every statement. If everything goes right, then we’ll see a simple OK for success, or a result set for SELECT, SHOW, etc.; and even a successful statement may be qualified by a set of warnings or notices. If the statement fails for some reason then we’ll receive an error regarding the failure.
  2. On the server, we’ll see a variety of logs depending on the server configuration. Queries exceeding a certain execution time may get logged to the slow query log, while in some debug scenarios all queries may be logged to the general query log either as soon as they are received (--log-raw) or after parsing and having security sensitive information such as passwords replaced. Both the slow query log and the general query log may be sent to a file or table. The third major log is the error log.

The error log — what and why

Like the clients, the error log may receive messages of varying severities. It collects messages that do not have an obvious query, client, or connection that is to blame, messages that need persistence beyond that of a connection or the server’s runtime, and others that have an intended audience of the DBA or system administrator rather than the application developer. Examples are exhaustion of resources on the host machine, certain errors reported by the host operating system, backtraces of crashes, messages about damaged databases and indexes, errors on start-up, failed or refused connections, etc.

The error log — where and why

Unlike the slow and general query logs, the error log cannot be kept in a table — this sidesteps questions such as how to safely log an error about the table engine malfunctioning while using that same engine for error logging. This also makes the error log persistent beyond the runtime of the server, and makes the log file accessible to specialized log analyser tools.

Enter syslog

Both Unix and Windows do however already have standardized logging facilities — syslog on Unix and EventLog on Windows. Using those facilities gives us access to a plethora of tools written specifically for them, it lets us log to a different machine, etc. While the MySQL server (mysqld) previously had very basic support for the Windows EventLog, syslogging was implemented by simply writing error messages to a stream (STDOUT/STDERR). Through this stream, whatever started the server (e.g. mysqld_safe) may receive or redirect this information at the OS level (note: if you start mysqld directly these streams will be visible in your terminal window; on Windows you have to use the --console option). The mysqld_safe wrapper script would start the MySQL server and optionally direct this error output to a program that would write it to the Unix syslog.

Onwards and upwards

While that syslog support solved the immediate problem, there was room for improvement — all MySQL server errors were filed with a syslog severity of error, even if the message said something different. A line could contain one time-stamp from the MySQL server, and another from the syslog facilities. And those were just the cosmetic issues!

Native syslogging

Having native support for syslog in the MySQL server itself enables us to turn syslogging on or off at runtime, and to query its status.

The syslog facility—information about what category of software the message were from—is also no longer fixed to daemon but can be queried and configured at runtime. This should make it easier to adapt the MySQL syslog messages to your syslog environment and enable better message filtering. See the manual page for syslog.conf (shell> man syslog.conf) for more on filtering.

The following options have also been added and can be set at start-up as well as viewed and set at runtime via system variables (setting them requires the SUPER privilege):

  1. --log_syslog
  2. --log_syslog_include_pid
  3. --log_syslog_facility
  4. --log_syslog-tag

mysqld_safe

While the mysqld_safe wrapper script has been updated to be aware of the server’s new native syslog support, the options to the script remain unchanged. Where before, mysqld_safe used the program logger to pass the server’s error messages to syslog, the script now selects server options consistent with the options given to the script. Therefore mysqld_safe --syslog --syslog-tag=AZOU would result in mysqld being started with mysqld --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=AZOU.

It bears noting that the options to mysqld_safe control both the server and the mysqld_safe script itself. In the above example, the MySQL server would log as mysqld-AZOU, whereas the mysqld_safe wrapper script would log as mysqld_safe-AZOU.

systemd-journal

A variety of linux systems have recently adopted systemd. Its logging facilities offer a sink that is compatible to traditional syslog, making the MySQL server’s native support compatible out of the box. When systemd journaling is used, corresponding tools should be used to query and filter the logs, e.g. the journalctl program to query the logs. In the following example, our running MySQL server has the tag production (and therefore logs as mysqld-production).

We could then use journalctl to ask for all log entries using that identifier:
journalctl -f SYSLOG_IDENTIFIER=mysqld-production

Due to the richness of systemd-journal logging, this is somewhat verbose. That’s why in the following example we ask for the output to be JSON-formatted. We’ll then use the jq tool to filter this JSON stream so that the only the fields we are interested in remain—SYSLOG_IDENTIFIER, SYSLOG_FACILITY, and MESSAGE (see the systemd.journal-fields manual page for others):
journalctl -f SYSLOG_IDENTIFIER=mysqld-production -o json | jq '.SYSLOG_IDENTIFIER + " " + .SYSLOG_FACILITY + ": " + .MESSAGE' 2>/dev/null

Windows

On Windows EventLog is used in lieu of syslog. Like on Unix logging can be enabled, disabled, and queried at runtime. The facility and process ID options, however, are not available on Windows. A tag can be set and queried at runtime, but to create a new tag (start using a tag that does not already exist) the MySQL server needs to be started with sufficient Windows system privileges needed to create an entry in the Windows Registry. Once this entry is created then the elevated system privileges are not required to use the newly created tag. As the entry exists in the Windows registry and therefore outside the MySQL server, it will persist through restarts of the MySQL server and indeed the host machine. Care should therefore be taken not to pollute the registry with a great number of unused tags.

All’s well that sends well

Finally, after talking so much about the server (and laying the groundwork for future Log Lady reports), I would be amiss not to note that the MySQL client now also supports syslog. I look forward to your feedback on these new features! If you encounter any issues, please let us know by filing a bug or opening a support ticket.

Thank you for using MySQL!

GDB Tips: Inspecting MySQL Plugin Variables in Core File

Recently I had a need to determine the session value of a MySQL plugin variable in a core file.  Here I use the word plugin variable to refer to MySQL system variables provided by plugins.  While this is not a very difficult task, it is not as straight forward as printing a global variable. It took some time to figure out the layout of the plugin variables and get the needed information. This short article is to share this gdb tip with other interested MySQL developers and support engineers.

In this article, let us inspect the session value of the plugin variable innodb_strict_mode, which is of type boolean. Quite obviously, this plugin variable is provided by InnoDB storage engine.

The Quick Answer

This is what you can do to get the session value of the plugin  variable innodb_strict_mode from the mysql server core file.  For more details, refer to subsequent sections.

According to the above output, innodb_strict_mode has been enabled.

MySQL System Variables

The global values of the MySQL system variables are available in the global object (refer to sql/mysqld.cc):

Here we are interested in the session value of a system variable.  So we will not make use of this global object.   The session values of the MySQL system variables are available in the member variables of the THD object:

There are two types of system variables — the system variables provided by the MySQL server, and the system variables provided by the plugins (also known as “plugin variables”). The system variables provided by the MySQL server will be proper members of the struct system_variables .  But the plugin variables are kept opaque at the server layer. The plugin variables are dynamically allocated and are pointed to by the member dynamic_variables_ptr.

All the session values of the plugin variables are available in this memory block.  To access the session value of the plugin variables, we need to access it at a certain offset within the memory pointed to by THD::variables::dynamic_variables_ptr.

The memory for the session variables are allocated in the function  alloc_and_copy_thd_dynamic_variables() , which is documented to “Allocate memory and copy dynamic variables from global system variables to per-thread system variables copy.”

Accessing Session Value of Plugin Variables

As mentioned in previous section, all session values of plugin variables are stored in a single block of memory.  To access the session value of any particular plugin variable we need to know the offset within that block of memory.   This information is provided by a global object for each plugin variable.

All MySQL plugin variables will need a global object pointing to their location.  This global object is declared and defined using the macro MYSQL_THDVAR_BOOL , if the plugin variable is of type boolean.  For plugin variables of different types, different macros are available.  For the variable innodb_strict_mode this declaration is as follows:

This creates a global object of an anonymous struct type.  There are a series of macros involved in expanding the above. All the related macros are given below for easy reference:

When run through the pre-processor our declaration of “strict_mode” is expanded as follows:

A global object of an anonymous struct type has been defined by the macro MYSQL_THDVAR_BOOL .  As seen above, the offset is initialized to an invalid value of -1.  The offset will be set to the correct value at process run time, when a plugin variable is registered with the server process (refer to register_var() ).  Using this offset we can access the session value of the plugin variable.  If you look at the Quick Answer section now, it might be more clear.

If we want to inspect the session value of another plugin variable innodb_optimize_point_storage then the following gdb command can be used:

The above output indicates that innodb_optimize_point_storage is disabled.  I’ll leave it as an exercise for the reader to inspect innodb_support_xa.

The THDVAR macro

The previous sections explained the layout of the plugin variables and provided information to access their session value manually through a debugger like gdb.  If you are a MySQL developer and writing code, then you need to make use of the THDVAR() macro to access a session value of the plugin variable.  To access innodb_strict_mode value, one will use code as follows:

Conclusion

This article provided information to help in quickly identifying the session value of a plugin variable within a mysqld core file. It also provided some details regarding the layout of the plugin variables and the macros to use when you declare, define, and access them. Comments and feedback are always welcome. Thank you for using MySQL!

 

 

 

Everything about MySQL Users and Logins You Didn’t Know and Were Afraid to Ask

Logging into a MySQL server is generally dead simple—supply a username, a password, and you’re all set!

There are, however, more complex use cases such as when making use of our Enterprise Authentication plugins. It’s also sometimes helpful to have a more detailed understanding of what happens “under the hood”. So I’ll attempt to lift the hood and walk you through all of the nitty-gritty details regarding exactly just what happens when you log into a MySQL server.

Firstly, there are no less than 4 “users” involved in the authentication process. And a distinction between a user id and a user account exists. And it gets more and more advanced with each release. So I thought I’d take the time to try to walk you through the MySQL authentication process and clarify once and for all the stages involved, the information exchanged at each stage and the expected outcome.

I’ll use the tools that come with the server for my examples, but I’m sure it can be extended to most of the existing clients.

It all starts with the application, e.g. the mysql command line tool.

To open a connection it calls mysql_real_connect() and supplies, among other parameters,  a user id and a password. Let’s call this user id “application user name“.

Now the client library opens a network connection to the server. It then reads the server hello packet.

The client library takes from the server hello packet the data coming from the server’s default authentication plugin, combines them with the password supplied by the application and calls the client’s default authentication plugin.

The client’s default authentication plugin uses the information to create the authentication data to be sent into the client’s hello packet, together with the application user name.

Now the server has the all it needs to begin its own authentication process:

  • The application user name
  • The host from which the client is connecting from. We’ll call this “application user host
  • The name of the client’s default authentication plugin
  • The first batch of authentication data sent by the client authentication plugin
  • The server’s user account table

The server first stores the application user name into the security context so it can later be retrieved through the USER() SQL function.

Next it needs to find the user account to authenticate against. A user account is the object created by CREATE USER or GRANT, that also can have privileges granted to it.

MySQL user accounts are a user name@host combinations. But we happen to have one at this point. We’ll call “application user” the combination of “application user name“@”application user host“.

Now the server iterates over the user accounts in “specificity order” and tries to match the application user against one of them.

Specificity order means that it starts from the most specific ones (no wildcards) and them moves towards the mixed ones (letters and wildcards) and finally scans the least specific ones, consisting of wildcards only.

This is where we need to mention the infamous anonymous account. An anonymous account is defined as having an empty user name (“”). This means that there can be several anonymous accounts : e.g. “”@localhost, “”@”%.domain.com”, “”@”%” etc.

After the matching process is done we end up with a user account of the form: <user name mask>@<host name mask>.

Since it’s a wildcard match the user account may or may not be equal to the application user.

E.g. one can expect that the application user joro@localhost will match the user account “joro”@”%”.

Of course if there was another user account “joro”@”l%” the application user “joro”@”localhost” would have matched that since it’s more specific than the “joro”@”%” user account.

What the server does next is to store the user account found into the session’s security context so it can be returned later. We’ll call this account “authentication user account“.

Since each MySQL user account defines what authentication plugin to use to authenticate to it the server now knows what server authentication plugin to call to conclude the authentication process.

If the selected server authentication plugin is compatible with the default client authentication plugin it can work with the data already submitted in the client hello packet. In this case the server just passes along the data already received to the server’s authentication plugin.

If they are not compatible then the server needs to ask the client to re-start the authentication dialog using another client authentication plugin that’s compatible with the selected server authentication plugin.

Either way, the client/server authentication plugin pair concludes the authentication process and the server plugin returns the following pieces of information to the server:

  1. Result of the authentication (success or failure)
  2. The user name of the external user account used by the plugin to conclude the authentication against an external repository
  3. An proxy user name this session should use instead of the authentication user account name.

#1 is obvious. It’s a “yes” or “no”.

The server stores #2 into the system variable @@external_user. Since it’s not a MySQL user account nor is it a application login name it’s just for information and can be used to track the access to 3d party user directories that the plugin does. Currently @@external_user is used by the windows authentication plugin to return the windows user account name that was used (since it can be different from the supplied application user id).

Now #3 is a bit less straightforward. The MySQL server supports the concept of proxy users. The authentication plugin for the authentication user account can “redirect” the session to use the credentials of a more suitable MySQL user account based on some authentication plugin logic. This allows external selection of MySQL user accounts relevant for the external user at hand.

Why is this useful ? Imagine you want to use UNIX as a back-end to authenticate your MySQL server users. But you may have 1000s of UNIX users. You can’t define a corresponding MySQL user account for each of them ! That would defeat the purpose, since you’ll have to maintain both sets in parallel and keep them in sync.

You can of course define a wildcard account (e.g. “”@”%”) and all the application user names will match it. But this only gives you a single set of MySQL permissions (the ones defined for the wildcard account) to work with. And you definitely don’t want all of your external users to share the same MySQL permissions !

Proxy users to the rescue. You define a handful of “proxy” user accounts that carry  different permissions sets. You then make your authentication plugin return one of these when it sees that a UNIX account is a member of a certain UNIX group. Now all you have to do to make your UNIX users part of one of these groups. And they map to the relevant MySQL “proxy” user account through the magic of the proxy users support.

Note that the proxy user name defaults to the application user name. So if the plugin chooses not to submit a different one the server knows that no proxying will be used and can conclude the authentication and use the authentication user account.

Note that the proxy user name is only a user name. The application user host is reused. We’ll call the “proxy user name”@”application user host” couple “proxy user“.

The proxy user is also matched in specificity order to the list of MySQL user accounts until an account is found. We’ll call this a “proxy user account“.

Now the server has the MySQL user account to use for the session. It’s either the authentication user account or the proxy user account.

The server stores this account into the security context so that it can be returned through the CURRENT_USER() SQL function.

And, if a proxy user account is used, the authentication user account is stored into the @@proxy_user system variable.

So to recap, when a new session is created we have the following user functions and status variables:

  • USER(): the application user, or a combination of the application user name and the application host name.
  • @@EXTERNAL_USER: the non-MySQL related user id that was used by the plugin to authenticate. Plugin specific.
  • CURRENT_USER():
    • if proxying was used this is the selected proxy user account
    • if no proxying was used this is the selected authentication user account
  • @@PROXY_USER
    • if proxying was used this is the authentication user account
    • if not proxying was used it’s empty (NULL)

Now you know everything there is to know about users, logins and accounts in MySQL.

The Story of One Contribution

The MySQL Server 5.7.5 Development Milestone Release includes support for acquiring multiple user-level locks within the same connection. The implementation of this feature is based on a contributed patch by Konstantin Osipov. This post tells the story about what happened with this patch on its way into the MySQL Server codebase.

If you are more interested in using this new functionality and the feature itself, rather than in the history behind it, then it is better to simply read the corresponding entry in the Release Notes or the updated documentation for the GET_LOCK() function.

Requests to extend the semantics of user-level locks in MySQL to support acquisition of multiple locks in the same connection have been around at least since 2003 (see bug#1118).

But for a long time we lacked crucial infrastructure for implementing this change. In MySQL Server 5.5 we at last introduced the metadata locking (MDL) subsystem — an engine-independent lock manager with support for deadlock detection. This finally made the implementation of the feature request feasible. Unfortunately, tasks with higher priority took precedence and support for multiple user-level locks didn’t get attention in either the 5.5 or in the 5.6 release.

Naturally, we were really happy when our former colleague Konstantin Osipov came up with a patch implementing this feature and was kind enough to contribute his patch to MySQL Server.

So what happened after the necessary steps (as described in this post by Erlend Dahl)—like signing the OCA, reporting a bug (bug #67806) and adding the patch to it—were all completed by Konstantin?

First of all, even though this was a contribution we still needed to provide a proper design document for it. There are several reasons why:

  • Having a design document allows us to perform a proper design review without diving too much into implementation details and see if there are issues with the design or if there is some nice-to-have functionality which might be missing.
  • The design document serves as input for the QA Team for building test plans.
  • The design document also serves as input for the Documentation Team when time comes to document the changes implemented.

In this case we simply changed the existing WorkLog #1159 “Allow multiple locks in GET_LOCK()” to follow the implementation in the contributed patch.

After that the design was reviewed, review comments discussed and adjustments to the document and the design were made. A few examples of issues which we identified while working on the design document for this particular contribution:

  • It is a bad idea to use ER_LOCK_DEADLOCK for deadlocks caused by user-level locks as this error code implies transaction rollback, which doesn’t happen in the case of user-level locks.
  • It would be nice to have a function which releases all locks held by a connection. So we decided to add RELEASE_ALL_LOCKS().
  • Existing code always assumed that lock names are in UTF-8 and compared them in case-insensitive fashion. We decided that we need to convert names to UTF-8 if they are provided in different charset and preserve case-insensitive comparison.

Then the patch was adjusted according to the updated design (some contributions are even totally rewritten as a result of this process) and the code was polished. For example, we decided to use a somewhat different API for finding the lock owner in the metadata locking subsystem instead of the one provided in the patch contributed. Sometimes we might find bugs in the old code during this step. In this case we found bug #73123 “Possible access to freed memory in IS_FREE_LOCK() and IS_USED_LOCK()”.

Around the same time the QA team reviewed the design, provided their comments, and prepared the test plan. Occasionally design and code needs to be changed as result of these discussions. This didn’t happen in this case though. Test cases were extended accordingly.

Code review happened. Issues discovered during it were fixed. Finally, the patch was approved by the reviewer and passed into the hands of the QA team.

The QA team ran the tests according to their test plan (including performance benchmarks and RQG runs) and checked code coverage. Often issues are discovered which need to be fixed at this stage. In this particular case no issues were found. We also checked that the code builds and passes tests on all platforms we support.

Once the QA team said that patch was good enough to go into trunk, it was then pushed into the main MySQL development branch (MySQL 5.7).

The work on the task/patch didn’t stop there. After it was pushed the Documentation Team updated the documentation describing the related functionality. See the updated descriptions of GET_LOCK() and RELEASE_ALL_LOCKS() functions in our manual. Also an entry on the 5.7.5 Release Notes was added and the corresponding feature requests were closed in the bug system.

And only after these final steps did the work on this contribution came to completion.

A big THANK YOU is owed to several people:

  • First of all, to Konstantin Osipov for his contribution!
  • Rune Humborstad and Jon Olav Hauglid for reviewing the design and the code.
  • Matthias Leich for doing the QA review of the design and performing the QA work.
  • Paul Dubois for documenting the changes.

Resizing the InnoDB Buffer Pool Online

As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7.5.

You can now use the "SET GLOBAL innodb_buffer_pool_size = xxxx" command which causes a resizing job to begin in background. We can monitor the progress and state of the resizing work using the Innodb_buffer_pool_resize_status variable and the server error log.

In order to minimize the impact of the resulting memory allocation/freeing process, thus avoiding huge memory copy tasks which would then block other transaction processing related tasks, the resizing is done in fixed size chunks. And because InnoDB is designed to use equally sized buffer pool instances, the total buffer pool size is (upper) aligned to a multiple of [innodb_buffer_pool_instances × innodb_buffer_pool_chunk_size].

All allocate/free/search calls for pages within the buffer pool are only blocked during the critical path in the resizing operation. The critical path consists of the following parts:

  • Allocating memory from the OS and freeing memory back to the OS
  • Resizing internal hash tables (page_hash, zip_hash), if needed

The length of the time for the critical path is largely dependent on the OS memory manager’s performance.

The preparation phase for a resizing operation that shrinks the buffer pool may potentially take a long time, but it doesn’t block other concurrent transaction processing related tasks. The preparation phase consists of the following parts:

  • Reduce the number of pages in order to fit within the new smaller size
  • Relocate the pages from chunks that must now be freed

Naturally if there will now be too many dirty pages for the now smaller buffer pool then they will need to be flushed and then freed. This work could cause significant disk IO and thus could potentially indirectly interfere with other transaction related tasks while it runs.

Also of note, if a transaction has locks on any of the pages in the chunk to be freed, then the relocation of those pages should wait for the transaction end. So high transaction throughput or long running transactions can potentially block the buffer pool resize operation. Any resulting long waits are logged to the server error log file along with information about the suspected transactions that are causing the operation to wait/block.

Lastly, there is also one limitation to note. During the buffer pool resizing process the adaptive hash index (AHI) is disabled. This is done because the AHI uses buffer pool pages and when active it’s very difficult to relocate the necessary pages.

That’s all for now. I 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. Thank you for using MySQL!

What’s New in MySQL 5.7? (So Far)

We recently announced our 5.7.5 Milestone Release, yet another milestone on our road to 5.7 GA. The purpose of our milestone releases is to get community feedback and to ensure high quality on 5.7 from day one. This blog post gives the reader a high level view on 5.7 so far, while also attempting to demonstrate where we are heading as many of the individual pieces make much more sense when you can see the bigger picture. You might further drill into the series of milestone blog posts (5.7.1, 5.7.2, 5.7.3, 5.7.4, 5.7.5) and even further down to the individual worklogs with their specifications and implementation details. Or perhaps you prefer to just look at the source code at github.com/mysql.

The MySQL development team is stronger than ever, and so far we have implemented (as of September 2014) 244 worklogs, added 497 MTR tests, and fixed 1263 bugs (5.7 specific). It might all be a bit overwhelming, so this little guide might be useful. Note: we’ve also released a number of early access features that I won’t discuss in this post as I’m focused on 5.7 DMRs here, but you can find an overview of the features recently made available via the MySQL Labs here, and you can find additional details on the individual features here, here, here, here, here, here, here, here, & here. OK, now on to the 5.7 DMR overview to date.

Performance & Scalability

Performance and scalability is a priority for MySQL, learning from  community feedback and taking into account trends and developments in computer hardware and its architecture. So far in 5.7 we have delivered impressive read-only scalability results with InnoDB and significantly speeded up the connection handling in the server layer. We are also having good progress on InnoDB read-write scalability, fast flushing, and fast bulk data loads.

InnoDB Read-Only Scalability. We have improved the performance for Read-Only (RO) and Read-Mostly workloads. We have significantly improved how InnoDB handles RO trans-actions (WL#6047, WL#6899, WL#6906, WL#6578). We have also removed server layer contentions related to Meta Data Locking (MDL) and removed the use of “thread locks” (thd_locks) for InnoDB (WL#7304, WL#7305, WL#7306, WL#6671). See Dimitri Kravtchuk’s articles “MySQL Performance: reaching 500K QPS with MySQL 5.7“,  “MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin“,  Sunny Bains’ article “Transaction life cycle improvements in 5.7.3“, and Jimmy Yang’s article “MySQL 5.7.3: Deep dive into 1mil QPS with InnoDB Memcached“.

InnoDB Read-Write Scalability. We have also improved the performance of Read-Write (RW) workloads. We have removed the “index lock contention” in InnoDB (WL#6363, WL#6326). The index lock that was used to protect the entire index tree structure is now replaced by more fine grained “block locks” in the tree. See Yasufumi Kinoshita’s article “MySQL-5.7 improves DML oriented workloads“.

InnoDB Faster & Parallel Flushing. We have reduced the number of pages scanned when doing flush list batches, speeding up page flushing (WL#7047). The time complexity of a scan is reduced from O(n*n) to O(n). We have also implemented parallel flushing by having multiple page_cleaner threads (WL#6642). This gives improved scalability and throughput on multi-core systems and avoids flushing becoming the bottleneck.

Speeding up Connection Handling. In some application scenarios (e.g. PHP applications) client connections have very short life spans, perhaps only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance. In 5.7 we have offloaded thread initialization and network initialization to a worker thread (WL#6606) and more than doubled MySQL’s ability to do high frequency connect/disconnect cycles, from 26K to 56K connect/disconnect cycles per second. See also Jon Olav Hauglid’s article “Improving connect/disconnect performance“.

Bulk Data Load Improvements.  Bulk Load for Create Index (WL#7277). This work implements sorted index builds, thus making CREATE INDEX operations much faster. Prior to this work InnoDB looped through the base table and created one record in the index table for each record in the base table. After this work InnoDB reads many records from the base table, sorts the records using the index key, and then inserts the chunked set of rows into the index table.

Online Operations

Always ON properties are essential to state of the art web solutions. It is important for DBAs or DevOps to be able to tune and extend their production system while continuing to serve users.  Thus, we continue to ensure that MySQL is in the front in this area. So far in 5.7 we have delivered:

Resize the InnoDB Buffer Pool Online (WL#6117). This work adds the capability to change the value of innodb_buffer_pool_size dynamically. This provides the ability to tune the buffer pool size—without incurring any downtime—as your database usage patterns evolve over time.

Automatic Truncation of UNOD Logs (WL#6965). This work implements automatic truncation of UNDO logs when separate UNDO tablespaces have been configured. InnoDB operates with several UNDO tablespaces that are periodically truncated, one at a time. While one UNDO tablespace is being truncated, the other UNDO tablespaces will still be available to service transaction management and ensure minimal impact on transaction processing. The purpose of this work is to avoid ever growing UNDO log file sizes that could occur in some usage scenarios. See also Bug#1287 reported by Scott Ellsworth.

InnoDB Online Alter Table. We have added support for online RENAME INDEX (WL#6752, WL#6555) and enlarge VARCHAR column size operations (WL#6554).

Setting Replication Filters Without Server Restart (WL#7057). With this work the slave options --replicate-* become settable via the new CHANGE REPLICATION FILTER command. These options can now be changed dynamically while the server is running, enabling users to modify replication filtering rules without requiring a server stop and restart. This work originates in a contribution from Davi Arnaut (Bug#67362). See also Venkatesh Duggirala’s article “Making MySQL Slave Replication Filters Dynamic“.

CHANGE MASTER Without Stopping the SQL Thread (WL#6120). In order to add/alter an option using the CANGE MASTER TO command, it was previously necessary to issue a STOP SLAVE command before the CHANGE MASTER TO command. This work relaxes that constraint. See also Shivji Jha’s article “Change master without stopping slave altogether“.

Optimizer Improvements

Many exciting things are going on in the optimizer area, such as Cost Model work, a new parser, a layered optimizer architecture, and a new GIS implementation. In addition we have implemented the following set of feature requests:

Improved “IN queries” With Row Value Expressions to Be Executed Using Range Scans (WL#7019). We removed the previous requirement on rewriting the WHERE condition into its equivalent AND/OR form. See Bug#31188 reported by Mark Callaghan and Bug#16247 reported by Domas Mituzas. See also Martin Hansson’s article “Range access: now in an IN predicate near you“.

“UNION ALL” No Longer Creates a Temporary Table (WL#1763). In 5.7 the optimizer avoids creating a temporary table for the result of UNION ALL queries when there is no need for it, i.e., when there is no top-level ORDER BY clause. This reduces the need for disk space and disk I/O when executing large unions, and perhaps even more important, the client will get the first rows immediately. See Bug#50674 reported by Mark Callaghan. See also Norvald H. Ryeng’s article “State of the UNION“.

Non-Sorted Fields in the Sort Buffer are Now Compacted (WL#1509). This optimization is about better utilizing the sort buffer, thus avoiding/reducing the need to go to disk while sorting. The user benefit is increased query performance.

EXPLAIN for Running Queries (WL#6369). This feature is useful if you are running a statement in one session that is taking a long time to complete; using EXPLAIN FOR CONNECTION in another session may yield useful information about the cause of the delay and thus help you optimize your schema and statements.

JSON EXPLAIN (WL#6510). We have enhanced the JSON EXPLAIN output by printing the total query cost, the cost per table, and the amount of data processed. This will make it easier for a user to see the difference between good and bad execution plans. See also Øystein Grøvlen’s article “MySQL EXPLAIN Explained“.

Make Use of Condition Filtering in the Optimizer (WL#6635). This work improves join ordering. It provides a much better prefix rows estimate by taking into account not only conditions that are used by the chosen access method, but all other relevant conditions as well. See Jørgen Løland’s articles “A New Dimension to MySQL Query Optimization” part1 and part2.

Improved ONLY_FULL_GROUP_BY SQL Mode (WL#2489). We have improved the behavior of the ONLY_FULL_GROUP_BY SQL mode and also enabled it by default in 5.7.5+. This work makes the SQL mode far less strict about selected/order expressions because the server now properly recognizes functional dependencies. This work addresses many user complaints, such as those described in Roland Bouman’s article “Debunking GROUP BY myths” and Bug#51058.

Parser Refactoring

We are in the process of refactoring the SQL parser in an incremental way. The old parser had critical limitations because of its grammar complexity and top-down parsing style which lead to poor maintainability and extensibility. So far we have done a lot of preparatory work (WL#5967, WL#7199), refactored the SELECT statement (WL#7200), and the SET statement (WL#7203). We plan to rewrite the entire parser. See the article by Gleb Shchepa “SQL parser refactoring in 5.7.4 LAB release“.

Optimizer Refactoring

Currently the phases of parsing, optimizing, and execution are all intermixed. Almost every module is spread over different parts and sections of the optimizer. As a consequence, the cost of maintaining the codebase is high and extensibility is poor. We started out on an optimizer refactoring project with the goal of clear separation of these phases. With a refactored code base, the optimizer will be able to evolve much faster. For example, we see this as a prerequisite for improving prepared statements. See WL#6016WL#6042WL#7082, and WL#7540. See also Guilhem Bichot’s article “Re-factoring some internals of prepared statements in 5.7“.

Work towards a New Cost Model

We want to improve the cost based optimizer and replace existing heuristics with cost based decisions. We want to produce better cost estimates which also take into account new hardware architectures (larger buffers, caches, SSDs, etc.). Better cost estimates will lead to better decisions by the optimizer and thus to better query performance. We have started to refactor the existing cost model code and to remove hard-coded constants. This will make the code more maintainable and make it possible to tune and configure the cost model for your particular hardware configuration, as well as laying the groundwork for storage engines to provide costs that factor in whether the data requested resides in memory or on disk. So far we have done preparatory infrastructure work and removed hard coded cost constants.  These are now replaced by configurable cost values that can be changed by the user, without touching the source code, and that can be adjusted by the server administrator. (WL#7182, WL#7209WL#7338, WL#5869, WL#6068, WL#7339, WL#7276WL#7315, WL#7316). See also Jørgen Løland’s article “The MySQL Optimizer Cost Model Project“.

InnoDB Fulltext Search

We introduced InnoDB Fulltext Search in 5.6. We have now added greater flexibility and further optimizations. For example, fulltext indexes in InnoDB now support an external parser just like MyISAM (WL#6943). The plugin can either replace the built-in parser or it can act as a front-end for it. See also Feature requests from Daniel van Eeden (Bug#68816) and Hartmut Holzgraefe (Bug#70400). We have also implemented optimizer hints that are passed down to InnoDB about a query so that InnoDB may skip part of the full text search processing, e.g. not to compute the ranking values if they are not needed (WL#7123). See Shaohua Wang’s article “InnoDB supports plugin parser in fulltext index” and Matt Lord’s article “Rankings with InnoDB Full-Text Search“.

Performance Schema

Monitoring is important to our users and customers, and essential to any data management system. Our goal is to be “best in class”. At the core of our monitoring strategy we have Performance Schema, introduced in 5.5. Performance Schema is a MySQL Storage Engine built for the special purpose of storing dynamically created events, and at the same time providing a uniform well known interface to events and their configuration. In 5.7 we continue to extend and enhance our monitoring, utilizing the Performance Schema infrastructure.  We have instrumented Metadata Locking (WL#5879), Transactions (WL#5864), Memory Usage (WL#3249, WL#7777), Stored Programs (WL#5766), Prepared Statements (WL#5768). We have also exposed SHOW SLAVE STATUS information (WL#3656) and USER VARIABLES (WL#6884) in Performance Schema, all while further reducing the overhead (WL#7802) and footprint. See also Mayank Prasad’s article “Performance Schema implementation Internals: Registering instruments” and “MySQL Performance Schema : Prepared Statements Instrumentation“.

Fabric Support

Oracle announced GA for MySQL Fabric on May 27, 2014. The Server team is working on some features to improve sharding, failover, and management of server farms.  We have implemented a new server method for clearing session state (WL#6797). It is now possible for a client to do a reset of the existing connection, i.e. to clean the existing session context and free up resources.  We have also implemented a new server method to bring servers off line (WL#3836). The intended usage is for upgrade purposes. Setting the server to offline mode will gracefully disconnect all connected clients except those with the SUPER privilege. “Super” users are allowed to connect and manage the system while in offline mode.

Security

We are continuously working on improving MySQL security, for example to ensure “secure by default” installations. We are also working on better data encryption, better password handling, better transport layer security, and more.

Secure Deployments. MySQL deployments installed using RPM packages are now secure by default. The installation process creates only a single root account, ‘root’@’localhost’, automatically generates a random password for this account, and marks the password as expired. The MySQL administrator must then connect as root using the generated random password and use the SET PASSWORD command to set a new password (WL#6962). The installation process creates no anonymous user accounts, no test database, and no demo related files (WL#6977WL#6973).

Improved Encryption.  We now support multiple AES Encryption modes (WL#6781). We have enhanced the security strength of Advanced Encryption Standard (AES) encryption/decryption functions (AES_ENCRYPT/AES_DECRYPT) by adding support for larger key sizes and different block modes. See also Georgi Kodinov’s article “Understand and satisfy your AES encryption needs with 5.6.17“.

Password Rotation. We have added a timestamp to the mysql.user table on the last time the password was changed (WL#7131). This work provides the means for implementing password rotation policies. See Todd Farmer’s article “Implementing a password policy in MySQL“.

SSL Improvements.  We have redefined the client --ssl option to imply enforced encryption (WL#6791). Before, when a MySQL client specified the --ssl option a connection could still happen without encryption being enforced. Now if specified, and no ssl connection is available, the connection will instead fail. We have also added SSL support for mysqlbinlog (WL#7198).

Partitioning

Our overall plan is to move in the direction of InnoDB native partitioning, which is now in Labs. This will pave the way for better partitioning; e.g. features such as parallel query processing, full-text indexes, and foreign keys on partitioned tables. So far in 5.7 we have added support for Index Condition Pushdown (ICP) for partitioned tables (WL#7231, motivated by Bug#70001), we have added support for the [{WITH|WITHOUT} VALIDATION] clause to the EXCHANGE PARTITION (WL#5630, motivated by Bug#57708), and we have added support for transportable tablespaces for partitioned innodb tables (WL#6867, WL#6868).  See also Mattias Jonsson’s article “MySQL 5.7.4 now supports Transportable Tablespaces for InnoDB Partitions“.

InnoDB Temporary Table Performance

One of the goals of 5.7 is to optimize InnoDB temp tables for better performance (normal SQL temporary tables). First, we made temp table creation and removal a more light-weight operation by avoiding the unnecessary step of persisting temp table metadata to disk. We moved temp tables to a separate tablespace (WL#6560) so that the recovery process for temp tables becomes a single stateless step by simply re-creating it at start-up. We removed unnecessary persistence for temp tables (WL#6469). Temp tables are only visible within the connection/session in which they were created, and they are bound by the lifetime of the server. We optimized  DML for Temp Tables (WL#6470) by removing unnecessary UNDO and REDO logging, change buffering, and locking. We added an additional type of UNDO log (WL#6915), one that is not REDO logged and resides in a new separate temp tablespace. These non-redo-logged UNDO logs are not required during recovery and are only used for rollback operations.

Second, we made a special type of temporary tables which we call “intrinsic temporary tables” (WL#7682, WL#6711). An intrinsic temporary table is like a normal temporary table but with relaxed ACID and MVCC semantics. The purpose is to support internal use cases where internal modules such as the optimizer demand light-weight and ultra-fast tables for quick intermediate operations. Finally, we made the optimizer capable of using InnoDB “intrinsic temporary tables” for internal storage (WL#6711). Historically the optimizer has been using MyISAM for storage of internal temporary tables created as part of query execution. Now InnoDB can be used instead, providing better performance in most use-cases. While MyISAM currently remains the default, our intention is to switch to InnoDB Temp Tables as the default.

Buffer Pool—Dump and Load

InnoDB Buffer Pool Dump and Load Enhancements (WL#6504). This work improves both the dump and load scenarios. It is now possible to dump only the hottest N% of the pages from each buffer pool. The load operation is also made less disruptive to user activity because the load now happens in the background while continuing to serve clients; while also attempting not to be too aggressive and taking too much IO capacity away from servicing new client requests.

Tools

innochecksum.  Improve Innochecksum tool (WL#6045). This work significantly extends the innochecksum utility’s functionality. It is now possible to specify the checksum algorithm (innodb/crc32/none), rewrite the current checksum using the specified algorithm, rewrite the checksum even if the current checksum is invalid, and specify the maximum checksum mismatch allowed before terminating the program. Innochecksum can also now operate on multiple tablespace files and on multiple files in the same tablespace. See Anil Toshniwal’s article “Improving Innochecksum“.

mysql_upgrade. Refactor mysql_upgrade tool (WL#7308). This work is a rewrite of the mysql_upgrade tool, which fixes many reported bugs while also making mysql_upgrade more robust and easier to maintain. For example, this work fixes Bug#65288 reported by Nicholas Bamber and Bug#71579 reported by Florian Weimer.

mysqlbinlog. Adding SSL support for the mysqlbinlog tool (WL#7198). This work adds SSL options and support for the mysqlbinlog client program, allowing system administrators to perform remote binlog queries (--read-from-remote-server) over secure connections. This was previously the last remaining MySQL client program without SSL support.

mysql_secure_installation. Convert mysql_secure_installation script to C/C++ (WL#6441). This program can now connect to the server directly and execute the specified commands using the C API (libmysql). This removes the need for storing the user supplied password in a temporary option file on the filesystem.

mysql_install_db. Convert the mysql_install_db script to C/C++ (WL#7688). This work makes the program usable on all platforms—particularly on Windows—while also redesigning the program to provide a better user experience, cover more functionality, and improve security.

Community Contributions

Statement Timeouts. Implementation of server-side statement timeouts (WL#6936). This work is based on a contribution submitted by Davi Arnaut (Bug#68252). The work implements a server-side time limit for the execution of top-level read-only SELECT statements. After the specified amount of time, the statement will be aborted without affecting the session (connection) or the transaction contexts. See Praveen Hulakund’s article “Server-side SELECT statement timeouts“.

Multiple User Level Locks. Allow multiple locks in GET_LOCK() (WL#1159). User-level locks are often used to organize mutual exclusion when accessing some resource in cases when table or row-level locks are not appropriate. This work allows for multiple user level locks per connection. The work is based on a contribution by Konstantin Osipov, see Bug#67806.

Triggers

BEFORE Triggers Are Not Processed for NOT NULL Columns (WL#6030). This work ensures that we check column constraints at the end of the SQL statement. This is in compliance with the SQL standard. In 5.6 and earlier, MySQL checks the column constraints too soon. See Bug#6295 reported by Peter Gulutzan, and Dmitry Shulga’s article “BEFORE triggers and NOT NULL columns in MySQL“.

Multiple Triggers Per Table (WL#3253). This work provides the ability to have more than one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). This is in accordance with the SQL standard. See Dmitry Shulga’s article “Support for multiple triggers per table for the same value of action/timing“.

IGNORE Clause

Define and Reimplement IGNORE (WL#6614). This work properly defines the meaning and handling of the IGNORE clause, a MySQL extension to the SQL standard. It reimplements IGNORE so that it is consistent across all supported SQL statements while also making it much easier to maintain. See also Bug#30191, Bug#49539, Bug#55421, Bug#54543, Bug#54106, Bug#49534, Bug#47788, Bug#46539, and Bug#46425.

STRICT Mode

Define and Reimplement STRICT Mode (WL#6891). We have made STRICT MODE behaviour consistent across all supported SQL statements. We have also made STRICT MODE the default for all transactional storage engines (WL#7764). The IGNORE clause downgrades errors to warnings to allow statements to skip row(s) which would have otherwise have caused the entire statement to abort. STRICT MODE does just the opposite—it upgrades warnings to errors. Similar to IGNORE, STRICT MODE has not previously been clearly and consistently defined, thus the implementation has been the source of many bugs like Bug#42910, Bug#5929, Bug#43880, Bug#48637, Bug#5912, and Bug#5913.

MySQL Client

Log Interactive Commands to Syslog (WL#6788). This work introduces a new client option, --syslog, which enables/disables the logging of attributes like sudo_user (or user), mysql_user, connection_id, db_server, DB, and query entered in an interactive session. This has been requested by some users due to auditing compliance requirements, i.e. requirements to log all interactive commands entered at the mysql command-line prompt to syslog.

Client Side Protocol Tracing (WL#6226). This work creates hooks inside the client library code which allows tracing of protocol events such as sending packets to the server, receiving server replies, and authentication handshakes. This provides a mechanism for collecting performance data about the client-server connections, from the client’s perspective.

Client-Server Protocol

Extending the Protocol’s OK Packet (WL#4797). This work extends the client-server protocol’s OK packet to allow the server to send additional information, e.g. server state changes. By default the server now sends information about the effect of SET character_set and USE database commands. This avoids situations like, for example,  after SET NAMES big5 the server assumes that the client will send big5 encoded data, while the client character set is still using latin1.

Flag for Session-Specific State (WL#6885). This work makes it possible for the client to tell the server that it wants notification about session state changes. One possible usage for this feature is to be able to detect if it is possible to migrate a user session context to another physical connection within a load balanced or clustered environment.

InnoDB Spatial Indexes

We have implemented spatial indexing within InnoDB. InnoDB spatial indexes can be used with all existing syntax that has been developed for MyISAM spatial indexes. In addition, InnoDB spatial indexes supports full transactional properties, as well as isolation levels. It employs predicate locks to prevent phantom scenarios. See Jimmy Yang’s article “InnoDB Spatial Indexes in 5.7.4 LAB release“.

GEOMETRY Datatype Support (WL#6455). This work adds a new InnoDB internal datatype called DATA_GEOMETRY, and maps all MySQL GEOMETRY datatypes to this new internal datatype.

R-tree Index Support (WL#6968). This work implements R-tree search and key positioning. The R-tree search and traversal is different from that of B-tree in the sense that a search criteria could be met in multiple leaf pages of different search paths. A querying bounding box can intersect or contain multiple leaf and non-leaf bounding boxes.

Support DML Operations for InnoDB R-tree Indexes (WL#6745). This work adds R-tree index support in InnoDB on the Geometry datatype (R-tree split and shrink operations). This work adopts all of MyISAM’s Minimum Bounding Box (MBB/MBR) manipulation functions and supports 2 dimension GIS datatypes, just as MyISAM does.

Support Predicate Locking for Spatial Indexes (WL#6609). This work implements predicate locks to enforce consistent reads for spatial indexes.

Store the GIS POINT Datatype as a Fixed Length Column Rather Than as a BLOB (WL#6942). This is an optimization for POINTs, a commonly used GIS datatype.

Optimizer GIS

Now that InnoDB is the default storage engine for MySQL, our user base is rapidly transitioning to InnoDB. One capability that they have been demanding is a performant and scalable GIS implementation. Along with adding spatial (R-tree) index support to InnoDB, we also decided to replace the original GIS algorithms with a more powerful, reliable, effective and efficient geometric engine. After many comparisons of the available open source geometry engines on multiple aspects, Boost.Geometry was finally chosen. See also Manyi Lu’s article “Why Boost.Geometry in MySQL?“, David Zhao’s article “Making Use of Boost Geometry in MySQL GIS“, and Matt Lord’s article “MySQL 5.7 and GIS, an Example“.

Spatial Relation Check Functions (WL#7220). This work implements MySQL GIS relation checking functions using Boost.Geometry. Many type combinations which were not previously supported are now supported, e.g. for functions like within, intersects, equals, disjoint, cross, and overlaps.

Geometry Set Operations (WL#7221). This work implements MySQL GIS geometry set operations using Boost.Geometry. Many type combinations which were previously not supported are now supported, e.g. for set operations like intersection, union, and difference.

Spatial Analysis Functions (WL#7236). This work refactors spatial analysis functions including area(), centroid(), convexhull(), distance() and envelope() using Boost.Geometry functions. The semantics of these functions are found in OGC standard specifications.

WKB Geometry Container (WL#7280). This work implements WKB containers that conform to the Boost.Range concept so as to be used as an effective adapter between existing WKB geometry data and Boost.Geometry algorithms. The aim is to avoid conversions between WKB enconded byte strings and Boost.Geometry objects, since such conversions can be expensive.

Geohash Encoding and Decoding Functions (WL#7928). This work adds functions to encode and decode geohashes. Geohash is a system for encoding latitude and longitude coordinates of arbitrary precision into a text string. These added MySQL functions will make it possible for applications to import and export data from MySQL using the Geohash format. It will also make it possible to index and search for geographic data in one-dimensional (e.g. B-tree) indexes.

GeoJSON Support for GIS (WL#7444). This work by adds functions for parsing and generating GeoJSON documents into GIS data types: ST_AsGeoJSON and ST_GeomFromGeoJSON. GeoJSON is an open standard for encoding geometric/geographical features. GeoJSON supports the same geometric/geographic datatypes that are already supported by MySQL. GeoJSON also includes the possibility to declare which coordinate reference system (CRS) is used (WKT and WKB lack this).

Replication

Non-Blocking SHOW SLAVE STATUS (WL#6402). This work adds a non-blocking option to SHOW SLAVE STATUS. The new option makes SHOW SLAVE STATUS non-blocking when run in parallel with STOP SLAVE (stopping the slave may take a long time to complete when it is waiting for an SQL thread to finish applying large events).

Add Idempotent Mode to mysqlbinlog (WL#6403). This work provides an idempotent mode of operation for the mysql server. In this mode the server will ignore errors while applying ROW based events from a binlog file. This mode is useful when a DBA wants to replay binlogs using mysqlbinlog, but against a MySQL server which may not contain all of the data, so suppressing duplicate-key and no-key-found errors can be very useful.

Add a Rewrite-DB Option to mysqlbinlog for ROW Events (WL#6404). This work adds support for a rewrite-db option to mysqlbinlog, so that a ROW based events can be applied to a different database/schema. I.E. replace “from_db” to ‘to_db’ when reading a ROW based event from the binlog.

Binlog_sender: Do Not Reallocate (WL#7299). This work implements an optimization on the dump thread that removes unnecessary reallocation of the send buffer. The user visible effect is that CPU usage will be lessened for each dump thread that the master spawns. See also Bug#31932 reported by Mark Callaghan.

Move Status Variables to Replication Performance Schema Tables (WL#7817). This work moves replication system variables to Performance Schema tables so that they can be monitored per-source and not simply as global variables. This work is a requirement for Multi-source Replication.

Make Master-Slave Syncing Option Independent of the Slave Threads (WL#7796). This work adds a new SQL function WAIT_FOR_EXECUTED_GTID_SET which makes the master-slave syncing option independent of the slave threads. If the slave thread is not running the WAIT_FOR_EXECUTED_GTID_SET(GTID_SET [, TIMEOUT]) function, then keep waiting until success (0) or timeout (1).

Optimize GTIDs for Passive Slaves — Store GTIDs in a Table (WL#6559). This work adds the option of storing GTIDs in a table instead of in the binary log. The use case may be a slave that is only used for read scale-out and is never going to become a master, thus it may not have any use for the transactions in the binary log, but it may have a use for the related GTID features (e.g. to initiate a fail over to another new master).

Waiting for More Transactions to Enter Binlog Group Commit (BGC) Queues (WL#7742). This work adds two new options to introduce an artificial delay to make the binary log group commit procedure pause. This increases the likelihood that more transactions are flushed and synced together to disk, thus reducing the overall time spent to commit a group of transactions (the bigger the groups the less number of sync operations). With the correct tuning, this can make the slave perform several times faster without compromising the master’s throughput.

Semi-Sync Replication

Make the Master Wait for More than One Slave to Acknowledge Back (WL#7169). This work implements an option to make the master wait for N slaves to acknowledge back, instead of just one, when semi-sync is enabled. Choosing to wait for N slaves (N > 1), adds resiliency to consecutive failures. It also improves transaction durability, as one transaction gets persisted in more than two servers before the results are externalized on the master.

Externalize Transactions Only after ACK is Received (WL#6355). This work allows for true lossless failovers when using semi-sync replication. If the master crashes, the slave is still ensured to be up to date. The implementation makes the master wait for the ACK after preparing within the storage engine and writing to the binary log, but before committing to the storage engine. See also Libing Song’s article “Loss-less Semi-Synchronous Replication on MySQL 5.7.2“.

Semi-Sync — Separate ACKs Collector (WL#6630). This work reduces semi-sync delays by using separate threads to send and receive semi-sync acknowledgements. So event and ACK streams can now be sent and received simultaneously.

Multi-Threaded Slaves (MTS)

Intra-Schema Parallel Slave Execution (WL#6314). This work implements intra-schema multi-threaded slaves. With this implementation the slave will be able to apply transactions in parallel, even within a single database or schema, as long as they have a disjoint read and write set. See also Rohit’s article “MySQL 5.7 Enhanced MTS: configuring slave for Intra-database parallelization“.

Ordered Commits (Sequential Consistency) (WL#6813). This work ensures that the commits by slave applier threads running in parallel will be done in the same order as they were on the master. This also means that the slave will never externalize a database state which was never externalized by the master. This is a requirement when the applications reading from the slave must observe the same set of states that existed on the master due to some application enforced constraint. This has become a necessity after WL#6314, which enables multiple transactions to be executed in parallel by the slave threads, some of which may be modifying a single database.

Support Slave Transaction Retries in Multi-Threaded Slave Mode (WL#6964). This work enables Multi-Threaded Slave (MTS) replication slave servers to retry a transaction after a temporary failure. Before this, only non-MTS replication slave servers attempted to retry a transaction after a temporary failure. See Bug#68465 reported by Yoshinori Matsunobu.

Other Improvements

DTrace Support (WL#7894). We have added full DTrace support to MySQL in the Server 5.6+ packages we ship for Oracle Linux 6+.

Update_time for InnoDB Tables (WL#6658). This work implements in-memory maintenance of update_time for InnoDB tables. This functionality was previously missing in InnoDB and people have been asking for it, see Bug#2681 reported by Phil Sladen.

TRUNCATE TABLE Statement Becomes Atomic (WL#6501) This work makes the internal InnoDB TRUNCATE TABLE statement atomic by reinitializing the original table-space header with the same space id and then physically truncating its .ibd file during the truncation of a single table tablespace.

Proper Connection ID Handling (WL#7293). This work avoids the attempted reuse of any connection IDs that are still being used. See also Bug#44167 reported by Jan Kneschke.

Error Reporting — Stacked Diagnostic Areas (WL#6406). This work implements support for stacked diagnostic areas as defined by the SQL standard. The GET DIAGNOSTICS statement has been extended to support GET [STACKED] DIAGNOSTICS.

Error Reporting — Most Statements Should Clear the Diagnostic Area (WL#5928). This work makes MySQL follow the SQL standard with respect to clearing the diagnostic area. This work fixes bugs like Bug#35296, Bug#43012, and Bug#49634.

Error Logging — Allow Control of Verbosity (WL#6661, WL#6755). This work gives the DBA control of how verbose the MySQL Server should be when writing to the error log (ERROR/WARNING/NOTE). This work also changes the timestamp format printed to a more standard format (UTC timestamp) and converts the existing fprintf(stderr, …) in the server layer to use the new internal error logging API.

Add Native Support for Syslog on Unixoid Platforms (WL#7793). This work adds full support for logging server output to the system’s native syslog facility. See also Bug#55370 reported by Kyle Joiner and later enhanced by Simon Mudd and Mark Alff.

GB 18030 Chinese Character Set Support (WL#4024). This work adds the MySQL character set gb18030 which supports the China National Standard GB 18030 character set.

That’s it for now. Please stay tuned for the next 5.7 milestone release, and thank you for using MySQL!

GeoJSON Functions

In recent years, GeoJSON has become a popular data format for exchanging GIS data due to several factors. The primary factors being that it’s easy to read, and it’s simple and lightweight. In 5.7.5, we added support for parsing and generating GeoJSON documents via two new functions: ST_GeomFromGeoJson() and ST_AsGeoJson(). These functions makes it easy to connect MySQL with other GeoJSON enabled software and services, such as the Google Maps Javascript API.

Since GeoJSON is a JSON format, we needed a library to parse and write JSON documents. After evaluating several candidates, we ended up with rapidjson due to its features, speed, and compatible license.

The new functions support all of the geometry types specified in the GeoJSON specification, as well as collections. The parsing function also extracts the geometry information from feature objects, as shown below:

When creating GeoJSON documents from spatial data, you have the possibility to specify a maximum number of decimal digits in the output. If GeoJSON output length is a concern for you, then this is a simple and efficient way to reduce the output length if your geometry contains a lot of coordinates with many decimals.

You can even add a URN in the OGC namespace to the GeoJSON output. The function uses the SRID from the geometry input, and outputs a short or long format OGC URN depending the input parameters (note that the output below is formatted for readability):

URNs in the OGC namespace are also recognized by the parsing function, and are stored in the geometry output from the function.

What about Importing GeoJSON with 3D Geometries?

You might think that your lovely collection of GeoJSON documents with 3D geometries are useless with these functions, and that they can’t be imported, but this is exactly what the options argument in ST_GeomFromGeoJson is for. By default, the parsing function will refuse to parse documents with 3D geometries. We do, however, realize that this would reject a lot of existing documents out there, so setting the options argument to 2, 3, or 4 will allow you to import these documents:

Option values of 2, 3, and 4 all have the same effect for now, but in the future when 3D geometries are supported, they will produce different results. We recommend using option 3, since this will mean that documents with 3D geometries will be rejected when 3D is introduced in MySQL. This will ensure that the change of behavior won’t happen silently and that you can take the appropriate action in your applications.

We look forward to your feedback on the new Geohash and GeoJSON features! Please let us know if you have any comments or if you run into any bugs. Thank you for using MySQL!

Geohash Functions

In MySQL 5.7.5, we introduced new functions for encoding and decoding Geohash data. Geohash is a system for encoding and decoding longitude and latitude coordinates in the WGS 84 coordinate system, into a text string. In this blog post we will take a brief look at a simple example to explain just how geohash works.

Where on earth is “u5r2vty0″?

Imagine you get a email from your friend, telling you that there is free food at “u5r2vty0″. But where on earth is “u5r2vty0″?

The first step in converting from “u5r2vty0″ to latitude and longitude data, is decoding the text string into its binary representation. Geohash uses base32 characters, and you can find the character mapping on several online resources. Anyway, the decoded text string is as follows:

“u5r2vty0″ = 11010 00101 10111 00010 11011 11001 11110 00000

The binary digits are then used to repeatedly divide the earth in two, and thus increasing the accuracy with each digit used. Without using any digits, all we know is that the location is somewhere in the range [-180, 180] longitude and [-90, 90] latitude, which is anywhere on the earth! This won’t help you too much in finding the food. Note that latitude 0 is the IERS Reference Meridian, and longitude 0 is equator.

The first digit will halve the longitude, where ‘0’ means discarding the right half and ‘1’ means discarding the left half. In this example, we discard the left half since the first digit is a ‘1’. Now we know that the food is located somewhere in the range [0, 180] longitude and [-90, 90] latitude, and thus you know that you should not be heading towards the Americas.

geohash_first_digit

The next digit halves the latitude, using the same rules. Since the second digit also is a ‘1’, we’ll discard the lower half, and now we are in the range [0, 180] longitude and [0, 90] latitude. We still have few digits left before we know where the free food is, but as you can see the accuracy increases dramatically with the first two digits.

geohash_second_digit

The third digit halves the longitude again, and now you’ll probably get the idea on how decoding a geohash string works. Do you manage to find out where the free food is?

geohash_third_digit

Properties and uses of Geohash

  1. Since a coordinate tuple is represented as a string, it is possible to index and search for geographic data in one-dimensional indexes (B-tree for example).
  2. As you might have figured out from the example, you can see that two geohash strings with the same prefix will be close to each other. This makes proximity searches easy, but remember that the opposite also can be true in some cases; two geohash strings with different prefixes can be located right next to each other. Imagine two locations that are on each side of the IERS Reference Meridian, but only a few meters apart. One geohash string will begin with a ‘0’, and the other will begin with a ‘1’.
  3. The accuracy of the geohash string depends on the number of characters. The more characters you have, the more accurate position you get. Thus you can remove characters from the end to gradually reduce its size and accuracy as required.
  4. It has been proposed to be used for geotagging.
  5. Since similar locations (often) share similar prefixes, it’s simple to spread out geohashes on different partitions, where similar locations are located on the same partitions.

Improvements to STRICT MODE in MySQL

As a part of improving the error handling, in MySQL 5.7.5 we have re-implemented STRICT sql mode.

STRICT mode in MySQL affects the errors that arise from invalid, missing, or out of range values in DML statements such as INSERT, UPDATE, and DELETE. The new implementation aims to make the behavior of STRICT mode more consistent, yet maintain backward compatibility as much as possible.

In MySQL 5.7.5, we have made three general improvements to STRICT mode behavior:

 1. STRICT mode got simpler

It was observed that having a large number of sql modes dependent on STRICT mode creates confusion among users. Specifically, we are talking about NO_ZERO_DATE, NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO modes. You can se further details on the above sql modes here. These modes only took effect if STRICT mode was also enabled, otherwise they produced warnings.

In MySQL 5.6 we deprecated these three modes and in 5.7.4 we have made their functionality part of STRICT mode itself. Basically the effects of enabling these three modes have been folded into STRICT mode. However, these modes are still retained due to upgrade issues and for backward compatibility. These modes will be completely removed in 5.8. Setting these modes will have no effect from 5.7.4 onwards. More details can be found here: WL#7467. Also you can check the documentation for this task here.

2. STRICT mode got better

It was also observed that the implementation of STRICT mode was not very good, and we got many related bug reports. For example, see Bug #42910 (triggers override strict sql_mode).

The behavior of Stored Routines with respect to STRICT mode was not consistent either. Sometimes, they were allowed to perform actions which are otherwise prohibited in STRICT mode.

The problem was that there was no central place in the code where the statements and errors affected by STRICT mode were handled. The code had the abort_on_warning flag which was switched off and on at various places across the codebase, making the implementation hard to understand and error prone.

In MySQL 5.7.4, we have re-implemented STRICT mode. The usage of the abort_on_warning flag is completely removed. Now, for each statement affected by STRICT mode, we push an error handler which is active during execution of the statement, and pop it when the statement execution is finished. There is also now a central place in the code where all errors that are generated during statement execution, and which are affected by STRICT mode, are handled. For additional information, you can look here: WL#6891.

Following is the list of errors affected by STRICT mode:

  • ER_TRUNCATED_WRONG_VALUE –  “Truncated incorrect value”
  • ER_WRONG_VALUE_FOR_TYPE –  “Incorrect value for function”
  • ER_WARN_DATA_OUT_OF_RANGE –  “Out of range value”
  • ER_TRUNCATED_WRONG_VALUE_FOR_FIELD –  “Incorrect value for column at row”
  • WARN_DATA_TRUNCATED –  “Data truncated for column”
  • ER_DATA_TOO_LONG –  “Data too long for column”
  • ER_BAD_NULL_ERROR –  “Column cannot be null”
  • ER_DIVISION_BY_ZERO –  “Division by 0″
  • ER_NO_DEFAULT_FOR_FIELD –  “Field doesn’t have a default value”
  • ER_NO_DEFAULT_FOR_VIEW_FIELD –  “Field of view underlying table doesn’t have a default value”
  • ER_CUT_VALUE_GROUP_CONCAT –  “Row was cut by GROUP_CONCAT()”
  • ER_DATETIME_FUNCTION_OVERFLOW –  “Datetime function field overflow”
  • ER_WARN_NULL_TO_NOTNULL –  “Column set to default value: NULL supplied to NOT NULL column”
  • ER_WARN_TOO_FEW_RECORDS –  “Row doesn’t contain data for all columns”
  • ER_TOO_LONG_KEY –  “Specified key was too long”
  • ER_WRONG_ARGUMENTS –  “Incorrect arguments”
  • ER_INVALID_ARGUMENT_FOR_LOGARITHM –  “Invalid argument for logarithm”

STRICT mode applies to the following types of DML statements:

  • INSERT
  • UPDATE
  • DELETE
  • LOAD DATA
  • ALTER TABLE
  • INSERT… SELECT
  • CREATE TABLE
  • CREATE INDEX
  • CREATE TABLE… SELECT
  • SELECT sleep()

 3. STRICT mode is the default

We have decided to add STRICT_TRANS_TABLES to the list of default sql modes in MySQL 5.7.5. (WL#7764).

Previously, if the user tried to insert 15 characters into a CHAR(10) column, then by default (STRICT Mode OFF) it would insert the first 10 characters, present the user with a warning, and then throw away the remaining five characters. Now the default behaviour (STRICT Mode ON) will be that the above statement will be rejected with an error.

Lots of changes have been made to existing test cases so that they are run with the new default SQL MODE. Around 500 mtr testcases were updated as part of this work.

WL#7467, WL#6891, WL#7764 were designed and implemented by Raghav Kapoor.

Improvements to the MySQL `IGNORE` Implementation

In 5.7.5, as a part of the larger effort to improve error handling, we re-implemented the IGNORE clause (WL#6614). The IGNORE clause is a MySQL extension to the SQL standard. It affects the errors which occur for each row. The new implementation aims to make the behavior of the IGNORE clause more consistent.

Statements which support the IGNORE clause are:

  • INSERT [ IGNORE ]
  • UPDATE [ IGNORE ]
  • DELETE [ IGNORE ]
  • LOAD DATA [ IGNORE ]
  • LOAD XML [ IGNORE ]
  • CREATE TABLE… [ IGNORE ] SELECT

When the INSERT statement is used to insert a number of rows into a table, an exception during processing would normally abort the statement and return an error message. With the IGNORE keyword, rows that cause certain exceptions are ignored, but the remaining rows are inserted and the execution of the statement is regarded as successful. The same principle applies to UPDATE, DELETE, LOAD, and CREATE…SELECT statements (the IGNORE keyword only affects the DML part of a CREATE…SELECT statement).

The IGNORE clause has two basic functions which we’ll now describe.

Independent of ‘STRICT’ mode

The IGNORE clause downgrades errors to warnings and continues the processing of a statement. The IGNORE keyword affects the following error codes:

  • ER_SUBQUERY_NO_1_ROW : Subquery returned more than 1 row when one is expected
  • ER_ROW_IS_REFERENCED_2 : Foreign key constraint violation in parent table
  • ER_NO_REFERENCED_ROW_2 : Foreign key constraint violation in child table
  • ER_BAD_NULL_ERROR : NOT NULL constraint violation
  • ER_DUP_ENTRY : Unique key constraint violation
  • ER_DUP_ENTRY_WITH_KEY_NAME : Unique key constraint violation
  • ER_DUP_KEY : Unique key constraint violation
  • ER_VIEW_CHECK_FAILED : CHECK constraint for view fails
  • ER_NO_PARTITION_FOR_GIVEN_VALUE : No partition in the table for the given value
  • ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT : No partition in the table for the existing value
  • ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET : Row not found matching the given

The row which caused the error is then skipped and the operation continues with the next row. One warning is output for each ignored exception. If two rows violate a UNIQUE constraint, we will get 2 warnings. We do not allow constraints to be broken inside the database.

A Practical Use Case for IGNORE

We have a table ‘t’ created with the following statement:
CREATE TABLE t(a INT) ENGINE = InnoDB;

Let’s look at an example of 3 rows with 1 duplicate among them.

We can not add a UNIQUE constraint directly here, but there is a workaround using the IGNORE clause (when adding a UNIQUE key, we will lose the data found in duplicate rows).

In the case of 3 rows there is little reason to use IGNORE, but if the number of rows is very large IGNORE can be of much help. This simply serves as a simple example.

When ‘STRICT’ Mode is ON

STRICT mode controls how MySQL handles invalid or missing values in data changing statements such as INSERT or UPDATE. With STRICT mode ON, MySQL produces an error for invalid values and aborts the statement. Using the IGNORE keyword in a statement cancels the effects of STRICT mode for that statement. The following error codes are not upgraded from warnings to errors by STRICT mode when the IGNORE clause is used:

  • ER_TRUNCATED_WRONG_VALUE : Truncated incorrect value
  • ER_WRONG_VALUE_FOR_TYPE : Incorrect value for function
  • ER_WARN_DATA_OUT_OF_RANGE : Out of range value
  • ER_DIVISION_BY_ZERO : Division by 0″
  • ER_TRUNCATED_WRONG_VALUE_FOR_FIELD : Incorrect value for column at row
  • WARN_DATA_TRUNCATED : Data truncated for column
  • ER_DATA_TOO_LONG : Data too long for column
  • ER_BAD_NULL_ERROR : Column cannot be null
  • ER_NO_DEFAULT_FOR_FIELD : Field doesn’t have a default value”
  • ER_NO_DEFAULT_FOR_VIEW_FIELD : Field of view underlying table doesn’t have a default value
  • ER_CUT_VALUE_GROUP_CONCAT : Row was cut by GROUP_CONCAT()
  • ER_DATETIME_FUNCTION_OVERFLOW : Datetime function field overflow
  • ER_WARN_TOO_FEW_RECORDS : Row doesn’t contain data for all columns
  • ER_WARN_NULL_TO_NOTNULL : Column set to default value: NULL supplied to NOT NULL column”
  • ER_INVALID_ARGUMENT_FOR_LOGARITHM : Invalid argument for logarithm
  • ER_WRONG_ARGUMENTS : Incorrect arguments

In these cases MySQL inserts the adjusted values for invalid or missing values and produces warnings.

An Example Use Case

Bugs Fixed by This Worklog

  1. Bug#6196: INSERT IGNORE should return warnings
    For the error codes mentioned in case (a) above, there were no warnings before this worklog when a constraint would fail. For example, INSERT IGNORE will silently ignore the duplicate values.
  2. Bug#43895: Multi-DELETE IGNORE does not report warnings
    Same case as mentioned in #1 above.
  3. Bug#68726: Update trigger invoked when update ignore means that no update is performed
    When IGNORE is used with an UPDATE statement, for all supported error codes there will be no error and the statement will be marked as having successfully executed. For the rows which did not get updated, however, the AFTER UPDATE trigger will not be executed.