Troubleshooting and solution of occasional warehousing failure of MySQL database


 utilize python The script controls the key processes of the server 24 Hour continuous monitoring , The monitored data is stored in the database , It is convenient for post analysis .

List of questions
1、 solve The total number of locks exceeds the lock table size error
2、 solve 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1") error

Find the problem 1:

 Find occasionally mysql The problem of warehousing failure will be reported 

Location analysis :

modify python Stored in the database source code ; adopt try except To capture error messages , The details of the error report are stored in e variable , You can print directly ; Because it's a coincidence bug, The error reporting time and the executing statement are also printed in the log .
# perform SQL sentence
# Commit to database execution
#print(' Data sheet submitted successfully ')
except Exception as e:
# Rollback on error
print(" Failure rollback "+str(

As a result, the problem of grasping and positioning

Error message prompt The total number of locks exceeds the lock table size, Insufficient cache caused .
Cause analysis : Because the number of processes monitored is large , And it performs batch entry operation , Therefore, it may lead to insufficient data fetching values .

Problem solving

 You can log in mysql database server , Modify the configuration problem my.ini Zhonghu configuration
innodb_buffer_pool_size=2GB # The default value is 8M It is amended as follows 2G or 3G, According to the table size , tuning .

restart MySQL The server

stay linux You can directly use the script to modify ,window Server reference Windows Command line restart MySQL service To restart .

problem 2
The error information is as follows
Problem analysis
May have been used for mysql Keywords lead to

Reference resources
1:mysql Solution of database cache tuning The total number of locks exceeds the lock table size error
1:Windows Command line restart MySQL service

Please bring the original link to reprint ,thank
Similar articles