ER_CON_COUNT_ERROR: Too many connection errors in node -mysql

Question

I am a beginner node. Below is the code that I am trying to execute using node mysql, but it continues to give 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 can I reverse engineer the above code to successfully execute it?

Answer 1

At first you don't need 1,500 connections to insert 1,500 elements. Remove this code from the for loop.

Something like that:

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

Answer 2

MySQL by default only accepts 100 concurrent connections max. In your case, you create ~ 1500, so the error you get is normal. You can increase this value, but the problem is here in your code.

Instead, you should use the pool. This will make node create a connection pool (I think the default is 10) and let them be used by your requests:

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

You can also consider using one connection and insert all at once. You can see in this the answer how to achieve this.