| Bug #79248 | GRANT ... IDENTIFIED BY '' expires password on slave but not on master | ||
|---|---|---|---|
| Submitted: | 12 Nov 2015 12:19 | Modified: | 12 Nov 2015 20:16 |
| Reporter: | Miguel Angel Nieto | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Nov 2015 12:26]
Miguel Angel Nieto
Version 5.7.9
[12 Nov 2015 15:06]
MySQL Verification Team
Thank you for the bug report. Please provide both my.cnf(ini) files. Thanks.
[12 Nov 2015 18:19]
Miguel Angel Nieto
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
[12 Nov 2015 20:16]
MySQL Verification Team
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)
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