MySQL 5.7 DMR5 Release Notes - what we know so far

The release notes for MySQL 5.7 DMR5 are starting to take shape on dev.mysql.com.

For me, what is one of the most exciting changes is that "Strict SQL mode is now enabled by default for transactional storage engines". This aligns very nicely with a post I wrote just recently about transitioning changes using initially a whitelist and then a blacklist.

User's with legacy applications still be able to opt-out of STRICT mode, but importantly new applications won't be accidentally trapped into sub-optimal defaults.

Announcing Planet MySQL: Meta

A couple of months back, I wrote that we were looking to improve the quality of Planet MySQL. Today, I am very excited to share the results of this with the announcement of Planet MySQL: Meta.

What is Meta?

Meta is a new category of posts that appear on Planet MySQL, which is intended for posts that are more social and less technical in nature. That is to say that instead of all posts appearing in one central feed, blog authors will now be able to target their posts for either Planet MySQL or Planet MySQL: Meta.

Readers will also have a choice as to which categories of posts they would like to subscribe to:

The +more above denotes that as well as reducing noise surrounding the technical posts, we also recognizing that there is a social aspect to being part of the MySQL Community. We are encouraging new content to Meta that authors may have not posted before out of fear it was too spammy.

Note for authors: Existing blogs will stay defaulted to Planet MySQL, but for more details on what content belongs where, please see our FAQ.

How can I subscribe to meta?

If you are reading via Planet MySQL, ensure that the tab "Planet MySQL: Meta" is selected. For RSS feed subscriptions, we offer both Planet MySQL and Planet MySQL: Meta.

How can I unsubscribe from meta?

If you are reading via Planet MySQL, ensure that the tab "Planet MySQL" is selected. For RSS feed subscriptions, we offer both Planet MySQL and Planet MySQL: Meta.

How can I continue as things were?

We will continue to offer the option to subscribe to both categories, in a very similar way to the way that Planet MySQL previously operated.

  • Morgan

Proposed changes to user management in MySQL 5.7

In May we proposed deprecating and removing the old password format in MySQL 5.7. I am happy to report, that this proposal has gone ahead, and can already be seen in the 5.7 DMR5 release notes!

With old passwords removed, and with recent versions of MySQL also offering new options for authenticating to MySQL, today I wanted to write about three additional proposed changes that follow suit:

  1. Deprecate the PASSWORD() function

    As we already support multiple authentication methods (and may desire additional in the future), calling the PASSWORD() function to generate a password hash has a strange user experience.

    What I mean by this, is that PASSWORD() is not able to determine what hash format to generate without knowing the user's authentication method. i.e.

    mysql [localhost] {msandbox} (mysql) > select user,host,password,plugin from mysql.user;
    +------------+-----------+-------------------------------------------+-----------------------+
    | user       | host      | password                                  | plugin                |
    +------------+-----------+-------------------------------------------+-----------------------+
    | root       | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | mysql_native_password |
    | sha256user | localhost | $5$;Uq=HtOa2X:GRS$qKn9rc0xhh4rq3XKz | sha256_password       |
    +------------+-----------+-------------------------------------------+-----------------------+
    2 rows in set (0.00 sec)
    

    In current releases of MySQL there exists a variable called old_passwords which can hint at which plugin should be used. This creates a usability issue, since having a 'back door' influence the return value of a function is not very intuitive:

    mysql> select password('test'); /* mysql_native_password */
    +-------------------------------------------+
    | password('test')                          |
    +-------------------------------------------+
    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> set old_passwords=1; /* old passwords */
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select password('test');
    +------------------+
    | password('test') |
    +------------------+
    | 378b243e220ca493 |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> set old_passwords=2; /* sha256_password */
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select password('test');
    +----------------------------------------------------------------------+
    | password('test')                                                     |
    +----------------------------------------------------------------------+
    | $5$HN
    wN_=@;`|uzm//b$uxYgsCdA.Y/35HKXtb/DhHceH78G8QSjBA/GPfxVH70  |
    +----------------------------------------------------------------------+
    1 row in set (0.01 sec)
    
    

    A more consistent usage would be password(password_string, auth_plugin), but we believe we have a more intuitive solution than this (see #3) and are proposing to deprecate the use of the PASSWORD() function in MySQL 5.7.

  2. Deprecate the old_passwords variable

    The old_passwords variable was originally designed to be a boolean, and offer a way to restore old password hash generation. Starting with MySQL 5.6, this has been repurposed to have 3 possible values:

    • 0 = mysql_native_password (4.1 and above)
    • 1 = mysql_old_password (legacy format, removal in 5.7)
    • 2 = sha256_password (5.6 and above)

    Since we will be removing support for what is called "the old password hash" in MySQL 5.7, this makes the name choice of this variable somewhat confusing. While we could rename the variable to auth_plugin (or similar), the variable won't actually be required, provided that the PASSWORD() function no longer requires a 'back door' variable as described above.

    We are proposing to deprecate the variable old_password in MySQL 5.7.

  3. Deprecate the syntax SET PASSWORD .. = PASSWORD()

    The last piece of this proposal is to change the syntax for setting a user's password
    to eliminate the use of the PASSWORD() function. The current syntax is:


    mysql> SET PASSWORD = PASSWORD('test');
    mysql> SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('test');

    We are proposing that this be changed to:


    mysql> SET PASSWORD = 'test'; /* raw password */
    mysql> SET PASSWORD FOR 'sha256user'@'localhost' = 'test'; /* raw password */

    With the new syntax, we will always know the user's account which we are operating on and consequently be able to apply the hashing function which applies to their authentication plugin.

    Further more, it will encourage users to reduce usage of copying password hashes from one account to another (although inserting/updating hashes in the raw mysql.user table will still be supported).

    We are aware that using password hashes is a way of obfuscating a password locally on the client so that it does not need to be sent over the wire, but we believe we have a superior solution to this in TLS.

We are seeking feedback from the community in regards to these proposed changes:

  • Do you agree that setting a variable before calling the PASSWORD() function causes a usability issue?
  • Would you agree that the syntax SET PASSWORD = 'test'; is more intuitive?
  • In the case of the SET PASSWORD syntax no longer accepting hashes but instead passwords, do you agree that it is reasonable to require users use TLS for safe password changes? An alternative to TLS is to make changes locally via unix socket or via named pipe.

Please leave a comment, or get in touch!

Suggestions for transitioning into Strict SQL-Mode

As MySQL 5.6 now enables the SQL Mode STRICT_TRANS_TABLES for new installations, I have been running into users who have been upgrading from previous versions, and not quite sure how to test their application for compatibility with this new default.

For some applications converting warnings to errors presents an unknown-unknown, in that the operators of the system can not easily detect what may break. Certainly many deployments will have QA environments, but there is always some fear production queries are just a little bit different.

Since sql_mode is configurable on a per-session basis, there are some strategies that I have been recommending to ease transition:

  • Whitelist: Have all new application components enable strict mode by default. For example, if you are building a set of cron jobs to rebuild caches of data - have these scripts set sql-mode strict as soon as they connect to MySQL, but initially leave existing applications without STRICT mode.
  • Blacklist: Modify existing application components that have not extensively been tested with strict mode to explicitly unset this SQL mode when connecting to MySQL. This is an important change, since all new components will then default to being strict.
  • Staged Rollout: Have the ability to turn on/off strict SQL mode on a per user-basis, with perhaps internal users or beta users being the first to have strict mode enabled for. This offers a more gradual transition where you can contain any errors to within a small number of users. This strategy was suggested by @geodbz

That's my list to date. I would love to hear if anyone has any suggestions on how to better manage the transition process!