After the Node mysql program has been running for a period of time, there is an exception in reading and writing to the database.

the program is written in Node, and the back-end database provides APP back-end API and socket connection services for MySQL,. In the current single instance deployment, a MySQL connection is used in the instance. Everything works normally at the beginning of the service. As the service time becomes longer (ranging from one day to one week), an API call will fail at a certain point in time. After this error occurs, all subsequent database read and write operations will fail. The reason for the error in the API service code is that it failed to insert a record into MySQL. I inserted some code at the key code: 

self.conn.query(sql, function(err, res, fields){
        if(err){
            callback && callback(ERRCODE.MYSQL.code, err);
        }else{
            if (!res.insertId) {
                logger.error("activity.insertActivitySpeaker: insertId undefined");
            } else {
                logger.info("success: activity.insertActivitySpeaker: insertId %s", res.insertId);
            }
            callback && callback(0, res);
        }
    });

what is more difficult to understand is that when the insert fails, the code 

logger.info("success: activity.insertActivitySpeaker: insertId %s", res.insertId);

is still executed, and a normal id (self-increment) is printed, but there is no corresponding operation log in MySQL binlog, and in the end, the record corresponding to id is not found in the database. The current guess is that the service is running for a long time or a loophole in the code logic leads to an abnormal MySQL connection: when you try to write to MySQL, you can get a write id, but the final write operation is not successful, and the data returned from the read from the database is empty. I wonder if my friends have encountered similar problems. Thank you very much for giving me some suggestions on investigation.

Node.js node-mysql
Mar.17,2021

the reason for the problem is that when using a transaction, a lack of rollback or commit, can stably reproduce the error using the following code: 

let mysql = require("mysql");

function createConn() {
    return mysql.createConnection({
        host     : '192.168.1.100',
        user     : 'yangqiang',
        password : '123456',
        database : 'node_mysql_demo'
    });
}

function transactionWithoutCommit(conn) {
    conn.beginTransaction(function (err) {
        if (err) {
            throw err;
        }
        conn.query('SELECT * from user where name="jonny"', function (error, results, fields) {
            if (error) {
                return conn.rollback(function () {
                    throw error;
                })
            } else {
                //  commit
            }
        });
    });
}

function transactionWithCommit(conn) {
    conn.beginTransaction(function (err) {
        if (err) {
            throw err;
        }
        conn.query('SELECT * from user where name="jonny"', function (error, results, fields) {
            if (error) {
                return conn.rollback(function () {
                    throw error;
                })
            } else {
                conn.commit(function (err) {
                    if (err) {
                        return conn.rollback(function(){ throw err;})
                    } else {
                        console.log('transaction committed');
                    }
                });
            }
        });
    });
}

function write(conn) {
    conn.query('INSERT INTO user (name) VALUES ("Mei");', function (error, results, fields) {
        if (error) throw error;
        console.log(JSON.stringify(results));
    });
}


var connection = createConn();
connection.connect();

transactionWithoutCommit(connection); // 
// transactionWithCommit(connection); // 
write(connection);

Previous: Which api document generator is more suitable for generating function directories of front-end systems in front-end separate development?

Next: Is there an api that moves up and down in element-ui table?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b32496-2bde5.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b32496-2bde5.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?