[MDEV-11031] replicate_ignore_db does not work Created: 2016-10-11  Updated: 2016-10-11  Resolved: 2016-10-11

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.1.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Karl Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 14.04.5 LTS (10.1.12)


 Description   

Master:

M:7914290 [ci4]>SHOW VARIABLES LIKE '%replicate_ignore_db';
 
+---------------------+-------+
 
| Variable_name       | Value |
 
+---------------------+-------+
 
| replicate_ignore_db | tmp   |
 
+---------------------+-------+
 
1 row in set (0.00 sec)
 
 
 
M:7919802 [ci4]>SHOW processlist;
 
+----+---------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
| Id | User    | Host             | db   | Command     | Time | State                                                                 | Info                                                                                                 | Progress |
 
+----+---------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
[...]
 
| 27 | joe     | 172.18.0.5:37334 | ci4  | Query       | 1762 | Writing to binlog                                                     | CREATE TABLE IF NOT EXISTS tmp.sitemap_511_nl (
 
  `id_sitemap` bigint(20) unsigned NOT NULL AUTO_INC |    0.000 |
 
[...]
 
+----+---------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
12 rows in set (0.00 sec)

I cannot check the binlog to see if this statement was filtered later as mysqlbinlog is not in the package:

ubuntu@ip-172-31-30-16:/wp/ci$ mysqlbinlog ../data/mysql-bin.000001
 
The program 'mysqlbinlog' can be found in the following packages:
 
 * mysql-server-5.5
 
 * mariadb-server-5.5
 
 * mysql-server-5.6
 
 * percona-xtradb-cluster-server-5.5
 
Try: sudo apt-get install <selected package>

Actually. those tables sitemap* are present on the slave although they had been dropped before:

Slave:

S:421801 [ci4]>SHOW tables from tmp;
 
+----------------+
 
| Tables_in_tmp  |
 
+----------------+
 
| benchmark      |
 
| counter        |
 
| se_lng_ex_de   |
 
| sitemap_511_de |
 
| sitemap_511_en |
 
| sitemap_511_fr |
 
| sitemap_511_nl |
 
+----------------+
 
7 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-10-11 ]

Please check documentation to see how the replicate_ignore_db variable works, your expectations are wrong.
https://mariadb.com/kb/en/mariadb/replication-and-binary-log-server-system-variables/#replicate_ignore_db
http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_replicate-ignore-db
Here is the most relevant fragment, but make sure you've read it all:

Statement-based replication. Tells the slave SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master:

USE prices;
UPDATE sales.january SET amount=amount+1000;

The UPDATE statement is replicated in such a case because --replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered.

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