Proposal to change additional defaults in MySQL 5.7 (February Edition)

Following on from my two earlier posts, in the MySQL team we are proposing a new set of changes to defaults for MySQL 5.7:

Setting Old Default New Default
log_slow_admin_statements OFF ON
log_slow_slave_statements OFF ON
long-query-time 10 2
log-queries-not-using-indexes OFF ON
min-examined-row-limit 0 1000
MySQL Command Line Client - +show_warnings
group_concat_max_len 1024 1M
max_allowed_packet 4M 64M
symbolic-links - OFF
temp-pool 1 0
table_open_cache_instances 1 16

To explain these changes in more details:

  • The slow query log remains disabled by default, but when enabled our hope is that we can make it immediately useful. A query exceeding 2 seconds will now be considered slow, and since queries that do not use indexes have the highest chance of causing future problems, they will also be logged unless they examine fewer than 1000 rows. The number of 1000 was chosen because it should eliminate the majority of false positives from queries retrieving a static set of records such as navigation menu items, which are well suited to tablescans.

    In proposing this change, we considered the defaults that other products use for considering a query slow. A popular web performance tool uses a default of 0.5 seconds for an acceptable time to render a full page server side, with 2 seconds (4x) slow enough to warrant logging. Our own MySQL Enterprise Monitor considers 100ms acceptable, and 400ms (4x) as slow. We also considered that setting the min-examined-row-limit to a non zero value will now require an additional step for those that set their long-query-time to zero seconds. We would like to thank Daniel Black for suggesting we change our slow query log options.

  • The MySQL command line client has the ability to automatically print warnings out to screen as they occur. We feel like ON is the more useful default, as a user will typically not intend to execute statements that cause warnings. This behavior applies to both interactive and batch modes, and can be disabled with show_warnings=0. We would like to thank Ryuta Kamizono for this suggestion.
  • We find the GROUP_CONCAT() function in MySQL to be incredibly useful for summarizing results in an aggregate query. We also believe that the default maximum length for grouping values has not kept pace with the larger amounts of memory available on modern hardware. We would like to thank Shlomi Noach for this suggestion.
  • In MySQL 5.6 we increased the max_allowed_packet from 1M to a conservative 4M. We have received a lot of feedback on this change, and are proposing to increase the default to 64M for MySQL 5.7. We would like to thank Shlomi Noach for this suggestion.
  • Many of our configuration files for MySQL packages (as well as those that ship with Linux distributions) default to disabling symbolic links for security reasons. We feel that changing a de facto default to a compiled default will improve user experience (as well as security). We would like to thank Honza Horak for this suggestion.
  • The temp-pool option was originally created as a means to work around potential filesystem issues on Linux when using internal MyISAM based temp tables. The underlying OS issues have since been resolved, and disabling this option removes mutex contention for all disk based temp table engines (MyISAM and InnoDB).
  • We are proposing to increase the default table_open_cache_instances to 16 in order to reduce contention on internal mutexes when opening tables.

For those of you who would like to test out these changes (along with previous changes proposed), I have sample configuration files available:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Proposal to change additional defaults in MySQL 5.7

Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:

Setting Old Default New Default
binlog_error_action IGNORE_ERROR ABORT_SERVER
innodb_checksum_algorithm INNODB CRC32
innodb_page_cleaners 1 4
innodb_purge_threads 1 4
innodb_strict_mode OFF ON
innodb_log_file_size 48M 128M
innodb_buffer_pool_dump_at_shutdown OFF ON
innodb_buffer_pool_load_at_startup OFF ON
innodb_buffer_pool_dump_pct 100 25
innodb_file_format Antelope Barracuda
innodb_large_prefix OFF ON
binlog_gtid_recovery_simplified OFF ON
sync_binlog 0 1
slave_net_timeout 3600 60
P_S config Enable events_statements_history and events_transactions_history consumers by default
MySQL CLI Prompt mysql> prompt="\u@\h [\d] > "
sql-mode +NO_AUTO_CREATE_USER

