mysql module in node.js not works in my case

Question

So i have next test code, which i found here

// Include http module,
var http = require('http'),
// And mysql module you've just installed.
   mysql = require("mysql");
// Create the connection.
// Data is default to new mysql installation and should be changed according to your configuration.
var connection = mysql.createConnection({
   user: "root",
   password: "pass",
   database: "db_name"
});
// Create the http server.
http.createServer(function (request, response) {
   // Attach listener on end event.
   request.on('end', function () {
      // Query the database.
      connection.query('SELECT * FROM your_table;', function (error, rows, fields) {
         response.writeHead(200, {
            'Content-Type': 'x-application/json'
         });
         // Send data as JSON string.
         // Rows variable holds the result of the query.
         response.end(JSON.stringify(rows));
      });
   });
// Listen on the 8080 port.
}).listen(8080);

i download mysql module, install mysql of course, and run next script, and get no results. Can you give me advice what i do wrong?

When i trying to load

http://localhost:8080/

browser trying to load page several minutes, and also no result.

Thanks.

update enter image description here

Possible duplicate of [Node.js response from http request not calling 'end' event without including 'data' event](http://stackoverflow.com/questions/23817180/node-js-response-from-http-request-not-calling-end-event-without-including-da) — T J, Feb 12 '16 at 11:52

Thanish · Answer 1 · 2012-10-15T12:19:27.900

2

I think you forgot

connection.connect()

I'm using these for while now and it works fine for me.

// Include http module,
var http = require('http'),
// And mysql module you've just installed.
   mysql = require("mysql");
// Create the connection.
// Data is default to new mysql installation and should be changed according to your configuration.
var connection = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "pass",
   database: "db_name"
});
connection.connect();
// Create the http server.
http.createServer(function (request, response) {
   // Attach listener on end event.
   request.on('end', function () {
      // Query the database.
      connection.query('SELECT * FROM your_table;', function (error, rows, fields) {
         response.writeHead(200, {
            'Content-Type': 'x-application/json'
         });
         // Send data as JSON string.
         // Rows variable holds the result of the query.
         response.end(JSON.stringify(rows));
      });
   });
// Listen on the 8080 port.
}).listen(8080);

edited Oct 15 '12 at 12:19

answered Oct 15 '12 at 12:01

Thanish

309
2
5

hm, i change my code according to yours answer, but the same result – Ishikawa Yoshi Oct 15 '12 at 12:08
npm install mysql@2.0.0-alpha2 is this enough to install mysql module? – Ishikawa Yoshi Oct 15 '12 at 12:15
1

yeah that's exactly what I did. Try adjusting your db configs and add 'host' – Thanish Oct 15 '12 at 12:18
Are you sure MySQL is working and listening on default port? else try adding 'port' when creating the connection. It's confusing. And just to make sure, try it without the http server. – Thanish Oct 17 '12 at 05:45
i work with mysql server from command line, and it works great, also i connected to server with mysql java connector, and it also works great. i try to go step by step from node cli and show you logs – Ishikawa Yoshi Oct 17 '12 at 07:03
`connection.query` will connect automatically – T J Feb 12 '16 at 11:48

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

Few previous answers that should help you

Express.JS + Node-Mysql and 1 connection per http request

How to query mysql before starting http server in nodejs

get simple info from db into a node.js http server response

to use pools in mysql Node.js MySQL Needing Persistent Connection

A more little complex solution separate your code into files and put the db code either in db.js or in reponse handler or router.

My code is using pools.

Save to the five files then run npm install

e.g (please excuse the terrible formatting)

    // requestHandlers.js
    // request handler with mysql code

    var mysql = require("mysql");
    var Query1 = "SELECT * from Tablexyz WHERE xx = 'doh'";

    var body = '<html>'+
'<head>'+
'<meta http-equiv="Content-Type" content="text/html; '+
'charset=UTF-8" />'+
'</head>'+
'<body>'+
'<h1> Query </h1>';

    var body1 = '</body>'+
'</html>';

    var pool = mysql.createPool({
    host     : 'host',
    user     : 'dbuser',
    password : "pword",
    database : 'database',
    connectionLimit: 10,
    queueLimit: 10
     });


    function qquery(callback){
    console.log('in qquery');
        pool.getConnection(function(err, connection) {
        pool.query( Query1 , function(err, rows ,fields) {
            if (err) {
                    return callback(new Error("An error has occured" + err));
                }
            if (rows.length > 0){
             callback( rows);
             connection.release();          
            }
        });
        });
        }

    function start(response) {
    console.log("Request handler 'start' was called.");


    qquery( function (rows , err) {
             if (err) {
                console.log(err);
             }
             else{
                 console.log(rows);
                 response.writeHead(200, {"Content-Type": "text/html"});
                             response.write(body);
                 response.write( 'query: ' + JSON.stringify( rows ) );
                 response.write(body1);
                             response.end();
             }
    });


}

exports.start = start;

    ///////////////////////////////////- new file
    //server.js

    var http = require("http");
    var url = require("url");

    function start(route, handle) {
     function onRequest(request, response) {
    var pathname = url.parse(request.url).pathname;
    console.log("Request for " + pathname + " received.");
    route(handle, pathname, response);
     }
     http.createServer(onRequest).listen(8888);
     console.log("Server has started.");
    }

    exports.start = start;
  ///////////////////////////////////-new file
  // router.js

  function route(handle, pathname, response) {
   console.log("About to route a request for " + pathname);
   if (typeof handle[pathname] === 'function') {
    handle[pathname](response);
   } else {
    console.log("No request handler found for " + pathname);
    response.writeHead(404, {"Content-Type": "text/plain"});
    response.write("404 Not found");
    response.end();
   }
  }

  exports.route = route;
  ///////////////////////////////////- new file
  //index.js

  var server = require("./server");
  var router = require("./router");
  var requestHandlers = require("./requestHandlers");
  var handle = {}
  handle["/"] = requestHandlers.start;
  handle["/start"] = requestHandlers.start;
  handle["/upload"] = requestHandlers.upload;
  server.start(router.route, handle);

  ///////////////////////////////////- new file
  //package.json
   {
     "name": "application-name",
     "version": "0.0.1",
     "private": true,
     "scripts": {
     "start": "node app.js"
    },
     "dependencies": 
    {
      "express": "3.4.0",
      "jade": "*",
      "stylus": "*",
      "mysql": "*"
     }

}

score 0 · Answer 3 · answered Oct 23 '13 at 06:40

@Ishikawa Yoshi

use a callback

// Include http module
var http = require('http')
// And mysql module you've just installed.
 mysql = require("mysql");

 var Query1 = "SELECT * from Tablexyz WHERE xx = 'doh'";

   var connection = mysql.createConnection({
    host     : 'host',
    user     : 'dbuser',
    password : "pword",
    database : 'database',
    });

   connection.connect();

   function getData(callback){
   console.log('in getData');
    connection.query( Query1 , function(err, rows ,fields) {
    if (err) {
        return callback(new Error("An error has occured" + err));
    }
    if (rows.length > 0){
        callback( rows);
    }

});
}

// Create the http server.
http.createServer(function (request, response) {
// Attach listener on end event.

  // Query the database.
 getData(function (rows , err) {
    if (err) {
       console.log(err);
    }
     else{
         response.writeHead(200, {"Content-Type": "text/html"});
         response.write( 'query: ' + JSON.stringify( rows ) );
         response.end();
     }
});
   // Listen on the 4001 port.
   }).listen(4001);

mysql module in node.js not works in my case

3 Answers3