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
>
>
