With the latest releases of Galera, password changes using "SET PASSWORD" syntax are not replicated properly.
I have duplicated this in a 3 node cluster in version 5.7.25.
In 5.7.23 everything works fine, but it seems in both 5.7.24 & 5.7.25 there are issues.
Galera will replicate the plain text value into the authentication_string on nodes 2 & 3 instead of the hashed password.
Below is what I happened when executing on a 5.7.25 cluster.
Node 1:
mysql> select @@version, user, authentication_string from mysql.user where user='testadmin';
+------------+-----------+-------------------------------------------+
| @@version | user | authentication_string |
+------------+-----------+-------------------------------------------+
| 5.7.25-log | testadmin | *1254F0F4B61FBBE85C10367B586C830683C14121 |
+------------+-----------+-------------------------------------------+
mysql> set password for testadmin = password('NewPwd_2');
mysql> select @@version, user, authentication_string from mysql.user where user='testadmin';
+------------+-----------+-------------------------------------------+
| @@version | user | authentication_string |
+------------+-----------+-------------------------------------------+
| 5.7.25-log | testadmin | *FDC571EE3BC4E5114848E31F64CE9E80929021E3 |
+------------+-----------+-------------------------------------------+
Node 2:
mysql> select @@version, user, authentication_string from mysql.user where user='testadmin';
+------------+-----------+-----------------------+
| @@version | user | authentication_string |
+------------+-----------+-----------------------+
| 5.7.25-log | testadmin | NewPwd_2 |
+------------+-----------+-----------------------+
Node 3:
mysql> select @@version, user, authentication_string from mysql.user where user='testadmin';
+------------+-----------+-----------------------+
| @@version | user | authentication_string |
+------------+-----------+-----------------------+
| 5.7.25-log | testadmin | NewPwd_2 |
+------------+-----------+-----------------------+
temeo
commented
5 years ago
With the latest releases of Galera, password changes using "SET PASSWORD" syntax are not replicated properly.
I have duplicated this in a 3 node cluster in version 5.7.25.
In 5.7.23 everything works fine, but it seems in both 5.7.24 & 5.7.25 there are issues. Galera will replicate the plain text value into the authentication_string on nodes 2 & 3 instead of the hashed password.
Below is what I happened when executing on a 5.7.25 cluster.
Node 1: mysql> select @@version, user, authentication_string from mysql.user where user='testadmin'; +------------+-----------+-------------------------------------------+ | @@version | user | authentication_string | +------------+-----------+-------------------------------------------+ | 5.7.25-log | testadmin | *1254F0F4B61FBBE85C10367B586C830683C14121 | +------------+-----------+-------------------------------------------+
mysql> set password for testadmin = password('NewPwd_2');
mysql> select @@version, user, authentication_string from mysql.user where user='testadmin'; +------------+-----------+-------------------------------------------+ | @@version | user | authentication_string | +------------+-----------+-------------------------------------------+ | 5.7.25-log | testadmin | *FDC571EE3BC4E5114848E31F64CE9E80929021E3 | +------------+-----------+-------------------------------------------+
Node 2: mysql> select @@version, user, authentication_string from mysql.user where user='testadmin'; +------------+-----------+-----------------------+ | @@version | user | authentication_string | +------------+-----------+-----------------------+ | 5.7.25-log | testadmin | NewPwd_2 | +------------+-----------+-----------------------+
Node 3: mysql> select @@version, user, authentication_string from mysql.user where user='testadmin'; +------------+-----------+-----------------------+ | @@version | user | authentication_string | +------------+-----------+-----------------------+ | 5.7.25-log | testadmin | NewPwd_2 | +------------+-----------+-----------------------+