Bug #79248 GRANT ... IDENTIFIED BY '' expires password on slave but not on master

Description:
If you use this command for an already created user:

GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost' IDENTIFIED BY '' WITH GRANT OPTION;

The password is marked as expired on slave but not on the master.

How to repeat:
Master:

CREATE USER 'test3'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost' IDENTIFIED BY '' WITH GRANT OPTION;
ALTER USER 'test3'@'localhost' PASSWORD EXPIRE NEVER;

select * from user where User="test3"\G
*************************** 1. row ***************************
                  Host: localhost
                  User: test3
[...]
      password_expired: N
 password_last_changed: 2015-11-12 13:10:14
     password_lifetime: NULL
        account_locked: N

SLAVE:

select * from user where User="test3"\G
*************************** 1. row ***************************
                  Host: localhost
                  User: test3

[...]
      password_expired: Y
 password_last_changed: 2015-11-12 13:10:14
     password_lifetime: 0
        account_locked: N

Version 5.7.9

Thank you for the bug report. Please provide both my.cnf(ini) files. Thanks.

Server 1:

[client]
user               = msandbox
password           = msandbox
port               = 14901
socket             = /tmp/mysql_sandbox14901.sock

[mysqld]
user               = root
port               = 14901
socket             = /tmp/mysql_sandbox14901.sock
basedir            = /root/5.7.9
datadir            = /root/sandboxes/rcsandbox_mysql-5_7_9/node1/data
tmpdir             = /root/sandboxes/rcsandbox_mysql-5_7_9/node1/tmp
lower_case_table_names = 0
pid-file           = /root/sandboxes/rcsandbox_mysql-5_7_9/node1/data/mysql_sandbox14901.pid
bind-address       = 127.0.0.1
# slow-query-log = on
# slow-query-log-file=/root/sandboxes/rcsandbox_mysql-5_7_9/node1/data/msandbox-slow.log
# general-log = on
# general-log-file=/root/sandboxes/rcsandbox_mysql-5_7_9/node1/data/msandbox-general.log
#
# additional options passed through 'my_clause'
#
server-id=101
relay-log-index=mysql-relay
relay-log=mysql-relay
log-bin=mysql-bin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=1
report-host=SBnode1
report-port=14901
log-error=msandbox.err

Server 2:

[mysql]
prompt='node2 [\h] {\u} (\d) > '
#

[client]
user               = msandbox
password           = msandbox
port               = 14902
socket             = /tmp/mysql_sandbox14902.sock

[mysqld]
user               = root
port               = 14902
socket             = /tmp/mysql_sandbox14902.sock
basedir            = /root/5.7.9
datadir            = /root/sandboxes/rcsandbox_mysql-5_7_9/node2/data
tmpdir             = /root/sandboxes/rcsandbox_mysql-5_7_9/node2/tmp
lower_case_table_names = 0
pid-file           = /root/sandboxes/rcsandbox_mysql-5_7_9/node2/data/mysql_sandbox14902.pid
bind-address       = 127.0.0.1
# slow-query-log = on
# slow-query-log-file=/root/sandboxes/rcsandbox_mysql-5_7_9/node2/data/msandbox-slow.log
# general-log = on
# general-log-file=/root/sandboxes/rcsandbox_mysql-5_7_9/node2/data/msandbox-general.log
#
# additional options passed through 'my_clause'
#
server-id=102
relay-log-index=mysql-relay
relay-log=mysql-relay
log-bin=mysql-bin
log-slave-updates
replicate-same-server-id=0
auto_increment_increment=2
auto_increment_offset=2
report-host=SBnode2
report-port=14902
log-error=msandbox.err

Thank you for the feedback.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-log Source distribution PULL: 2015-NOV-07

Copyright (c) 2000, 2015, 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.

master > show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

master > create user "repli";
Query OK, 0 rows affected (0.01 sec)

master > GRANT REPLICATION SLAVE ON *.* TO 'repli'@'localhost' IDENTIFIED BY 'miguel';
Query OK, 0 rows affected, 1 warning (0.00 sec)

master > show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 655
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

master > CREATE USER 'test3'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.02 sec)

master > GRANT ALL PRIVILEGES ON *.* TO 'test3'@'localhost' IDENTIFIED BY '' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)

master > ALTER USER 'test3'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

master > use mysql
Database changed
master > select * from user where User="test3"\G
*************************** 1. row ***************************
                  Host: localhost
                  User: test3
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
          <CUT>
 authentication_string:
      password_expired: N
 password_last_changed: 2015-11-12 18:09:27
     password_lifetime: 0
        account_locked: N
1 row in set (0.00 sec)

========================================================================

c:\dbs>5.7s\bin\mysql -uroot -pmiguel --port=3307 --prompt="slave> "
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-log Source distribution PULL: 2015-NOV-07
<CUT>
slave> CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_USER='repli',
    -> MASTER_PASSWORD='miguel',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=655;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

slave> start slave;
Query OK, 0 rows affected (0.01 sec)

slave> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 655
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
         <CUT>
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
1 row in set (0.00 sec)

slave> use mysql
Database changed
slave> select * from user where User="test3"\G
*************************** 1. row ***************************
                  Host: localhost
                  User: test3
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
          <CUT>
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: Y
 password_last_changed: 2015-11-12 18:09:27
     password_lifetime: 0
        account_locked: N
1 row in set (0.00 sec)

Posted by developer: Bug status updated to 'Patch pending'