Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3.22, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
-
## 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 ~]#
## 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 ~]#
## Additional notes:
binlog_format is not relevant and --replicate-ignore-db=mysql does not avoid the problem.
## 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 ~]# ## 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 ~]# ## Additional notes: binlog_format is not relevant and --replicate-ignore-db=mysql does not avoid the problem.
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' |
Attachments
Issue Links
- is duplicated by
-
MDEV-28530 Revoking privileges from a non-existing user on a master breaks replication on the slave in the presence of replication filters
- Closed
- relates to
-
MDEV-4992 'DROP DATABASE IF EXISTS' causes replication to break (does not break in MySQL)
- Open
-
MDEV-14716 Wrong binlog entries for a multi-user CREATE USER statement
- Confirmed