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!

Meet the MySQL Team at Percona Live London

The MySQL team will be presenting three sessions at Percona Live in 2 weeks:

In addition, Tomas will be presenting a not-to-be missed keynote on how MySQL just keeps getting better.

Please stop by and introduce yourself! We would love to hear feedback from users who have downloaded and tested our latest 5.7 DMR5 and labs releases.

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

Experimenting with the new Data Dictionary Labs Release

Labs releases are intended to provide early access to new features. As Gopal notes in his recent blog post About the Data Dictionary Labs Release, there is a notable restriction where upgrading from any previous MySQL database version is not supported.

Today, I thought I would demonstrate how to get the data dictionary lab up and running on a fresh Ubuntu 14.04 installation:

# Download from labs.mysql.com
wget http://downloads.mysql.com/snapshots/pb/mysql-5.7.5-labs-dd/mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz

# extract it to /usr/local/mysql
# more or less following instructions in INSTALL-BINARY

groupadd mysql
useradd -r -g mysql mysql
 
tar -xzf mysql-5.7.5-labs-dd-linux-el6-x86_64.tar.gz
mv mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/
ln -s /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64 /usr/local/mysql

cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .

At this point, the next step in the instructions actually failed for me:

bin/mysql_install_db --user=mysql
2014-10-15 09:22:34 [ERROR]   The data directory needs to be specified.

This behavior change appears to be the result of mysql_install_db being rewritten in C++. Specifying the datadir addresses this, but also results in another error:

bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
2014-10-15 09:24:15 [ERROR]   The child process terminated prematurely. Errno= 32
2014-10-15 09:24:15 [ERROR]   Failed to execute /usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US
-- server log begin --

Error 32 is a broken pipe. From the description, we can see that mysql_install_db tried to call a subprocess (mysqld --install-server), but it exited prematurely. The description here doesn't show why it exited prematurely, but we can execute this command directly to find out:

/usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld --no-defaults --install-server --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US
/usr/local/mysql-5.7.5-labs-dd-linux-el6-x86_64/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

OK! We don't have the async IO dependency installed on this system, which is used by InnoDB:

apt-get install libaio1
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-3.13.0-36 linux-headers-3.13.0-36-generic
  linux-image-3.13.0-36-generic linux-image-extra-3.13.0-36-generic
Use 'apt-get autoremove' to remove them.
The following NEW packages will be installed:
  libaio1
