Author: morgo

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!

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!

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

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!

In keeping with a MySQL tradition, we will be holding a storage engine vendors meeting to coincide with MySQL Central @ Open World this year.

The day and venue is TBD, but will be in the San Francisco/Bay Area week of 29th September – October 3rd.

For an invitation, please send me an email!