List:Replication« Previous MessageNext Message »
From:Rick James Date:January 6 2010 6:43pm
Subject:RE: Don't replicating without USE db;
View as plain text  
Assuming you have binlog/replication-do/ignore...
(If you don't, then everything gets replicated.)

Case 1: 
USE dbname;
INSERT INTO dbname.tbl ...
This case is rather redundant.  Except that replication looks at the USE to decide whether
to replicate the INSERT.  (One can argue that this is a stupid design.  It does bite a lot
of users.)

Case 2:
USE dbname;
INSERT INTO anotherdb.tbl ...
The INSERT works with 'anotherdb', but replication could be fooled (see above).

Case 3:
USE dbname;
INSERT INTO tbl ...
Here, 'dbname' is needed to say which 'tbl' is to be INSERTed into.  There is no ambiguity
in replication.

Case 4:
INSERT INTO onedb.tbl SELECT * FROM anotherdb.foo;
If onedb is replicated but anotherdb is not, you have a problem.

Cases:
LOAD DATA; SELECT INTO OUTFILE; etc
These have various issues.

 
Rick James
MySQL Geeks - Consulting & Review

 

> -----Original Message-----
> From: Database System [mailto:database100@stripped] 
> Sent: Wednesday, January 06, 2010 10:25 AM
> To: Alper Oguz; replication@stripped; Rick James
> Subject: RE: Don't replicating without USE db;
> 
> If we use USE db, then in the code still use db.table, will 
> that able to be replicated?
> 
> --- On Wed, 1/6/10, Rick James <rjames@stripped> wrote:
> 
> > From: Rick James <rjames@stripped>
> > Subject: RE: Don't replicating without USE db;
> > To: "Alper Oguz" <alper2@stripped>, replication@stripped
> > Date: Wednesday, January 6, 2010, 11:07 AM
> > Workaround:  issue "USE db"
> > before the INSERT.
> > 
> >  
> > Rick James
> > MySQL Geeks - Consulting & Review
> > 
> >  
> > 
> > > -----Original Message-----
> > > From: Alper Oguz [mailto:alper2@stripped] 
> > > Sent: Wednesday, January 06, 2010 6:15 AM
> > > To: replication@stripped
> > > Subject: Don't replicating without USE db;
> > > 
> > > Hello,
> > > 
> > > I've two Mysql 5 servers that work on master-master 
> > > replication. Both are 
> > > Mysql 5.0.51a on Debian 5 machines.
> > > 
> > > I've discover this issue on my system:
> > > 
> > > http://bugs.mysql.com/bug.php?id=37483
> > > 
> > > Our developers sometimes use queries like INSERT INTO
> > 
> > > db.table... and these 
> > > rows aren't replicated to the second server.
> > > 
> > > There is a suggestion to remove binlog-do-db lines
> > from 
> > > my.cnf end of that bug 
> > > entry. (I know, this is not a bug)
> > > 
> > > But how to work replication without binary logs? Could
> > you 
> > > please inform me?
> > > 
> > > thank you
> > > 
> > > 
> > > -- 
> > > Alper Oguz
> > > alper2@stripped
> > > 
> > > -- 
> > > MySQL Replication Mailing List
> > > For list archives: http://lists.mysql.com/replication
> > > To unsubscribe:    
> > > http://lists.mysql.com/replication?unsub=1
> > > 
> > > 
> > 
> > --
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:    
> http://lists.mysql.com/replication?unsub=1
> > 
> > 
> 
> 
>       
> 
Thread
Don't replicating without USE db;Alper Oguz6 Jan
  • RE: Don't replicating without USE db;Rick James6 Jan
    • RE: Don't replicating without USE db;Database System6 Jan
      • RE: Don't replicating without USE db;Rick James6 Jan
        • Re: Don't replicating without USE db;Suresh Kuna7 Jan
          • Re: Don't replicating without USE db;Alper Oguz19 Jan
            • Re: Don't replicating without USE db;Mats Kindahl19 Jan
              • Re: Don't replicating without USE db;Song Voong19 Jan
                • Re: Don't replicating without USE db;Rick James19 Jan
                  • Re: Don't replicating without USE db;Jeff Smelser19 Jan
                • Re: Don't replicating without USE db;Mats Kindahl19 Jan