To explain the motivation behind these changes:

  • The binlog_error_action variable was introduced in MySQL 5.6 after the GA release, and for compatibility defaulted to previous behavior (ignore errors). Since some applications depend on reading from slaves after writing to masters, we feel that the better behavior is to abort.
  • The InnoDB CRC32 checksum was introduced in MySQL 5.6, and offers CPU accelerated page checksum computation. Since previous versions of MySQL were unable to read the new checksum, enabling this feature would have restricted downgrades to dump-and-restore. With MySQL 5.6 supporting the CRC32 checksum, the timing now makes sense to make this the default in 5.7.
  • In some workloads the InnoDB purge and page cleaning operations will not be able to keep up with modifications made inside InnoDB. In combination with newer storage devices that offer very high throughput and accommodate parallel writes, we feel that having multiple threads is a better default.
  • Currently the InnoDB storage engine ignores CREATE TABLE options that it does not understand (innodb_strict_mode=0). We feel this negatively impacts user experience, and are proposing to enable InnoDB strict mode in advance. The downside of this change is that some DDL statements that worked in 5.6 failing by default in 5.7. Thank you to Daniël van Eeden for providing this suggestion.
  • Since the InnoDB buffer pool now supports online resizing (and the innodb log files do not), we believe it makes sense to increase the size of the log files slightly in anticipation of an increased buffer pool size. This comes at a small cost of additional disk space usage. We felt that 128M was a good balance in size, since while larger log files are better for performance, there is also a trade off point where the log files benefit from filesystem caches. Thank you to Justin Swanhart for providing this suggestion.
  • With the increased availability of large memory, waiting for caches to warm back up after restarts has been an increased operational concern. MySQL 5.6 introduced a feature to be able to dump buffer pool page addresses (at a small fraction of total size) to assist in warming caches after restarts. MySQL 5.7 introduces an additional feature to dump only a percentage of the buffer pool, and we felt that by limiting this to 25% was a good tradeoff to enable this by default. Thank you to Daniel Black and Daniël van Eeden for this suggestion.
  • The InnoDB Barracuda file format was first introduced in the InnoDB plugin for MySQL 5.1, and enables InnoDB to use the new DYNAMIC row format (with improved BLOB handling) and COMPRESSED tables. Setting the InnoDB file format to Barracuda depended on innodb-file-per-table=1 (default changed in MySQL 5.6), and introduced an incompatibility that would prevent downgrades to earlier releases. With both MySQL 5.5 and 5.6 supporting Barracuda, the timing now makes sense to enable this by default. Thank you to Daniël van Eeden for providing this suggestion.
  • The innodb_large_prefix prefix option was introduced for Barracuda tables as an option to support much longer index key prefixes (up to 3072 bytes). It was disabled by default so that users did not accidentally create Barracuda tables that would be incompatible on Antelope systems (which would not allow downgrading). With Barracuda becoming the default, and our friends at WordPress and Drupal wanting to create much larger indexes to support utf8mb4 encoded columns, we decided to change this to default to on.
  • The binlog_gtid_recovery_simplified option was released after MySQL 5.6 was released as GA, and defaulted to OFF for backwards compatibility. We feel that ON is the better default, and plan to make this change for MySQL 5.7.
  • With the addition of binary log group commit in MySQL 5.6, the impact from enabling sync_binlog=1 on many non-synthetic workloads should be reduced considerably. This change makes MySQL binary logs durable/crash safe by default.
  • The slave_net_timeout defaults to one hour, which is too long for most modern systems. In addition: MySQL 5.7 now uses this value divided by two to configure the default master heartbeat period. We believe that a one minute default is more appropriate. Thank you to Miguel Angel Nieto for providing this suggestion.
  • In MySQL 5.7, performance_schema can now instrument transactions, which can be incredibly useful when enabled by default. In addition, enabling statements history (introduced in 5.6) provides a lot of useful meta-data for diagnosing performance problems. Thank you to Daniël van Eeden for providing this suggestion.
  • The MySQL command line client has long supported the ability to change the default prompt from "mysql >" to include additional details such as the username and database selected. This is mostly a cosmetic change, but it is useful when you are managing multiple servers. Thank you to Daniël van Eeden for providing this suggestion.
  • In MySQL 5.7 we have already made changes to the default SQL mode: enabling ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. There is a long standing usability issue with user grants, where a typo in the username could result in a new user being created. We have decided that it is best to disable this behavior by enabling the NO_AUTO_CREATE_USER sql mode by default. Thank you to Daniël van Eeden for providing this suggestion.

In addition to these changes, we have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED. We will re-evaluate this decision for future releases.

For those wishing to test out these new configuration settings, I have configuration file available to try out:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Proposal to change Replication and InnoDB Settings in MySQL 5.7

In the MySQL Team, we are currently evaluating potential changes to the default server configuration for MySQL 5.7. For more context, please see my earlier post here.

