Option required to start TRANSACTION

[sapkal-manish]

I have created pool cluster in my app as follows :

var mysql = require('mysql');
var poolCluster = mysql.createPoolCluster();

//This is MASTER for both 'read' as well as 'write'
poolCluster.add('READ-WRITE', {
    host: 'rds.amazonaws.com', 
    user: 'user1',
    password: 'pswrd1',
    database: 'Database1',
    multipleStatements : true,
    debug: false,
    connectionLimit: 200,
//  queueLimit : 0,
//  waitForConnections : false
    timezone : '+05:30'  
});

//This is SLAVE (read-replica) for both 'read' queries
poolCluster.add('READ', {
    host: 'read-replica1.rds.amazonaws.com', 
    user: 'user1',
    password: 'pswrd1',
    database: 'Database1',
    multipleStatements : true,
    debug: false,
    connectionLimit: 200,
//  queueLimit : 0,
//  waitForConnections : false
    timezone : '+05:30'  
});

poolCluster.on('remove', function (nodeId) {
    console.log('REMOVED NODE : ' + nodeId); // nodeId 
});

function executeQuery(query, readQuery ) {

    //Question1: Here I want TRANSACTION (connection.beginTransaction() ), when my 'readQuery' variable = false, means every "READ-WRITE' queries should be warped with 'TRANSACTION', not read query. 

    //Question2: If I write my code in following style, Does 'pooling' feature used?

    poolCluster.of((readQuery ? "READ" : "READ-WRITE"), 'RANDOM').getConnection( 
function(err,     connection) {
    console.log('connected id ' + connection.threadId); //Here I am not getting threadId, why ?????

    connection.query(query, function(err, rows, fields) {
         connection.release(); //send back to pool
    });
})

}

In short (about 1 question), I need on option in getConnection() method, to start TRANSACTION.

[dougwilson]

Hi! You should be able to write your executeQuery as follows:

function executeQuery(query, readQuery) {
  var performQuery = readQuery ? runQuery : runTransactQuery;
  var pool = readQuery ? 'READ' : 'READ-WRITE';
  poolCluster.getConnection(pool, 'RANDOM', function(err, conn) {
    if (err) throw err; // always handle your errors in some way
    performQuery(conn, query, function (err) {
      conn.release();
      if (err) throw err; // always handle your errors in some way
    });
  });
}

function runQuery(conn, query, callback) {
  conn.query(query, callback);
}

// mostly take from https://github.com/felixge/node-mysql#transactions
function runTransactQuery(conn, query, callback) {
  conn.beginTransaction(function (err) {
    if (err) return callback(err);
    conn.query(query, rollbackOnError(conn, callback, function (err) {
      if (err) return callback(err);
      conn.commit(rollbackOnError(conn, callback, callback));
    }));
  });
}

function rollbackOnError(conn, callback, fn) {
  return function (err) {
    if (!err) return fn.apply(this, arguments);
    conn.rollback(function () {
      callback(err);
    });
  };
}

[sapkal-manish]

Thanks @dougwilson for reply. But sorry to say, when I have write code as you suggest, it's not work.
and "threadId" also not getting.

[dougwilson]

Unfortunately there isn't any more information you provided that I can work with. For example, what does it mean to not work? Are any of those error variables populated? In your original example, you were not checking your error variable, and if you don't, then you cannot expect threadId to work.

[sapkal-manish]

Hi @dougwilson, In my question, I have wrote code as follows :

function executeQuery(query, readQuery ) {
    poolCluster.of((readQuery ? "READ" : "READ-WRITE"), 'RANDOM').getConnection( 
function(err,     connection) {
   if (err) { 
        //log error here
       return;
   else if (connection) {
        console.log('connected id ' + connection.threadId); //Here I am not getting threadId, why ?????
        connection.query(query, function(err, rows, fields) {
             connection.release(); //send back to pool
             if (err) { throw err; }
             elseif (rows && rows.length > 1) { 
                  callback(null, rows);
             }
        });
    }
})

and it's working fine.

but when I have changed my code as you mention then rows.length is showing undefined and my console.log('rows:' + JSON.stringify(rows) + ', len:' + rows.length);

rows:{"fieldCount":0,"affectedRows":0,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}, len:undefined

and for threadId, If err on null/undefined, still "threadId" is undefined/null.

my last code is as follows (As per your example)

function executeQuery(query, readQuery ) {
   poolCluster.of((readQuery ? "READ" : "READ-WRITE"), 'RANDOM').getConnection( 
function(err,     connection) {
   if (err) { 
        //log error here
       return;
   else if (connection) {
        console.log('connected id ' + connection.threadId); //Here I am not getting threadId, why ?????
        var performQuery = readonly ? runQuery : runTransactQuery;
        performQuery(connection, query, function(err, rows, fields) { 
              console.log('result ' + err + ', rows:' + JSON.stringify(rows) + ', len:' + rows.length);
              connection.release(); //send back to pool
             if (err) { return callback(err, null); }
             elseif (rows && rows.length > 1) { 
                  callback(null, rows);
             }
        });
    }
})

I have just copy paste your functions (e.g. runQuery, runTransactQuery, rollbackOnError) as it is.
Thanks.

[dougwilson]

@sapkal-manish what version of this library are you using and what version of Node.js?

[sapkal-manish]

I have node-mysql (2.0.1) with nodejs (v0.10.37)

[dougwilson]

ThreadId was not added until version 2.2.0. You will need to upgrade to get the threadId.

[sapkal-manish]

Ok @dougwilson, that I have got it. but still I am not getting fetched row from mysql. In your "rollbackOnError" function, I have got rows with data (eg. console.log(arguments) shows me data), but when I tried in callback, there is only matadata comes

[dougwilson]

Ah, I see the error in my example. I am currently on a cell phone and should be back to a computer next week. Please remind me then to update my example if I haven't responded by then.

[dougwilson]

Or if you cannot figure out the fix to the example in the meantime :)