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

Source: Internet
Author: User
Tags connection pooling http 200 mysql client mysql connection pool require create database install node npm install node

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.

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.