Step on pit node.js + MySQL


In my spare time a few days ago, I wrote an order management system using node + Express + mysql. No problem was found in the local test of the project, and there was no problem in deploying it to the server; Strangely, for example, I deployed at night. After the deployment test, there was no problem. All visits were normal, but once I slept, the query was 502; After restarting the service, it’s OK again. After a while, it hangs up again

Later, the following errors were found by checking the log
Step on pit node.js + MySQL
It was a database error, but why?

Let’s take a look at the usage of MySQL module. The official recommends that we establish a connection in this way
Step on pit node.js + MySQL
That’s the problem!!! Because of the blind area of knowledge, I originally thought that saving the instance after establishing a connection and using it every query in the future could save the system overhead

However, after data collection by various parties, it was found that MySQL would disconnect when it was idle for a certain time (about 8 hours). After verification, it was true, but it didn’t seem to be eight hours

The verification method at that time was to record the state of the connection object in the log file when an error occurred. Sure enough, the connection object still exists when the error occurred, but the connection state has changed to disconnected. No wonder the above error will occur!

If you find a problem, you have a 90% chance of successfully solving the problem; It’s a solution to read that there is a regular task on the Internet and query the database every other hour (this query is actually a useless query, just to prevent the database from being disconnected, which is a bit similar to the feeling of heartbeat package). But I always feel that this is not the best solution, So I read the MySQL document from beginning to end and found the answer
Step on pit node.js + MySQL
Use the connection pool to return the connection to the connection pool after the query is completed, so that other connections can continue to use it
Next, change the code to pool connection
Step on pit node.js + MySQL
At present, the project has been deployed in Alibaba cloud for almost a week, and the problem has not recurred; Write this article and record this pit. Your writing is not good. Forgive me!

Recommended Today

Python data processing in the third stage of employment class

Chapter 1 Introduction to pandas 1.1 / 1.2 jupyter Foundation Create virtual environment a. Create a virtual environment for the specified Python version conda create -nEnvironment variable name python = 3.6 b. Virtual environment command conda env list ——View * * currently owned virtual environments conda remove-N environment variable name — all——delete*Environmental variables activateEnvironment variable […]