MySQL Innodb Deadlock - how to remove old event?
alarm, deadlock, innodb, mysql, zenoss
Solution
There is no way to clear the `LATEST DETECTED DEADLOCK` section in `SHOW ENGINE INNODB STATUS` unfortunately. The query has already been killed by the deadlock detector, so there's nothing you can `KILL`. The only way to reset that information is to restart the server.
No properly written software should continuously alert due to the presence of something in that output.
Problem
My monitoring tool, Zenoss, is reporting a 'LATEST DETECTED DEADLOCK' event from a MySQL Innodb engine. When I run 'show engine innodb status\G' I get the following information about the deadlock: ``` ------------------------ LATEST DETECTED DEADLOCK ------------------------ 130819 14:01:12 *** (1) TRANSACTION: TRANSACTION 0 108626388, ACTIVE 0 sec, process no 8726, OS thread id 47220783470912 starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1 MySQL thread id 4283, query id 21974219 10.92.210.108 ddrsrbe Updating update ddrsproduction.requests set request_priority_grade_id=3, costs=0, data_version=1, date_completed='2013-08-19 13:59:39', date_end='2013-05-24 23:59:00', date_required_by='2013-08-26 13:57:31', date_start='2013-05-19 00:01:00', date_submitted='2013-08-19 13:57:31', designated_authority_id=84528, is_manual=1, missed_sla_reason=null, missed_sla_reason_ask_user=null, product_id=35, request_cost_status_id=0, request_delivery_method_id=0, request_legislation_id=0, request_method_id=0, request_reason_id=1, request_result_status_id=null, origin_id=0, request_status_id=2, request_type_id=1, result_row_count=2, results_last_downloaded=null, site_processed='KNOW', time_zone=null, urn='LBP/281/35/12 (CONS)', user_id=2357, vf_rep_id=8 where request_id=132536 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ``` You can see that the deadlock was in August last year, and I cannot kill the thread as it no longer exists: ``` mysql> kill 4283; ERROR 1094 (HY000): Unknown thread id: 4283 ``` I am getting bored receiving this event in to Zenoss, and would very much like to purge the innodb engine of this alarm. I have Googled high and low for an answer and have come up with zilch, nada, nothing. Any help hugely appreciated :-)