Node-mysql not working properly... need debug ideas

Question

I am using node-mysql for the first time, and I have a program with no errors, no warnings, but is not working properly... Here is the code for the program, very simple:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database:  'nodetest',
  port: 8080
});

connection.connect();
var usr = "BLASHDASD"
var userId = usr;
var sql = 'UPDATE userlist SET user1= ' + connection.escape(userId) + ' WHERE id=1 ';
console.log(sql);

connection.query('sql', function(err, rows, fields) {
  console.log(err);
  console.log("BLAHSDBKASD");
});

connection.end();

And here is the console output:

C:\wamp\www\nodePHP-master\js>node nodeTest.js
UPDATE userlist SET user1= 'BLASHDASD' WHERE id=1

But nothing is happening in my MySQL table... I even copied and pasted the UPDATE line above and just ran it as SQL code and it worked great... Need some ideas of what is going on. Thanks a bunch

EDIT:

Answered my own question... was listening on wrong port, so connection was failing. Here is updated code for those interested/search in the future:

//TEST

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database:  'nodetest',
  port: 3306,
});

connection.connect(function(err){
    if(err) {
        console.log(err)
    } else {
        console.log("connected");
    }
});
var usr = "BLASHDASD"
var userId = usr;
var sql = 'UPDATE userlist SET user1= ' + connection.escape(userId) + ' WHERE id=1 ';
console.log(sql);

connection.query(sql, function(err, rows, fields) {
  console.log(err);
});

connection.end();

Please see my answer below. If your code works as it is written, you are just getting very lucky with timing. Try it against a remote database with a 'select' query and see if you have any results to read. — barry-johnson, Mar 03 '14 at 23:29

score 0 · Accepted Answer · edited May 23 '17 at 12:10

0

You are having problems with node's asynchronous nature, a very common issue when coming to Node. You also had a small but significant error in your code (you have 'sql' as a quoted string), but here is something structurally similar that should point you in the right direction.

var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'locahost',
    user     : 'foo',
    password : 'bar',
    database : 'test'
});

// the callback inside connect is called when the connection is good
connection.connect(function(err){
    var sql = "select 'Joe' as name from dual";

    connection.query(sql, function(err, rows, fields) {
        if (err) return console.log(err);
        //  you need to end your connection inside here.
        connection.end();
        console.log(rows[0].name);
    });
});

You will likely start wondering about ways to avoid all these callbacks. You may wish to look at my answer to this question for a more extended mysql example as well as an alternative implementation which offers an alternative to callback-mania.

edited May 23 '17 at 12:10

Community

1
1

answered Mar 03 '14 at 23:26

barry-johnson

3,204
1
17
19

Caught the 'sql' error thanks! Figured out but read through your post and the question you linked too... Appreciate it! – Andrew Bell Mar 03 '14 at 23:28
You're welcome. I'd be curious to hear from the person who downvoted as to the reason?? – barry-johnson Mar 03 '14 at 23:33
@barry-johnson I liked your answer. I am gonna upvote it to make it up for you! :D – Ravi Shankar Bharti Jan 08 '20 at 06:53
1

@RaviShankarBharti LOL - thanks, you're a prince! Although I have to say this is a very dated answer (although true as far as it goes). The linked question that discussed the promise-based implementation aged less well, as I would tend to recommend Bluebird or very often just native promises over Q. – barry-johnson Jan 08 '20 at 21:25
@barry-johnson Even if dated, the answer would work even now! and looks good to me! There was no need for a downvote! I don't what was the person thinking :D That's why i chose to upvote :D – Ravi Shankar Bharti Jan 09 '20 at 11:49

Node-mysql not working properly... need debug ideas

1 Answers1