0

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

8
  • Locate those transactions, show them to us, provide 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 have log_slow_slave_statements turned on.
    – Rick James
    Jan 29, 2019 at 1:26
  • What MariaDB version and what replication configuration? Looks like a parallel replication setup. Is there multiple masters?
    – danblack
    Jan 29, 2019 at 4:32
  • @RickJames - added the CREATE TABLE code. Don't know how to locate the transactions(?) and can't see where I can check the log_slow_slave_statements (presumably that's in the .cnf file, for which I am waiting on access to the server)
    – Sean
    Jan 29, 2019 at 9:31
  • @danblack - MariaDB is 10.1.14 and as far as I am aware, there is only a single master DB -- we don't have control over the master DB, as it's provided by a 3rd party and they setup the replication.
    – Sean
    Jan 29, 2019 at 9:33
  • Oh, so the 3rd party is responsible for fixing it then :-) Is that the master version or the slave? What is the slave configuration of replication variables? And `SHOW SLAVE STATUS'
    – danblack
    Jan 29, 2019 at 9:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.