Slave SQL Thread stops because of lock
Hi,
I seem to have stumbled upon a recurring problem with replication.
The replicated tables are MyISAM.
When the SQL thread updates tables, it puts a lock on them.
I can see that by looking at the process list.
When lauching a big SELECT query, the SQL thread tries to put a lock on a table that is used in the query to update it.
If the query goes on too long, the SQL thread just dies with the following error appearing in the error log (and also if I try to restart the thread):
051006 10:17:47 [ERROR] Slave: Error 'Duplicate entry '-1' for key 2' on query. Default database: 'imtprod'. Query: 'INSERT INTO `RelayTestsLog` (`DieId`, `JigId`, `PiggyBoardId`, `ActContext`, `ActCurrentModelId`, `ActTimingModelId`, `TestType`, `TTParamValueRev`, `TTParamNameId`, `ParentLogId`, `TestSeqNbr`,`ErrorNumber`, `ComputerName`, `UserName`, `NbCycles`, `SampleRate`, `LimitContext`, `EnvContext`, `AppLabel`, `TestedTime`, `FinishedTime`, `Status`) VALUES (556700, -1, -1, -1, -1, -1, 3003, -1, -1, 6959322, -1, 0, "IMTINSP", "imtuser", -1, -1, "", "", "ProdExp-2.4.0", "2005-10-06 08:56:50", "2005-10-06 08:56:50", 0)', Error_code: 1062
051006 10:17:47 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'imtprod-bin.000065' position 285498569
This is a major problem because the SQL thread is constantly updating and locking tables, so any big query on the slave will kill the SQL thread.
Does anyone have an idea how to fix this issue ?