Bug #12209 replicate-wild-do-table=a%.% causes slave to ignore replicated changes
Submitted: 27 Jul 2005 15:32 Modified: 6 Feb 2006 18:30
Reporter: James Day Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.25-debug-log 4.0.24-standard-log OS:Microsoft Windows (windows for .25 linux for .24)
Assigned to: Andrei Elkin CPU Architecture:Any

[27 Jul 2005 15:32] James Day
Description:
Customer upgraded from linux 4.0.18 to 4.0.24 then found that the slave in an existing replication setup with replicate-wild-do-table=cache%.% stopped applying changes to the slave. Removing the replicate-wild-do-table=cache%.% or switching back to 4.0.18 caused updates to be applied normally.

Reproduced on Windows XP Pro using both replicate-wild-do-table=cache%.%  and replicate-wild-do-table=a%.% .

Not yet tested with 4.1.n or 5.0.n.

How to repeat:
On Windows create two 4.0.25 installations and make one the master and the other the slave, using different ports for each and different my.ini files roughly as described at http://dev.mysql.com/doc/mysql/en/multiple-windows-command-line-servers.html.

On the slave include replicate-do-db=test in my.ini. On the master create a table in the test db and add a record, observe that the creation and new record are added to the slave.

Shut down the slave and add replicate-wild-do-table=a%.% to its my.ini. Start up the slave then add another record to the table on the master. Observe that it is not replicated at the slave. Delete the new record on the master.

Shut down the slave and comment out the replicate-wild-do-table=a%.%. Start it up again and add a record at the master. Observe that it is replicated to the slave.
[1 Aug 2005 0:35] James Day
Now a test set which should result in updates being applied.

replicate-wild-do-table=t%.%
replicate-do-db=test

use test;
CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM;

insert into t (s) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f');

insert into t (s) values ('g');

The values were inserted. This passes the replication rules: default database is test which matches replicate-do-db=test and table is t which matches replicate-wild-do-table=t%.% .

So, working as documented.

Next, a different table name in default database test.

