4月 272014
 

node.js项目中有个项目发现运行一段时间后总会自动关闭,查看输出日志会看到下面的输出: error Caught exception: Error: Connection lost: The server closed the connection.  原来使用的node-mysql模块在mysql连接timeout或出现异常情况下默认是没有重连机制的,可以用下面的类似代码解决:

  var db;

  function handleConnect() {
    db = mysql.createConnection({
      host     : config.mysql.host,
      user     : config.mysql.user,
      password : config.mysql.password,
      database : config.mysql.database
    }); // Recreate the connection, since the old one cannot be reused.

    db.connect(function(err) {              // The server is either down
      if(err) {                                     // or restarting (takes a while sometimes).
        console.log(‘error when connecting to db:’, err);
        setTimeout(handleConnect, 2000); // We introduce a delay before attempting to reconnect,
      }                                     // to avoid a hot loop, and to allow our node script to
    });                                     // process asynchronous requests in the meantime.
                                            // If you’re also serving http, display a 503 error.
    db.on(‘error’, function(err) {
      console.log(‘db error’, err);
      if(err.code === ‘PROTOCOL_CONNECTION_LOST’) { // Connection to the MySQL server is usually
        handleConnect();                         // lost due to either server restart, or a
      } else {                                      // connnection idle timeout (the wait_timeout
        throw err;                                  // server variable configures this)
      }
    });
  }

  handleConnect();

7月 112013
 

先更新一条数据

UPDATE user_info SET userName = 'kk' WHERE userId = 1;

SELECT * FROM user_info

 
现在的数据
 
写一个事务,只选中前面的语句执行:
 
SET AUTOCOMMIT=0;

START TRANSACTION;
SELECT * FROM user_info FOR UPDATE;
COMMIT;

如果使用node.js执行,控制台上会看到待执行的sql处于等待状态:
 

上面报错,是因为超时了。
如果执行commit后,再执行node.js脚本(执行的是一句更新sql–UPDATE user_info SET userName = ‘张一’ WHERE userId = 1)

 

这里主要用到了node-mysql-queues,它需要先安装node-mysql

可能因为版本问题吧,它的页面给出的操作方法已经失效了。正确的应该如下:

/**
 * Created with JetBrains WebStorm.
 */
var mysql = require('mysql');

var connection = mysql.createConnection({
    host : 'localhost',
    port : 3306,
    user : 'root',
    password : '123456',
    database : 'test',
    charset : 'UTF8_GENERAL_CI',
    debug : false
});

connection.connect();

//connection.query('CALL proc_simple(1, @a, @b);', function(err, rows, fields) {
//    if (err) {
//       throw err;
//    }
//
//    var results = rows[0];
//    var row = results[0];
//    console.log("userName:",row.uName, "  count:", row.totalCount);
//});

var queues = require('mysql-queues');
const DEBUG = true;
queues(connection, DEBUG);

var trans = connection.startTransaction();
trans.query("UPDATE user_info SET userName = ? WHERE userId = ?", ["张一", 1], function(err, info) {
   if (err) {
       throw err;
       trans.rollback();
   } else {
       trans.commit(function(err, info) {
           console.log(info);
       });
   }
});
trans.execute();
console.log('execute');
//connection.end();

 

 

 

 

 

 

 

 

 

它还支持一次执行多条sql,其实就是把sql语句放入一个数组,然后循环执行该数组内的每条SQL。

var q = client.createQueue();
q.query(...);
q.query(...);
q.execute();



正常使用的话,请参考如下的代码:

var mysql = require('mysql');
var client = mysql.createConnection({//createClient(已失效) -> createConnection
    user: 'root',
    password: 'root'
});
//Enable mysql-queues
var queues = require('mysql-queues');
const DEBUG = true;
queues(client, DEBUG);
//Start running queries as normal...
client.query(...);

//Now you want a separate queue?
var q = client.createQueue();
q.query(...);
q.query(...);
q.execute();

client.query(...); //Will not execute until all queued queries (and their callbacks) completed.

//Now you want a transaction?
var trans = client.startTransaction();
trans.query("INSERT...", [x, y, z], function(err, info) {
    if(err)
        trans.rollback();
    else
        trans.query("UPDATE...", [a, b, c, info.insertId], function(err) {
            if(err)
                trans.rollback();
            else
                trans.commit();
        });
});
trans.execute();
//No other queries will get executed until the transaction completes
client.query("SELECT ...") //This won't execute until the transaction is COMPLETELY done (including callbacks)

//Or... as of version 0.3.0, you can do this...
var trans = client.startTransaction();
function error(err) {
    if(err && trans.rollback) {trans.rollback(); throw err;}
}
trans.query("DELETE...", [x], error);
for(var i = 0; i < n; i++)
    trans.query("INSERT...", [ y[i] ], error);
trans.commit(); //Implictly calls resume(), which calls execute()
/* In the case written above, COMMIT is placed at the end of the Queue, yet the
entire transaction can be rolled back if an error occurs. Nesting these queries
was not required. */

 
query方法的实现:
Connection.prototype.query = function(sql, values, cb) {
  this._implyConnect();

  var options = {};

  if (typeof sql === 'object') {
    // query(options, cb)
    options = sql;
    cb      = values;
    values  = options.values;

    delete options.values;
  } else if (typeof values === 'function') {
    // query(sql, cb)
    cb          = values;
    options.sql = sql;
    values      = undefined;
  } else {
    // query(sql, values, cb)
    options.sql    = sql;
    options.values = values;
  }

  options.sql = this.format(options.sql, values || []);

  if (!('typeCast' in options)) {
    options.typeCast = this.config.typeCast;
  }

  return this._protocol.query(options, cb);
};

如果第二个参数不是函数,调用format则转换sql,默认将?替换为数组参数的值(一一对应)
Connection.prototype.format = function(sql, values) {
  if (typeof this.config.queryFormat == "function") {
    return this.config.queryFormat.call(this, sql, values, this.config.timezone);
  }
  return SqlString.format(sql, values, this.config.timezone);
};

默认的format方法实现如下:
SqlString.format = function(sql, values, timeZone) {
  values = [].concat(values);

  return sql.replace(/?/g, function(match) {
    if (!values.length) {
      return match;
    }

    return SqlString.escape(values.shift(), false, timeZone);
  });
};

也可以使用自定义的函数进行处理,在创建连接的时候,传入queryFormat参数即可。