error connecting: Error: connect ECONNREFUSED

created at 11-20-2021 views: 50

Connecting Node.js to XAMPP MySQL server on Mac OS always reports 

error connecting: Error: connect ECONNREFUSED
The following is how to connect to the local mysql database through node.js:

var mysql = require('mysql');
var connection = mysql.createConnection({
   host:'localhost',
   user:'xxxx', // where xxxx is replaced with your mysql login user name
   password:'xxxx',//Replace xxxx here with your mysql login password
   database:'xxxx', // where xxxx is replaced with the name of the database you want to connect
   socketPath:'/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock',
   port:3306
});

connection.connect(function(err) {
   if (err) {
     console.error('error connecting: '+ err.stack);
     return;
   }

   console.log('connected as id '+ connection.threadId);
});


The key here is to configure socketPath, otherwise the following error will be reported:

error connecting: Error: connect ECONNREFUSED

The above is what I found from the Internet, and it didn’t work to change it according to the writing~~ It still can’t connect.

final solution

Create a MYSQL service on the server, build a database, or start a MYSQL service on another computer in the intranet, call here

MAC native code No need to add socketPath, you can call the local nodejs interface perfectly

simple version

simple version code is as follows

var express = require('express');
var app = express();
const routes = require('./routers');

const index=require('./routers/index');
app.get('/', (req, res) => res.send('App is working'));

// app.use('/', routes);
// app.use('/api/idToken', routes);

app.use('/api/index',index);
app.get('/aa', (req, res) => {
  res.send('hello')
  // res.sendFile(__dirname + '/dist/index.html')
})
app.listen(5000, () => {
    console.log("server up");
});

module.exports = {
    app
};

complete version

The full version can refer to the code as follows

app.js

const express=require('express');
// const bodyParser=require('body-parser');
const session = require('express-session')
var MySQLStore = require('express-mysql-session')(session);

//Create a web server
var server=express();
var options = {
  host: '22.22.12.122',
  port: 3306,
  user:'root',
  password:'root',
  database:'cake'
};
var sessionStore = new MySQLStore(options);

//Import the routing module
const cors=require('cors');
// const index=require("./routes/index");
const userRouter=require('./routers/user.js');
const cartRouter=require('./routers/cart.js');
const details=require('./routers/detail.js');
const product=require('./routers/product');
const index=require('./routers/index');
const pics=require('./routers/pics');
const login=require('./routers/login');//This interface is for testing only
const find=require('./routers/find');
const kind=require('./routers/kind');
const captcha=require('./routers/captcha');
const portal=require('./routers/portal');

//Host static resources under public;
server.use(express.static('public'));
server.use(express.static('dist'));
//Cross-domain request cors
server.use(cors
  (
    {
  origin:"*",
  // origin: "http://localhost:8080",
  // origin: "http://localhost:4200",
  // origin: "http://127.0.0.1:5500",
  credentials: true
}
)
);
// server.writeHead(200,{"Access-Control-Allow-Credentials":true});
server.use(express.json())
server.use(express.urlencoded({
  extended:false
}));

//session enables session middleware to save user login status and verification code
server.use(session({
  name:'sessionId',
  resave: true,
  saveUninitialized: true,
  secret:'APP_SESSION_SECRET',
  store: sessionStore // save the session to the database
}))

server.use('/api/user',userRouter);
server.use('/api/cart',cartRouter);

server.use('/api/product',product);
server.use('/api/details',details);
server.use('/api/index',index);
server.use('/api/pics',pics);
// server.use('/api/login',login); This interface is for testing only
server.use('/api/find',find);
server.use('/api/kind',kind);
server.use('/api/captcha',captcha);
server.use('/api/portal',portal);

// server.get('*', (req, res) => {
// res.sendFile(__dirname +'/dist/index.html')
// })
// server.get('/aa', (req, res) => {
// res.send('hello')
// // res.sendFile(__dirname +'/dist/index.html')
// })
// exception handler
server.use((req,res,next,err)=>{
  console.log(err.message)
  res.sendStatus(500);
})

