List:Replication« Previous MessageNext Message »
From:Jeff Smelser Date:January 19 2010 5:13pm
Subject:Re: Don't replicating without USE db;
View as plain text  
When I set this up way back, this was a set up problem. If you set up using
only certain tables on the source, this was a problem. Its better to set up
replication for the entire thing, then eliminate tables/db's, If possible.

On Tue, Jan 19, 2010 at 11:07 AM, Rick James <rjames@stripped> wrote:

> We are using 5.1 heavily.  As far as I know, the USE is still required.
> We are not using ROW based replication.  I would worry that the USE and the
> binlog-do/ignore might be too closely tied to not have that mistake there,
> too.
>
> --
> Rick James
> MySQL Geek
>
>
>
> > From: Song Voong <song.voong@stripped>
> > Date: Tue, 19 Jan 2010 08:01:44 -0800
> > To: Mats Kindahl <mats@stripped>
> > Cc: Alper Oguz <alper2@stripped>, Rick James
> <rjames@stripped>,
> > <replication@stripped>
> > Subject: Re: Don't replicating without USE db;
> >
> > Does this only happen on 5.0? In 5.1 using the row replication will fix
> > this?
> >
> > On Tue, Jan 19, 2010 at 2:33 AM, Mats Kindahl <mats@stripped> wrote:
> >
> >>
> >>
> >> Alper Oguz wrote:
> >>> 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.. :(
> >>
> >> I'm not going to say that it's not stupid (because it is), but to
> >> extracting the database require additional processing even for
> >> statements that do not require it. This introduces a penalty to all
> >> statement even if they do not qualify the table name.
> >>
> >> In addition, multi-table updates will have problem in deciding if the
> >> statement would be filtered or not if the tables are in different
> >> databases, and using a special rule for updates would just be confusing.
> >>
> >> These two things, taken together, was probably the reason that it was
> >> not added initially.
> >>
> >> Now, there are users that rely on this behavior and therefore it
> >> cannot be removed.
> >>
> >> If you want to have filtering correct, use row-based replication.
> >> There the filtering is on the actual table and database, and not on
> >> the current database.
> >>
> >> Just my few cents,
> >> Mats Kindahl
> >>
> >>>
> >>> 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>
> >>>
> >>
> >> --
> >> Mats Kindahl
> >> Senior Software Engineer
> >> Database Technology Group
> >> Sun Microsystems
> >>
> >> --
> >> 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