MySql command is work from terminal but is not working from node js

Question

I have a strange problem in my nodejs website. I have a function to execute an SQL command. It works perfectly but after running server for a few days. It start to return an empty array!!! And when I restart the server it back to work again but still crashing after few days.

This is the error (the empty array error) :

TypeError: Cannot read property '0' of undefined
    at Query._callback (/home/mikro/www/my_modules/database.js:47:27)
    at Query.Sequence.end (/home/mikro/www/node_modules/mysql/lib/protocol/sequences/Sequence.js:86:24)
    at /home/mikro/www/node_modules/mysql/lib/protocol/Protocol.js:226:14
    at _combinedTickCallback (internal/process/next_tick.js:73:7)
    at process._tickDomainCallback (internal/process/next_tick.js:128:9)

and this is the function :

login: function (username, password, callback) { 
        db.query('SELECT id, password, expdate FROM users WHERE username=' +
            mysql.escape(username) + ';', function (err, rows) {
            var row = rows[0];
            if (err) console.error(err)
            if (!row)
                callback(ERR_USER_NOT_FOUND, null);
            else
            bcrypt.compare(password, row.password, function (err, result) {
                if (result){
                    var expdate = row.expdate;
                    var currDate = moment(new Date()).format('YYYY-MM-DD HH:mm:ss');
                    if (moment(currDate).isAfter(expdate) && row.id !== 1)
                        callback ('Please pay your monthly fee to be able to login', null);
                    else
                        callback(null, row)
                }
                else {
                    callback(ERR_WRONG_PASSWORD, null);
                }
            })
        });
    }

I have Ubuntu server and run pm2 with nginx proxy. my dependencies :

"dependencies": {
    "bcrypt-nodejs": "latest",
    "body-parser": "~1.16.0",
    "connect-flash": "^0.1.1",
    "cookie-parser": "~1.4.3",
    "debug": "~2.6.0",
    "ejs": "~2.5.5",
    "express": "~4.14.1",
    "express-session": "^1.15.1",
    "moment": "^2.18.1",
    "morgan": "~1.7.0",
    "mysql": "latest",
    "serve-favicon": "~2.3.2",
    "socket.io": "latest",
    "jwt-simple": "latest",
    "jsonfile": "latest",
    "express-mysql-session": "latest",
    "express-rate-limit": "latest"
  }

Update1

This is the connection code :

var mysql  = require('mysql');
var db     = mysql.createConnection(require('./config'));
db.connect();

Is username meant to be a string? If so, it should be wrapped in quotes. If that's not the problem, would you mind posting the query that works successfully? — RToyo, Aug 01 '17 at 14:15
@RobbieToyota Yes it is a string and that wasn't the problem because it worked before and worked again after restarting the server. But the problem appears many time and solved every time that I reset the server. But I can't just reset the server every time that the problem appears! — Ammar Al-Kawaldeh, Aug 01 '17 at 16:03

score 3 · Answer 1 · answered Aug 01 '17 at 14:19

3

First you have to handle potential errors, if err is null, you can use the returned result. Also the returned result might be empty

if (err) callback(/*undefined_error*/, null);
if (!rows || rows.length == 0) callback(ERR_USER_NOT_FOUND, null);
var row = rows[0];
............

answered Aug 01 '17 at 14:19

Goolishka

210
1
11

Yes that is good but why the code is working many times before it is completely stopped? And the databases value didn't change. And why the function work again after restarting server ?? – Ammar Al-Kawaldeh Aug 01 '17 at 15:57
1

The server crashes when somebody tries to login with not existed username. your code looks for the entered username in the mysql database, the returned result is empty, next step your code tries do this `var row = rows[0]`, but rows array is empty, because the username has not been found in the database. – Goolishka Aug 02 '17 at 08:07

score 1 · Accepted Answer · answered Aug 01 '17 at 14:30

1

if I understood the issue correctly, it might be an issue of MySQl connection reset due to network timeout, or connection Idle time out.

better check the error at MySql connection code.

e.g. One of MySql connection issue logged at : nodejs mysql Error: Connection lost The server closed the connection

answered Aug 01 '17 at 14:30

jeetendra Mandal

150
8

It is good idea. But if it was a connection problem shouldn't there be an error that describe the problem ?? I didn't find anything except the error above. – Ammar Al-Kawaldeh Aug 01 '17 at 15:55
Hello, I've reinstall the server and clear all logs. First of all I tried to login and that success. But I know that the problem is still there . I check the error log file every hour and I got this error : Error: Connection lost: The server closed the connection. (/home/ammar/node_modules/mysql/lib/protocol/Protocol.js:113:13) So it is really a connection problem. thanks you very much – Ammar Al-Kawaldeh Aug 02 '17 at 15:35

MySql command is work from terminal but is not working from node js

Update1

2 Answers2