Reason for MySQL Replication Slave Crash

--=_5k6o35evcmps
Content-Type: text/plain;
charset=UTF-8
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit


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

--=_5k6o35evcmps
Content-Type: multipart/related;
boundary="=_5nof11thwhds";
start="5npaxj8y65ts@mail.artificialmachines.com"
Content-Transfer-Encoding: 7bit

This message is in MIME format.

--=_5nof11thwhds
Content-Type: text/html;
charset=UTF-8
Content-Description: HTML Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-ID: 5npaxj8y65ts@mail.artificialmachines.com

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: 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-implement ation-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-rep lication.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=gcdmg-mysql-2@m.gmane.org

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-implement ation-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-rep lication.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=gcdmg-mysql-2@m.gmane.org