=====================================
2016-12-19 07:01:24 7f119e9fb700 INNODB MONITOR OUTPUT
Per second averages calculated from the last 20 seconds
BACKGROUND THREAD
srv_master_thread loops: 798139 srv_active, 0 srv_shutdown, 5799744 srv_idle
srv_master_thread log flush and writes: 6597883
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 2471926
OS WAIT ARRAY INFO: signal count 4149066
Mutex spin waits 3630779, rounds 58833445, OS waits 1840009
RW-shared spins 932579, rounds 15337674, OS waits 417875
RW-excl spins 249151, rounds 12191443, OS waits 183040
Spin rounds per wait: 16.20 mutex, 16.45 RW-shared, 48.93 RW-excl
LATEST FOREIGN KEY ERROR
2016-10-04 07:03:09 7f118bfb6700 Transaction:
TRANSACTION 955844513, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 54560, OS thread handle 0x7f118bfb6700, query id 522797 hostname ip update
INSERT INTO tblMessageDataItems(messageId , reportId , reportVersion, profileId , publicationStatus, sentTime, clientMessageUUID ) VALUES (57852787 , '16-00032856' , '1' , '5786fbfb08a406224a92c055' , 'SENT' , NOW(), 'a7eda91c-f10e-4ca6-b23d-6fbbf928a976')
Foreign key constraint fails for table deliverystaging.tblMessageDataItems:
,
CONSTRAINT tblMessageDataItems_ibfk_1 FOREIGN KEY (messageId) REFERENCES tblMessage (messageId)
Trying to add in child table, in index tblMessageDataItems_ibfk_1 tuple:
DATA TUPLE: 2 fields;
0: len 8; hex 000000000372c373; asc r s;;
1: len 8; hex 0000000004203409; asc 4 ;;
But in parent table deliverystaging.tblMessage, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 8; hex 000000000372c376; asc r v;;
1: len 6; hex 000038f90723; asc 8 #;;
2: len 7; hex dd000005370110; asc 7 ;;
3: SQL NULL;
4: len 6; hex 57f35428003b; asc W T( ;;;
5: len 10; hex 4441494c595f56554c4e; asc DAILY_VULN;;
6: len 5; hex 454d41494c; asc EMAIL;;
LATEST DETECTED DEADLOCK
2016-12-18 16:03:00 7f118efff700
*** (1) TRANSACTION:
TRANSACTION 988786769, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x7f11bb3b4700, query id 79545552 Write_rows_log_event::write_row(68180292)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 3 n bits 88 index PRIMARY of table deliverystaging.tblJobStates trx table locks 1 total table locks 2 trx id 988786769 lock_mode X insert intention waiting lock hold time 0 wait time before grant 0
*** (2) TRANSACTION:
TRANSACTION 988786768, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 7082647, OS thread handle 0x7f118efff700, query id 79545550 hostname ip update
INSERT INTO tblJobStates (JobId, JobType, JobInput, Priority, JobDetails, JobStatus, RunCount, CreatedTime, Creator,
ModifiedTime, Modifier, Timeout)
VALUES ( NAME_CONST('jobId',_utf8'aa35062b0b51fe5898fbcd01d411ce9fa0e801ed2c25e7dbdf0e8d678f42dffe' COLLATE 'utf8_general_ci'), NAME_CONST('jobType',_utf8'abc.delivery.main.jobs.HourlyJob' COLLATE 'utf8_general_ci'), NAME_CONST('jobInput',_binary'▒▒\0sr\0/com.abc.delivery.main.jobs.HourlyJobm▒▒ >)▒f\0L\0scheduledFireTimet\0Ljava/util/Date;xpsr\0java.util.Datehj▒KYt\0\0xp\0\0Y▒ x' COLLATE 'binary'), NAME_CONST('priority',11), NAME_CONST('jobDetails',_utf8'{"scheduledFireTime":"Sun Dec 18 16:03:00 UTC 2016"}' COLLATE 'utf8_general_ci'), NAME_CONST('jobStatus',_utf8'READY' COLLATE 'utf8_general_ci'), NAME_CONST('runCount',0), NAME_CONST('cu
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 345 page no 3 n bits 88 index PRIMARY of table deliverystaging.tblJobStates trx table locks 1 total table locks 2 trx id 988786768 lock mode S lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 345 page no 3 n bits 88 index PRIMARY of table deliverystaging.tblJobStates trx table locks 1 total table locks 2 trx id 988786768 lock_mode X insert intention waiting lock hold time 0 wait time before grant 0
*** WE ROLL BACK TRANSACTION (2)