SET GLOBAL sql_log_bin – We need your feedback!

Jeremy Cole recently blogged about the feature SET GLOBAL sql_log_bin. In his blog post, Jeremy suggested that there is no strong use-case for this feature, and that it is too easy to run accidentally (i.e. SET GLOBAL instead of SET [SESSION]). The result of this incorrect usage is that slaves will drift out of sync from masters.

We discussed Jeremy’s request in the MySQL team, and we agree that the syntax should produce an error in MySQL 5.7. However, we have not yet determined which steps should be taken for MySQL 5.5 and MySQL 5.6. The two proposals we would like to float are:

  1. The statement SET GLOBAL sql_log_bin=N produces a warning.
  2. The statement SET GLOBAL sql_log_bin=N produces an error.

Option #1 does not specifically solve Jeremy’s reported issue, because even though the command provides a warning, it will still execute. Thus, the data-drift from accidental usage will still occur; the operator will simply be aware of the problem sooner.

Option #2 does not follow our standard policy regarding functionality changes in GA releases. That is to say that we are discussing making a special exception since this behaviour is regarded as dangerous and almost certainly unintended.

We are seeking input from the community as to which is the better of these two options:

  • Which is your preference – option #1 or option #2?
  • Do you agree that this specific situation warrants a behaviour change in GA releases?

Please leave a comment, or get in touch!

MySQL 5.6.20 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.20.

In particular:

  • Thank you to Jeremy Cole for reporting a case where InnoDB’s redo log could become corrupt with externally stored blobs. Jeremy also provided a testcase to demonstrate the problem. Bug #69477.
  • Thank you to Ronald Bradford for identifying that MySQL would continue to operate even though the binary log could not be written. Our team picked up Ronald’s blog post, and filed a bug in response. Bug #51014.
  • Thank you to Daniël van Eeden, Andrew Garner, Detlef Ulherr and Bjoern Boschman for reporting that mysql_install_db would always create a configuration file in /usr. We have added a new option --keep-my-cnf to preserve any existing configuration files, and not create a new file in /usr. Bug #71600, Bug #68318, Bug #68117, Bug #68416.
  • Thank you to David Newgas for reporting that loading InnoDB full-text search as a plugin would fail. David also provided a suggested patch. Bug #70178.
  • Thank you to Inaam Rana for reporting that the fix for a previous bug added superfluous buf_flush_list() logic to InnoDB startup code. Bug #70899.
  • Thank you to Stewart Smith for reporting that some InnoDB internal locks did not account for memory ordering differences on non-intel platforms. While we had discovered this issue earlier in Bug #47213, Stewart’s report and sample patches helped considerably. Bug #72539, Bug #47213.
  • Thank you to Valeriy Kravchuk for reporting a case where queries on partitioned tables could return wrong results. Bug #71095.
  • Thank you to Domas Mituzas for reporting that mysqlbinlog’s --raw feature did not correctly handle errors caused by failed writes, leading to potential corruption. Bug #72597.
  • Thank you to Raolh Rao for reporting that updating or deleting a row on a master that did not exist on a slave can create problems using row-based replication. Bug #72085.
  • Thank you to Vlad Lesin for reporting that quotation marks were not always handled correctly by LOAD DATA INFILE when writing to the binary log. Bug #71603.
  • Thank you to Simon Mudd for reporting that manually specifying an AUTO_INCREMENT value could cause a replication error in certain conditions. Bug #70583.
  • Thank you to Julien Duponchelle for reporting that client applications could no longer set the BINLOG_DUMP_NON_BLOCK flag in the initial handshake to a MySQL 5.6 server. This functionality was removed by mistake and has now been restored. Bug #71178.
  • Thank you to Arthur O’Dwyer for reporting a wrong results bug when using MIN(), MAX() and an aggregate function. Bug #69833.
  • Thank you to Shlomi Noach and David Vaughan for reporting that a view defined on a UNION could create an invalid view definition. Bug #65388, Bug #72018.
  • Thank you to Lynn Garren for reporting that configuring with cmake -DWITHOUT_SERVER to build clients without the server failed for builds outside of the source tree. Bug #66000.
  • Thank you to Dan Kloke for reporting a case when a query using COUNT(DISTINCT) could return wrong results. Bug #52582.
  • Thank you to Laurynas Biveinis and Sergey Vojtovich for reporting a situation where a thread deadlock could occur. Bug #71236.
  • Thank you to Raghavendra Prabhu for reporting that MySQL did not compile with Bison 3. Bug #71250.
  • Thank you to Santosh Praneeth Banda for reporting that uninstalling and reinstalling semisynchronous replication plugins while semisynchronous replication was active caused replication failures. Bug #70391.
  • Thank you to Valeriy Kravchuk for reporting a situation when wrong results could be returned for GROUP BY queries. Bug #71097.
  • Thank you to Yoshinori Matsunobu for reporting that client auto-reconnect did not work for clients linked against libmysqlclient. Bug #70026.
  • Thank you to Daniël van Eeden for reporting that upgrades using RPM packages could change the ownership of an installation directory. Bug #71715.
  • Thank you to Valeriy Kravchuk for requesting that MySQL provide option to link against libCstd instead of stlport4 on Solaris. We agree, and have added this functionality. Bug #72352.
  • Thank you to Stewart Smith for reporting that various test-suite file permissions were not correct. Bug #71112, Bug #71113.

