A course to build windows+node.js+mysql environment through Node-mysql _node.js

Objective

MySQL is a commonly used open source database products, is usually the first choice for a free database. Checked the NPM list and found that Nodejs has 13 libraries to visit Mysql,felixge/node-mysql seems to be the most watched item, I also decided to try it.

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

Directory

• Node-mysql Introduction
• Build MySQL Test library
• Node-mysql Installation
• Node-mysql use

1. Node-mysql Introduction

Felixge/node-mysql is a pure nodejs MySQL client program implemented with JavaScript. Felixge/node-mysql encapsulates Nodejs's basic operations on MySQL, 100% the MIT Public License.

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

2. Set up MySQL test library

Create a 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 ';

Re-login MySQL

C:\users\administrator>mysql-unodejs-p
Enter Password: Hu Jintao

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 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)

There's a little episode here.

After installing "Node-mysql", open the Package.json file and find that this project address is

Https://github.com/redblaze/node-mysql.git
From the dependencies you can see that it relies on the MySQL library and is the encapsulation of the felixge/node-mysql.

Node-mysql1

Because of this project star is 0,fork also 0. So, I'm not going to take the time to test it, 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 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

This time is right, continue the following development!

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.js the
solution Is:2

So we let Nodejs connect to MySQL.

4. Node-mysql use

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

Table New Censored Check
Connection Pool Configuration
MySQL disconnection connection
Connection Pooling Timeout test
1). Table New Censored Check
Modify App.js

~ VI app.js

var mysql = require (' mysql '); var conn = Mysql.createconnection ({host: ' localhost ', User: ' Nodejs ', Password: ' Nodejs ', database: ' Nodejs ', p
ort: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-2013 12:09:15 gmt+0800 (China Standard Time) {id:34, Name: ' Fens.me ', create_date:wed Sep 2013 12:09:15 gmt+0800 (China Standard Time)} UPDATE return ==> {Fieldcoun  t: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-2013 12:09:15 gmt+0800 (China Standard Time)} 

/div>

Because node is asynchronous, 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.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 ';

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-2013 13:41:18 gmt+0800 (China Standard Time)}
{ id:40,
 name: ' fens.me ',
 create_date:wed Sep 2013 13:41:18 gmt+0800 (China Standard Time)}

3). mysql Disconnection connection

Simulate 3 different errors separately

A. Login Password error
B. Database Downtime
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
  });

  Connection error, 2 seconds retry
  conn.connect (function (err) {
    if (err) {
      console.log (' Error when connecting to DB: ', err); C14/>settimeout (HandleError, Watts);
    }
  );

  Conn.on (' Error ', function (err) {
    console.log (' db error ', err);
    If it is disconnected, automatically reconnect if
    (Err.code = = ' Protocol_connection_lost ') {
      handleerror ();
    } else {
      throw err ;
    }
  });
}
HandleError ();

A. Analog password error

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:y ES)
 code: ' Er_access_denied_error ',
 errno:1045,
 sqlState: ' 28000 ',
 fatal:true}

   

B. Analog 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. Analog connection Timeout, Protocol_connection_lost
switch to root account, modify MySQL wait_timeout parameter, set 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 |
+---------------+-------+
1 row in Set (0.00 sec)

Modify file: App-reconnection.js, 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 do a query every 15 seconds.

Console output

D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed Sep 2013 15:21:14 GMT+0800 (China Standard Time)
[{variable_name: ' Wait_timeout ', Value: ' {'}]
db error {[error:connection lost:the server closed the CO Nnection.] Fatal:true, code: ' Protocol_connection_lost '}
Wed Sep 2013 15:21:28 gmt+0800 (China Standard Time)
[{variable_name: ' W Ait_timeout ', Value: ' The
db error {[error:connection lost:the server closed the Connection.] fatal:true, cod E: ' Protocol_connection_lost '}
Wed Sep 2013 15:21:43 gmt+0800 (China Standard Time)
[{variable_name: ' wait_timeout ', Va Lue: ' 10 '}]

Our own program captures the "Protocol_connection_lost" exception and automatically implements the database connection.

4. mysql Connection pool timeout test

For the wait_timeout problem, we will test the connection again.

modifying 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.js
Wed Sep 2013 15:32:25 GMT+0800 (China Standard Time)
[ {variable_name: ' Wait_timeout ', Value: ' Ten '}]
Wed Sep One 2013 15:32:30 gmt+0800 (China Standard Time)
[{variable_name: ' Wait_timeout ', Value: ' 11 '}]
Wed Sep 2013 15:3 2:35 gmt+0800 (China Standard Time)
[{variable_name: ' Wait_timeout ', Value: ' 10 '}]

Connection pool, has solved the problem of automatic reconnection, the following our development, we can try to use the pooling way.