One of the specific changes we would like to make is with the following three related replication and InnoDB settings:

Setting Old Default New Default
binlog_format STATEMENT ROW
transaction_isolation REPEATABLE-READ READ-COMMITTED
innodb_autoinc_lock_mode 1 ("consecutive" lock mode) 2 ("interleaved" lock mode)

We are considering these changes as a package, for the following reasons:

  • Enabling row based binary logging is the safer option for new applications. It allows all types and combinations of statements to be replicated safely (deterministically), and makes slaves more crash-resilient when using features such as temporary tables.

  • InnoDB requires row-based replication in order to provide the READ-COMMITTED isolation level. In making these two changes together, InnoDB performance is improved:

    • There is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition (link to the manual here).

    • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

      CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

      When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s." (link to the manual here)

    It is important to note that READ-COMMITTED represents a relaxed consistency over REPEATABLE-READ, but is also the default isolation level chosen by SQL Server, PostgreSQL and Oracle.

    For some additional context, Peter Zaitsev recently blogged about the advantages of READ-COMMITTED.

  • Setting innodb_autoinc_lock_mode to 2 also requires row based replication, and improves concurrency since the Auto Increment number no longer needs to be locked to provide a consecutive set of numbers for a bulk insert operation. The MySQL manual describes this new lock mode as "interleaved".

It is important to remember that these represent changes to the default configuration, and reflect what we believe is the best default choice for new applications. We will of course continue to fully support the previous configuration settings should a user prefer these.

With that being said, we are seeking feedback from the community: Do you agree that these represent the best set of defaults for new applications?

Please leave a comment, or get in touch!

Update Jan 23 2015: We have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED for MySQL 5.7. We will reevaluate this decision in the the future.

What defaults would you like to see changed in MySQL 5.7?

In MySQL 5.6, one of the most well received changes was improving the default configuration to be safer, and easier to use.

We are seeking community feedback for improvements that can be made to the default configuration in MySQL 5.7. Please leave a comment, or get in touch with me via email.

For elimination of confusion, please state both the setting you would like changed, and the new desired value.

Thanks!

MySQL 5.6.22 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.22.

In particular:

  • Thank you to Ramesh Sivaraman for reporting that setting the innodb_limit_optimistic_insert_debug debug configuration option to 1 caused an infinite B-tree page split. Ramesh also provided a minimal testcase to reproduce. Bug #74605.
  • Thank you to Jeremy Cole for reporting that InnoDB pages with a checksum value of zero were incorrectly treated as empty pages. Bug #73689.
  • Thank you to Zhai Weixiang for reporting that the MySQL server would fail to shutdown when started with innodb-force-recovery = 6. Bug #73341.
  • Thank you to Bart Butler for reporting that InnoDB would shutdown unclean on systems with lower_case_table_names=2 and containing tables using uppercase. Bug #72043.
  • Thank you to Laurynas Biveinis for reporting two assertions that could occur in InnoDB using debug builds. Bug #71343, Bug #68116.
  • Thank you to Stewart Smith for reporting that InnoDB was missing a memory barrier when setting up asynchronous I/O (AIO). Stewart was also kind enough to provide a patch addressing the issue. Bug #72809.
  • Thank you to Jean-François Gagné for reporting an out of memory situation when using row-based replication and having different data types on the slave. Jean-François also provided an example shell script, which saved us a lot of time in reproducing the issue. Bug #72610.
  • Thank you to Jean-François Gagné for reporting that relay log recovery did not correctly handle the case where Relay_Master_Log_File could be empty. Bug #73039.
  • Thank you to Santosh Praneeth Banda for reporting that replication masters did not correctly handle the case when slaves requested GTIDs which had already been purged. Bug #73032.
  • Thank you to Domas Mituzas for reporting that mysqlbinlog incurred significant kernel mutex contention by calling localtime() for every event read. We have since modified mysqlbinlog to use localtime_r instead. Bug #72701.
  • Thank you to Jeremy Cole for reporting that the variable SQL_LOG_BIN having global scope is dangerous. We agree with Jeremy and have modified the behaviour so that it may only be set on a session basis. Bug #67433.
  • Thank you to Jean-François Gagné for reporting that the mysqld startup script would fail on CentOS when the socket option was relative. Bug #74111.
  • Thank you to Eugene Zheganin for reporting that MySQL would not compile with DTrace on Solaris 11.2. Bug #73826.
  • Thank you to Nikola Glavinceski for reporting that certain queries for which subquery materialization or UNION DISTINCT was used together with a hash index on a temporary table could produce incorrect results or cause a server exit. Bug #73368.
  • Thank you to Hartmut Holzgraefe for reporting that the ENABLED_LOCAL_INFILE cmake option was incorrectly enabled by default. Bug #72106.
  • Thank you to Jason Rider for reporting that the use of ODBC-format date literals could produce incorrect query results. Bug #69233.
  • Thank you to Nicholas Bamber for reporting that mysql_setpermission failed to properly quote user names in SQL statements that it generated. Bug #66317.

