Troubleshooting and solution of occasional warehousing failure of MySQL database

background

 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 .
 #print(insert_sql)
try:
# perform SQL sentence
cursor.execute(insert_sql)
# Commit to database execution
db.commit()
#print(' Data sheet submitted successfully ')
except Exception as e:
# Rollback on error
db.rollback()
print(" Failure rollback "+str(datetime.now()))
print(e)
print("SQL:"+insert_sql)

As a result, the problem of grasping and positioning

image.png
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
image.png
Problem analysis
May have been used for mysql Keywords lead to
https://blog.csdn.net/wukong_...

Reference resources
1:mysql Solution of database cache tuning The total number of locks exceeds the lock table size error
https://www.cnblogs.com/nulig...
1:Windows Command line restart MySQL service
https://cloud.tencent.com/dev...
2

Please bring the original link to reprint ,thank
Similar articles