Our replication seems to randomly stop/fail; a restart of the server resolves the issue (temporarily); this morning I picked up on the conversation, noting that a reboot doesn't actually fix the issue - from the Windows Logs, we see the following entries.
MariaDB 10.1.14
Would be interested in reasonable ideas on how we might fix the problem...
Slave SQL: Could not execute Update_rows_v1 event on table [[database]].[[table]]; Deadlock found when trying to get lock; try restarting transaction, Error_code: 1213; handler error HA_ERR_LOCK_DEADLOCK; the event's master log master1-bin.002810, end_log_pos 43437750, Gtid 0-1-145033132, Internal MariaDB error code: 1213
Slave SQL: Lock wait timeout exceeded; try restarting transaction, Gtid 0-1-145094499, Internal MariaDB error code: 1205
Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master1-bin.002810' position 91699198
Slave SQL: Could not execute Update_rows_v1 event on table [[database]].[[table]]; Lock wait timeout exceeded; try restarting transaction, Error_code: 1205; handler error HA_ERR_LOCK_WAIT_TIMEOUT; the event's master log master1-bin.002810, end_log_pos 91700741, Gtid 0-1-145094499, Internal MariaDB error code: 1205
Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.
The CREATE TABLE for the [[database]].[[table]] looks something like the following (which we had to anonymise a little, before posting)
CREATE TABLE `[[table]]` (
`_rowid` INT(11) NOT NULL,
`linkID` INT(11) NULL DEFAULT NULL,
`name_sur` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`name_fore` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`name_title` CHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`address` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_postcode` CHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_street_no` INT(11) NULL DEFAULT NULL,
`add_postalID` INT(11) NULL DEFAULT NULL,
`add_ms_residue` CHAR(3) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_ms_direct` CHAR(3) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_ms_dps` CHAR(2) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_ms_dps_crc` CHAR(4) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_os_northing` INT(11) NULL DEFAULT NULL,
`add_os_easting` INT(11) NULL DEFAULT NULL,
`add_phone` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`add_fax` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`clientschemeID` INT(11) NOT NULL,
`client_ref` VARCHAR(30) NOT NULL COLLATE 'latin1_general_ci',
`client_batch` VARCHAR(25) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`event_date` DATETIME NULL DEFAULT NULL,
`event_court` DATETIME NULL DEFAULT NULL,
`event_reg` DATETIME NULL DEFAULT NULL,
`event_details` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`event_number` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`account_amount` DECIMAL(12,2) NOT NULL,
`account_costs` DECIMAL(12,2) NOT NULL,
`account_fees` DECIMAL(12,2) NOT NULL,
`account_vat` DECIMAL(12,2) NOT NULL,
`account_balance` DECIMAL(12,2) NOT NULL,
`userID` INT(11) NULL DEFAULT NULL,
`user_allocated` DATETIME NULL DEFAULT NULL,
`user_current` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`user_visited` DATETIME NULL DEFAULT NULL,
`user_visits` INT(11) NOT NULL,
`user_levied` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`user_walker` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`last_stageID` INT(11) NOT NULL,
`last_date` DATETIME NULL DEFAULT NULL,
`status_open_closed` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`status` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`status_nextdate` DATETIME NULL DEFAULT NULL,
`status_nextdays` INT(11) NULL DEFAULT NULL,
`arrange_started` DATETIME NULL DEFAULT NULL,
`arrange_amount` DECIMAL(12,2) NULL DEFAULT NULL,
`arrange_interval` INT(11) NULL DEFAULT NULL,
`arrange_next` DATETIME NULL DEFAULT NULL,
`arrange_broken` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`return_date` DATETIME NULL DEFAULT NULL,
`return_codeID` INT(11) NULL DEFAULT NULL,
`return_details` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`return_user` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`return_fee` DECIMAL(12,2) NOT NULL,
`return_remitID` INT(11) NULL DEFAULT NULL,
`return_form` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`_createdBy` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`_createdDate` DATETIME NULL DEFAULT NULL,
`_lockowner` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`status_hold` CHAR(1) NOT NULL COLLATE 'latin1_general_ci',
`status_holdexpires` DATETIME NULL DEFAULT NULL,
`date_period` INT(11) NOT NULL,
`date_year` INT(11) NOT NULL,
`event_dvlc` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`arrange_giro` INT(11) NULL DEFAULT NULL,
`name2` VARCHAR(45) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`status_hold_client` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`isCompany` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colA` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colB` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colC` CHAR(15) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addCleaned` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addConfirmed` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addNotes` VARCHAR(250) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`isSpecial` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`statusCode` INT(11) NULL DEFAULT NULL,
`useriffSequence` INT(11) NULL DEFAULT NULL,
`date_week` INT(11) NULL DEFAULT NULL,
`imageCount` INT(11) NULL DEFAULT NULL,
`emptyID` INT(11) NULL DEFAULT NULL,
`addScore` INT(11) NULL DEFAULT NULL,
`addBand` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empName` VARCHAR(40) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empAddress` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empPhone` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empFax` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empRollNumber` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`empNI` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`dateOfBirth` DATETIME NULL DEFAULT NULL,
`colD` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colE` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colF` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colG` VARCHAR(40) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colH` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`eventFrom` DATETIME NULL DEFAULT NULL,
`eventTypeID` INT(11) NULL DEFAULT NULL,
`nameTA` VARCHAR(45) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`prevReference` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`eventLocation` VARCHAR(75) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colI` VARCHAR(40) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colJ` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`clientSchemeReturnID` INT(11) NULL DEFAULT NULL,
`arrangeBrokenCount` INT(11) NULL DEFAULT NULL,
`eventModel` VARCHAR(25) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`eventColour` VARCHAR(14) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`eventCourtCode` CHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`account_original` DECIMAL(12,2) NULL DEFAULT NULL,
`arrange_initial_amount` DECIMAL(12,2) NULL DEFAULT NULL,
`arrange_initial_due` DATETIME NULL DEFAULT NULL,
`addEmail` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`clientPeriod` CHAR(15) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`returnLastAgentID` INT(11) NULL DEFAULT NULL,
`returnLastStatus` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`returnLastRemitID` INT(11) NULL DEFAULT NULL,
`accountPaid` DECIMAL(12,2) NULL DEFAULT NULL,
`holdDate` DATETIME NULL DEFAULT NULL,
`traceDate` DATETIME NULL DEFAULT NULL,
`eventValue` DECIMAL(12,2) NULL DEFAULT NULL,
`commRate` DECIMAL(12,2) NULL DEFAULT NULL,
`addWarning` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colK` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colL` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colM` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colN` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colO` VARCHAR(10) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colP` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colQ` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colR` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colS` DATETIME NULL DEFAULT NULL,
`colT` DATETIME NULL DEFAULT NULL,
`colU` DATETIME NULL DEFAULT NULL,
`outcomeDate` DATETIME NULL DEFAULT NULL,
`colV` DATETIME NULL DEFAULT NULL,
`colW` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colX` DECIMAL(12,2) NULL DEFAULT NULL,
`turnaroundDate` DATETIME NULL DEFAULT NULL,
`lastStageDate` DATETIME NULL DEFAULT NULL,
`lastCodeStatusDate` DATETIME NULL DEFAULT NULL,
`contactRef` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`contactAddress` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`contactPhone1` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`contactPhone2` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`contactEmail` VARCHAR(40) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`contactIsApplicant` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`solicitorName` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`solicitorAddress` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`colY` DATETIME NULL DEFAULT NULL,
`colZ` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`hcDivision` VARCHAR(20) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`lodgementDate` DATETIME NULL DEFAULT NULL,
`docTypeRequested` INT(11) NULL DEFAULT NULL,
`docReceived` CHAR(1) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`col1` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`col2` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`col3` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`outcomeCode` INT(11) NULL DEFAULT NULL,
`awardedDate` DATETIME NULL DEFAULT NULL,
`col4` DATETIME NULL DEFAULT NULL,
`col5` DATETIME NULL DEFAULT NULL,
`addPhone3` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addPhone4` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addPhone5` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addPhone6` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`addEmail2` VARCHAR(30) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
PRIMARY KEY (`_rowid`),
INDEX `AccountorLockowner` (`_lockowner`),
INDEX `AccountorAddFax` (`add_fax`),
INDEX `AccountorAddPhone` (`add_phone`),
INDEX `AccountorAdd_postcode` (`add_postcode`),
INDEX `AccountorUseriff` (`userID`),
INDEX `AccountorUseriffCurrent` (`user_current`),
INDEX `AccountorClientBatch` (`client_batch`),
INDEX `AccountorClientRef` (`client_ref`),
INDEX `AccountorClientScheme` (`clientschemeID`),
INDEX `AccountorClientSchemeReturn` (`clientSchemeReturnID`),
INDEX `AccountorCreatedDate` (`_createdDate`),
INDEX `AccountorDate` (`_createdBy`),
INDEX `AccountorDatePeriod` (`date_period`),
INDEX `AccountorDateWeek` (`date_week`),
INDEX `AccountorDateYear` (`date_year`),
INDEX `AccountorEmpFax` (`empFax`),
INDEX `AccountorEmpPhone` (`empPhone`),
INDEX `AccountorGiro` (`arrange_giro`),
INDEX `AccountorHold` (`status_hold`),
INDEX `AccountorHoldDate` (`holdDate`),
INDEX `AccountorLinkID` (`linkID`),
INDEX `AccountorName_sur` (`name_sur`),
INDEX `AccountorEvent` (`eventTypeID`),
INDEX `AccountorEventCourt` (`event_court`),
INDEX `AccountorPrevRef` (`prevReference`),
INDEX `AccountorReturnDate` (`return_date`),
INDEX `AccountorReturnID` (`return_codeID`),
INDEX `AccountorStatusCodeID` (`statusCode`),
INDEX `AccountorStage` (`last_stageID`),
INDEX `AccountorStatus` (`status`),
INDEX `AccountorStatusOpenClosed` (`status_open_closed`),
INDEX `AccountorTraceDate` (`traceDate`),
INDEX `AccountorVRM` (`colC`),
INDEX `AccountorAgentAllocated` (`user_allocated`),
INDEX `AccountorEventNumber` (`event_number`),
INDEX `AccountorTurnaroundDate` (`turnaroundDate`),
INDEX `AccountorName2` (`name2`),
INDEX `AccountorNameTA` (`nameTA`),
INDEX `AccountorLastStageDate` (`lastStageDate`),
INDEX `AccountorLastCodeStatusDate` (`lastCodeStatusDate`),
INDEX `AccountorLastDate` (`last_date`),
INDEX `Accountor_return_remitID` (`return_remitID`),
INDEX `AccountorAddPhone3` (`addPhone3`),
INDEX `AccountorAddPhone4` (`addPhone4`),
INDEX `AccountorAddPhone5` (`addPhone5`),
INDEX `AccountorAddPhone6` (`addPhone6`),
CONSTRAINT `Accountor_userID` FOREIGN KEY (`userID`) REFERENCES `useriff` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_clientSchemeReturnID` FOREIGN KEY (`clientSchemeReturnID`) REFERENCES `clientschemereturn` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_clientschemeID` FOREIGN KEY (`clientschemeID`) REFERENCES `clientscheme` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_last_stageID` FOREIGN KEY (`last_stageID`) REFERENCES `stage` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_eventTypeID` FOREIGN KEY (`eventTypeID`) REFERENCES `event` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_return_codeID` FOREIGN KEY (`return_codeID`) REFERENCES `codereturns` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_return_remitID` FOREIGN KEY (`return_remitID`) REFERENCES `remit` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `Accountor_statusCodeID` FOREIGN KEY (`statusCode`) REFERENCES `codestatus` (`_rowid`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB
;
SHOW SLAVE STATUS
Slave_IO_State, "Waiting for master to send event",
Master_Host, [[master host ip]]
Master_User, [[master user]]
Master_Port, 3306,
Connect_Retry, 60,
Master_Log_File, master1-bin.002810,
Read_Master_Log_Pos, 808399708,
Relay_Log_File, mysqld-relay-bin.658440,
Relay_Log_Pos, 977,
Relay_Master_Log_File, master1-bin.002810,
Slave_IO_Running, Yes,
Slave_SQL_Running, Yes,
Replicate_Do_DB, ,
Replicate_Ignore_DB, ,
Replicate_Do_Table, ,
Replicate_Ignore_Table, ,
Replicate_Wild_Do_Table, [[database]].%,
Replicate_Wild_Ignore_Table, ,
Last_Errno, 0,
Last_Error, ,
Skip_Counter, 0,
Exec_Master_Log_Pos, 808399708,
Relay_Log_Space, 12653,
Until_Condition, None,
Until_Log_File, ,
Until_Log_Pos, 0,
Master_SSL_Allowed, No,
Master_SSL_CA_File, ,
Master_SSL_CA_Path, ,
Master_SSL_Cert, ,
Master_SSL_Cipher, ,
Master_SSL_Key, ,
Seconds_Behind_Master, 0,
Master_SSL_Verify_Server_Cert, No,
Last_IO_Errno, 0,
Last_IO_Error, ,
Last_SQL_Errno, 0,
Last_SQL_Error, ,
Replicate_Ignore_Server_Ids, ,
Master_Server_Id, 1,
Master_SSL_Crl, ,
Master_SSL_Crlpath, ,
Using_Gtid, No,
Gtid_IO_Pos, ,
Replicate_Do_Domain_Ids, ,
Replicate_Ignore_Domain_Ids, ,
Parallel_Mode conservative
SHOW CREATE TABLE
for the relevant tables; it is possibly a poor index and/or poorly formulated query. If you have the Slave's slowlog turned on, be sure to also havelog_slow_slave_statements
turned on.