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

--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...

[Mats Kindahl]

Hi Markus!


I suppose that the table TestTable is a MyISAM table and that you are
using statement-based replication? If so, then it has nothing to do with
it being a beta.

Without knowing the details, the problem is along these lines: The
INSERT or transaction was partially completed when you hit the power
switch (as you mention). When the slave is then restarted, it will try
to execute the  transaction/statement again *from the beginning*, and
will fail since some of the rows were already added to the table. Here,
you are using a transaction, but the situation occurs also if you are
using MyISAM tables and a statement outside a transaction is interrupted
in middle of execution.

If you use only transactional tables or use row-based replication, you
should not have this problem.

Best wishes,
Mats Kindahl

--
Mats Kindahl
Replication Team
MySQL AB, www.mysql.com




Markus Wenke wrote:

> 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
>

--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...

[Markus Wenke]

Mats Kindahl schrieb:

> Hi Markus!
>
>
> I suppose that the table TestTable is a MyISAM table and that you are
> using statement-based replication? If so, then it has nothing to do
> with it being a beta.
>
> Without knowing the details, the problem is along these lines: The
> INSERT or transaction was partially completed when you hit the power
> switch (as you mention). When the slave is then restarted, it will try
> to execute the  transaction/statement again *from the beginning*, and
> will fail since some of the rows were already added to the table.
> Here, you are using a transaction, but the situation occurs also if
> you are using MyISAM tables and a statement outside a transaction is
> interrupted in middle of execution.
>
> If you use only transactional tables or use row-based replication, you
> should not have this problem.

I use InnoDB on Master and Slave:

           Name: TestTable
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 84716
 Avg_row_length: 43
    Data_length: 3686400
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 85205
    Create_time: 2006-08-31 10:08:12
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 202752 kB

But I use statement-based replication.


Thanks

Markus

--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...

[Mats Kindahl]

Markus Wenke wrote:

> Mats Kindahl schrieb:
>> Hi Markus!
>>
>>
>> I suppose that the table TestTable is a MyISAM table and that you are
>> using statement-based replication? If so, then it has nothing to do
>> with it being a beta.
>>
>> Without knowing the details, the problem is along these lines: The
>> INSERT or transaction was partially completed when you hit the power
>> switch (as you mention). When the slave is then restarted, it will
>> try to execute the  transaction/statement again *from the beginning*,
>> and will fail since some of the rows were already added to the table.
>> Here, you are using a transaction, but the situation occurs also if
>> you are using MyISAM tables and a statement outside a transaction is
>> interrupted in middle of execution.
>>
>> If you use only transactional tables or use row-based replication,
>> you should not have this problem.
>
> I use InnoDB on Master and Slave:
>
>           Name: TestTable
>         Engine: InnoDB
>        Version: 10
>     Row_format: Compact
>           Rows: 84716
> Avg_row_length: 43
>    Data_length: 3686400
> Max_data_length: 0
>   Index_length: 0
>      Data_free: 0
> Auto_increment: 85205
>    Create_time: 2006-08-31 10:08:12
>    Update_time: NULL
>     Check_time: NULL
>      Collation: latin1_swedish_ci
>       Checksum: NULL
> Create_options:
>        Comment: InnoDB free: 202752 kB
>
> But I use statement-based replication.
>

Strange, then it should work.

Could you send me a trace file and an error log and I'll see if I can
see anything obvious?

Best wishes,
Mats Kindahl

>
> Thanks
>
> Markus
>


--
Mats Kindahl
Replication Team
MySQL AB, www.mysql.com







--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...

[Markus Wenke]

Hi,

of course, but
 - how can I create a trace File? a strace of the mysqld? I think it
will be very big.
 - which debug-level?


My theory is, that last part of the binlog from the Master is anywhere
in cache (HD Cache, OS Cache, or Mysql-Cache) and after crash it isn't
on Disk, but in the table,....
 (I don't have a battery cache, and "hdparm -W0 /dev/hda" doesn't work
on my HD (SAMSUNG SP2504C))

Thanks

Markus Wenke





Mats Kindahl schrieb:

