it has been a long day and i don't know why i am stuck at this problem for such a long time so any help is very much appreciated!
my problem is the famus code: 'ECONNREFUSED' which suddenly appeared for my project. i am trying to run a mysql db with PMA in docker and trying to connect to it from my nodejs (not in docker but on host) to get a fetch response but everything that i try and any suggestion i look at i find no solution! so any idea at this point would help me greatly and get me to bed sooner.
i cut the story short and put my codes here please do let me know with any tips, thanks in advance!
this is my server.js file
const express = require('express');
const apiroutes = require('./routes');
const app = express();
app.use(express.json());
app.use('/api/data', apiroutes);
app.listen('3000', ()=>{
console.log('server is running on port: 3000');
});
this is my routes file
const express = require('express');
const db = require('../db');
const router = express.Router();
router.get('/', async(req, res, next)=>{
try{
let results = await db.all();
res.json(results);
} catch(error){
console.log(error);
res.sendStatus(500);
}
});
module.exports = router;
this is my db file
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit : 100,
password: 'secret',
user: 'user',
database: 'db_test',
host: 'localhost',
port: '3306'
});
let wholeDB = {};
wholeDB.all = () => {
return new Promise((resolve, reject)=>{
pool.query(`SELECT * FROM table1`,(error, result)=>{
if(error){
return reject(error);
}
return resolve(result);
});
});
};
module.exports = wholeDB;
this is my dockerfile
FROM php:7.2-fpm-alpine
RUN docker-php-ext-install pdo pdo_mysql
and finaly this is my docker-compose file
version: '3'
networks:
vuePhP:
services:
nginx:
image: nginx:stable-alpine
container_name: nginxWebServer
ports:
- "8088:80"
volumes:
- ./src:/var/www/html
- ./nginx/default.conf:/etc/nginx/conf.d/default.conf
depends_on:
- mysql
- php
networks:
- vuePhP
mysql:
image: mysql
container_name: mysqlService
restart: unless-stopped
tty: true
ports:
- "4306:3306"
volumes:
- ./mysql:/var/lib/mysql
environment:
MYSQL_DATABASE: db_test
MYSQL_USER: user
MYSQL_PASSWORD: secret
MYSQL_ROOT_PASSWORD: root
networks:
- vuePhP
phpmyadmin:
image: phpmyadmin
container_name: phpMyAdmin
restart: always
depends_on:
- mysql
- php
environment:
- PMA_HOST=mysql
- PMA_PORT=3306
ports:
- "8081:80"
networks:
- vuePhP
php:
build:
context: .
dockerfile: Dockerfile
container_name: phpService
volumes:
- ./src:/var/www/html
ports:
- "9000:9000"
networks:
- vuePhP
and this is the error that i get when i use postman to do a get command
errno: -111, code: 'ECONNREFUSED', syscall: 'connect',
address: '127.0.0.1', port: 3306, fatal: true
Error: connect ECONNREFUSED 127.0.0.1:3306 at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1157:16)
thank you!
edit: i have added the port 4306 to the node app and get this error :
code: 'ER_NOT_SUPPORTED_AUTH_MODE', errno: 1251, sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client', sqlState: '08004', fatal: true
i added this line in my compose file but it didn't help:
mysql:
image: mysql
container_name: mysqlService
restart: unless-stopped
tty: true
ports:
- "4306:3306"
volumes:
- ./mysql:/var/lib/mysql
environment:
MYSQL_DATABASE: KFV_test
MYSQL_USER: user
MYSQL_PASSWORD: secret
MYSQL_ROOT_PASSWORD: root
command: --default-authentication-plugin=mysql_native_password
networks:
- vuePhP
CodePudding user response：
ah i found it, the problem is the version of mysql in composer and usage of mysql library instead of mysql2 as Phil graciously mentioned. so i downgraded mysql version by version until i found the one that worked and before that added mysql2 (npm i mysql2) to my nodejs app. and it worked. here my is my changes in docker-compose file. btw changed the ports as well not that it helped before the downgrade.
mysql:
image: mysql:5.7.24
container_name: mysqlService
restart: unless-stopped
tty: true
ports:
- "3306:3306"
volumes:
- ./mysql:/var/lib/mysql
environment:
MYSQL_DATABASE: db_test
MYSQL_USER: user
MYSQL_PASSWORD: secret
MYSQL_ROOT_PASSWORD: root
networks:
- vuePhP
and changed the connection method in my db file: const mysql2 = require('mysql2');
const connection = mysql2.createConnection({
host: 'localhost',
user: 'user',
password: 'secret',
database: 'db_test',
});
let wholeDB = {};
wholeDB.all = () => {
return new Promise((resolve, reject)=>{
connection.query(`SELECT * FROM table1`,(error, result)=>{
if(error){
return reject(error);
}
return resolve(result);
});
});
};
module.exports = wholeDB;
CodePudding user response：
var mysql = require('mysql')
var connection = mysql.createPool({
host: "localhost",
user: "root",
password: "",
database: "dbName",
port : "3306"
})
connection.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
console.error('Database connection was closed.')
}
if (err.code === 'ER_CON_COUNT_ERROR') {
console.error('Database has too many connections.')
}
if (err.code === 'ECONNREFUSED') {
console.error('Database connection was refused.')
}
}
if (connection) connection.release()
return
})
module.exports = connection
example :
import connection from '../Modules/connection.js';
let LoginQuery = (values) => {
return new Promise((resolve, reject) => {
// let sql = `SELECT * FROM user_tbl WHERE mobile_number ="${values.mobile_number}" OR email_id="${values.mobile_number}"`;
let sql = "SELECT * FROM `admin_tbl` WHERE mobile_number = ? OR email_id = ?";
connection.query(sql, Object.values(values), (err, result) => {
console.log(err);
err ? reject(err) : resolve(result);
});
});
};
Please use this connecting pool hope it'll work.