In addition, we would like to thank the MySQL Community for their feedback on our earlier proposal to deprecate mysqlhotcopy. Based on your feedback we have decided to officially deprecated mysqlhotcopy in 5.6.20, and it will be removed in MySQL 5.7.

Thank you again to all the community contributors listed above. If I missed a name here, please let me know!

– Morgan

The InnoDB Team is looking for your feedback!

Restarting production database servers with gigabytes of memory is difficult. It can lead to cold caches and other operational complexities.

The InnoDB team is looking to make improvements so that restarting MySQL is required as little as possible. Please help them out by answering which configuration settings you are most eager to see made dynamic.


Beyond the FRM: ideas for a native MySQL Data Dictionary

The frm file has provided long service since the earliest days of MySQL. Now, it is time to replace it with a native InnoDB-based Data Dictionary.

This is a change that has been on our wish list for a long time, as well as others in the MySQL development community:

For historical context:

  • Every table in MySQL has at least a corresponding .frm file. For example in the case of MyISAM these three files constitute a complete backup:


  • The .frm file stores information such as column names and data-types. It is a binary format, which as Stewart explains predates MySQL and spans back to 1979 with UNIREG. In addition to the .frm file, there are .trn, .trg and .par files which have been added over time to support triggers and partitioning.

Our motivation to develop a native dictionary spans from a number of issues with the current filesystem-based formats:

  1. The current .frm file predates MySQL’s support for transactions, and has a lot of complexity to handle various failure states in replication and crash recovery. For example: Bug#69444. Using a native data dictionary simplifies code and makes handling failure states very simple.

  2. Our information_schema implementation currently suffers, and has been subject to years of complaints. By using a native dictionary, we will be able implement information_schema as views over real tables, significantly improving the speed of queries.

  3. On a closely related point, we currently build information_schema on top of a series of differing filesystem properties, while attempting to provide the same cross-platform experience. The code to account for filesystem case insensitivity increases code complexity, and ties up developer resources that could be better spent elsewhere.

  4. Aside from the MySQL server’s data dictionary, storage engines may also store their own data dictionary. In the case of the InnoDB storage engine, this redundant storage has led to complexity in troubleshooting an out-of-sync data dictionary.

  5. The current non-comformity of the data dictionary (using .frm, .par, .trn and .trg files) spans from a lack of extensibility from the original .frm format. Not having a centralized extensible repository makes it difficult to incorporate feature requests that require additional meta data stored, or to offer new relational objects in the future.

  6. The current format does not support versioning meta-data in such a way that we can use it to assist in the upgrade experience between MySQL versions.

This change is of course in addition to my recent post about storing system tables in InnoDB.

While this change will be transparent for many of our users, we are inviting feedback from the community. Please let us know:

  • If you have a use-case where you interact with the file-based formats directly.
  • What features you want to see in a native data dictionary!

You can either leave a comment, or email me.

MySQL 5.6.19 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.19.

