node-mysql
A pure node.js JavaScript Client implementing the MySql protocol.
I 'am trying to insert some data with Node.js. I installed mysql support with npm . I just checked arround some source code, I've wrote following code , I can follow sql output in console.log and SQL output is correct. But It does not affect on any rows in mySQL database.
Here is my code :
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'cccc.net',
user : 'username',
password : 'password',
});
var post = {srcUserID: userSrcID, destUserID: msg.userid, messageContent: msg.txt, messageSendDate:sendDate };
connection.query('INSERT INTO messages VALUES ?', post, function(err, result) {
});
Source: (StackOverflow)
I need a persistent MySQL connection for my Node web app. The problem is that this happens about a few times a day:
Error: Connection lost: The server closed the connection.
at Protocol.end (/var/www/n/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:79:10)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:895:16
at process._tickCallback (node.js:415:13)
error: Forever detected script exited with code: 8
error: Forever restarting script for 2 time
info: socket.io started
Here is my connection code:
// Yes I know multipleStatements can be dangerous in the wrong hands.
var sql = mysql.createConnection({
host: 'localhost',
user: 'my_username',
password: 'my_password',
database: 'my_database',
multipleStatements: true
});
sql.connect();
function handleDisconnect(connection) {
connection.on('error', function(err) {
if (!err.fatal) {
return;
}
if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
throw err;
}
console.log('Re-connecting lost connection: ' + err.stack);
sql = mysql.createConnection(connection.config);
handleDisconnect(sql);
sql.connect();
});
}
handleDisconnect(sql);
As you can see, the handleDisconnect code does not work..
Source: (StackOverflow)
I'm trying to reproduce a MySQL error I'm seeing in my node.js app on EC2 with the node mysql library:
Connection lost: The server closed the connection.
I am unable to reproduce the error locally- killing the database is handled just fine by my code- it just rechecks every few seconds and reconnects to the db once it is restarted. On EC2, it happens around 4am Pacific, but the db is still up and running fine.
I'd like to
- Reproduce the crash with my local mysql
- Add whatever logic I need in my mysql helper module to handle this
Here's the error in my node.js app:
2012-10-22T08:45:40.518Z - error: uncaughtException date=Mon Oct 22
2012 08:45:40 GMT+0000 (UTC), pid=14184, uid=0, gid=0,
cwd=/home/ec2-user/my-app, execPath=/usr/bin/nodejs,
version=v0.6.18, argv=[/usr/local/bin/node,
/home/ec2-user/my-app/app.js, --my-app], rss=15310848,
heapTotal=6311392, heapUsed=5123292, loadavg=[0.0029296875,
0.0146484375, 0.04541015625], uptime=3238343.511107486, trace=[column=13,
file=/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js,
function=Protocol.end, line=63, method=end, native=false, column=10,
file=stream.js, function=Socket.onend, line=80, method=onend,
native=false, column=20, file=events.js, function=Socket.emit,
line=88, method=emit, native=false, column=51, file=net.js,
function=TCP.onread, line=388, method=onread, native=false],
stack=[Error: Connection lost: The server closed the connection.,
at Protocol.end
(/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js:63:13), at Socket.onend (stream.js:80:10), at Socket.emit
(events.js:88:20), at TCP.onread (net.js:388:51)]
Here's my code (mysql helper module):
module.exports = function (conf,logger) {
var mysql = require('mysql');
var connectionState = false;
var connection = mysql.createConnection({
host: conf.db.hostname,
user: conf.db.user,
password: conf.db.pass,
database: conf.db.schema,
insecureAuth: true
});
function attemptConnection(connection) {
if(!connectionState){
connection = mysql.createConnection(connection.config);
connection.connect(function (err) {
// connected! (unless `err` is set)
if (err) {
logger.error('mysql db unable to connect: ' + err);
connectionState = false;
} else {
logger.info('mysql connect!');
connectionState = true;
}
});
connection.on('close', function (err) {
logger.error('mysqldb conn close');
connectionState = false;
});
connection.on('error', function (err) {
logger.error('mysqldb error: ' + err);
connectionState = false;
/*
if (!err.fatal) {
return;
}
if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
throw err;
}
*/
});
}
}
attemptConnection(connection);
var dbConnChecker = setInterval(function(){
if(!connectionState){
logger.info('not connected, attempting reconnect');
attemptConnection(connection);
}
}, conf.db.checkInterval);
return connection;
};
Source: (StackOverflow)
I'm trying to work through integrating the express framework for node.js and the mysql module https://npmjs.org/package/mysql. I have a simple application setup (by using the express command line) and I also have a module declared for working with some of the database properties.
My DB module is setup like this:
app.js
node_modules
|___db
|
node_modules
|___mysql
With the mysql module setup to be a dependency of the db module.
In my index.js for the db module I have some module exports setup to be accessed by the application:
/*
* Connection params for database
*/
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'localhost',
user: 'user',
password: 'password',
database: 'database',
});
var connect = connection.connect(function(err){
if(!err){
console.log("You are connected to the database.");
}
else{
throw err;
}
});
var end = connection.end(function(err){
if(!err){
console.log("Mysql connection is terminated.")
}
else{
throw err;
}
});
module.exports = {
connect: connect,
connection: connection,
end: end,
}
In my app.js file I am requiring my db module and specifying some routes. I'm also trying to use a route middleware function (estDb) inside the app.get method for the clients route:
/**
* Module dependencies.
*/
var express = require('express')
, routes = require('./routes')
, clients = require('./routes/clients')
, user = require('./routes/user')
, http = require('http')
, path = require('path')
, db = require('db');
var app = express();
app.configure(function(){
app.set('port', process.env.PORT || 3000);
app.set('views', __dirname + '/views');
app.set('view engine', 'jade');
app.use(express.favicon());
app.use(express.logger('dev'));
app.use(express.bodyParser());
app.use(express.methodOverride());
app.use(app.router);
app.use(express.static(path.join(__dirname, 'public')));
});
app.configure('development', function(){
app.use(express.errorHandler());
});
var estDb = function(req, res, next){
db.connect;
db.connection.query("SELECT * FROM Table", function(err, results){
if(!err){
req.results = results;
}
else{
throw err;
}
});
db.end;
next();
}
app.get('/', routes.index);
app.get('/clients', estDb, clients.view);
http.createServer(app).listen(app.get('port'), function(){
console.log("Express server listening on port " + app.get('port'));
});
The Problem
I'm having is that it seems that my db functions (my module exports) are getting called when I start the application as I'm getting the log:
Express server listening on port 3000
You are connected to mysql.
Mysql connection is terminated.
NOT when the url http://localhost/clients is requested (which is what I have the route defined as). As you can see, it is firing the db.connect() and the db.end() right after the console logs the "Express server listening on port 3000" message -- which leads me to believe it's firing from the custom db module I'm using. Subsequently, when I go to the route http://localhost/clients I get an error:
500 Error: Cannot enqueue Query after invoking quit.
If I remove the connection.end() function from the db module, I can connect to the database and retrieve results; however, if I reload the page and try to load the results again, I get an error:
Cannot enqueue Handshake after already enqueuing a Handshake
I don't understand why my module exports are firing when I start the application? I think this is where I'm getting in trouble.
Any suggestions or help on this would be great.
Source: (StackOverflow)
With the node-mysql module, there are two connection options - a single connection and a connection pool. What is the best way to set up a connection to a MySQL database, using a single global connection for all requests, or creating a pool of connections and taking one from the pool for each request? Or is there a better way to do this? Will I run in to problems using just a single shared connection for all requests?
Source: (StackOverflow)
I want to test hapi routes with lab, I am using mysql database.
The problem using Server.inject to test the route is that i can't mock the database because I am not calling the file that contains the handler function, so how do I inject mock database in handler?
Source: (StackOverflow)
My Node.js Code is like below
CODE1: below
var http=require('http');
var MySQL = require('mysql');
mysql = MySQL.createConnection(...)
http.createServer(function(req, res){
// the query will take several seconds
mysql.query("SELECT SLEEP(1)", function....)
});
http.listen(...);
The problem is the server will be crash when I refresh the page too fast. I think is the node-mysql module's problem, it process the query in a queue.So I try to create a connection pool.
CODE2: below
....
var pool = require('generic-pool');
var mp = pool.Pool({
...
create: function(cb){
client = MySQL.createConnection(...);
cb(null, client)
},
max: 10, // up to 10 connection
min: 2,
...
});
....
mp.acquire(function(err, mysql){
// the query will take several seconds
mysql.query("SELECT SLEEP(1)", function....)
mp.release(mysql);
});
....
But the problem still here, Why? How can I fix this.
EDIT: I launch 100 requests with 100 concurrency, 10 seconds expected. But it take 20 seconds. Why? Is The pool only support up to 5 connection?
Source: (StackOverflow)
I am trying to figure out the best way to pass a mysql connection (using node-mysql) between my routes for express.js. I am dynamically adding each route (using a for each file loop in routes), meaning I can't just pass in the connection to routes that need it. I either need to pass it to every route or none at all. I didn't like the idea of passing it to ones that dont need it so I created a dbConnection.js that the routes can individually import if they need. The problem is that I dont think I am doing it correctly. As of now, my dbConnection.js contains:
var mysql = require('mysql');
var db = null;
module.exports = function () {
if(!db) {
db = mysql.createConnection({
socketPath: '/tmp/mysql.sock',
user: '*********',
password: '*********',
database: '**********'
});
}
return db;
};
And I am importing it into each route using:
var db = require('../dbConnection.js');
var connection = new db();
But I would like to do it like this:
var connection = require('../dbConnection.js');
When I try it like this, however, I get an error saying connection has no method 'query' when I try to make a query.
Source: (StackOverflow)
I am doing a quick performance test for NodeJS vs. Java. The simple use case chosen is querying a single table in MySQL database. The initial results were as follows:
Platform | DB Connections | CPU Usage | Memory Usage | Requests/second
==============================|================|===========|===============|================
Node 0.10/MySQL | 20 | 34% | 57M | 1295
JBoss EAP 6.2/JPA | 20 | 100% | 525M | 4622
Spring 3.2.6/JDBC/Tomcat 7.0 | 20 | 100% | 860M | 4275
Note that Node's CPU and memory usage are way lower than Java but the throughput is also about a third! Then I realized that Java was utilizing all four cores on my CPU, whereas Node was running on only one core. So I changed the Node code to incorporate the cluster module and now it was utilizing all four cores. Here are the new results:
Platform | DB Connections | CPU Usage | Memory Usage | Requests/second
==============================|================|===========|===============|================
Node 0.10/MySQL (quad core) | 20 (5 x 4) | 100% | 228M (57 x 4) | 2213
Note that the CPU and memory usage have now gone up proportionately but the throughput has only gone up by 70%. I was expecting a four fold increase, exceeding the Java throughput. How can I account for the descrepancy? What can I do to increase the throughput linearly?
Here's the code for utilizing multiple cores:
if (Cluster.isMaster) {
var numCPUs = require("os").cpus().length;
for (var i = 0; i < numCPUs; i++) {
Cluster.fork();
}
Cluster.on("exit", function(worker, code, signal) {
Cluster.fork();
});
}
else {
// Create an express app
var app = Express();
app.use(Express.json());
app.use(enableCORS);
app.use(Express.urlencoded());
// Add routes
// GET /orders
app.get('/orders', OrderResource.findAll);
// Create an http server and give it the
// express app to handle http requests
var server = Http.createServer(app);
server.listen(8080, function() {
console.log('Listening on port 8080');
});
}
I am using the node-mysql driver for querying the database. The connection pool is set to 5 connections per core, however that makes no difference. If I set this number to 1 or 20, I get approximately the same throughput!
var pool = Mysql.createPool({
host: 'localhost',
user: 'bfoms_javaee',
password: 'bfoms_javaee',
database: 'bfoms_javaee',
connectionLimit: 5
});
exports.findAll = function(req, res) {
pool.query('SELECT * FROM orders WHERE symbol="GOOG"', function(err, rows, fields) {
if (err) throw err;
res.send(rows);
});
};
Source: (StackOverflow)
Is it possible to prevent SQL injections in Node.js (preferably with a module) in the same way that PHP had Prepared Statements that protected against them.
If so, how? If not, what are some examples that might bypass the code I've provided (see below).
Some Context:
I'm making a web application with a back-end stack consisting of Node.js + MySql using the node-mysql module. From a usability perspective, the module is great, but it has not yet implemented something akin to PHP's Prepared Statements (though I'm aware it is on the todo).
From my understanding, PHP's implementation of prepared statements, among other things, helped greatly in the prevention of SQL injections. I'm worried, though, that my node.js app may be open to similar attacks, even with the string escaping provided by default (as in the code snippet below).
node-mysql seems to be the most popular mysql connector for node.js, so I was wondering what other people might be doing (if anything) to account for this issue - or if it is even an issue with node.js to begin with (not sure how this wouldn't be, since user/client-side input is involved).
Should I switch to node-mysql-native for the time being, since it does provide prepared statements? I'm hesitant to do this, because it does not seem to be as active as node-mysql (though that may just mean that it is complete).
Here is a snippet of user registration code, which uses the sanitizer module, along with node-mysql's prepared statement-like syntax (which, as I mentioned above, does character escaping), to prevent cross site scripting and sql injections, respectively:
// Prevent xss
var clean_user = sanitizer.sanitize(username);
// assume password is hashed already
var post = {Username: clean_user, Password: hash};
// This just uses connection.escape() underneath
var query = connection.query('INSERT INTO users SET ?', post,
function(err, results)
{
// Can a Sql injection happen here?
});
Source: (StackOverflow)
i have a recursive query like this (note: this is just an example):
var user = function(data)
{
this.minions = [];
this.loadMinions = function()
{
_user = this;
database.query('select * from users where owner='+data.id,function(err,result,fields)
{
for(var m in result)
{
_user.minions[result[m].id] = new user(result[m]);
_user.minions[result[m].id].loadMinions();
}
}
console.log("loaded all minions");
}
}
currentUser = new user(ID);
for (var m in currentUser.minions)
{
console.log("minion found!");
}
this don't work because the timmings are all wrong, the code don't wait for the query.
i've tried to do this:
var MyQuery = function(QueryString){
var Data;
var Done = false;
database.query(QueryString, function(err, result, fields) {
Data = result;
Done = true;
});
while(Done != true){};
return Data;
}
var user = function(data)
{
this.minions = [];
this.loadMinions = function()
{
_user = this;
result= MyQuery('select * from users where owner='+data.id);
for(var m in result)
{
_user.minions[result[m].id] = new user(result[m]);
_user.minions[result[m].id].loadMinions();
}
console.log("loaded all minions");
}
}
currentUser = new user(ID);
for (var m in currentUser.minions)
{
console.log("minion found!");
}
but he just freezes on the while, am i missing something?
Source: (StackOverflow)
I'm coming from a mostly PHP / MySQL background, and dabbing into node.js to create a blogging system for my own testing and for learning how to do things. The issue I'm having here is that I can't seem to figure out how to do a proper query. I've checked the docs for node-mysql but haven't been able to find out much about doing queries.
If I do var posts = rows[0]; I get the array of the the table and it looks good, but I'm trying to pass the specific fields to variables, but when I do the code below, I get a bunch of "unspecified"'s. I know there's something wrong here, and I would normally do a mysql_fetch_array in PHP, but I don't know the method for doing this in node.js and node-mysql.
connection.query('SELECT * FROM posts ORDER BY date', function(err, rows, fields) {
if (err) throw (err);
var post_date = rows[1];
var post_author = rows[2];
var post_content = rows[3];
console.log('Date: ', post_date);
console.log('Author: ', post_author);
console.log('Content: ', post_content);
});
Source: (StackOverflow)
How to generate a XML response for a request ?
Currently what i am doing::
I have a simple code that gives a JSON response for a specific route,
My present CODE::
var express = require('express')
, async = require('async')
, http = require('http')
, mysql = require('mysql');
var app = express();
var connection = mysql.createConnection({
host: 'localhost',
user: '****',
password: "****",
database: 'restaurants'
});
connection.connect();
// all environments
app.set('port', process.env.PORT || 1235);
app.use(express.static(__dirname + '/public/images'));
app.get('/DescriptionSortedRating/',function(request,response){
var name_of_restaurants;
async.series( [
// Get the first table contents
function ( callback ) {
connection.query('SELECT * FROM restaurants ORDER BY restaurantRATING', function(err, rows, fields)
{
console.log('Connection result error '+err);
name_of_restaurants = rows;
callback();
});
}
// Send the response
], function ( error, results ) {
response.json({
'restaurants' : name_of_restaurants
});
} );
} );
http.createServer(app).listen(app.get('port'), function(){
console.log('Express server listening on port ' + app.get('port'));
});
What i am trying to do::
- I want to generate a XML response for the code above
- What changes should i need to make
Source: (StackOverflow)
I have following code.
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database : 'test'
});
connection.connect();
var userdata = '24';
var sql = 'SELECT COUNT(*) FROM names WHERE age = ?'
connection.query(sql, [userdata], function(err, rows, fields) {
if (err) throw err;
console.log('Query result: ', rows);
});
connection.end();
I want to get the total number of records from table 'names' where 'age' = 24. I receive the following on my node.js command prompt.
Query result: [ { 'COUNT(*)': '14' } ]
My question is how to store this number 14 in a variable for further use.
I know I can do this just by changing the 'sql' line to
var sql = 'SELECT * FROM names WHERE age = ?'
and then console line to
console.log('Query result: ', rows.length);
But still is there another way?
Source: (StackOverflow)
That is my code:
var mysql = require('mysql');
var client = mysql.createConnection({
host : 'somehost',
port : 'myport',
user : 'username',
password : 'password',
database : 'mydb'
});
//get posts
function get(callback){
var result = [];
var queryString = "SELECT id,title,short_text,creation_date FROM posts");
var query = client.query(queryString);
query
.on('error', function(err){
callback(err, null);
})
.on('fields', function(fields){
// the field packets for the rows to follow
})
.on('result', function(row){
result.push(row);
})
.on('end', function(){
callback(null, result);
// all rows have been received
});
}
It works succees sometimes but sometimes return error:
{ [Error: Cannot enqueue Query after being destroyed.] code: 'PROTOCOL_ENQUEUE_A
FTER_DESTROY', fatal: false }
Who can say what's wrong?
Source: (StackOverflow)