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
– RToyo, Commented Aug 1, 2017 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
– Ammar Al-Kawaldeh, Commented Aug 1, 2017 at 16:03

Goolishka · Accepted Answer · 2017-08-01 14:19:14Z

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 1, 2017 at 14:19

Goolishka

2501 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ammar Al-Kawaldeh Over a year ago

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 ??

Goolishka Over a year ago

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.

jeetendra Mandal · Accepted Answer · 2017-08-01 14:30:42Z

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 1, 2017 at 14:30

jeetendra Mandal

1508 bronze badges

2 Comments

Ammar Al-Kawaldeh Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

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

Update1

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update1

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related