Parsing the MySQL deadlock log from errorlog based on Innodb_print_all_deadlocks

This article describes how to get deadlock logging, not how to resolve a deadlock problem.

The deadlock of MySQL can be viewed through show engine InnoDB status;
But show engine InnoDB status; only the most recent deadlock can be displayed, and this mode cannot fully capture the deadlock information that occurs on the system.
If you want to log all deadlock logs, open the Innodb_print_all_deadlocks parameter to log all deadlock logs to errorlog,
So the question becomes how to parse the deadlock log from errorlog.

As the following, is a typical deadlock log information in errorlog, in addition to the deadlock log, you can create some other interference log (deliberately do not enter a password to log into the database to let it record some other error log information)
If you want to parse this deadlock log (excluding other unrelated logs), which resolves the deadlock content, splits two (or) more transactions in the contents of the deadlock log
1, deadlock start and end tag ====> intercept single deadlock log content
2, parse the deadlock log in step 1th ===> intercept each transaction log

The following parsing is based on the rules, which are basically matched according to this keyword and regular
1, the deadlock log begins with the tag: Transactions deadlock detected, dumping detailed information.
2, token of the start of the transaction in the deadlock: * * * (N) TRANSACTION:
3, Deadlock end tag: WE Roll back TRANSACTION (N)

ImportOSImport TimeImportdatetimeImportRe fromIo.mysql_operationImportmysql_operationclassmysql_deadlock_analysis:def __init__(self):Pass    defis_valid_date (self,strdate):Try:            if ":" inchstrdate:time.strptime (strdate,"%y-%m-%d%h:%m:%s")            Else: Time.strptime (strdate,"%y-%m-%d")            returnTrueexcept:            returnFalsedefinsert_deadlock_content (self,str_id, str_content): ConnStr= {'Host':' ***,***,***,***',                   'Port': 3306,                   'User':' username',                   'Password':'pwd',                   'DB':'DB01',                   'CharSet':'UTF8MB4'} mysqlconn= Mysql_operation (host=connstr['Host'], Port=connstr['Port'], user=connstr['User'], password=connstr['Password'], DB=connstr['DB'], CharSet=connstr['CharSet'])        " "Deadlock Log table structure, a complete deadlock log is based on the first transaction start time in the deadlock, deadlock_id, stating that it belongs to a deadlock create table Deadlock_log (            ID int auto_increment PRIMARY KEY, deadlock_id varchar (), deadlock_transaction_content text, create_date datetime)" "Str_sql="INSERT INTO Deadlock_log (deadlock_id,deadlock_transaction_content,create_date)"                   "values ('%s ', '%s ', now ())"%(str_id, str_content)Try: Mysqlconn.execute_noquery (Str_sql, None)exceptException as err:Raise(Exception,"Database operation Error")    #parsing deadlock Log content    defRead_mysqlerrorlog (self,file_name):Try: Deadlock_flag=0 Deadlock_set=set () deadlock_content=""with open (file_name,"R") as F: forLineinchF:if(Deadlock_flag = =0): Str_datetime= Line[0:19].replace ("T"," ")                        if(Self.is_valid_date (str_datetime)):if(Line.find ("Deadlock") >0):#contains a deadlock string that indicates that the deadlock log starts                                #output deadlock log, flag deadlock log StartDeadlock_content = deadlock_content+Line Deadlock_flag= 1elif(Deadlock_flag = = 1):                        #Output Deadlock LogDeadlock_content = Deadlock_content + Line#Deadlock Log End                        if(Line.find (" Roll Back") >0):#contains roll back string, which indicates the end of deadlock logDeadlock_flag =0#parsing end of a complete deadlock logDeadlock_set.add (deadlock_content) deadlock_content=""        exceptIOError as err:Raise(IOError,"Read File Error")        returnDeadlock_set#parsing individual transaction information in the deadlock log    defAnalysis_mysqlerrorlog (self,deadlock_set):#single transaction start tagTransaction_begin_flag =0#single transaction information in a deadlockTransaction_content =""        #Deadlock Occurrence TimeStr_datetime =""        #Match Transaction start tag regularPattern = Re.compile (r'[*]* [(0-9)]* TRANSACTION:')         forStr_contentinchdeadlock_set:arr_content= Str_content.split ("\ n")             forLineincharr_content:if(Self.is_valid_date (Line[0:19].replace ("T"," "))):                    #deadlock occurs when the deadlock log content is parsed, each set of deadlock logs is assigned only one time, and all the things in a deadlock are used for the first timeStr_datetime = Line[0:19].replace ("T"," ")                #transaction start tag in the deadlock log                if(Pattern.search (line)): Transaction_begin_flag= 1#transaction begins, writes the previous transaction content to the database                    if(transaction_content): Self.insert_deadlock_content (str_datetime,transaction_content) #a new transaction starts in the deadlock log, resets the transaction_content, and the transaction start tagTransaction_content =""Transaction_begin_flag=0Else:                    #A specific log of a transaction that produces a deadlock                    if(transaction_begin_flag==1): Transaction_content= Transaction_content +"\ n"+ Line#last transaction information in the deadlock log            if(transaction_content): Self.insert_deadlock_content (Str_datetime, transaction_content) Transaction_content=""Transaction_begin_flag=0if __name__=='__main__': File_path="\path\mysql.err" Analysis=mysql_deadlock_analysis () str_content=Analysis.read_mysqlerrorlog (File_path) analysis.analysis_mysqlerrorlog (str_content)

The following is a set of deadlock-related transaction information that is written to the database after the effect ID, corresponding to a set of deadlock transactions with ID 3,4

is purely a quick attempt to own some of the ideas, there are many deficiencies
1, the parsed log format is very coarse
2, parsing is the normal deadlock, not sure to hold all the deadlock log format, according to the keyword parsing, do not know whether it is always valid
3, how to avoid repeated parsing, that is, the timing of the error analysis of MySQL, did not judge the previous resolution of the content of the judgment
4, no efficiency test.

Parsing the MySQL deadlock log from errorlog based on Innodb_print_all_deadlocks