Re: #216 [NEW]: Replication issues with "UPDATE database.table"

> In the example I submitted to you for 'friendly viewing' I used a database
> name 'enquiry', in real life the database name is 'pdc' and table is
> 'sdetails'.
>
> Master:
> =======
>
> server-id=1
> log-bin=d:/mysql/logs/replication.log
> binlog-do-db=pdc

> > How to repeat:
> > d:\mysql\bin\mysql -e "UPDATE pdc.sdetails SET dMake = 'Panasonic' WHERE
> > dID = 7;"
> > ..causes the master table to update, but not the slave.
> >
> > Changing the syntax to:
> > d:\mysql\bin\mysql -e "USE pdc; UPDATE sdetails SET dMake = 'Panasonic'
> > WHERE dID = 7;"
> > ..works.
> >

ok, I had guessed well.
Please (re-)read
http://www.mysql.com/doc/en/Replication_Options.html
http://www.mysql.com/doc/en/Binary_log.html
and you will see that binlog-do-db and binlog-ignore-db
only check the _default_ database (the one you select with 'USE')
to decide whether to write to the binlog or not (hence replicate
or not). As stated in the manual,

"...Note that if you
use this, you should ensure that you do updates only in the current
database.... Example: binlog-do-db=sales"

i.e. updates outside of the USEd databases won't work (will not be
filtered the way you would expect them to be).

If you want to do such updates (UPDATE pdc.sdetails) and filter them,
then you must remove this binlog-do-db option from your master's
configuration, and filter only on the slave. Your slave config is :

Slave:
======
replicate-do-db=simon
replicate-wild-do-table=simon.%
replicate-do-db=pdc
replicate-wild-do-table=pdc.%
replicate-do-db=pdcdatabases
replicate-wild-do-table=pdcdatabases.%

Tip : it may not be necessary to use replicate-do-db
as you already use replicate-wild-do-table for the same databases.

Only replicate-(wild-)(do|ignore)-table options will work for
'UPDATE pdc.sdetails' ; replicate-do-db will not work for these updates,
as explained in the manual.

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org