MySQL Bugs: #30145: slave get wrong relay log position when it crashes under high load

Bug #30145	slave get wrong relay log position when it crashes under high load
Submitted:	31 Jul 2007 12:46	Modified:	9 Jun 2008 17:16
Reporter:	Oli Sennhauser	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We were running heavy tests on customers environment. Then we stopped the slave for 10 minutes and restarted it. so it was falling behind the master for around 500 seconds.
The we crashed the slave and got several times duplicate key errors!!!

How to repeat:
The following skript was used for the test: http://www.shinguz.ch/MySQL/commit_demo.txt

mysql> start slave;

master# ./commit_demo.pl -u root -c
master# while [ 1 ] ; do ./commit_demo.pl -u root -i 100 ; done

after a few seconds stop the slave so we can be sure it is properly working:

mysql> stop slave

then waiting for about 10 minutes.

mysql> start slave

we are now a few hundreds seconds behind master

slave# kill -9 <mysqld-pid>

angel process should restart mysqld.

then we get

mysql> show slave status\G

Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '7601' for key 1' on query. Default database: 'test'. Query: 'INSERT INTO test.commit_demo (id)
  VALUES ('NULL')'
               Skip_Counter: 0

mysql> SELECT * from test.commit_demo

shows that we are completly at a wrong position!!!

This could be repeated in about 50% of all cases!!!

Suggested fix:
relay log position and trx log should be written atomically!

This looks like a duplicate of bug #26540.

Oli,

Do you agree that this is a duplicate of bug #26540?

It could be the same bug...
So I will test the situation again if it is fixed in 5.2/6.0...

Hi Oli,
Did you do the test to verify if this bug is the same as http://bugs.mysql.com/bug.php?id=26540?. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".