0 upgraded, 1 newly installed, 0 to remove and 3 not upgraded.
Need to get 6,364 B of archives.
After this operation, 53.2 kB of additional disk space will be used.
Get:1 http://mirrors.digitalocean.com/ubuntu/ trusty/main libaio1 amd64 0.3.109-4 [6,364 B]
Fetched 6,364 B in 0s (319 kB/s)
Selecting previously unselected package libaio1:amd64.
(Reading database ... 116531 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.109-4_amd64.deb ...
Unpacking libaio1:amd64 (0.3.109-4) ...
Setting up libaio1:amd64 (0.3.109-4) ...
Processing triggers for libc-bin (2.19-0ubuntu6.3) ...

Now to try the original install command again:

bin/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data

It should return no errors now. To follow the remaining steps in the INSTALL-BINARY instructions:


chown -R root .
chown -R mysql data
bin/mysqld_safe --user=mysql &

In MySQL 5.7, installation is secure by default. This means that a root password is automatically generated, and stored in ~/.mysql_secret:

cat ~/.mysql_secret
# Password set for user 'root@localhost' at 2014-10-15 09:32:09
Wpckgmde+U,o

It is possible to change this password with the MySQL command line client:

./bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.5-labs-dd

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD=PASSWORD('acdc');
Query OK, 0 rows affected (0.00 sec)

As Gopal mentioned in his blog post, the labs release runs completely without frm files:

ls /usr/local/mysql/data/mysql
catalogs.ibd                  help_relation.ibd       slave_relay_log_info.ibd
character_sets.ibd            help_topic.ibd          slave_worker_info.ibd
collations.ibd                index_column_usage.ibd  slow_log.CSM
columns.ibd                   indexes.ibd             slow_log.CSV
columns_priv.MYD              index_partitions.ibd    table_partitions.ibd
columns_priv.MYI              index_stats.ibd         table_partition_values.ibd
column_type_elements.ibd      innodb_index_stats.ibd  tables.ibd
db.MYD                        innodb_table_stats.ibd  tablespace_files.ibd
db.MYI                        ndb_binlog_index.MYD    tablespaces.ibd
db.opt                        ndb_binlog_index.MYI    tables_priv.MYD
engine_cost.ibd               parameters.ibd          tables_priv.MYI
event.MYD                     plugin.ibd              table_stats.ibd
event.MYI                     proc.MYD                time_zone.ibd
events.ibd                    proc.MYI                time_zone_leap_second.ibd
foreign_key_column_usage.ibd  procs_priv.MYD          time_zone_name.ibd
foreign_keys.ibd              procs_priv.MYI          time_zone_transition.ibd
func.MYD                      proxies_priv.MYD        time_zone_transition_type.ibd
func.MYI                      proxies_priv.MYI        triggers.ibd
general_log.CSM               routines.ibd            user.MYD
general_log.CSV               schemata.ibd            user.MYI
gtid_executed.ibd             server_cost.ibd         view_table_usage.ibd
help_category.ibd             servers.ibd
help_keyword.ibd              slave_master_info.ibd

information_schema is a now also a set of views on top of real InnoDB tables (stored in the mysql schema). As a VIEW we can explain it, and since extended explain is always enabled in 5.7, SHOW WARNINGS will show the rewritten form:

mysql> SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 1
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: 1
    CREATE_TIME: 2014-10-15 09:32:10
    UPDATE_TIME: 2014-10-15 09:32:10
     CHECK_TIME:
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 CREATE_OPTIONS: stats_persistent=1
  TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM information_schema.tables WHERE table_name = 'user'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cat
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: name
      key_len: 194
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: sch
   partitions: NULL
         type: ref
possible_keys: PRIMARY,catalog_id
          key: catalog_id
      key_len: 8
          ref: mysql.cat.id
         rows: 3
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl
   partitions: NULL
         type: eq_ref
possible_keys: schema_id
          key: schema_id
      key_len: 202
          ref: mysql.sch.id,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: mysql.tbl.collation_id
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: stat
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 388
          ref: mysql.sch.name,const
         rows: 1
     filtered: 100.00
        Extra: NULL
5 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,`tbl`.`engine` AS `ENGINE`,`tbl`.`version` AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,ifnull(`stat`.`table_rows`,1) AS `TABLE_ROWS`,`stat`.`avg_row_length` AS `AVG_ROW_LENGTH`,`stat`.`data_length` AS `DATA_LENGTH`,`stat`.`max_data_length` AS `MAX_DATA_LENGTH`,`stat`.`index_length` AS `INDEX_LENGTH`,`stat`.`data_free` AS `DATA_FREE`,ifnull(`stat`.`auto_increment`,1) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,`tbl`.`last_altered` AS `UPDATE_TIME`,'' AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,`stat`.`checksum` AS `CHECKSUM`,get_dd_create_options(`tbl`.`options`) AS `CREATE_OPTIONS`,`tbl`.`comment` AS `TABLE_COMMENT` from `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) left join `mysql`.`table_stats` `stat` on(((`stat`.`schema_name` = `sch`.`name`) and (`stat`.`table_name` = 'user'))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`name` = 'user') and can_access_table(`sch`.`name`,`tbl`.`name`)) order by `sch`.`name`,`tbl`.`name`
1 row in set (0.00 sec)

That's it for today. Enjoy testing the data dictionary, and please send in your feedback!

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!