NodeJS and MySQL Getting 500 error after first query

Question

I have declared mysql, and my connection in app.js like so:

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: '192.168.1.75',
    user: 'dev',
    password: 'devaccount',
    database: 'PugIt'
});

app.set('connection', connection);

And in my User.js for registration I have:

router.route('/register/steam/finish')
    .get(function(req, res) {
        res.render('user/register_steam');
    })
    .post(function(req, res) {
        var connection = req.app.get('connection');
        connection.connect();

        // Look For Users
        connection.query("SELECT * FROM Users", function(err, rows, fields) {
            console.log('We Found Something!');
        });

        connection.end();
    });

When the page first loads and I hit register, it works fine, but if I hit the button a second time I get a 500 error on my server.

But if I manually declare var connection inside each route file, this does not happen.

How come I cannot use req.app.get with MySQL, I used this method when I used to use MongoDB which worked great that way I had one main config in app.js I could alter to change in all route files if I needed. Not sure why I'm getting a 500 error on second POST

mscdex · Accepted Answer · 2014-11-02 04:15:08Z

1

I think the connection.connect() and connection.end() on every POST request is causing problems. Drop those two lines and you should be good to go. This way the connection is only established once and all requests can re-use the same connection without constantly trying to tear it down and bring it back up again.

You can also create a pool of mysql connections if you find yourself needing greater concurrency with your database queries.

answered Nov 2, 2014 at 4:15

mscdex

107k15 gold badges201 silver badges159 bronze badges

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

1 Comment

Datsik Over a year ago

Thanks, I'm new to MySQL, I thought it was a security issue to leave the connection open after each query

Collectives™ on Stack Overflow

NodeJS and MySQL Getting 500 error after first query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related