Through node-mysql Build Windows + Node. js + MySQL environment tutorial, node-mysqlnode.js

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

Through node-mysql Build Windows + Node. js + MySQL environment tutorial, node-mysqlnode.js

Preface

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

Note the name, "felixge/node-mysql" rather than "node-mysql". The installation department will introduce this episode!

Directory

  • Node-mysql Introduction
  • Create a MySQL Test Database
  • Install node-mysql
  • Use node-mysql

1. Introduction to node-mysql

Felixge/node-mysql is a pure nodejs MySQL client program implemented by 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. Create a MySQL Test Database

Create a MySQL Test Database 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 nodejs@'%' IDENTIFIED BY 'nodejs';mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';

Log on to MySQL again

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 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. Install node-mysql

My system environment

Win7 64bit
Nodejs: v0.10.5
Npm: 1.2.19
MySQL: Server version: 5.6.11 MySQL Community Server (GPL)
Create a 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-mysqlnode-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 an episode

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

Https://github.com/redblaze/node-mysql.git
From the dependency, we can see that it depends on the mysql database and is an encapsulation of felixge/node-mysql.

Node-mysql1

Because this project star is 0 and fork is also 0, I am not prepared to take the time to test and re-install 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-alpha9npm 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.1mysql@2.0.0-alpha9 node_modules\mysql├── require-all@0.0.3└── bignumber.js@1.0.1

This is the right time. Continue the development below!

Create a 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.jsThe solution is: 2

In this way, we connected Nodejs to MySQL.

4. Use node-mysql

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

New table deletion and modification Query
Connection Pool Configuration
MySQL Reconnection
Connection Pool timeout Test
1). New table deletion and modification Query
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"';//deleteconn.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: 'conance', create_date: wed Sep 11 2013 12:09:15 GMT + 0800 (China Standard Time)} {id: 34, name: 'fens. me ', create_date: Wed Sep 11 2013 12:09:15 GMT + 0800 (China Standard Time)} 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 (China Standard Time)} {id: 34, name: 'fens. me ', create_date: Wed Sep 11 2013 12:09:15 GMT + 0800 (China Standard Time )}

Due to node Asynchronization, the above is a continuous operation, and the code will be broken down. We can encapsulate the above Code through the async library. 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 (China Standard Time)} {id: 40, name: 'fens. me ', create_date: Wed Sep 11 2013 13:41:18 GMT + 0800 (China Standard Time )}

3). MySQL Reconnection

Simulate three types of errors respectively

A. the logon password is incorrect.
B. Database downtime
C. Database Connection timeout
Add 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}); // connection error, retry conn in 2 seconds. 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 the connection is disconnected, automatically reconnect if (err. code = 'Protocol _ CONNECTION_LOST ') {handleError () ;}else {throw err ;}) ;}handleerror ();

A. Incorrect simulated Password

Modify password: 'nodes11'

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 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. Simulate database downtime
Start the 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. Simulate connection timeout, PROTOCOL_CONNECTION_LOST
Switch to the root account, modify the wait_timeout parameter of MySQL, and set it to 10 ms timeout.

~ 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 last

~ 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 performs 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 (China Standard Time) [{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 (China Standard Time) [{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 (China Standard Time) [{Variable_name: 'Wait _ timeout', Value: '10'}]

Our own program captures the "PROTOCOL_CONNECTION_LOST" exception and automatically implements database reconnection.

4). MySQL connection pool timeout Test

For the wait_timeout problem, let's test the connection.

Modify app-pooling.js files

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 (China Standard Time) [{Variable_name: 'Wait _ timeout', Value: '10'}] Wed Sep 11 2013 15:32:30 GMT + 0800 (China Standard Time) [{Variable_name: 'Wait _ timeout', Value: '10'}] Wed Sep 11 2013 15:32:35 GMT + 0800 (China Standard Time) [{Variable_name: 'Wait _ timeout', Value: '10'}]

The connection pool has solved the problem of automatic reconnection. We will try our best to use the pooling method later.

Articles you may be interested in:
  • Node. js to operate mysql (add, delete, modify, and query)
  • Example of calling mysql stored procedure in Node. js
  • Node. js Development Guide-Node. js connects to MySQL and performs database operations
  • Simple example in Node. js Development Guide (mysql version)
  • Common Methods for operating MySQL using JavaScript in Node. js

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.