Monitoring Deadlocks using performance_schema.error_log table
Hi
I want to monitor the details of each deadlock in my MySQL database, but the instance is in the cloud, and I don't have the actual log files easily accessible.
My plan was to set the innodb_print_all_deadlocks variable on the instance, and then monitor the performance_schema.error_logs tables.
Unfortunately, all I get is the following... The actual log files have a whole bunch of information about the deadlock, which is what I hoped to capture.
I tried message with the error log verbosity, to no avail. Am I doing something wrong here, or do I have to find away to get the error logs files directly?
Any assistance would be appreciated. I couldn't find any information about this behavior online.
Gavin Glynn :)
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.564912
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012468
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
Transactions deadlock detected, dumping detailed information.
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.564948
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (1) TRANSACTION:
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.564994
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (1) HOLDS THE LOCK(S):
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.565551
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.565622
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (2) TRANSACTION:
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.565659
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (2) HOLDS THE LOCK(S):
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.565717
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
=======================================================
=======================================================
Logged Date .. .. .. ..: 2024-03-21 16:07:17.566280
Thread ID .. .. .. .. ..: 0
Priority .. .. .. .. ..: Note
Error Code.. .. .. .. ..: MY-012469
Sub System.. .. .. .. ..: InnoDB
Data.. .. .. .. .. .. ..:
*** WE ROLL BACK TRANSACTION (1)
=======================================================
Subject
Written By
Posted
Monitoring Deadlocks using performance_schema.error_log table
March 24, 2024 05:02PM
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.