[MDEV-21721] Partially successful multi-user DROP breaks filtered replication Created: 2020-02-13  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 5.5, 10.0, 10.1, 10.3.22, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: MG Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:
    1. Master has binary logging enabled:

[root@mg-m13-m ~]# my_print_defaults --mysqld
--server-id=101
--log-bin
[root@mg-m13-m ~]# systemctl restart mysql
[root@mg-m13-m ~]#

    1. Replica has a --replicate-wild-do-table option:

[root@mg-m13-s ~]# my_print_defaults --mysqld
--server-id=102
--replicate-wild-do-table=foo.t1_%
[root@mg-m13-s ~]# systemctl restart mysql
[root@mg-m13-s ~]#

    1. Additional notes:

binlog_format is not relevant and --replicate-ignore-db=mysql does not avoid the problem.

Issue Links:
Relates
relates to MDEV-4992 'DROP DATABASE IF EXISTS' causes repl... Open
relates to MDEV-14716 Wrong binlog entries for a multi-user... Confirmed

 Description    

## Master creates a user and it exists:
 
 
 
MariaDB [(none)]> select user, host from mysql.user where user not in ('repl','root') order by user, host;
 
Empty set (0.000 sec)
 
 
 
MariaDB [(none)]> create user mgtest@'192.168.100.%';
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [(none)]> select user, host from mysql.user where user not in ('repl','root') order by user, host;
 
+--------+---------------+
 
| user   | host          |
 
+--------+---------------+
 
| mgtest | 192.168.100.% |
 
+--------+---------------+
 
1 row in set (0.000 sec)


## User does not exist on replica due to replication filtering:
 
 
 
MariaDB [(none)]> select user, host from mysql.user where user not in ('repl','root') order by user, host;
 
Empty set (0.000 sec)
 
 
 
MariaDB [(none)]> pager grep Seconds
 
PAGER set to 'grep Seconds'
 
MariaDB [(none)]> show slave status\G
 
         Seconds_Behind_Master: 0
 
1 row in set (0.000 sec)


## Master drops valid user along with nonexistent user:
 
 
 
MariaDB [(none)]> drop user mgtest@'192.168.100.%', mgtest;
 
ERROR 1396 (HY000): Operation DROP USER failed for 'mgtest'@'%'
 
MariaDB [(none)]> select user, host from mysql.user where user not in ('repl','root') order by user, host;
 
Empty set (0.001 sec)


## Replica is broken:
 
 
 
Last_SQL_Error: Query caused different errors on master and slave.     Error on master: message (format)='Operation %s failed for %.256s' error code=1396 ; Error on slave: actual message='no error', error code=0. Default database: ''. Query: 'drop user mgtest@'192.168.100.%', mgtest'



 Comments   
Comment by Alice Sherepa [ 2020-02-13 ]

Thank you for the report! Reproducible on 5.5-10.4

#run as ./mtr --mysqld=--replicate-wild-do-table=test.%
 
source include/master-slave.inc;
 
connection master;
 
 
 
create user u1@'%';
 
--error 1396
 
drop user u1@'%',u2@'%';
 
 
 
sync_slave_with_master;
 
--vertical_results
 
show slave status;
 
 
 
--source include/rpl_end.inc


Last_SQL_Error	Query caused different errors on master and slave.     Error on master: message (format)='Operation %s failed for %.256s' error code=1396 ; Error on slave: actual message='no error', error code=0. Default database: 'test'. Query: 'drop user u1@'%',u2@'%''

Generated at Thu Feb 08 09:09:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.