List:Replication« Previous MessageNext Message »
From:Mats Kindahl Date:January 19 2010 10:33am
Subject:Re: Don't replicating without USE db;
View as plain text  

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