List:Replication« Previous MessageNext Message »
From:Rick James Date:January 19 2010 5:07pm
Subject:Re: Don't replicating without USE db;
View as plain text  
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
>> 
>> 

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