MySQL Bugs: #100093: Replica error when update mysql.user

Bug #100093	Replica error when update mysql.user
Submitted:	2 Jul 2020 13:21	Modified:	7 Jul 2020 2:26
Reporter:	George Ma	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.16, 8.0.18	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
1) Create a replica between 8.0.16 and 8.0.18, where the version of master is 8.0.16 and version of slave is 8.0.18

2) Create a new user on master

3) Change the host info of the new user by update mysql.user

And then execute `show slave status`, the error info is:

Last_SQL_Errno: 13146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s)...
 

How to repeat:
create user 'test_user'@'%' identified by 'xxxxxx';

update mysql.user set Host = 'localhost' where user = 'test_user';

I see that the definition of column `Host` in mysql.user(and many other system tables) has changed in 8.0.17:

8.0.16

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT ''

8.0.17

`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT ''

This may cause the error.

Hi,

There is not much we can do in this case.
If you directly edit system tables you can expect this type of problems. Proper way would be to create a proper user and disable the old one.

all best
Bogdan

Hi, Bogdan

This error may lead to confusion. Actually, any operations to the system table with `Host` column will cause this replica error. For example:

1. update the privilege in mysql.user
2. insert an user to mysql.user
3. delete an user from mysql.user
3. update the privilege in mysql.db
4. etc

I have checked the binlog files and find that if you use the ACL syntax(`create/drop/grant`), the binlog records are in statement format. But if you change the system table directly, the binlog records are in row format, which cause the compatible check fail.

So, does it mean that the way change system table directly is not recommend/support any longer, and users should use the standard ACL syntax?

Hi,

Using proper ACL commands was always a preferred way but with changes 8.0 brought this is required if you expect this type of operations to work.

So while you can still directly edit the system tables you have to expect side-effects of those actions (like changes not replicated between all versions). The changes in system tables don't happen often but they do happen (IIRC this change was introduced by the fix of bug#94581 )

kind regards
Bogdan