In particular:

  • Thank you to Daniël van Eeden for reporting that the utility charset2html was unmaintained and obsolete. We followed Daniël’s suggestion and removed it from MySQL distributions. Bug #71897.
  • Thank you to Dario Kampkaspar for reporting that upgrading from 5.6.10 to a more recent 5.6 release would fail. Bug #72079.
  • Thank you to Fangxin Flou for reporting that InnoDB would call memset too often. Fangxin also provided a suggested patch to fix the issue. Bug #71014.
  • Thank you to Daniël van Eeden for reporting an assertion when enabling the innodb_table_monitor. Bug #69641.
  • Thank you to Domas Mituzas for reporting an off by one error in the handling of innodb_max_dirty_pages_pct. Thanks also to Valeriy Kravchuk for suggesting that the value be a float, and Mark Callaghan for providing a sample patch. Bug #62534.
  • Thank you to Guangpu Feng for reporting an issue where an aborted TRUNCATE TABLE command could still be written to the binary log. Guangpu also provided detailed code-analysis, which helped with our investigation. Bug #71070.
  • Thank you to Justin Swanhart for reporting an issue where the server did not always correctly handle file permissions on the auto.cnf file. Bug #70891.
  • Thank you to Frédéric Condette for reporting an issue when using replication with GTIDs and replicate-ignore-db. Bug #71376.
  • Thank you to Michael Gmelin for reporting compilation errors when building in C++11 mode. Michael also provided a patch with a suggested fix. Bug #66803.
  • Thank you to Hartmut Holzgraefe for reporting that CMake produced not-useful warnings. Bug #71089.
  • Thank you to John Curtusan for reporting that on Windows REPAIR TABLE and OPTIMIZE TABLE failed on MyISAM tables with .MYD files larger than 4GB. Bug #69683.
  • Thank you to Elena Stepanova for reporting an issue that could break row-based replication. Elena also managed to reduce the issue down to a simple test case. Bug #71179.
  • Thank you to Yu Hongyu for reporting that an UPDATE statement could fail to update all applicable rows. Bug #69684.
  • Thank you to Tom Lane for his comments on why the test “outfile_loaddata” could be failing. His suggestion proved to be correct, and helped tremendously in tracking down the root cause. Bug #46895.

In Addition:

Thank you again to all the community contributors listed above. If I missed a name here, please let me know!

Please also note:
There was no MySQL Community Server 5.6.18. That version number was used for an out-of-schedule release of the Enterprise Edition to address the OpenSSL “Heartbleed” issue. This issue did not affect the Community Edition because it uses yaSSL, not OpenSSL, so a new release of the Community Server was not needed, and 5.6.17 is followed by 5.6.19.

– Morgan

MySQL soon to store system tables in InnoDB

In the MySQL team, we are changing the system tables currently located in the mysql schema from MyISAM to InnoDB.

Looking at this historically:

  • MyISAM was the default storage engine up until MySQL 5.5.
  • In 5.5 almost 4 years ago, the default storage engine changed to InnoDB, however system tables used for features such as storing privileges and timezones remained as MyISAM.

Unlike MyISAM, InnoDB is an ACID compliant storage engine, with the behaviour that once a transaction commits, modifications are able to survive power-loss or other failures. This is a solid foundation to build applications on, since developers will need to handle fewer failures. To use an example:

  1. Customer places an order
  2. A confirmation email is sent
  3. Power is lost

Without durability, (2) could occur with no record of (1) occurring! Durability is a great feature. However, we do not currently offer this for the system tables which use MyISAM. To use an example:

  1. A DBA revokes a user’s privilege to MySQL (the command returns success)
  2. Power loss occurs
  3. Upon restore, the revoke never applied.

By switching to InnoDB we are improving the experience of system-related tasks by ensuring durability that ACID provides.

This change will have the effect that InnoDB will be required for all MySQL installations, and the configuration setting --skip-innodb will no longer make sense. Users will still be able to use the MyISAM storage engine, and MyISAM-heavy installations can continue to configure the InnoDB buffer pool to as low as 5MB – taking up very little memory.

This is a great step forward for MySQL, and I am personally very excited to see this change. Many in the MySQL community have been requesting this change for years, and we’re happy to now be working on it. If you have any thoughts, please leave a comment, or email me!

Proposal to deprecate COM_REFRESH packet

