Song Voong wrote:
> Does this only happen on 5.0? In 5.1 using the row replication will fix
> this?
This change is only for row-based, for statement-based the behavior is
the same in 5.1 as in 5.0 (and earlier versions).
Just my few cents,
Mats Kindahl
>
> 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
>>
>>
>
--
Mats Kindahl
Senior Software Engineer
Database Technology Group
Sun Microsystems
