NodeJS Unable To Open MySQL Connection Port

Question

I'm trying to insert some data into my mysql database using a loop. As you can see, I open/insert/close the connection once per cycle. However, it seems that my code isn't reopening my database connection after a loop.

Source Code:

var MySQLModule = require('mysql');
//var SleepModule = require('sleep');

var MySQL_Connection = MySQLModule.createConnection({host: '10.0.0.254', user: 'User', password: 'Password', database: 'Air_Pollution_Project'});

var i = 0;
while (i < 5) {
  MySQL_Connection.connect();
  //SleepModule.sleep(0);
  MySQL_Connection.query('INSERT INTO Air_Pollution_Reading_Record (Station_ID, Air_Pollution_Reading_Value) VALUES (2, 666)');
  MySQL_Connection.end();
  i++
};

Result:

events.js:183
      throw er; // Unhandled 'error' event
      ^

Error: Cannot enqueue Handshake after invoking quit.

Am I opening the connection correctly? What should I do.

score 0 · Accepted Answer · answered Mar 23 '18 at 13:58

0

You don't need to keep opening and closing the connection like that. I'm not sure if it is specifically causing your issue but you can try moving it outside the loop. Also wrapping your code in a try catch might reveal more info on the error

var i = 0;

try {
    MySQL_Connection.connect();

    while (i < 5) {
      MySQL_Connection.query('INSERT INTO Air_Pollution_Reading_Record (Station_ID, Air_Pollution_Reading_Value) VALUES (2, 666)');
      i++
    };

    MySQL_Connection.end(); 

}catch(err){
    console.log(err)
}

answered Mar 23 '18 at 13:58

Stretch0

8,362
13
71
133

Would it be a big deal if I never close my connection? I mean, I'm planning to use this loop to to insert data into the database indefinitely. This loop could be going on for days, or weeks (Actually as long as possible without intervention). – Marc Mar 26 '18 at 15:23
I think the connection will timeout after a while any way. So it's not a big deal but if you have a loop that is running for that long, you are doing something wrong. Each time you want to insert data into your DB, you should open the connection, insert / update / delete, then close connection. This will save you running out of memory on your server. – Stretch0 Mar 26 '18 at 15:26
That's why I'm trying to open then close the connection right after I finished inserting the data into the database. Should I just leave the close connection statement outside of the loop, then before the insert statement I do an If...Else statement such that it checks if the connection is open ELSE reopen the connection. – Marc Mar 26 '18 at 15:31
Or just an open connection at the start of each loop – Marc Mar 26 '18 at 15:31
No need for an `if / else`. Just open at start of loop and close after loop. – Stretch0 Mar 26 '18 at 15:53

score 0 · Answer 2 · answered Mar 23 '18 at 13:59

instead of opening and closing mysql port each time you need to insert data into bulk and close the connection after query execution

Possible Duplicate : Link

Useful link : How to insert bulk data into mysql using node js

MySQL_Connection.connect();
var sql = "INSERT INTO Air_Pollution_Reading_Record (Station_ID, Air_Pollution_Reading_Value) VALUES ?";
var values = [];
var i = 0;
while (i < 5) {
  var newArray = [2,666]; 
  values.push(newArray);
  i++
};

MySQL_Connection.query(sql, [values], function(err) {
    if (err) throw err;
    MySQL_Connection.end();
});

NodeJS Unable To Open MySQL Connection Port

2 Answers2