2

I am using felixge/node-mysql. Also I am using express-myconnection which prevents mysql timeout and in turn prevents killing of node server. What I am doing is logging the activities in mysql. The scenario is I have a file upload functionality once the file is uploaded I am performing different operations on the file. During every stage of processing I am logging those activities in database. This works fine if the file is small. If the file is large say 100 MB it takes some time to load so in the mean time the mysql server reconnects and creates a new connection but the logging code still uses the old reference. Error: Cannot enqueue Query after fatal error. So, my question is is there a way that i can use the new connection reference instead of the old one. There is a single function in which all the different phases of activities regarding file takes place. Any help is greatly appreciated. thanks

Hi @paul, if you have seen the gist link you can see that I have the upload.on('begin', function (fileInfo, reqs, resp) { } where I have logged the activity that file upload process has begin. Once the file is uploaded upload.on('end', function (fileInfo,request,response) { } is triggered. I am also logging some activity here. As I said in my question, if the file is big the upload takes time. In the mean time a new MySql connection is created but the insert query in 'end' event still refers to the old myconnection. So, I wanted to know how can I use the new mysql connection reference in this scenario? I hope this has explained the scenario better.

Rahul Singh
  • 133
  • 1
  • 2
  • 11
  • Show some code, please. – Paul Jun 03 '16 at 17:10
  • Please check the link https://gist.github.com/anonymous/ac4310635d6087cc6fe98d2cc27e27d9 The first insert works when the upload process starts. But the second insert is kicked off when the upload process is completed.Here it still refers to the old reference of mysql process. – Rahul Singh Jun 03 '16 at 17:21
  • I'm still not understanding your question. You should use a different connection for each unit of work. Don't rely on your events to be emitted within a certain timeframe. – Paul Jun 03 '16 at 21:07
  • Hi @paul, I have explained the scenario in question above. Please have a look at it. – Rahul Singh Jun 05 '16 at 04:08
  • Sorry, this still doesn't make sense to me. From your gist, it looks like you're using the 'pool' option from express-myconnection. Given that you're asking that middleware to give you a connection each time you want to make a query (in the begin and end steps), you should rely on it to manage your connections for you from its pool, and not worry about whether the connection is the 'same' or not. – Paul Jun 05 '16 at 12:20
  • @paul, i thought that the pool will manage the connection each time a new query is executed. But its not. I get this error:- Error: Cannot enqueue Query after fatal error. I believe that since the file upload process and its operation are handled in single request its not managing the connections – Rahul Singh Jun 05 '16 at 13:18
  • No, it probably means you have an error somewhere that you're not handling. The request lifecycle would tie each connection to a single request; the pool lifecycle should manage a pool of connections and issue one that's free. – Paul Jun 05 '16 at 16:25
  • when the file size is small say 1 MB, it works fine. But if the file size is big say 100 MB it doesn't work. If error handling had been the case then it should not work for small file either. – Rahul Singh Jun 05 '16 at 16:31
  • That's only true if the file size itself isn't the cause of the exception, or related in some fashion. You haven't shared all your code; for all I know the system you're saving files to has a limit or is over disk capacity at 90MB. – Paul Jun 05 '16 at 16:33
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/113854/discussion-between-rahul-singh-and-paul). – Rahul Singh Jun 05 '16 at 16:34

4 Answers4

3

Actually, I decided to google your error for you, and after reading this thread: https://github.com/felixge/node-mysql/issues/832 I realized that you're not releasing the connection after the first query completes, and so the pool never tries to issue you a new one. You were correct that the stale connection might be the problem. here's how you fix that if it is:

upload.on('begin', function (fileInfo, reqs, resp) { 
    var fileType = resp.req.fields.file_type;
    var originalFileName = fileInfo.name;
     var renamedFilename = file.fileRename(fileInfo,fileType);
    /*renaming the file */
    fileInfo.name = renamedFilename;

    /*start: log the details in database;*/
    var utcMoment = conf.moment.utc();
    var UtcSCPDateTime = new Date( utcMoment.format() );
    var activityData = {
        activity_type     : conf.LIST_UPLOAD_BEGIN,
        username          : test ,
        message           : 'test has started the upload process for the file',
        activity_datetime : UtcSCPDateTime 
    };
    reqs.params.activityData = activityData;
    req.getConnection(function(err,connection) {
    var dbData = req.params.activityData;
    var activity_type = dbData.activity_type;
    console.dir("[ Connection ID:- ] "+connection.threadId+' ] [ Activity type:- ] '+activity_type);
    var insertQuery = connection.query("INSERT INTO tblListmanagerActivityLog SET ? ",dbData, function(err, result) {
        if (err) {
            console.log("Error inserting while performing insert for activity "+activity_type+" : %s ",err );
        } else {
            console.log('Insert successfull');
        }
        /// Here is the change:
        connection.release();
    });

});
    /*end: log the details in database;*/
});
Paul
  • 35,689
  • 11
  • 93
  • 122
  • Hi @paul I added connection.release(); but its still using the old reference. '[ Connection ID:- ] 1110 ] [ Activity type:- ] List Upload Begin' Insert successfull '[ Connection ID:- ] 1110 ] [ Activity type:- ] List Upload Complete' '[ Connection ID:- ] 1110 ] [ Activity type:- ] List Copy' Error inserting while performing insert for activity List Upload Complete : Error: Cannot enqueue Query after fatal error. – Rahul Singh Jun 06 '16 at 07:02
  • Hi @paul, it seems that express-myconnection module has some issues. I changed the pool option to single and it seems to work fine for 90MB file too. I will test it again with some more big files. Thanks for all the help. – Rahul Singh Jun 06 '16 at 13:40
  • Glad I could help, however small. I was going to mention that I've never used that particular module, and that most folks I know tend to manage their own connections (either by creating a new one each request or explicitly pulling / releasing from a pool). – Paul Jun 06 '16 at 14:25
  • For pool connection to work we need to comment out https://github.com/pwalczyszyn/express-myconnection/blob/master/lib/express-myconnection.js#L84 this line. Hope it helps anyone facing the same issue. – Rahul Singh Jun 07 '16 at 09:24
  • @RahulSingh, you should a) answer your own question with that then (makes it easier for others to find) and b) submit a pull request to the module owner to fix it. Looking at the code you referenced, it looks like a straight-up misimplementation of the pool. – Paul Jun 07 '16 at 12:29
  • Or instead of connection.release(); could be `connection.end();`. – Lucas Oct 08 '19 at 12:20
2

I fixed mine. I always suspect that when errors occur along with my queries it has something to do with the MySQL80 Service being stopped in the background. In case other solutions failed. Try going to the task manager, head to the services, find MySQL80 and check if it is stopped, when it is, click start or set it as automatic so that it will start at the moment desktop is running.

Dimes
  • 21
  • 5
0

For pool connection to work we need to comment out https://github.com/pwalczyszyn/express-myconnection/blob/master/lib/express-myconnection.js#L84 this line. Hope it helps anyone facing the same issue.

Also we can use single connection option.

Rahul Singh
  • 133
  • 1
  • 2
  • 11
-3

I had the same issue. Came across one solution -

Re-Install MySQl and while doing so, in the configuration step, select "legacy encryption" option instead and finish the installation.

Hope this helps!

Arunabh Arjun
  • 1
  • 2