Nodejs: mysql: just can query maximum two times

Question

I'm using mysql for my nodejs project.

Here is my sql code:

var mysql = require('mysql');

var config = {
    host: 'localhost',
    username: 'root',
    password: 'root',
    port: 8889,
    database: "BookDB",
    connectionLimit: 100,
    charset: 'UTF8_GENERAL_CI',
    dialect: "mysql"
};


var pool = mysql.createPool({
   connectionLimit: config.connectionLimit,
    host     : config.host,
    user     : config.username,
    password : config.password,
    database : config.database,
    port     : config.port,
    charset  : config.charset,
    dialect  : config.dialect
});

module.exports.pool = pool;

Here is my database query code. I insert to database 3 tables: User, Profile, Activity, respectively. My problem is: I always just run to two queries, and the last query look like never touch. I print error but show nothing.

For example: If I insert (User, Profile, Activity). Just Activity table cannot insert. If I insert (User, Activity, Profile). Just Profile table cannot insert. It means my insert code for each table is correct.

var async = require('async');
var pool = require('../config/mysql').pool;

var createUser = function createUser (username, password, email, firstName, lastName, avatarURL, // user table
                                      quote, about, homepage,                                    // profile table
                                      lastLoginIP,                                               // activity table
                                      callback) {
    // database connection
    var dbc;
    // user id for later reference
    var userId;
    var dateCreated = new Date();
    var dateUpdated = dateCreated;

    async.waterfall([
       // get connection
        function (callback) {
            pool.getConnection(callback);
        },

        // insert user table
        function (connection, callback) {
            dbc = connection;
            var params = {
                Username: username,
                Password: password,
                EmailAddress: email,
                FirstName: firstName,
                LastName: lastName,
                avatarURL: avatarURL,
                DateCreated: dateCreated,
                DateUpdated: dateUpdated
            };
            var query = "INSERT INTO USER SET ?";
            dbc.query(query, params, callback);
        },

        // insert activity table
        function(result, callback) {
            userId = result.insertId;
            console.log('second user id: ' + userId);
            var params = {
                ActivityID: userId,
                ProfileView: 0,
                LastLoginIP: lastLoginIP
            };
            var query = "INSERT INTO ACTIVITY SET ?";
            dbc.query(query, params, callback);
        },

        // insert profile table
        function (result, callback) {
           // userId = result.insertId;
            console.log('inserted userid: '+ userId);
            var params = {
                ProfileID: userId,
                Quote: quote,
                About: about,
                HomePage: homepage,
                DateCreated: dateCreated,
                DateUpdated: dateUpdated
            };
            var query = "INSERT INTO PROFILE SET ?";
            dbc.query(query, params, callback);
        },



        function (error, userData) {
            console.log('end block');
            if (dbc) dbc.release();
            if (error) {
                console.log('error');
                report_error(error);
            } else {
                callback(null, userData);
            }
        }
    ]);

};

module.exports.createUser = createUser;

Please figure out my problem.

Thanks :)

Answer 1

You should put your callback function outside the waterfall array. Like this:

var async = require('async');
var pool = require('../config/mysql').pool;

var createUser = function createUser (username, password, email, firstName, lastName, avatarURL, // user table
                                  quote, about, homepage,                                        // profile table
                                  lastLoginIP,                                               // activity table
                                  callback) {
// database connection
var dbc;
// user id for later reference
var userId;
var dateCreated = new Date();
var dateUpdated = dateCreated;

async.waterfall([
   // get connection
    function (callback) {
        pool.getConnection(callback);
    },

    // insert user table
    function (connection, callback) {
        dbc = connection;
        var params = {
            Username: username,
            Password: password,
            EmailAddress: email,
            FirstName: firstName,
            LastName: lastName,
            avatarURL: avatarURL,
            DateCreated: dateCreated,
            DateUpdated: dateUpdated
        };
        var query = "INSERT INTO USER SET ?";
        dbc.query(query, params, callback);
    },

    // insert activity table
    function(result, callback) {
        userId = result.insertId;
        console.log('second user id: ' + userId);
        var params = {
            ActivityID: userId,
            ProfileView: 0,
            LastLoginIP: lastLoginIP
        };
        var query = "INSERT INTO ACTIVITY SET ?";
        dbc.query(query, params, callback);
    },

    // insert profile table
    function (result, callback) {
       // userId = result.insertId;
        console.log('inserted userid: '+ userId);
        var params = {
            ProfileID: userId,
            Quote: quote,
            About: about,
            HomePage: homepage,
            DateCreated: dateCreated,
            DateUpdated: dateUpdated
        };
        var query = "INSERT INTO PROFILE SET ?";
        dbc.query(query, params, callback);
    }
],
    function (error, userData) {
        console.log('end block');
        if (dbc) dbc.release();
        if (error) {
            console.log('error');
            report_error(error);
        } else {
            callback(null, userData);
        }
    }
)};

module.exports.createUser = createUser;