Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.18, 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
I found that ALTER USER failed to replicate on slave(s).
To reproduce this problem, I tried to change password on master server.
master [localhost] {msandbox} ((none)) > ALTER USER 'msandbox'@'localhost' IDENTIFIED BY 'msandbox1';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
master [localhost] {msandbox} ((none)) > show grants for 'msandbox'@'localhost';
|
+--------------------------------------------------------------------------------------------------------------------------+
|
| Grants for msandbox@localhost |
|
+--------------------------------------------------------------------------------------------------------------------------+
|
| GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost' IDENTIFIED BY PASSWORD '*E4042948BD58ED92656A17F1F5ADE60136095E6B' |
|
+--------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Now, on slave password didn't changed. Check password hash between master and slave.
slave1 [localhost] {msandbox} ((none)) > show grants for 'msandbox'@'localhost';
|
+--------------------------------------------------------------------------------------------------------------------------+
|
| Grants for msandbox@localhost |
|
+--------------------------------------------------------------------------------------------------------------------------+
|
| GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost' IDENTIFIED BY PASSWORD '*6C387FC3893DBA1E3BA155E74754DA6682D04747' |
|
+--------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
I also verified from master binary log that ALTER USER .. is not written into binary log.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
We found the same bug today with ALTER USER updating to all galera nodes but not to async slave. SET PASSWORD works normally. Using 10.3.11 on CentOS 7.
I agree with the error
+--error ER_CANNOT_USER
+alter user 'non_exist_user1'@'fakehost' identified by 'foo', 'barbar'@'fakehost' identified by 'bar';
and also there must be no record in the binlog. But we ALTER in reverse order? The error must stay, how about binlogging? Will there be a split-out "sub"-ALTER for the succeeding (presumably) 1st part?
6:55 PM
@sachin: answering myself after reading that part of sources. Yes, the 1s user will make into binlog thanks to
if (some_users_altered)
result|= write_bin_log(thd, FALSE, thd->query(),
thd->query_length());
6:57 PM
@sachin: then I ask you to check binlog in such case (to prove a binlog record), and in the first-to-fail too (to prove no binlog record). (edited)
sachin.setiya.007 Btw, what version of 10.3 will this fix appear in? 10.3.13?
If you execute the following:
| version() || 10.2.19-MariaDB-log |You can see that the CREATE USER and SET PASSWORD are written to the binary log, but the ALTER USER is not:
# at 386#181116 16:04:52 server id 1 end_log_pos 504 CRC32 0x86ae998a Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1542402292/*!*/;SET @@session.pseudo_thread_id=9/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;SET @@session.sql_mode=1411383296/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;CREATE USER 'testuser'@'localhost' identified by '1234'/*!*/;# at 504#181116 16:06:00 server id 1 end_log_pos 546 CRC32 0x9f2ab113 GTID 0-1-2055772 ddl/*!100001 SET @@session.gtid_seq_no=2055772*//*!*/;# at 546#181116 16:06:00 server id 1 end_log_pos 692 CRC32 0x89e90b6e Query thread_id=9 exec_time=0 error_code=0SET TIMESTAMP=1542402360/*!*/;SET PASSWORD FOR 'testuser'@'localhost'='*1BF5B468E9090C8DBE711DF031C68FF05D3F3BEC'/*!*/;