Bug #82979 SET PASSWORD replicates incorrectly between 5.6 and 5.7
Submitted: 13 Sep 2016 22:11 Modified: 19 Sep 2016 8:07
Reporter: Gillian Gunson (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7, 5.7.15 OS:Any
Assigned to:
Tags: password, replication, SET

[13 Sep 2016 22:11] Gillian Gunson
Description:
As documented (http://dev.mysql.com/doc/refman/5.7/en/set-password.html), between 5.6 and 5.7, one format of "SET PASSWORD" for a user changes its syntax from

SET PASSWORD [for user] = [hash of password]

to

SET PASSWORD [for user] = [password]

However, in a replication topology where there is a 5.6 master and a 5.7 replica, and where the mysql schema is replicated, the "SET PASSWORD" can get replicated incorrectly and result in the user having a different password on the replica than intended (in fact, a hash of the hash of the password).

How to repeat:
5.6 master (5.6.33-log MySQL Community Server (GPL))
5.7 replica (5.7.15-log MySQL Community Server (GPL))

On 5.6 master:

grant all on *.* to 'ggunson'@'%' identified by 'foo';
set password for 'ggunson'@'%' = password('foo');
select user, password from mysql.user where user='ggunson' and host='%';

+---------+-------------------------------------------+
| user    | password                                  |
+---------+-------------------------------------------+
| ggunson | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+---------+-------------------------------------------+
1 row in set (0.00 sec)

On 5.7 replica:

select user, authentication_string from mysql.user where user='ggunson' and host='%';
+---------+-------------------------------------------+
| user    | authentication_string                     |
+---------+-------------------------------------------+
| ggunson | *D49A23AED1FFF8830421D5E90B207850E07A6235 |
+---------+-------------------------------------------+
1 row in set (0.00 sec)

select password(password('foo'));
+-------------------------------------------+
| password(password('foo'))                 |
+-------------------------------------------+
| *D49A23AED1FFF8830421D5E90B207850E07A6235 |
+-------------------------------------------+
1 row in set, 2 warnings (0.01 sec)

Note the 5.6 master's binlogs will contain these statements:

GRANT ALL PRIVILEGES ON *.* TO 'ggunson'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
SET PASSWORD FOR 'ggunson'@'%'='*F3A2A51A9B0F2BE2468926B4132313728C250DBF'

The 5.7 replica's binlogs (with log-slave-updates on) will contain these statements:

GRANT ALL PRIVILEGES ON *.* TO 'ggunson'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
ALTER USER 'ggunson'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*D49A23AED1FFF8830421D5E90B207850E07A6235'

Suggested fix:
Ideally, have the 5.7 sql thread parse the "SET PASSWORD" command differently based on the server version of the master as specified in the relay logs?

Otherwise, this should be documented as an incompatibility of 5.6 to 5.7 replication. I didn't see any mention of it in the expected pages:

https://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html
http://dev.mysql.com/doc/refman/5.7/en/set-password.html

I found the error on some 5.7 test replicas of a 5.6 topology; a user's password was changed via puppet and the user couldn't connect to the 5.7 hosts.
[19 Sep 2016 8:07] Umesh Shastry
Hello Gillian,

Thank you for the report.

- 5.7.15

mysql> select user, authentication_string from mysql.user where user='ggunson' and host='%';
+---------+-------------------------------------------+
| user    | authentication_string                     |
+---------+-------------------------------------------+
| ggunson | *D49A23AED1FFF8830421D5E90B207850E07A6235 |
+---------+-------------------------------------------+
1 row in set (0.00 sec)

-- 5.6.33

mysql> select user, password from mysql.user where user='ggunson' and host='%';
+---------+-------------------------------------------+
| user    | password                                  |
+---------+-------------------------------------------+
| ggunson | *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+---------+-------------------------------------------+
1 row in set (0.00 sec)

Seems some information has been added here http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Thanks,
Umesh
[27 Jun 17:00] Miguel Solorzano
https://bugs.mysql.com/bug.php?id=86838 marked as duplicate of this one.
[28 Jun 9:01] Riccardo Pizzi
This is quite a show stopper for migrating to 5.7, any chance someone can work on this ? Should be an easy fix....
[3 Jul 14:22] Riccardo Pizzi
OK, I managed to patch this myself. 
Below is the fix based on 5.7.18. This took me 10 minutes to figure it out....

*** sql/auth/sql_user.cc	Mon Jul  3 16:18:55 2017
--- sql/auth/sql_user.cc.patch	Mon Jul  3 16:07:01 2017
***************
*** 565,570 ****
--- 565,574 ----
    */
    if ((Str->uses_identified_by_clause ||
        (Str->auth.length == 0 && !user_exists)) 
+     	// Rick Pizzi: don't change to hash if thread is a replication thread and a 
+     	// SET PASSWORD was issued on master, to avoid double hash
+ 	// (fix for https://bugs.mysql.com/bug.php?id=82979)
+ 	&& (!thd->slave_thread || thd->lex->sql_command != SQLCOM_SET_OPTION))
    {
      st_mysql_auth *auth= (st_mysql_auth *) plugin_decl(plugin)->info;
      inbuf= Str->auth.str;