Nodejs connecting MySQL

Ext.: http://blog.fens.me/nodejs-mysql-intro/

Objective

MySQL is a popular open source database product and is often the first choice for a free database. Checking the NPM list and discovering that Nodejs has 13 libraries to access Mysql,felixge/node-mysql seems to be the most watched item and I decided to try it out.

Pay attention to the name, "Felixge/node-mysql" is not "Node-mysql", the installation section will introduce this episode!

Directory

1. Node-mysql Introduction
2. Build MySQL Test library
3. Node-mysql Installation
4. Node-mysql use

1. Node-mysql Introduction

Felixge/node-mysql is a pure nodejs MySQL client program implemented with JavaScript. The Felixge/node-mysql encapsulates the basic operation of Nodejs for MySQL, 100% mit Public License.

Project Address: Https://github.com/felixge/node-mysql

2. Build MySQL Test library

To create a MySQL test library locally: Nodejs

~ mysql -uroot -pmysql> 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 [email protected]‘%‘ IDENTIFIED BY ‘nodejs‘;mysql> GRANT ALL ON nodejs.* to [email protected] IDENTIFIED BY ‘nodejs‘;

Re-login to MySQL

C:\Users\Administrator>mysql -unodejs -pEnter password: ******mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || nodejs             || test               |+--------------------+3 rows in set (0.00 sec)mysql> USE nodejsDatabase 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[email protected] node_modules\node-mysql├── [email protected]├── [email protected]├── [email protected]└── [email protected] ([email protected], [email protected])

Here's a little episode.

After installing "Node-mysql", open Package.json file discovery, this project address is

https://github.com/redblaze/node-mysql.git

As you can see from the dependencies, it relies on the MySQL library, which is the encapsulation of felixge/node-mysql.

Because of this project star is 0,fork is also 0. So, I'm not going to take the time to test 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 [email protected]npm http GET https://registry.npmjs.org/mysql/2.0.0-alpha9npm http 200 https://registry.npmjs.org/mysql/2.0.0-alpha9npm http GET https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgznpm http 200 https://registry.npmjs.org/mysql/-/mysql-2.0.0-alpha9.tgznpm http GET https://registry.npmjs.org/require-all/0.0.3npm http GET https://registry.npmjs.org/bignumber.js/1.0.1npm http 304 https://registry.npmjs.org/require-all/0.0.3npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1[email protected] node_modules\mysql├── [email protected]└── [email protected]

This time is right, continue the following development!

Create node program startup file: App.js

First Test

~ vi app.jsvar 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.jsThe solution is:  2

So we let the Nodejs connect to MySQL.

4. Node-mysql use

The following is a common test for Node-mysql APIs.

• Table New Deletion check
• Connection Pool Configuration
• MySQL disconnected and re-connected
• Connection Pool Timeout test

1). New deletion and inspection of the table
Modify App.js

~ VI app.jsvar mysql = require (' mysql '); var conn = Mysql.createconnection ({host: ' localhost ', User: ' Nodejs ', PA ssWOrd: ' 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 '; var deletesql = ' Delete from T_user '; var updatesql = ' Update t_user set name= ' Conan Update ' where name= ' Conan ';//deleteconn.query (Deletesql, function (Err0, RES0) {if (E    RR0) 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.jsdelete 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 201 3 12:09:15 gmt+0800 (China Standard Time)} {id:34, name: ' Fens.me ', create_date:wed Sep 12:09:15 gmt+0800 (China Standard Time)}update Return ==>{fieldcount:0, affectedrows:1, insertid:0, Serverstatus:2, warningcount:0, message: ' (Rows matche D:1 changed:1 warnings:0 ', protocol41:true, changedrows:1}select ==>{id:33, Name: ' Conan update ', Create_ date:wed Sep 12:09:15 gmt+0800 (China Standard Time)} {id:34, name: ' fens.me ', create_date:wed Sep 12:09:15 gmt+0 800 (China Standard Time)} 

Because of the asynchronous node, the above is a continuous operation, the code will be written fragmented. 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.jsvar 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.jsSELECT ==>{ 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 re-connected

3 types of errors were simulated

• Incorrect login password
• Database Downtime
• Database connection timed out

Added Files: app-reconnect.js

~ vi app-reconnect.jsvar 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. Analog password error

Modified password: ' NODEJS11 '

Console output.

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.jserror when connecting to db: { [Error: ER_ACCESS_DENIED_ERROR: Access denied for user ‘nodejs‘@‘localhost‘ (using passrd: 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 passrd: YES)]  code: ‘ER_ACCESS_DENIED_ERROR‘,  errno: 1045,  sqlState: ‘28000‘,  fatal: true }

B. Simulated database downtime
Start node normally, and then kill the mysqld process.

Console output.

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.jsdb 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 anomaly directly causes the node program to be killed!

C. Analog connection Timeout, Protocol_connection_lost
Switch to the root account, modify the mysql wait_timeout parameter, and set the timeout to 10 milliseconds.

~ mysql -uroot -pmysql> 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 file: App-reconnection.js, add code at the end

~ vi app-reconnection.jsfunction 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 make a query every 15 seconds.

Console output

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.jsWed 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 captures the "Protocol_connection_lost" exception and automatically implements the database re-connection.

4). Timeout test for MySQL connection pool

For the wait_timeout problem, we test the connection again.

Modify the 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.jsWed 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‘ } ]

Connection pool, has solved the problem of automatic reconnection, behind our development, you can use the pooling way possible.

Nodejs connecting MySQL