Nodejs connecting MySQL

Source: Internet
Author: User
Tags http 200 mysql connection pool setinterval install node npm install node

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.