From: Rick James Date: January 19 2010 5:07pm Subject: Re: Don't replicating without USE db; List-Archive: http://lists.mysql.com/replication/1789 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 > Date: Tue, 19 Jan 2010 08:01:44 -0800 > To: Mats Kindahl > Cc: Alper Oguz , Rick James , > > 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 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 >>> > 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 >>>> ] >>>>> Sent: Wednesday, January 06, 2010 10:25 AM >>>>> To: Alper Oguz; 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 >>> > wrote: >>>>> >>>>>> From: Rick James >>> > >>>>>> Subject: RE: Don't replicating without USE db; >>>>>> To: "Alper Oguz" >, >>>> 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 >>>> ] >>>>>>> Sent: Wednesday, January 06, 2010 6:15 AM >>>>>>> To: 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 >>> >> >> -- >> 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=song.voong@stripped >> >>