Re: Reason for MySQL Replication Slave Crash
Dear Shawn, Thanks for the quick reply. To your points, First I have a query regarding your last line if I want to run master-master replication I should run it in active-passive mode. Does that mean that I should run only one master at a time. But I am doing it because I am not keeping two masters to distribute queries equally. For example:- I have two servers Server A and Server B I have db1 on Server A and db2 on Server B and replication db2 on Server A and db1 on Server B I am querying Server A for db1 and not Server B for db1. But I want Server B to be replicated same time with Server A. Same for Server B for db2. So ideally in this case I should not get duplicate entry error. Is that possible to be happened? I am not pretty sure that whether you have understood what I meant by above example. Please let me know if you have any questions. Thanks in advance. -- Regards, Manasi Save Quoting Shawn Green : Hello Manasi, Manasi Save wrote: > Dear All, > > I am using MySQL Master-Master Replication. > > Where most of the times it is happening that my slave crashes with two > errors either :- > > 1. Duplicate Entry (Error No:- 1062) > That means that a row with the same PRIMARY or UNIQUE key value(s) already exists on this server. Somehow you are not protecting yourself against writing the same things to both servers at the same time. > 2. Does not find the row for update or delete. (Error No :- 1032) > Same problem, in reverse. This time, though, the row you are trying to remove has already been removed. > Well I google the problem but unable to find exact reason behind this > behaviour of replication. > There is never "an exact reason" for this type of problem. It is a well-known engineering requirement that when replicating MySQL servers in a ring that you absolutely must avoid changing the same row of data (as identified by the tuple used for either the PRIMARY or UNIQUE keys) on both servers at nearly the same time. Your MASTER-MASTER configuration is simply a two-element ring configuration. > I read somewhere that this can happen when relay-log.info does not get > updated. But did not find any reason of how to deal with it. > > Does anyone faced similar type of issue in MySQL Replication. Any input > will be a great help. Here are my suggestions. 1) Read how the replication systems of MySQL actually work. Only completed changes to the database are written to the binary log as either statements (to be repeated on the slave) or as row deltas (to be applied by the slave to its data). Those binary log entries are spooled asynchronously to the slave where they are buffered into the relay logs. One the slave a second thread (different than the one used to fill up the relay logs with binary log events) then steps through the relay logs one statement or change at a time. http://dev.mysql.com/doc/refman/5.1/en/replication.html http://dev.mysql.com/doc/refman/5.1/en/replication-implementation-details.html 2) Read the specific sections and FAQs about ring-based replication. There are some good things you can configure that will mitigate, but not eliminate, your exposure to the errors you reported above. http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-5 http://dev.mysql.com/doc/refman/5.1/en/replication-features.html 3) Then read all of the warnings from other sites that tell you how to configure this type of replication ring. Here's just one: http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ring-based replication has many potential problems and very few benefits. It is a very difficult configuration to use properly. I do not recommend it for most purposes. The fact that you did not check the binary log entries against the actual data to detect that the duplicates or deletions were already on the table (and probably caused by another session) implies to me that your administrative skills may not yet be ready for this particular challenge. May I recommend that you switch back to the much easier to maintain master-slave replication configuration? If not that, at least use your masters in an active/passive mode, not active/active. Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
Martijn Tonies wrote: For example, the Firebird DBMS stores (longer) Blob data not right there in the record, so whenever you don't request the blob (that is, not selecting it), it ignores it completely and it can go through the file quickly. As do most of the MySQL storage engines. Cheers, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reason for MySQL Replication Slave Crash
Hello Manasi, Manasi Save wrote: Dear All, I am using MySQL Master-Master Replication. Where most of the times it is happening that my slave crashes with two errors either :- 1. Duplicate Entry (Error No:- 1062) That means that a row with the same PRIMARY or UNIQUE key value(s) already exists on this server. Somehow you are not protecting yourself against writing the same things to both servers at the same time. 2. Does not find the row for update or delete. (Error No :- 1032) Same problem, in reverse. This time, though, the row you are trying to remove has already been removed. Well I google the problem but unable to find exact reason behind this behaviour of replication. There is never "an exact reason" for this type of problem. It is a well-known engineering requirement that when replicating MySQL servers in a ring that you absolutely must avoid changing the same row of data (as identified by the tuple used for either the PRIMARY or UNIQUE keys) on both servers at nearly the same time. Your MASTER-MASTER configuration is simply a two-element ring configuration. I read somewhere that this can happen when relay-log.info does not get updated. But did not find any reason of how to deal with it. Does anyone faced similar type of issue in MySQL Replication. Any input will be a great help. Here are my suggestions. 1) Read how the replication systems of MySQL actually work. Only completed changes to the database are written to the binary log as either statements (to be repeated on the slave) or as row deltas (to be applied by the slave to its data). Those binary log entries are spooled asynchronously to the slave where they are buffered into the relay logs. One the slave a second thread (different than the one used to fill up the relay logs with binary log events) then steps through the relay logs one statement or change at a time. http://dev.mysql.com/doc/refman/5.1/en/replication.html http://dev.mysql.com/doc/refman/5.1/en/replication-implementation-details.html 2) Read the specific sections and FAQs about ring-based replication. There are some good things you can configure that will mitigate, but not eliminate, your exposure to the errors you reported above. http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-5 http://dev.mysql.com/doc/refman/5.1/en/replication-features.html 3) Then read all of the warnings from other sites that tell you how to configure this type of replication ring. Here's just one: http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ring-based replication has many potential problems and very few benefits. It is a very difficult configuration to use properly. I do not recommend it for most purposes. The fact that you did not check the binary log entries against the actual data to detect that the duplicates or deletions were already on the table (and probably caused by another session) implies to me that your administrative skills may not yet be ready for this particular challenge. May I recommend that you switch back to the much easier to maintain master-slave replication configuration? If not that, at least use your masters in an active/passive mode, not active/active. Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Reason for MySQL Replication Slave Crash
Dear All,I am using MySQL Master-Master Replication. Where most of the times it is happening that my slave crashes with two errors either :- 1. Duplicate Entry (Error No:- 1062)2. Does not find the row for update or delete. (Error No :- 1032)Well I google the problem but unable to find exact reason behind this behaviour of replication.I read somewhere that this can happen when relay-log.info does not get updated. But did not find any reason of how to deal with it. Does anyone faced similar type of issue in MySQL Replication. Any input will be a great help.Thanks in advance.--Regards,Manasi Save
Re: how things get messed up
Sir, Thanks for your suggestion, I will go for blob storage, because our application will maintain the data on yearly basis[stupersonal2008, stupersonal2009 etc.]. So i feel we may not face such kind of performance issue in our application. Thank you VIKRAM A From: Johan De Meersman To: Vikram A Cc: MY SQL Mailing list Sent: Fri, 12 February, 2010 2:23:01 PM Subject: Re: how things get messed up On Fri, Feb 12, 2010 at 9:19 AM, Vikram A wrote: I am in the situation to storing student and staff images. every year 2000 new photos has to be added in our application. > >Can i have your suggestion, which is the best one, storing as a blob Or using >NFS? >>It will be great help to me, because such experts are sharing your own >>experience on this binary storage issue. > I never said you had to grovel, though :-p This whole thread has been a discussion of just that. My personal opinion is that it's better to store binary objects (like images) out-of-band, for instance on an NFS system like you suggest. Other people on the list have made their own arguments for BLOB storage. In the end, it's down to your own situation and decisions, but I will keep defending the position that filesystems are made for storing files, and databases are made for storing data - it saves you on both database and PHP requests, as (from a web point of view) you can't return the image data inside your HTML - it requires a second HTTP call. Filesystem image serving, however, could perfectly be offloaded to a subdomain that runs a lightweight, threaded HTTP server that need not run the heavy PHP processes. You could even run that on your NFS server, if you want. If you do go for BLOBs, though, for god's sake keep them in a separate table, lest you fragment your datafiles. Split records are a disaster for performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: how things get messed up
On Fri, Feb 12, 2010 at 9:19 AM, Vikram A wrote: > I am in the situation to storing student and staff images. every year 2000 > new photos has to be added in our application. > > Can i have your suggestion, which is the best one, storing as a blob Or > using NFS? > It will be great help to me, because such experts are sharing your own > experience on this binary storage issue. > I never said you had to grovel, though :-p This whole thread has been a discussion of just that. My personal opinion is that it's better to store binary objects (like images) out-of-band, for instance on an NFS system like you suggest. Other people on the list have made their own arguments for BLOB storage. In the end, it's down to your own situation and decisions, but I will keep defending the position that filesystems are made for storing files, and databases are made for storing data - it saves you on both database and PHP requests, as (from a web point of view) you can't return the image data inside your HTML - it requires a second HTTP call. Filesystem image serving, however, could perfectly be offloaded to a subdomain that runs a lightweight, threaded HTTP server that need not run the heavy PHP processes. You could even run that on your NFS server, if you want. If you do go for BLOBs, though, for god's sake keep them in a separate table, lest you fragment your datafiles. Split records are a disaster for performance. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: how things get messed up
Sir, I am in the situation to storing student and staff images. every year 2000 new photos has to be added in our application. Can i have your suggestion, which is the best one, storing as a blob Or using NFS? It will be great help to me, because such experts are sharing your own experience on this binary storage issue. Thank you. VIKRAM A From: Johan De Meersman To: Martijn Tonies Cc: mysql@lists.mysql.com Sent: Fri, 12 February, 2010 1:09:32 PM Subject: Re: how things get messed up On Fri, Feb 12, 2010 at 8:27 AM, Martijn Tonies wrote: > Sounds logical, what's also nice to see, is that even though people here > tend to say "don't put binaries in the database", apparently Facebook > thought it would be nice to do so (for all sorts of reasons) and even took > the time to write their own blob storage mechanism ;-) The whole point is that they *aren't*' putting blobs in their database - that has way too much overhead. They're using a custom service that does nothing but "read from byte X to byte Y". No concepts of tablespaces, integrity, indices, whatever. The only thing they store in their database, is the start- and end-byte of each image. I doubt they even took it as far as to write a plugin engine - that would again bring too much overhead. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
