| Bug #105365 | replicate-wild-ignore-table=mysql.% and grant break replication | ||
|---|---|---|---|
| Submitted: | 28 Oct 2021 14:31 | Modified: | 12 Jan 2022 15:56 |
| Reporter: | lalit Choudhary | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7,8.0, 8.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 Oct 2021 9:27]
MySQL Verification Team
Hello Lalit,
Thank you for the report and feedback.
-- Simple master-> slave setup, followed steps from your report -
rm -rf master/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/master --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/master --core-file --socket=/tmp/mysql_master.sock --port=3306 --log-error=$PWD/master/log.err --log-bin=master-bin --server_id=1 2>&1 &
rm -rf slave/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/slave --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/slave --core-file --socket=/tmp/mysql_slave.sock --port=3307 --log-error=$PWD/slave/log.err --log-bin=slave-bin --server_id=2 --replicate-wild-ignore-table=mysql.% 2>&1 &
- master
bin/mysql -uroot -S /tmp/mysql_master.sock --prompt='Master>'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Master>
Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)
Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Master>
Master>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)
Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
Master>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Master>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1220 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- slave
bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Slave>CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='master-bin.000001',
-> MASTER_LOG_POS=1220;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Slave>start slave;
Query OK, 0 rows affected (0.00 sec)
Slave>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1220
Relay_Log_File: support-cluster03-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1220
Relay_Log_Space: 540
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 66c06f4d-3a2b-11ec-af67-000017016152
Master_Info_File: /export/home/tmp/ushastry/mysql-5.7.36/slave/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
- master
Master>create database test;
Query OK, 1 row affected (0.01 sec)
Master>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Master>create table test.t1(c1 int);
Query OK, 0 rows affected (0.02 sec)
Master>show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
Master>drop table test.t1;
Query OK, 0 rows affected (0.01 sec)
Master>grant select on test.t1 to msandbox@'127.%';
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
- slave
No issues observed.
Am I missing anything here? Could you please try in latest builds and confirm if you are seeing this? Thank you!
regards,
Umesh
regards,
Umesh
[6 Nov 2021 13:45]
lalit Choudhary
Hi Umesh,
I see in your test you are creating and dropping a table on MASTER only and that is the reason you don't see an issue.
Following are the reproducible steps:
1. Setup simple replication( default file position based replication)
2. On SLAVE add replication filter in my.cnf
replicate-wild-ignore-table=mysql.%
3. Restart slave mysqld service
#####On MASTER:
master [localhost] {msandbox} ((none)) > create table test.t1(c1 int);
Query OK, 0 rows affected (0.05 sec)
#####ON SLAVE: verify table replicated and DROP table on SLAVE only.
slave1 [localhost] {msandbox} (test) > show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
slave1 [localhost] {msandbox} (test) > drop table test.t1;
Query OK, 0 rows affected (0.02 sec)
####On MASTER
master [localhost] {root} ((none)) > grant select on test.t1 to msandbox@'127.%';
###On SLAVE check the replication status,
5.7:
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 1146
Last_Error: Error 'Table 'test.t1' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON `test`.`t1` TO 'msandbox'@'127.%''
[7 Nov 2021 7:20]
MySQL Verification Team
Thank you for the feedback. Verified as described. regards, Umesh
[10 Nov 2021 9:15]
Sven Sandberg
Posted by developer: Thank you for the bug report. AFAIU, the expectation is that SQL admin statements that implicitly update tables in the mysql database, such as GRANT, will be excluded by using a filter like replicate-wild-ignore-table=mysql.% However, replication filters don't work that way. Table-level filters are evaluated only for tables that are mentioned explicitly in the query. (And only for tables which the query actually operates on - not on test.t1 in the GRANT statement since the query does not actually operate on test.t1 - although this is not the reason for the error.) Since GRANT statements don't mention the grant tables in the query, the mysql.% filter does not help. The tool we have for achieving what I think was intended here, is to use a database filter. For DDLs and admin statements, database filters operate on the *used* database (as in `USE db`), so the following procedure would filter out the GRANT statement: - Configure: replicate-ignore-db=nonreplicated - Execute: USE `nonreplicated`; GRANT ...; This only works when the replication admin has sufficient control over the SQL, so that they can enforce the discipline to always execute USE `nonreplicated` before any GRANT or other admin statement. We do not have other tools to achieve this. The manual is does not describe these aspects, so changing this to a documentation bug.
[12 Jan 2022 15:56]
Margaret Fisher
Posted by developer: Thanks for the report! I've added the following which will show when the new release documentation is published: MySQL 8.0 Reference Manual / Replication / Configuring Replication / Replication and Binary Logging Options and Variables / Replica Server Options and Variables https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_rep... Important: Table-level replication filters are only applied to tables that are explicitly mentioned and operated on in the query. They do not apply to tables that are implicitly updated by the query. For example, a GRANT statement, which updates the mysql.user system table but does not mention that table, is not affected by a filter that specifies mysql.% as the wildcard pattern. https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_rep... Important: Table-level replication filters are only applied to tables that are explicitly mentioned and operated on in the query. They do not apply to tables that are implicitly updated by the query. For example, a GRANT statement, which updates the mysql.user system table but does not mention that table, is not affected by a filter that specifies mysql.% as the wildcard pattern. If you need to filter out GRANT statements or other administrative statements, a possible workaround is to use the replicate-ignore-db filter. This filter operates on the default database that is currently in effect, as determined by the USE statement. You can therefore create a filter to ignore statements for a database that is not replicated, then issue the USE statement to switch the default database to that one immediately before issuing any administrative statements that you want to ignore. In the administrative statement, name the actual database where the statement is applied. For example, if replicate-ignore-db=nonreplicated is configured on the replica server, the following sequence of statements causes the GRANT statement to be ignored, because the default database nonreplicated is in effect: USE nonreplicated; GRANT SELECT, INSERT ON replicated.t1 TO 'someuser'@'somehost'; MySQL 8.0 Reference Manual / Replication / Replication Implementation / How Servers Evaluate Replication Filtering Rules / Evaluation of Table-Level Replication Options https://dev.mysql.com/doc/refman/8.0/en/replication-rules-table-options.html Important: Table-level replication filters are only applied to tables that are explicitly mentioned and operated on in the query. They do not apply to tables that are implicitly updated by the query. For example, a GRANT statement, which updates the mysql.user system table but does not mention that table, is not affected by a filter that specifies mysql.% as the wildcard pattern.
Description: With replicate-wild-ignore-table=mysql.% setting on replica and on source grant on table to user which is not exists on replica result in replication error. How to repeat: Test case: Source-Replica , On replica replicate-wild-ignore-table=mysql.% master [localhost] {msandbox} ((none)) > create table test.t1(c1 int); Query OK, 0 rows affected (0.05 sec) slave1 [localhost] {msandbox} (test) > show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.01 sec) slave1 [localhost] {msandbox} (test) > drop table test.t1; Query OK, 0 rows affected (0.02 sec) master [localhost] {root} ((none)) > grant select on test.t1 to msandbox@'127.%'; 5.7: Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 1146 Last_Error: Error 'Table 'test.t1' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON `test`.`t1` TO 'msandbox'@'127.%'' Skip_Counter: 0 Exec_Master_Log_Pos: 4409 Relay_Log_Space: 892 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Error 'Table 'test.t1' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON `test`.`t1` TO 'msandbox'@'127.%'' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 8.0: Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 1146 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 5192. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 4962 Relay_Log_Space: 5959 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 5192. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 00024435-1111-1111-1111-111111111111 Master_Info_File: mysql.slave_master_info slave1 [localhost] {msandbox} (test) > select * from performance_schema.replication_applier_status_by_worker\G *************************** 1. row *************************** CHANNEL_NAME: WORKER_ID: 1 THREAD_ID: NULL SERVICE_STATE: OFF LAST_ERROR_NUMBER: 1146 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 5192; Error 'Table 'test.t1' doesn't exist' on query. Default database: ''. Query: 'GRANT SELECT ON `test`.`t1` TO 'msandbox'@'127.%'' LAST_ERROR_TIMESTAMP: 2021-10-28 19:35:48.134379 LAST_APPLIED_TRANSACTION: ANONYMOUS Suggested fix: If it's not a bug, This behaviour of grant with replication should be documented