> Markus Wenke wrote:
>> Mats Kindahl schrieb:
>>> Hi Markus!
>>>
>>>
>>> I suppose that the table TestTable is a MyISAM table and that you
>>> are using statement-based replication? If so, then it has nothing to
>>> do with it being a beta.
>>>
>>> Without knowing the details, the problem is along these lines: The
>>> INSERT or transaction was partially completed when you hit the power
>>> switch (as you mention). When the slave is then restarted, it will
>>> try to execute the  transaction/statement again *from the
>>> beginning*, and will fail since some of the rows were already added
>>> to the table. Here, you are using a transaction, but the situation
>>> occurs also if you are using MyISAM tables and a statement outside a
>>> transaction is interrupted in middle of execution.
>>>
>>> If you use only transactional tables or use row-based replication,
>>> you should not have this problem.
>>
>> I use InnoDB on Master and Slave:
>>
>>           Name: TestTable
>>         Engine: InnoDB
>>        Version: 10
>>     Row_format: Compact
>>           Rows: 84716
>> Avg_row_length: 43
>>    Data_length: 3686400
>> Max_data_length: 0
>>   Index_length: 0
>>      Data_free: 0
>> Auto_increment: 85205
>>    Create_time: 2006-08-31 10:08:12
>>    Update_time: NULL
>>     Check_time: NULL
>>      Collation: latin1_swedish_ci
>>       Checksum: NULL
>> Create_options:
>>        Comment: InnoDB free: 202752 kB
>>
>> But I use statement-based replication.
>>
>
> Strange, then it should work.
>
> Could you send me a trace file and an error log and I'll see if I can
> see anything obvious?
>
> Best wishes,
> Mats Kindahl
>
>>
>> Thanks
>>
>> Markus
>>
>
>

--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...

[Mats Kindahl]

Markus Wenke wrote:
> Hi,
>
> of course, but
> - how can I create a trace File? a strace of the mysqld? I think it
> will be very big.
> - which debug-level?

Sorry, I was referring to using the --trace flag when starting server.
Otherwise, send me SQL to trigger the bug and I can generate such a file
myself.

Best wishes,
Mats Kindahl

>
>
> My theory is, that last part of the binlog from the Master is anywhere
> in cache (HD Cache, OS Cache, or Mysql-Cache) and after crash it isn't
> on Disk, but in the table,....
> (I don't have a battery cache, and "hdparm -W0 /dev/hda" doesn't work
> on my HD (SAMSUNG SP2504C))
>
> Thanks
>
> Markus Wenke
>
>
>
>
>
> Mats Kindahl schrieb:
>> Markus Wenke wrote:
>>> Mats Kindahl schrieb:
>>>> Hi Markus!
>>>>
>>>>
>>>> I suppose that the table TestTable is a MyISAM table and that you
>>>> are using statement-based replication? If so, then it has nothing
>>>> to do with it being a beta.
>>>>
>>>> Without knowing the details, the problem is along these lines: The
>>>> INSERT or transaction was partially completed when you hit the
>>>> power switch (as you mention). When the slave is then restarted, it
>>>> will try to execute the  transaction/statement again *from the
>>>> beginning*, and will fail since some of the rows were already added
>>>> to the table. Here, you are using a transaction, but the situation
>>>> occurs also if you are using MyISAM tables and a statement outside
>>>> a transaction is interrupted in middle of execution.
>>>>
>>>> If you use only transactional tables or use row-based replication,
>>>> you should not have this problem.
>>>
>>> I use InnoDB on Master and Slave:
>>>
>>>           Name: TestTable
>>>         Engine: InnoDB
>>>        Version: 10
>>>     Row_format: Compact
>>>           Rows: 84716
>>> Avg_row_length: 43
>>>    Data_length: 3686400
>>> Max_data_length: 0
>>>   Index_length: 0
>>>      Data_free: 0
>>> Auto_increment: 85205
>>>    Create_time: 2006-08-31 10:08:12
>>>    Update_time: NULL
>>>     Check_time: NULL
>>>      Collation: latin1_swedish_ci
>>>       Checksum: NULL
>>> Create_options:
>>>        Comment: InnoDB free: 202752 kB
>>>
>>> But I use statement-based replication.
>>>
>>
>> Strange, then it should work.
>>
>> Could you send me a trace file and an error log and I'll see if I can
>> see anything obvious?
>>
>> Best wishes,
>> Mats Kindahl
>>
>>>
>>> Thanks
>>>
>>> Markus
>>>
>>
>>
>
>

--
Mats Kindahl
Replication Team
MySQL AB, www.mysql.com







--
MySQL Replication Mailing List
For list archives: http://lists.mysql.com/replication
To unsubscribe:    http://lists.mysql.com/replication?unsub=lists@...