Ext.: http://blog.fens.me/nodejs-mysql-intro/
Objective
MySQL is a popular open source database product and is often the first choice for a free database. Checking the NPM list and discovering that Nodejs has 13 libraries to access Mysql,felixge/node-mysql seems to be the most watched item and I decided to try it out.
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. The Felixge/node-mysql encapsulates the basic operation of Nodejs for MySQL, 100% mit Public License.
Project Address: Https://github.com/felixge/node-mysql
2. Build MySQL Test library
To create a MySQL test library 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 [email protected]‘%‘ IDENTIFIED BY ‘nodejs‘;mysql> GRANT ALL ON nodejs.* to [email protected] IDENTIFIED BY ‘nodejs‘;
Re-login to MySQL
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 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[email protected] node_modules\node-mysql├── [email protected]├── [email protected]├── [email protected]└── [email protected] ([email protected], [email protected])
Here's a little episode.
After installing "Node-mysql", open Package.json file discovery, this project address is
https://github.com/redblaze/node-mysql.git
As you can see from the dependencies, it relies on the MySQL library, which is the encapsulation of felixge/node-mysql.
Because of this project star is 0,fork is also 0. So, I'm not going to take the time to test 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 [email protected]npm 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.1[email protected] node_modules\mysql├── [email protected]└── [email protected]
This time is right, continue the following development!
Create node program startup file: App.js
First Test
~ vi app.jsvar 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
So we let the Nodejs connect to MySQL.
4. Node-mysql use
The following is a common test for Node-mysql APIs.
- Table New Deletion check
- Connection Pool Configuration
- MySQL disconnected and re-connected
- Connection Pool Timeout test
1). New deletion and inspection of the table
Modify App.js
~ VI app.jsvar mysql = require (' mysql '); var conn = Mysql.createconnection ({host: ' localhost ', User: ' Nodejs ', PA ssWOrd: ' 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 '; 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 (E RR0) 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.jsdelete 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 11 201 3 12:09:15 gmt+0800 (China Standard Time)} {id:34, name: ' Fens.me ', create_date:wed Sep 12:09:15 gmt+0800 (China Standard Time)}update Return ==>{fieldcount:0, affectedrows:1, insertid:0, Serverstatus:2, warningcount:0, message: ' (Rows matche D:1 changed:1 warnings:0 ', protocol41:true, changedrows:1}select ==>{id:33, Name: ' Conan update ', Create_ date:wed Sep 12:09:15 gmt+0800 (China Standard Time)} {id:34, name: ' fens.me ', create_date:wed Sep 12:09:15 gmt+0 800 (China Standard Time)}
Because of the asynchronous node, 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.jsvar 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.jsSELECT ==>{ id: 39, name: ‘conan update‘, create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }{ id: 40, name: ‘fens.me‘, create_date: Wed Sep 11 2013 13:41:18 GMT+0800 (中国标准时间) }
3). mysql Disconnected re-connected
3 types of errors were simulated
- Incorrect login password
- Database Downtime
- Database connection timed out
Added Files: app-reconnect.js
~ vi app-reconnect.jsvar mysql = require(‘mysql‘);var conn;function handleError () { conn = mysql.createConnection({ host: ‘localhost‘, user: ‘nodejs‘, password: ‘nodejs‘, database: ‘nodejs‘, port: 3306 }); //连接错误,2秒重试 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 (err.code === ‘PROTOCOL_CONNECTION_LOST‘) { handleError(); } else { throw err; } });}handleError();
A. Analog password error
Modified password: ' NODEJS11 '
Console output.
D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.jserror 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. Simulated database downtime
Start node normally, and then kill the mysqld process.
Console output.
D:\workspace\javascript\nodejs-node-mysql>node app-reconnect.jsdb 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 anomaly directly causes the node program to be killed!
C. Analog connection Timeout, Protocol_connection_lost
Switch to the root account, modify the mysql wait_timeout parameter, and set the timeout to 10 milliseconds.
~ 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 the end
~ vi app-reconnection.jsfunction 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 make 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 (中国标准时间)[ { 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 (中国标准时间)[ { 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 (中国标准时间)[ { Variable_name: ‘wait_timeout‘, Value: ‘10‘ } ]
Our own program captures the "Protocol_connection_lost" exception and automatically implements the database re-connection.
4). Timeout test for MySQL connection pool
For the wait_timeout problem, we 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.jsWed Sep 11 2013 15:32:25 GMT+0800 (中国标准时间)[ { Variable_name: ‘wait_timeout‘, Value: ‘10‘ } ]Wed Sep 11 2013 15:32:30 GMT+0800 (中国标准时间)[ { Variable_name: ‘wait_timeout‘, Value: ‘10‘ } ]Wed Sep 11 2013 15:32:35 GMT+0800 (中国标准时间)[ { Variable_name: ‘wait_timeout‘, Value: ‘10‘ } ]
Connection pool, has solved the problem of automatic reconnection, behind our development, you can use the pooling way possible.
Nodejs connecting MySQL