Tutorial on setting up Windows+Node.js+MySQL environment through node-mysql_node.js

Foreword

MySQL is a commonly used open source database product and is usually the first choice for free databases. I checked the NPM list and found that Nodejs has 13 libraries that can access MySQL. felixge/node-mysql seems to be the most popular project, so I decided to try it.

Pay attention to the name, "felixge/node-mysql" is not "node-mysql", this episode will be introduced in the installation part!

Table of Contents

• node-mysql introduction
• Build a MySQL test library
• node-mysql installation
• node-mysql use

1. Introduction to node-mysql

felixge/node-mysql is a MySQL client program implemented in pure nodejs using javascript. felixge/node-mysql encapsulates the basic operations of Nodejs on MySQL, 100% MIT public license.

Project address: https://github.com/felixge/node-mysql

2. Establish MySQL test library

Create MySQL test library locally: nodejs

~ mysql -uroot -p
mysql> CREATE DATABASE nodejs;
mysql> SHOW DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| mysql       |
| nodejs       |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';

Log in to MySQL again

C:\Users\Administrator>mysql -unodejs -p
Enter password: ******

mysql> SHOW DATABASES;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| nodejs       |
| test        |
+--------------------+
3 rows in set (0.00 sec)

mysql> USE nodejs
Database changed

Create a new user table

CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(16) NOT NULL ,
create_date TIMESTAMP NULL DEFAULT now()
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE UNIQUE INDEX t_quiz_IDX_0 on t_user(name);

mysql> SHOW TABLES;
+------------------+
| Tables_in_nodejs |
+------------------+
| t_user      |
+------------------+
1 row in set (0.04 sec)

3. node-mysql installation

My system environment

win7 64bit
Nodejs:v0.10.5
Npm:1.2.19
MySQL:Server version: 5.6.11 MySQL Community Server (GPL)
Create project: nodejs-node-mysql

~ D:\workspace\javascript>mkdir nodejs-node-mysql
~ D:\workspace\javascript>cd nodejs-node-mysql
~ D:\workspace\javascript\nodejs-node-mysql>npm install node-mysql
node-mysql@0.2.0 node_modules\node-mysql
├── better-js-class@0.1.2
├── cps@0.1.7
├── underscore@1.5.2
└── mysql@2.0.0-alpha9 (require-all@0.0.3, bignumber.js@1.0.1)

Here is a little episode

After installing "node-mysql", open the package.json file and find that the project address is

https://github.com/redblaze/node-mysql.git
As you can see from the dependency relationship, it depends on the mysql library and is an encapsulation of felixge/node-mysql.

node-mysql1

Since the star of this project is 0, the fork is also 0. Therefore, I am not going to spend time testing it and reinstall the felixge/node-mysql package.

Reinstall node-mysql

~ D:\workspace\javascript\nodejs-node-mysql>rm -rf node_modules
~ D:\workspace\javascript\nodejs-node-mysql>npm install mysql@2.0.0-alpha9
npm http GET https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http 200 https://registry.npmjs.org/mysql/2.0.0-alpha9
npm http GET https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http 200 https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgz
npm http GET https://registry.npmjs.org/require-all/0.0.3
npm http GET https://registry.npmjs.org/bignumber.js/1.0.1
npm http 304 https://registry.npmjs.org/require-all/0.0.3
npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1
mysql@2.0.0-alpha9 node_modules\mysql
├── require-all@0.0.3
└── bignumber.js@1.0.1

That’s it this time, let’s continue the development!

Create the node program startup file: app.js

First test

~ vi app.js

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost',
  user: 'nodejs',
  password: 'nodejs',
  database:'nodejs',
  port: 3306
});
conn.connect();
conn.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
  if (err) throw err;
  console.log('The solution is: ', rows[0].solution);
});
conn.end();

Run node

~ D:\workspace\javascript\nodejs-node-mysql>node app.js
The solution is: 2

In this way we let Nodejs connect to MySQL.

4. Using node-mysql

Now we will conduct common tests on the node-mysql API.

Table new, deleted, modified and checked
Connection pool configuration
MySQL disconnects and reconnects
Connection pool timeout test
1). Check table newness, deletion and modification
Modify app.js

~ vi app.js

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost',
  user: 'nodejs',
  password: 'nodejs',
  database: 'nodejs',
  port: 3306
});
conn.connect();

var insertSQL = 'insert into t_user(name) values("conan"),("fens.me")';
var selectSQL = 'select * from t_user limit 10';
var deleteSQL = 'delete from t_user';
var updateSQL = 'update t_user set name="conan update" where name="conan"';

//delete
conn.query(deleteSQL, function (err0, res0) {
  if (err0) console.log(err0);
  console.log("DELETE Return ==> ");
  console.log(res0);

  //insert
  conn.query(insertSQL, function (err1, res1) {
    if (err1) console.log(err1);
    console.log("INSERT Return ==> ");
    console.log(res1);

    //query
    conn.query(selectSQL, function (err2, rows) {
      if (err2) console.log(err2);

      console.log("SELECT ==> ");
      for (var i in rows) {
        console.log(rows[i]);
      }

      //update
      conn.query(updateSQL, function (err3, res3) {
        if (err3) console.log(err3);
        console.log("UPDATE Return ==> ");
        console.log(res3);

        //query
        conn.query(selectSQL, function (err4, rows2) {
          if (err4) console.log(err4);

          console.log("SELECT ==> ");
          for (var i in rows2) {
            console.log(rows2[i]);
          }
        });
      });
    });
  });
});

//conn.end();

Console output:

D:\workspace\javascript\nodejs-node-mysql>node app.js

DELETE Return ==>
{ fieldCount: 0,
 affectedRows: 2,
 insertId: 0,
 serverStatus: 34,
 warningCount: 0,
 message: '',
 protocol41: true,
 changedRows: 0 }
INSERT Return ==>
{ fieldCount: 0,
 affectedRows: 2,
 insertId: 33,
 serverStatus: 2,
 warningCount: 0,
 message: '&Records: 2 Duplicates: 0 Warnings: 0',
 protocol41: true,
 changedRows: 0 }
SELECT ==>
{ id: 33,
 name: 'conan',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
{ id: 34,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
UPDATE Return ==>
{ fieldCount: 0,
 affectedRows: 1,
 insertId: 0,
 serverStatus: 2,
 warningCount: 0,
 message: '(Rows matched: 1 Changed: 1 Warnings: 0',
 protocol41: true,
 changedRows: 1 }
SELECT ==>
{ id: 33,
 name: 'conan update',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }
{ id: 34,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 12:09:15 GMT+0800 (中国标准时间) }

Due to the asynchronous nature of node, the above is a continuous operation, and the code will be written in fragments. We can encapsulate the above code through the async library, please refer to the article: Nodejs asynchronous process control Async

2). Connection pool configuration

Add file: app-pooling.js

~ vi app-pooling.js

var mysql = require('mysql');
var pool = mysql.createPool({
  host: 'localhost',
  user: 'nodejs',
  password: 'nodejs',
  database: 'nodejs',
  port: 3306
});

var selectSQL = 'select * from t_user limit 10';

pool.getConnection(function (err, conn) {
  if (err) console.log("POOL ==> " + err);

  conn.query(selectSQL,function(err,rows){
    if (err) console.log(err);
    console.log("SELECT ==> ");
    for (var i in rows) {
      console.log(rows[i]);
    }
    conn.release();
  });
});

Console output:

D:\workspace\javascript\nodejs-node-mysql>node app-pooling.js

SELECT ==>
{ id: 39,
 name: 'conan update',
 create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }
{ id: 40,
 name: 'fens.me',
 create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }

3). MySQL disconnected and reconnected

Simulate 3 types of errors respectively

a. Incorrect login password
b. Database is down
c. Database connection timeout
New file: app-reconnect.js

~ vi app-reconnect.js

var mysql = require('mysql');
var conn;
function handleError () {
  conn = mysql.createConnection({
    host: 'localhost',
    user: 'nodejs',
    password: 'nodejs',
    database: 'nodejs',
    port: 3306
  });

  //连接错误，2秒重试
  conn.connect(function (err) {
    if (err) {
      console.log('error when connecting to db:', err);
      setTimeout(handleError , 2000);
    }
  });

  conn.on('error', function (err) {
    console.log('db error', err);
    // 如果是连接断开，自动重新连接
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      handleError();
    } else {
      throw err;
    }
  });
}
handleError();

a. Wrong simulation password

Modify password: ‘nodejs11’

Console output.

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js

error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using pass
rd: YES)]
 code: 'ER_ACCESS_DENIED_ERROR',
 errno: 1045,
 sqlState: '28000',
 fatal: true }
error when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nodejs'@'localhost' (using pass
rd: YES)]
 code: 'ER_ACCESS_DENIED_ERROR',
 errno: 1045,
 sqlState: '28000',
 fatal: true }

b. Simulate database downtime
Start node normally and then kill the mysqld process.

Console output.

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js

db error { [Error: read ECONNRESET]
 code: 'ECONNRESET',
 errno: 'ECONNRESET',
 syscall: 'read',
 fatal: true }

Error: read ECONNRESET
  at errnoException (net.js:884:11)
  at TCP.onread (net.js:539:19)

This exception directly causes the node program to be killed!

c. Simulated connection timeout, PROTOCOL_CONNECTION_LOST
Switch to the root account, modify the wait_timeout parameter of MySQL and set it to 10 milliseconds timeout.

~ mysql -uroot -p
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 10  |
+---------------+-------+
1 row in set (0.00 sec)

Modify the file: app-reconnection.js and add code at the end

~ vi app-reconnection.js

function query(){
  console.log(new Date());
  var sql = "show variables like 'wait_timeout'";
  conn.query(sql, function (err, res) {
    console.log(res);
  });
}

query();
setInterval(query, 15*1000);

The program will perform a query every 15 seconds.

Console output

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed Sep 11 2013 15:21:14 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }
Wed Sep 11 2013 15:21:28 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
db error { [Error: Connection lost: The server closed the connection.] fatal: true, code: 'PROTOCOL_CONNECTION_LOST' }
Wed Sep 11 2013 15:21:43 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

Our own program caught the "PROTOCOL_CONNECTION_LOST" exception and automatically reconnected the database.

4). Timeout test of MySQL connection pool

For the wait_timeout problem, let’s test the connection again.

Modify app-pooling.js file

var mysql = require('mysql');
var pool = mysql.createPool({
  host: 'localhost',
  user: 'nodejs',
  password: 'nodejs',
  database: 'nodejs',
  port: 3306
});

var selectSQL ="show variables like 'wait_timeout'";

pool.getConnection(function (err, conn) {
  if (err) console.log("POOL ==> " + err);

  function query(){
    conn.query(selectSQL, function (err, res) {
      console.log(new Date());
      console.log(res);
      conn.release();
    });
  }
  query();
  setInterval(query, 5000);
});

Console output:

D:\workspace\javascript\nodejs-node-mysql>node app-pooling.js
Wed Sep 11 2013 15:32:25 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:30 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:35 GMT+0800 (中国标准时间)
[ { Variable_name: 'wait_timeout', Value: '10' } ]

The connection pool has solved the problem of automatic reconnection. We can use pooling as much as possible in our subsequent development.