Thank you again to all the community contributors listed above. In addition, the MySQL team would like to thank the community for their feedback on Bug #67433.

If I missed a name here, please let me know!

- Morgan

Proposal to deprecate collation_database and character_set_database settings

In the MySQL team, we are currently discussing deprecating the ability to change the collation_database and character_set_database settings. As part of our plan, mysql clients will still be able to access these variables, but they will be read-only.

Introduction

The MySQL manual summarizes these two variables as saying:

This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

This is to say that usage is already discouraged, but these two variables originally had a purpose of allowing a DBA to change the default character-set temporarily for the current session only. For example:

SET SESSION character_set_database=latin5;
LOAD DATA infile 'foo.txt' ... ;
-- foo.txt is interpreted as if it was in latin5 character set

The most obvious use case for this is in loading data from a text file which contains an arbitrary character set. However, it is worth noting that the LOAD DATA INFILE syntax has since added the ability to specify a character-set.

Known Issues

The functionality of collation_database and character_set_database differs somewhat from the original design goal, and presents usability issues for our users. Specifically:

  1. Both variables can be set GLOBAL; yet it mostly does not apply

    The original feature was designed for these two variables to be set on a per-session basis. However, an undocumented feature is that they can both be set on a GLOBAL basis without error. For example:

    mysql> SET GLOBAL character_set_database=latin5;
    Query OK, 0 rows affected (0.01 sec)
    

    However, when set on a GLOBAL basis these variables will have largely no effect. New sessions will always copy character set information from the current database unless a database is not selected:

     SET GLOBAL @@character_set_database=latin5;
    
     [disconnect]
     [connect, without choosing the current database]
    
     SELECT @@character_set_database; -- session counterpart takes
     its initial value from the global counterpart
    

    We consider any behaviour which will only apply in specific circumstances like this as confusing, and potentially harmful to user experience.

  2. Behaviour broken by fix for Bug #2326

    As part of the fix for Bug #2326, the collation_database variable no longer affects the CREATE TABLE statement. That is to say that setting character_set_database and collation_database does not apply for all statements!

  3. Variables are not replicated prior to fix for Bug#15126

    Before Bug#15126 was fixed, setting of a local variable would not be replicated; thus relying on this feature would break replication. While this is no longer a current issue, it helps support our current understanding that the usage of this feature is minimal.

  4. Does not apply to stored programs

    Setting these variables has no effect on stored programs (triggers, functions, procedures), base tables or views -- all these objects use the character set and collation of the database they belong to.

Known bugs:

  • Bug #35357 - "character_set_database does not effects CREATE DATABASE without character set"
  • Bug #27208 If no current database, character_set_database !=character_set_server
  • Bug #27687 ALTER TABLE ... CONVERT TO uses @@collation_database

Our Proposal

Our plan is to deprecate both SESSION and GLOBAL variables collation_database and character_set_database starting with MySQL 5.7. In MySQL 5.8, we plan to remove the GLOBAL variables and make accessing the SESSION variables read-only.

We are currently seeking feedback relating to this plan:

  • Do you currently use the collation_database and character_set_database variables?
  • Do you have a use-case that we have not currently considered?

Please get in touch! You can either leave a comment, or contact me via email!

New Data Dictionary - upgrading from earlier MySQL releases

In July, I first wrote that we are currently working on a new data dictionary for MySQL. We have since shipped a labs release where you can try it out for yourself and then send us feedback!

Today, I wanted to write about our upgrade plan from the old data dictionary to the new data dictionary. Or more specifically, explain one of the nuances that we currently have with encoding characters that are not safe on all filesystems.

To quote from the MySQL manual:

For example, if a database in MySQL 5.0 has the name a-b-c, the name contains instances of the - (dash) character. In MySQL 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL 5.1 and later, the same database name is encoded as a@002db@002dc to produce a file system-neutral directory name.