use test;
CREATE TABLE u (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM;

insert into u (s) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f');

The table u was created on the slave and the records were present. That's expected, all tables in database test match the replicate-wild-do-table rule.

Next stop, narrow down the replicate-wild-do-table to t%.t%. t is expected to succeed with this, u is expected to fail.

replicate-wild-do-table=t%.t%
replicate-do-db=test

insert into t (s) values ('h');

This was replicated to the slave, the expected result. Worked as documented.

insert into u (s) values ('h');

This was not replicated, the expected result. u does not match t%.t%. Working as documented.
[1 Aug 2005 0:43] James Day
The original report which prompted this bug entry involved the following pair type:

replicate-do-db=Dash
replicate-wild-do-table=dash%.%

There is no possible update which is able to match both a replicate-do-db name of Dash and a replicate-wild-do-table of database name like dash%. The case  of Dash and dash differs, so the expected result is no updates being replicated.

That's what was happening, so this is working as documented.

The original reporter had had this working on a previous version, so it may be that a bug where it did not work as documented was fixed.

After allowing some time for discussion about whether it should work as documented, this one looks destined for docs.
[2 Aug 2005 16:25] Alexander Pachev
What queries exactly are and are not replicated? Could you provide a test case in the mysqltest format? ( see mysql-test/t/rpl*.test in the source tree)
[9 Aug 2005 1:29] Dathan Pattishall
I can duplicate this on linux with batch replace into of 1000 entries.
Replicate events do not take.

mysql-4.0.25

The master is setup as a submaster to a main master replicating 1 table.
log-bin
log-slave-updates  set in the my.cnf

A slave is connected to the submaster, the slave gets all updates from the log-slave-update table but fails to get updates from the table specific to the sub master.

The server is also a mixture of INNODB and myISAM.
[13 Aug 2005 6:50] James Day
Alexander, Dathan,

Alexander, while this one caused customer, dean and myself to initially think it was a bug, so far more careful reading of the detailed walk through of how replication rules work in the manual hasn't found any case where the behavior differs from the documentation. It appears that this may be a case of surprising implementation rather than not working as documented. Can you explain why the inclusion rules work as they do, since what seems to be surprising people is the way you need both a database and a table rule to get a table included?

Dathan, thanks for checking. Can you review this possible documentation change and the detailed walkthrough in the manual at http://dev.mysql.com/doc/mysql/en/replication-options.html to see if your test case is actually not working as documented? If it doesn't work as documented, please let me know all the replication options you are using, since the interactions can sometimes be surprising. Then I'll try to reproduce here and verify it.

Here's some proposed changed documentation wording which may clarify how it works:

 -----

In summary, the database and table inclusion rules are AND rules, not OR rules.
Both default database and table being changed rules must match, not just one of
them. Use one database include rule and you must have an include rule for every
default database you want replicated. Use one table include rule and you must
have a table include rule for every table you want replicated. Specifically: replicate-*-table disables replicate-do-db: if you have any *-table rule you'll need both the table rule for all tables and the do-db rule.

The --replicate-*-ignore-* exclusion rules are OR rules. Match any single test and
it's ignored. This is usually much easier to understand and is the preferred approach if it's practical.

 -----
[28 Aug 2005 4:14] Alexander Pachev
In essence, replicate-do-db is obsoleted by replicate-wild-do-table=db.% for the most part. The difference is that the former uses the currently selected databse, while the latter actually checks the table that is being updated. Most of the time the latter version is more useful. The rules for do, however, are not AND - they are OR. If one of the rules is matched, the update is applied. Similarly, for ignore - if one of the rules matches, the update is ignored. 

The current implementation works as follows:  (see tables_ok() in sql/slave.cc for details): if we have do-table rules, check the list. If we found a match, the table can be updated. If no match or no do-table rules, check if we have ignore-table rules. If there is a match, the update is ignored. If no match or no ignore rules, go to the wild rules, and use the same logic, except it is now wild - wild cards have a special meaning.

It is generally a good idea to not mix different types of rules, eg. if you are using wild rules already, stick to the wild. If you can list all of the tables easily with do-table, stick to do-table. If all of your queries always update in the current database, and you only need to include/exclude based on the database, use db rules. Be careful mixing do and ignore - make sure they are not conflictling. Otherwise, it is very easy to get confused.

If you really have to mix the rule types, and you are sure you can track them and keep them from overlapping each other and having unexpected matches, do it, but try to avoid it if possible.

I am going to move this bug to documenting for now.
[28 Aug 2005 4:23] Alexander Pachev
A correction - I misread the comment above about AND and db/table do rules. It was correct. db rules are checked first and then if they permit the query, the table rules are checked. Think of it this way - we first did db rules, then realized they were not adequate, so we added the table rules, but we still had to support the db rules. I recommend to use replicate-wild-do-table=db1.% instead of replicate-do-db=d1 to avoid confusion (although the latter has less CPU overhead which is very small compared to the time it takes to run the query, but if you care, and know what you are getting into, feel free to use the latter).
[28 Aug 2005 14:33] James Day
Thanks. Now for docs to find a better way to explain this, based on the suggestions and your description.
[30 Dec 2005 17:23] Andrei Elkin
In hunting over 9483/180 hit the bug with 5.0-tree (my build on Dec 29).
Symtoms as reported.
[6 Feb 2006 18:30] Paul Dubois
I've updated http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
as follows:

- Added comment recommending not to mix do/ignore rules or wild/non-wild
rules.
- Added some text pointing out some implications of the rule-evaluation
procedure. (For example, if there are any "do" table rules, a statement must
match one of them if it is to be executed.)
- Added some examples, including one that explicitly points out the behavior
that was felt in this bug report to be counterintuitive. (That is, the case where
there is a "do" rule for one database and a "do" table rule for another database,
resulting in no replication of tables in the first database.)

These changes will show up the next time the manual is rebuilt.