List:Replication« Previous MessageNext Message »
From:Alper Oguz Date:January 19 2010 8:29am
Subject:Re: Don't replicating without USE db;
View as plain text  
Thank you for explanations Rick.

But I don't understand such as Case 2. "INSET INTO anotherdb.tbl..." contains 
database information. Why it's not write that info to the binlogs? Why Mysql 
still need to USE db command? I think it's very stupidly.. :(

thank you again

Alper


Suresh Kuna wrote On 07-01-2010 07:40:
> Good use case explanation Rick.
>
> Suresh Kuna
> MySQL DBA
>
> On Thu, Jan 7, 2010 at 12:13 AM, Rick James <rjames@stripped
> <mailto:rjames@stripped>> wrote:
>
>     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
>     <mailto:database100@stripped>]
>      > Sent: Wednesday, January 06, 2010 10:25 AM
>      > To: Alper Oguz; replication@stripped
>     <mailto: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
>     <mailto:rjames@stripped>> wrote:
>      >
>      > > From: Rick James <rjames@stripped
>     <mailto:rjames@stripped>>
>      > > Subject: RE: Don't replicating without USE db;
>      > > To: "Alper Oguz" <alper2@stripped
> <mailto:alper2@stripped>>,
>     replication@stripped <mailto: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
>     <mailto:alper2@stripped>]
>      > > > Sent: Wednesday, January 06, 2010 6:15 AM
>      > > > To: replication@stripped
>     <mailto: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 <mailto:alper2@stripped>
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