Saturday, July 25, 2015

MySQL 5.7 : no more password column!

Maintaining a project like MySQL::Sandbox is sometimes tiring, but it has its advantages. One of them is that everything related to the server setup comes to my attention rather earlier than if I were an average DBA or developer.

I try to keep MySQL Sandbox up to date with every release of MySQL and (to a lesser extent) MariaDB [1]. For this reason, I am used to trying a new release with MySQL Sandbox, and … seeing it fail.

Of the latest changes in MySQL, probably the most disruptive was what happened in MySQL 5.7.6, where the mysql.user table lost the password column.

Yep. No ‘password’ column anymore. And just to make the setup procedure harder, the syntax of SET PASSWORD was changed, and deprecated.


Previously, I could run:


mysql [localhost] {msandbox} (mysql) > select version();  
+-----------+  
| version() |  
+-----------+  
| 5.6.25    |  
+-----------+  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mysql) > select host,user,password from user;  
+-----------+-------------+-------------------------------------------+  
| host      | user        | password                                  |  
+-----------+-------------+-------------------------------------------+  
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |  
+-----------+-------------+-------------------------------------------+  
8 rows in set (0.00 sec)

In the latest releases, though, this fails.


mysql [localhost] {msandbox} (mysql) > select version();  
+-----------+  
| version() |  
+-----------+  
| 5.7.8-rc  |  
+-----------+  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mysql) > select host,user,password from user;  
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

Instead of a password column (which was CHAR(41)), we have now an authentication_string column of type TEXT.


+-----------+-------------+-------------------------------------------+  
| host      | user        | authentication_string                     |  
+-----------+-------------+-------------------------------------------+  
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |  
+-----------+-------------+-------------------------------------------+

Fixing MySQL Sandbox to handle this issue and to be at the same time compatible with previous releases was quite challenging, but in the end I did it. Recent versions of the sandbox can handle all the releases from Oracle, Percona, and MariaDB without showing hiccups.

So, for testing, the issue is solved. Now comes the hard part: when thousands of database administration procedures will break down for lack of the password column. To all the DBAs and database developers out there: good luck!




  1. It is my pleasure to disclose that MariaDB 10.1 runs in MySQL Sandbox 3.0.55+, with only minimal changes.  ↩


3 comments:

Simon J Mudd said...

See: https://bugs.launchpad.net/percona-toolkit/+bug/1435370 which I filed in March.
Indeed this change is disruptive and has required me to change quite a few scripts used to maintain servers.
So the change in behaviour may make sense but I'm sure it's going to bite quite a few people.

Giuseppe Maxia said...

@Simon,
In my case, the fix required two changes. First I had to use a different syntax to create sandbox users. Then I had to use an alias for the password column in the test that checks the correct definition and capabilities of the various users.

What troubled me is that I complained about the lack of a deprecation process for this feature. I would have appreciated something like "the password column is deprecated and will be removed in future releases," but I was told that there is no process in place for this kind of things. And indeed we have seen it when the 'host' table disappeared in MySQL 5.6.
I suspect we will see more of these changes when the new data dictionary and (the performance_schema) will take over the grant tables as we know them now.

Shinguz said...

It looks like they are not understandable: http://bugs.mysql.com/bug.php?id=76655