I'm a node beginner. Following is the code that I'm trying to execute using node mysql but it keeps giving me this error:
error connecting: Error: ER_CON_COUNT_ERROR: Too many connections
::CODE::
var size = item.length;// size is ~1500
for (var i=0; i<size;i++) {
var connection = mysql.createConnection({
host : 'xxx.xxx.xxx.xxx',
database : 'mydb',
user : 'test',
password : 'test'
});
connection.connect(function(err, callback) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
});
var entry = {
id: item[i],
time_created: Math.floor(Date.now() / 1000),
time_created: Math.floor(Date.now() / 1000),
price_range: 0
};
var query = connection.query('INSERT INTO mytable SET ?', entry, function(err, result, callback) {
if(err) {
console.log(err.message);
}
});
connection.end(function(err) {
if(err) {
console.log(err.message);
}
});
}
How should I re-design the above code to successfully execute it?
By default 151 is the maximum permitted number of simultaneous client connections in MySQL 5.5. If you reach the limit of max_connections you will get the “Too many connections” error when you to try to connect to your MySQL server. This means all available connections are in use by other clients.
The MySQL “Too many connections” error occurs when more queries are sent to a MySQL database than can be processed. The error can be fixed by setting a new number of maximum connections in the configuration file or globally.
MySQL by default only accepts 100 simultaneous connections max. In your case you are creating ~1500 so the error you get is normal. You can increase this value but the problem here is in your code.
You should use a pool instead. This will make node create a pool of connections (i think the default is 10) and let them be shared by your queries:
var mysql = require('mysql');
var pool = mysql.createPool({
host : 'xxx.xxx.xxx.xxx',
database : 'mydb',
user : 'test',
password : 'test'
});
for (var i=0; i<size;i++) {
pool.getConnection(function(err, connection) {
connection.query( 'INSERT INTO ...', function(err, rows) {
connection.release();
});
});
}
Also you may want to consider using a single connection and insert everything at once. You can see in this answer how to achieve that.
First you don't need 1500 connections to insert 1500 items. Remove that code out of your for loop.
Something like this:
var size = item.length;// size is ~1500
var connection = mysql.createConnection({
host : 'xxx.xxx.xxx.xxx',
database : 'mydb',
user : 'test',
password : 'test'
});
connection.connect(function(err, callback) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
});
connection.end(function(err) {
if(err) {
console.log(err.message);
}
});
for (var i=0; i<size;i++) {
var entry = {
id: item[i],
time_created: Math.floor(Date.now() / 1000),
time_created: Math.floor(Date.now() / 1000),
price_range: 0
};
var query = connection.query('INSERT INTO mytable SET ?', entry, function(err, result, callback) {
if(err) {
console.log(err.message);
}
});
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With