server.listen(3006, () => {
  console.log("server up");
});

pool.js

const mysql=require('mysql');
//Create a connection pool object
// var pool=mysql.createPool({
// host:'localhost',
// port:'3306',
// user:'root',
// password:'',
// database:'cake',
// connectionLimit:20
// });
var pool=mysql.createPool({
     host: '22.222.12.122',
   port: 3306,
   user:'root',
   password:'root',
     database:'cake',
     connectionLimit:20
});
//Connect pool objects everywhere
module.exports=pool;

Examples of routing files are as follows:

./routers/index

const express = require("express");
const router = express.Router();
const pool = require("../pool");

//A query returns 4 pieces of data
router.get("/", (req, res) => {
  var status = req.query.status;
  var output = {
    product: {}
  }
  if (status !== undefined) {
    var sql1 = `select * from cake_index_product where index_status=?`;
    pool.query(sql1, [status], (err, result) => {
      if (err) console.log(err);
      output.product = result;
      // console.log(output);
      // console.log("haha1");
          res.send(output);
      })

  }else{
    res.send(output);
    console.log(444)
  }
})

//One query returns multiple pieces of data
router.get("/insale", (req, res) => {
  var status = req.query.insale;
  var output = {
    product: {}
  }
  if (status !== undefined) {
    var sql1 = `select * from cake_index_product where index_sale_new=?`;
    pool.query(sql1, [status], (err, result) => {
      if (err) console.log(err);
      output.product = result;
          res.send(output);
      })

  }else{
    res.send(output);
    // console.log(444)
  }
})
//Home product one-time display + carousel image query
router.get("/all", (req, res) => {
  var output = {
    carouselItems:{},
    product: {},
    pics:{},
    kinds:{}
  }
    var sql1 = `select * from index_img `;
    pool.query(sql1,(err, result) => {
      if (err) console.log(err);
      output.carouselItems = result;
      var sql2 = `select * from cake_index_product `;
      pool.query(sql2,(err, result) => {
      if (err) console.log(err);
      output.product = result;

      var sql3 = `select * from product_pic `;
      pool.query(sql3,(err, result) => {
      if (err) console.log(err);
      output.pics = result;

      var sql4 = `select * from product_kinds `;
      pool.query(sql4,(err, result) => {
      if (err) console.log(err);
      output.kinds = result;
          res.send(output);
      })
      })})
      })
})
// Test interface aa
router.post("/scan/collect", (req, res) => {
  console.log('wolaila11')
  let emp_no = req.body.emp_no;
  let emp_name = req.body.emp_name;
  let card_time = req.body.card_time;
  let qrcode = req.body.qrcode;
  var output = {
    error:0,
    message:'success'
  }
  var err = {
    error:1,
    message:'Parameters are wrong or missing parameters'
  }

  if(emp_no&&emp_name&&card_time&&qrcode){
    res.send(output);
  }else{
    res.send(err);
  }

})
// Test interface
//One query returns multiple data post
router.post("/postStatus", (req, res) => {
  console.log('wolaila')
  var status = req.body.status;
  var output = {
    product: {},
    lalala:'woshiceshi'
  }
  if (status !== undefined) {
    var sql1 = `select * from cake_index_product where index_sale_new=?`;
    pool.query(sql1, [status], (err, result) => {
      if (err) console.log(err);
      output.product = result;
          res.send(output);
      })

  }else{
    res.send(output);
    // console.log(444)
  }
})

//Homepage product carousel image query
router.get("/img", (req, res) => {
  console.log('/img')
  var output = {
    carouselItems:{}
  }
    var sql1 = `select * from index_img `;
    pool.query(sql1,(err, result) => {
      console.log('Enter SQL')
      if (err) console.log(err);
      output.carouselItems = result;
          res.send(output);
      })
})
module.exports = router;
created at:11-20-2021
edited at: 11-20-2021: