MySQL Bugs: #33593: Exporting deadlock and foreign key errors as a rule needed

Bug #33593	Exporting deadlock and foreign key errors as a rule needed
Submitted:	31 Dec 2007 6:14	Modified:	9 Jan 2015 10:00
Reporter:	Meiji KIMURA	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S4 (Feature request)
Version:	1.2, 2.0.5.7153	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	rule

Description:
Our customer want to create a rule for DEADLOCK/FORIEGN KEY ERROR. But in this version, there's no variable which is connected to DEADLOCK or FORIEGN KEY ERROR.

Please consider it in future version.

How to repeat:
(1) Try to create a fule for DEADLOCK/FORIEGN KEY ERROR.
(2) Click the create rule button, then we'll see the “Edit rule screen".
(3) But there's no variable which is connected to DEADLOCK or FORIEGN KEY ERROR.

Adjusting synopsis

Verifying feature request.

Hi,

Since this bug has been Verified status for long time, I'd like to have your thought about the future plan.

Having the data item which reflects last foreign key error field in SHOW INNODB STATUS output might be better than nothing.

As the state is changed to Closed I assume this is fixed. If so, in which version? I don't think this is the latest 3.0 release.

According to the internal bug, 
Bug 11747681 - EXPORTING DEADLOCK AND FOREIGN KEY ERRORS AS A RULE NEEDED 
it was fixed in Monitor build 3.0.0.2855.

Also note, innodb_trx table got column trx_last_foreign_key_error now.
http://bugs.mysql.com/bug.php?id=53336
(Improved InnoDB Transaction Reporting)

I had a look at the internal bug and it only gives the revision in which it is fixed, not how it's fixed.

I didn't know about the trx_last_foreign_key_error feature. Thanks for the note, that really helps.

However I don't really think this is fixed.

What I often encounter is this:
 - There are issues in the application (often performance related)
 - The output of SHOW ENGINE INNODB STATUS shows (very) recent FK errors and/or deadlocks.
 - The devs and/or DBA's are not aware of those as they're not shown in the MEM GUI.

The trx_last_foreign_key_error feature is nice, but that only works for running transactions. With autocommit enabled and/or short lived transactions this doesn't really help. So if MEM uses this it might not see the FK error. Also it doesn't show the deadlock.

How I could imagine this to be fixed is this:
1. A graph showing the number of deadlocks/foreign key errors over time. A deadlock implies a rollback and a rollback isn't helpful for performance. (SHOW ENGINE INNODB STATUS only shows the last FK error the last deadlock, so this would be a real improvement)

2. An advisor/rule which tells you there are FK errors and that your application might have a bug.

3. An advisor/rule which tells you there are recent deadlocks. And this might be an issue. And then point to https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html