Bug #95829 mysql DB statements failing on slave even after using replicate-ignore-db=mysql
Submitted: 17 Jun 6:04 Modified: 11 Oct 6:08
Reporter: anand aundhekar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: MIXED binlog format on master, mysql_replication, mysql_user_creation, mysql8

[17 Jun 6:04] anand aundhekar
Description:
Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql or replicate-wild-ingore-table=mysql.%
We have replication setup from master mysql 5.6 to slave mysql 8. And when we create a user on master it's replicating on slave with error message 

Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49''

How to repeat:
On Master 

use mysql;
CREATE USER 'testing'@'localhost' IDENTIFIED BY 'T3st7@asdM&';
flush privileges;

On Slave :

Replicate_Ignore_DB: mysql
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: db1.%,db2.%,db3.%
Replicate_Wild_Ignore_Table: 
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'

AFTER USING 

Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: db1.%,db2.%,db3.%
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'

Suggested fix:
Tried By using below commands on master :

IDENTIFIED WITH mysql_native_password BY

Again on making changes on slave configuration

[mysqld]
default-authentication-plugin=mysql_native_password
[17 Jun 6:06] anand aundhekar
Binlog Format is Mixed on master
[21 Jun 6:46] Umesh Shastry
Hello anand,

Thank you for the report.

regards,
Umesh
[24 Jul 5:35] anand aundhekar
Mysql version 8.0.13
[11 Oct 6:08] anand aundhekar
Anyone get same issue? or have any solution on it?