In the MySQL team we are proposing to deprecate the COM_REFRESH packet in favor of specific queries to execute FLUSH commands. To provide a bit of context:

  • The MySQL server protocol allows for clients to speak API commands via both a query and binary protocol interface. The set of the API commands can be seen in the MySQL Client/Server Protocol internals documentation, or very simply as they appear in a single switch statement:
    # ./sql/ (simplified view)
      switch (command) {
        /* do stuff */
      case COM_QUERY:
        /* parse query, do stuff */
      case COM_REFRESH:
       /* equivalent to running a FLUSH command */
      case COM_SHUTDOWN:
  • The historical advantage of having a binary protocol meant that less bytes needed to be transfered across the network as well as less processing on the server, since parsing of statements is not required.
  • The disadvantage of the binary protocol is that it is less extensible. In the case of COM_REFRESH it was designed to accept only a 1 byte payload to indicate which flush command to run. A quick look at the MySQL manual shows that there are now more than eight (1 byte payload = 8 possible flags) flush commands available. This leads to API inconsistency as some commands are available via the binary interface and others are not.

Our proposed solution to this problem is to deprecate the binary interface for refresh commands and promote usage of the query interface (FLUSH LOGS, FLUSH TABLES, FLUSH HOSTS etc) instead. While we have no immediate plans to remove the COM_REFRESH binary interface (and thus break older clients) we are seeking feedback on the direction of this change. Please leave a comment, or get in touch!

Proposal to deprecate the old password format

Improving security has been a high priority for MySQL 5.7. To give you an idea of some of the changes to date, I recommend reading Todd Farmer’s blog: Redefining –ssl option, Password expiration policy, AES256 encryption.

In MySQL 5.7, we are also proposing to remove the ‘old’ format for saving passwords. To provide some historical context:

  • The old password format is a 16-byte hash and considered to be cryptographically weak. Code appears online (I won’t link to it) which allows the hash to be brute-forced in a small-enough amount of time.
  • Ten years ago (2004), MySQL 4.1 introduced a stronger password hash format based on SHA1. The old password hash continued to be supported, since client libraries connecting to MySQL needed time to upgrade to support the new authentication scheme. A configuration setting of secure-auth=1 allowed a DBA to enforce that users could only authenticate using new password hashes, but it was not enabled by default. Another configuration setting (old-passwords=1) allowed passwords to still be generated in the weak format.
  • MySQL 5.6 (2013) enabled secure-auth=1 by default, preventing users from connecting to the server using old password hashes. However, it still permits a DBA to configure secure-auth=0 + old-passwords=1 allowing weak hashes to be generated and used. As an aside, 5.6 also introduced a new SHA256 password hash option.

This brings us to today’s discussion. We would like to propose that:

  • Setting secure-auth=0 + old-passwords=1 is officially deprecated as of MySQL 5.6. The MySQL manual pages will be updated to discourage against these settings, and warnings will be written to the server error log on startup when they are in use.
  • In a future version, support for setting secure-auth=0 + old-passwords=1 will be removed. Since our policy is to support each GA release for 8 years this means that using old-password hashing will already be supported via MySQL 5.6 until 2021. Extending support beyond this leaves us in a less secure position.

    We are proposing for removal in MySQL 5.7, but are open to suggestions as to what is an acceptable timeframe.

We value your feedback regarding this proposal:

  • Are you currently using secure-auth=0 or old-passwords=1?
  • Do you use a legacy connector that will not support the new (4.1+) authentication?
  • If you do not agree with removal in MySQL 5.7, which version would you consider more suitable?

Please leave a comment, or get in touch!

Calling for all mysql_install_db feature requests

mysql_install_db is the program that is responsible for bootstrapping a fresh copy of MySQL and making sure all of the system tables are setup correctly. This program is usually invoked by other programs as part of installation, but in the case of .tar.gz downloads will need to be executed manually.

As part of our ongoing efforts to refactor and improve MySQL, we are looking at converting mysql_install_db from its current Perl script to a program written in C++. The aim of this change is to reduce external dependencies (Perl + a set of modules) and improve our cross-platform experience.

We plan for this transition to be mostly function neutral, but are open to feature requests for additional improvements as well.

Have you worked in packaging MySQL?
Do you have any suggestions on how mysql_install_db can be improved?
Please leave a comment, or get in touch!