Of Stack

Windows+Node.js+MySQL using node mysql

  • 2021-01-18 06:15:09
  • OfStack

preface

MySQL is a commonly used open source database product and is often the first choice for free databases. After a quick check of the NPM list, I found that Nodejs has 13 libraries that can access MySQL. felixge/node-mysql seems to be the most popular project, so I decided to try 1.

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

directory

node - mysql is introduced Build the MySQL test library node - mysql installation node - mysql use

1. node - mysql is introduced

felixge/node-mysql is a pure nodejs with javascript implementation of an MySQL client program. felixge/node-mysql encapsulates the basic operation of Nodejs on MySQL, 100% MIT public license.

Address of the project: https: / / github com/felixge/node - mysql

2. Establish MySQL test library

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

Log back in to MySQL 


C:\Users\Administrator>mysql -unodejs -p
Enter password: ******

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

Here's an interlude

After installation "node - mysql", open the package. json files found that address this project is

https://github.com/redblaze/node-mysql.git
As you can see from the dependencies, it relies on the mysql library and is an encapsulation of felixge/node-mysql.

node-mysql1

Since this project star is 0, fork is also 0. So, I'm not going to spend time testing it, either, 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 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 200 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

That's it, move on!

Create node program startup file: app.js

Test 1 


~ 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 


mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
0

So we have Nodejs connected to MySQL.

4. node - mysql use

Now we are going to run some common tests on node- API for mysql.

Table new deletion check
Connection pool configuration
MySQL disconnected and reconnected
Connection pool timeout test
1). Check the table for new deletion
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"';

//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: 


mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
3 

mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
4

Because node is asynchronous, with one continuous operation above, the code can be written fragmenting. We can encapsulate the above code with the async library. See the article Nodejs Asynchronous Process Control Async

2). Connection pooling configuration

Added file: app-pooling.js 


mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
5 

mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
6

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

3). MySQL disconnection and reconnection

Three kinds of errors were simulated respectively

a. Login password error
b. Database down
c. Database connection timed out
New file: app-reconnect.js 


mysql> GRANT ALL ON nodejs.* to nodejs@'%' IDENTIFIED BY 'nodejs';
mysql> GRANT ALL ON nodejs.* to nodejs@localhost IDENTIFIED BY 'nodejs';
9 

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 to retry 
  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 the connection is broken, automatically reconnect 
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      handleError();
    } else {
      throw err;
    }
  });
}
handleError();

a. Error in emulation password

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: YES)]
 code: 'ER_ACCESS_DENIED_ERROR',
 errno: 1045,
 sqlState: '28000',
 fatal: true }

b. Simulated database down
Start node as normal, 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 node program to be killed!

c. Simulation connection timeout, PROTOCOL_CONNECTION_LOST
Switch to the root account and change the wait_timeout parameter for MySQL to a 10 millisecond 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 | 10  |
+---------------+-------+
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 one query every 15 seconds.

Console output 


D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.js
Wed Sep 11 2013 15:21:14 GMT+0800 ( Chinese 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 ( Chinese 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 ( Chinese Standard Time )
[ { Variable_name: 'wait_timeout', Value: '10' } ]

Our own program catches the "PROTOCOL_CONNECTION_LOST" exception and automatically implements the database reconnect.

4). Timeout test for MySQL connection pool

For the wait_timeout problem, let's 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.js
Wed Sep 11 2013 15:32:25 GMT+0800 ( Chinese Standard Time )
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:30 GMT+0800 ( Chinese Standard Time )
[ { Variable_name: 'wait_timeout', Value: '10' } ]
Wed Sep 11 2013 15:32:35 GMT+0800 ( Chinese Standard Time )
[ { Variable_name: 'wait_timeout', Value: '10' } ]

Connection pooling has already solved the problem of automatic reconnection. We can try to use pooling for our future development.

Related articles: