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 <shawn.gr...@sun.com>:
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
