Mysql: "read ECONNRESET" error is being thrown when trying to insert large size files.

[krishna2014]

Hi,
when I am trying to insert large size files I'm getting the __ECONNRESET__ error.
I've created the db connection pool and getting the connection using the pool.getConnection() method.
__Below is my scenario.__

• I am getting the zip file from the web request.
• Then reading the file data using fs.readFile() method and trying to insert the file stream data into a table's column of longblob datatype.
• I've seen no issue when the zip file is below 2 mb to 3 mb size.
• When it excceds the above size I'm getting this issue.
• I've gone through similar issues reported in github but couldn't able to fix it.

Stack Trace:

Error: read ECONNRESET
        at exports._errnoException (util.js:1012:11)
        at TCP.onread (net.js:563:26)
    --------------------
        at Protocol._enqueue (app\node_modules\mysql\lib\protocol\Protocol.js:141:48)
        at PoolConnection.query (app\node_modules\mysql\lib\Connection.js:214:25)
        at Query._callback (app\services\basedao.js:85:32)
        at Query.Sequence.end (app\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
        atQuery._handleFinalResultPacket(app\node_modules\mysql\lib\protocol\sequences\Query.js:144:8)
        at Query.EofPacket (\node_modules\mysql\lib\protocol\sequences\Query.js:128:8)
        at Protocol._parsePacket (app\node_modules\mysql\lib\protocol\Protocol.js:280:23)
        at Parser.write (app\node_modules\mysql\lib\protocol\Parser.js:74:12)
        at Protocol.write (app\node_modules\mysql\lib\protocol\Protocol.js:39:16)
        at Socket.<anonymous> (E:\app\node_modules\mysql\lib\Connection.js:109:28)
        at emitOne (events.js:96:13)
        at Socket.emit (events.js:188:7)
        at readableAddChunk (_stream_readable.js:177:18)
        at Socket.Readable.push (_stream_readable.js:135:10)
        at TCP.onread (net.js:542:20)

My code:

var pool = mysql.createPool({
    connectionLimit: 10,
    host: process.env.HOSTNAME,
    port: process.env.PORT,
    user: process.env.USERNAME,
    password: process.env.PASSWORD,
    database: process.env.NAME,
    debug: false
});

function insertpackage() {
   getdbconnection(function(err, connection){
        if (err) {
            console.log('Failed to get connection');
        }
        else {
                    var sqlQuery = 'INSERT INTO user_packages SET ?';
                   /* fileData is the stream that is read from my zip file */
                    var params = {
                              fileName: name,
                              content: fileData
                         };
                    connection.query(sqlquery, params, function (err, result) {
                      connection.release();
                      if (err) {
                            console.log( 'Error in  executing Insert query :' + err.stack);
                      }
                      else {
                         console.log('Package uploaded successfully');
                     }
                   });
           }
   });
}

function getdbconnection(callback) {
    pool.getConnection(function (err, dbconnection) {
        if (err) {
            console.log('Error in acquiring DB Connection :', err);
            callback(null, null);
        }
        else {
            console.log('Acquired Database Connection with Connection Id as :' + dbconnection.threadId);
            callback(null, dbconnection);
        }
    });
}

[dougwilson]

Hi @krishna2014 there is no obvious reason why the connection would reset from your example. The provided code is incomplete, however I tried to complete it but wasn't able to reproduce. Could you provide the following?

1. Version of the MySQL server.
2. Version of this module.
3. The complete code.
4. Any relevant instructions to run the code.

Thank you!

[krishna2014]

Hi __@dougwilson__ , thanks for your response .Below are the list of versions that I'm using.

• Mysql server - 5.7.11
• mysql (npm module) - 2.11.1

__My Scenario:__

• I've created an express project and in one of my REST APIs I am implementing this database insert operation and running into this issue.
• I've attached my code in the zip file.Please extract the code from the zip file and run the npm install and start the server(__npm start__).By default it will run on 3000 port.
• I'm running my db scripts on a db schema named __packagedb__ .Please try to create a schema with the same name or give your own schema and also change the database value in the code.
• Now hit this API http://localhost:3000/insert from rest client .
• Below is my Stack Trace .

Error: read ECONNRESET
    at exports._errnoException (util.js:1012:11)
    at TCP.onread (net.js:563:26)
    --------------------
    at Protocol._enqueue (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\Protocol.js:141:48)
    at PoolConnection.query (E:\Mysql-issue\testApp\node_modules\mysql\lib\Connection.js:208:25)
    at E:\Mysql-issue\testApp\routes\baseDAO.js:54:23
    at E:\Mysql-issue\testApp\routes\baseDAO.js:79:13
    at Ping.onOperationComplete [as _callback] (E:\Mysqlissue\testApp\node_modules\mysql\lib\Pool.js:110:5)
    at Ping.Sequence.end (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
    at Ping.Sequence.OkPacket (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\sequences\Sequence.js:95:8)
    at Protocol._parsePacket (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\Protocol.js:280:23)
    at Parser.write (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\Parser.js:75:12)
    at Protocol.write (E:\Mysql-issue\testApp\node_modules\mysql\lib\protocol\Protocol.js:39:16)

• Please download the code from the below dropbox link.This zip file consists of a sample express project.
  https://www.dropbox.com/s/0akcewbpqo7and2/testApp.zip

[krishna2014]

Hi __dougwilson__ , any insight in to the issue ??

[dougwilson]

Sorry, this issue got lost on my list. I just marked it to remember; I'll take a look soon.

[Setitch]

This might be due to your MySQL configuration - max packet size option. Its usually set there to prevent database from flood with data. (sadly works both ways).

[bruno147]

I am facing this same issue. When the file's size is greater than 4 mb the ECONNRESET is thrown.
I have succesfully inserted the same file straight to the database using MySQL without node, which led me to believe this is a node issue.
My code looks like that:

// main 
function (inner_callback) {
        var db = require('../lib/database')
        var file = ... // 14 Mb of base64 encoded file
        var binaryData = new Buffer(file, 'base64');
        var insertData = {
            'db': 'temporary_files',
            'json': {
                ... , // lots of smaller fields
                    'CONTEUDO': binaryData
            }
        };
        db.insertValue(insertData, function(err, insertResult, successCallback) {
            if (err) {
                return successCallback(new Error("File insertion error."));
            }
                else {
                process.nextTick(function(){ successCallback(null); });
                }
        }, inner_callback);
}

// lib/database

exports.insertValue = function(data, callback, successCallback) {
    var sql = "INSERT INTO " + pool.escapeId(data.db) + " SET ?";
    // get a connection from the pool
    pool.getConnection(function(err, connection) {
        if(err) {
            console.log("Insert value -> Get Connection: ");
            console.log(err);
            callback(true, null, successCallback);
            return;
        }
        connection.query(sql, data.json, function(err, results) {
            connection.release();
            if(err) {
                console.log("Insert value -> Insert: ");
                console.log(err);
                callback(true, null, successCallback);
                return;
            }
            callback(false, results, successCallback);
        });
    });
}

And i get:

Insert value -> Insert:
{ [Error: read ECONNRESET]
  code: 'ECONNRESET',
  errno: 'ECONNRESET',
  syscall: 'read',
  fatal: true }

[kai-koch]

As @Setitch said this https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html could be the possible cause of the error.
Quote:
"With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large."

@krishna2014 and @bruno147 could you check your database configuration and see if that is the case?

[i-m-sac]

Hi @krishna2014,
Could you please share the exact length of the query?
Also could you please take a look at this issue to see if these are related?

[edilsonlm217]

I'm building an API that track the database's binlog files and when it reach 4mb "read ECONNRESET" error is thrown.

Any update on it?

[Setitch]

Can you tell us what value is set for server variable max_allowed_packet ?

[edilsonlm217]

max_allowed_packet = 524288000
When I mentioned this error it was set to its default value. It seems that get a larger max_allowed_packet number stops the error as you said.

I'll do some extra test today and come with a more detailed information about it.

[Setitch]

Can you use other client to do the exact query triggering the error - that could cross out error in client