In order to focus our efforts, we are planning to only support upgrading from the 5.1+ database directory encoding. That is to say that users with data directories originating from 5.0 or earlier and have special characters will need to:

  • mysqldump and restore their data
  • or Step through an intermediate release and upgrade to the newer encoding:

    ALTER DATABASE #mysql50#a-b-c UPGRADE DATA DIRECTORY NAME;

It is worth noting that the ALTER TABLE .. UPDATE DATA DIRECTORY NAME step is automatically run by mysql_upgrade which has always been a recommended step to perform following any upgrade.

We are seeking feedback regarding the limitations of our proposed upgrade path. If this will impact you, please leave a comment, or get in touch!

MySQL 5.6.21 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.21.

In particular:

  • Thank you to Yoshinori Matsunobu who reported that during recovery GTID-based replication would need to scan all binary logs. We have since introduced an option for simplified recovery in the case a large number of binary log files exist. Bug #69097.
  • Thank you to Michael Newton for reporting that mysqldump should hexdump geometry data types when invoked with the --hex-blob command line option. Although we did not use his patch, we also thank Martin Hradil for contributing to the bug report. Bug #43544.
  • Thank you to Yoshinori Matsunobu for reporting that a large number of partitions could consume much more memory than previous versions of MySQL. This regression was introduced from an earlier bug fix. Bug #70641.
  • Thank you to Tianzhou Chen for reporting a security issue. Bug #73650.
  • Thank you to Qixiu Xiao for reporting that retrieving multiple values (gets) via InnoDB's memcached interface would return incorrect results. The InnoDB memcached interface does not currently support retrieval of multiple values at once, and has since been changed to produce an error instead. Bug #72453.
  • Thank you to David Schwartz for reporting that performing operations on a timed out key would fail using InnoDB's memcached interface. Bug #72586.
  • Thank you to Jeremy Cole for reporting that a failed in-place ALTER TABLE operation would leave behind a non-unique temporary file, preventing future modifications. Jeremy also provided a testcase demonstrating how to easily reproduce the issue. Bug #72594.
  • Thank you to Inaam Rana for reporting inaccurate handling of srv_activity_count in the InnoDB source code. Inaam also contributed a patch to fix the issue. Bug #72137.
  • Thank you to Jeremy Cole for providing an example where InnoDB will drastically under fill pages. Thank you also to Davi Arnaut, who commented on Jeremy's bug report, and demonstrated a simpler test-case to be able to reproduce. Bug #67718.
  • Thank you to Sadao Hiratsuka for reporting that mysqlbinlog could not safely concatenate files with GTID-based replication. Bug #71695.
  • Thank you to Yoshinori Matsunobu for reporting that with semi-sync replication, setting a slave to a binary log position in the future would cause an assertion on the master. We have changed the behaviour to produce an error. Bug #70327.
  • Thank you to Alexander Du for reporting that when an SQL thread which was waiting on a lock is killed, it could cause transactions to be skipped on a slave. Bug #69873.
  • Thank you Zhai Weixiang for reporting that a server could hang on shutdown when semi-sync replication is in use. Bug #71047.
  • Thank you to Santosh Praneeth Banda for reporting that starting/stopping the sql thread on a slave could cause a transaction to be logged with a different GTID than on the master. Bug #72313.
  • Thank you to Hartmut Holzgraefe for reporting that having two slaves with the same server_uuid resulted in a misleading error message. Based on Hartmut's feedback, the slave error now identifies the duplicate server UUID and the master error identifies the zombie thread that has been killed. Bug #72578.
  • Thank you to Jean-François Gagné for reporting an out of memory scenario when using RBR and slave having different data types. Bug #72610.
  • Thank you to Rolf Martin-Hoster for reporting that MySQL did not correctly handle the general or slow query log as a FIFO or socket file. Bug #67088.
  • Thank you to Laurynas Biveinis for reporting that A simultaneous OPTIMIZE TABLE and online ALTER TABLE on the same InnoDB table could result in deadlock. Laurynas also provided a test-case and example patch. Bug #71433.
  • Thank you to Elena Stepanova for reporting that the query cache was not correctly invalidated when a CASCADE DELETE or CASCADE UPDATE constraint was specified, and the table names contained special characters. Bug #72547.

Thank you again to all the community contributors listed above. In particular, the MySQL team would like to call out the names that appear more than once in this release:

Yoshinori Matsunobu (3), Laurynas Biveinis (2), Jeremy Cole (2)

If I missed a name here, please let me know!

- Morgan

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!