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

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