Slave crash

[Markus Wenke]

Hi,

I use a simple Master-Slave-Replication with one Master and one Slave.(
MySQL 5.1.11-beta-log)

If I crash's the slave (Power off) while some INSERT's in a Transaction
are running, and restarts the slave after that, the slave don't start:
060901 8:57:27 [ERROR] Slave: Error 'Duplicate entry '85105' for key
'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO
TestTable (text) values ("Test: 1 257")', Error_code: 1062
060901 8:57:27 [Warning] Slave: Duplicate entry '85105' for key
'PRIMARY' Error_code: 1062
060901 8:57:27 [Warning] Slave: Unknown error Error_code: 1105
060901 8:57:27 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with "SLAVE START".
We stopped at log 'Master-bin.000008' position 3672742


I use "sync_binlog=1" on Master and Slave.

Is it a Problem because I use a "beta"-Version?


thanks in advance

Markus

[Axel Schwenke]

"Markus Wenke" <M.Wenke@web.de> wrote:

>
> I use a simple Master-Slave-Replication with one Master and one Slave.(
> MySQL 5.1.11-beta-log)
>
> If I crash's the slave (Power off) while some INSERT's in a Transaction
> are running, and restarts the slave after that, the slave don't start:
> 060901 8:57:27 [ERROR] Slave: Error 'Duplicate entry '85105' for key
> 'PRIMARY'' on query. Default database: 'test'. Query: 'INSERT INTO
> TestTable (text) values ("Test: 1 257")', Error_code: 1062
> 060901 8:57:27 [Warning] Slave: Duplicate entry '85105' for key
> 'PRIMARY' Error_code: 1062
> 060901 8:57:27 [Warning] Slave: Unknown error Error_code: 1105
> 060901 8:57:27 [ERROR] Error running query, slave SQL thread aborted.
> Fix the problem, and restart the slave SQL thread with "SLAVE START".
> We stopped at log 'Master-bin.000008' position 3672742
>
> I use "sync_binlog=1" on Master and Slave.
>
> Is it a Problem because I use a "beta"-Version?

This is a general replication problem. The slave keeps track of its
current position in the binlog by writing the position to the files
'master.info' (I/O thread) and 'relay-log.info' (SQL thread).
However those files are not written synchronously.

If the slave experiences an unclean shutdown, chances are that the
real position of the slave in the binlog is behind the one recorded in
'relay-log.info'. In consequence the slave will do some statements
twice. If there are transactions, they may be done twice (as a whole).
This may lead to errors like above, or - even worse - to silent data
corruption.

So if your slave crashes hard, you better restore it from a backup.


Announcement: MySQL 5.1 introduces row based replication (RBR,
contrary to current statement based replication SBR). RBR replicates
the data of modified rows and thus avoids inconsistencies from certain
kinds of replication errors.

Example: with SBR the statement UPDATE foo SET bar=bar*2 WHERE baz=42
will lead to increasing inconsistency between master and slave if there
was a difference before or if this statement is executed twice on the
slave. With RBR master and slave will be in sync afterwards, no matter
what they were before.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]