This is my first stackoverflow post, I am currently working on a project for school, but unfortunately I can’t get this bug fixed.
I have a node.js script which should process a JSON file and insert it into a MariaDB database. However, when I execute the script, the following error message appears after 10 seconds (timeout). I suspect that it is not due to the processing function of the script, but already timed out before.
The database and the script are both on my Raspberry PI 4 with Rasbian
Regards
Alex from Frankfurt, Germany
Error message:
Error: SqlError: (conn=-1, no: 45028, SQLState: HY000) retrieve connection from pool timeout after 10002ms
(pool connections: active=0 idle=0 limit=5)
at module.exports.createError (/home/alexpi/node_modules/mariadb/lib/misc/errors.js:64:10)
at Pool._requestTimeoutHandler (/home/alexpi/node_modules/mariadb/lib/pool.js:349:26)
at listOnTimeout (node:internal/timers:564:17)
at process.processTimers (node:internal/timers:507:7) {
sqlMessage: 'retrieve connection from pool timeout after 10002msn' +
' (pool connections: active=0 idle=0 limit=5)',
sql: null,
fatal: false,
errno: 45028,
sqlState: 'HY000',
code: 'ER_GET_CONNECTION_TIMEOUT'
}
node.js script:
const mariadb = require('mariadb');
const moment = require('moment-timezone');
const fs = require('fs');
// Read the JSON file
const rawData = fs.readFileSync('weather.json');
const weatherData = JSON.parse(rawData);
// Database connection configuration
const pool = mariadb.createPool({
host: 'localhost',
user: 'query',
password: 'query_pw',
database: 'weather',
connectionLimit: 5,
});
async function processData() {
// Create a connection
let conn;
try {
conn = await pool.getConnection();
// Delete existing entries in the database
await conn.query('DELETE FROM allData');
// Iterate over each weather entry
for (const entry of weatherData.weather) {
// Parse and convert timestamp to CET
const timestampCET = moment(entry.timestamp).tz('Europe/Berlin');
// Round values
const temperature = Math.round(entry.temperature);
const windSpeed = Math.round(entry.wind_speed);
const precipitation = Math.round(entry.precipitation);
const precipitationProbability = Math.round(entry.precipitation_probability);
// Insert data into the database
await conn.query(
'INSERT INTO allData (date, time, temperature, wind_speed, precipitation, precipitation_probability, icon) VALUES (?, ?, ?, ?, ?, ?, ?)',
[
timestampCET.format('YYYY-MM-DD'),
timestampCET.format('HH:mm:ss'),
temperature,
windSpeed,
precipitation,
precipitationProbability,
entry.icon,
]
);
}
console.log('Data inserted successfully.');
} catch (err) {
console.error('Error: ', err);
} finally {
if (conn) conn.release(); // release connection
}
}
// Process the data
processData();
When I type "netstat -tunlp", I get the following output:
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN -
tcp6 0 0 :::1883 :::* LISTEN -
tcp6 0 0 ::1:631 :::* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -
udp 0 0 0.0.0.0:42112 0.0.0.0:* -
udp 0 0 0.0.0.0:5353 0.0.0.0:* -
udp 0 0 0.0.0.0:631 0.0.0.0:* -
udp6 0 0 :::5353 :::* -
udp6 0 0 fe80::fed7:dfaa:a02:546 :::* -
udp6 0 0 :::42625 :::* -
When I use a simple script without a connection pool I get a different error message:
Error fetching data: Error: connect ECONNREFUSED ::1:3306
at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1481:16)
From event:
at /home/alexpi/node_modules/mariadb/lib/connection.js:138:13
at new Promise (<anonymous>)
at Connection.connect (/home/alexpi/node_modules/mariadb/lib/connection.js:1 27:12)
at Object.createConnection (/home/alexpi/node_modules/mariadb/promise.js:38: 17)
at fetchDataFromDatabase (/home/alexpi/weather/testdirect.js:14:26)
at Object.<anonymous> (/home/alexpi/weather/testdirect.js:25:1)
at Module._compile (node:internal/modules/cjs/loader:1218:14)
at Module._extensions..js (node:internal/modules/cjs/loader:1272:10)
at Module.load (node:internal/modules/cjs/loader:1081:32)
at Module._load (node:internal/modules/cjs/loader:922:12) {
errno: -111,
code: 'ECONNREFUSED',
syscall: 'connect',
address: '::1',
port: 3306,
fatal: true,
sqlState: 'HY000'
}
I have read through every Google entry on the error message and asked AI, but unfortunately no approach has worked so far.
2
Answers
The error message "ECONNREFUSED ::1:3306" suggests that it’s trying to connect via IPv6, while mariadb is listening only in ipv4 (from the netstat output).
Try changing the code to force an ipv4 connection :
Reda’s answer is technically correct, however, I’d like to suggest an alternative approach: configuring MariaDB to listen on both IPv4 and IPv6. This method offers a more seamless integration with future applications, as it allows the use of the hostname ‘localhost’ without the need to specify IP addresses.
Moreover, adopting IPv6 is in line with current best practices and future-proofs your setup. Operating services in dual-stack mode ensures compatibility with both IPv4 and IPv6 connections. It’s also important for us as professionals to become increasingly familiar with IPv6 and its benefits.
To implement this, you need to add an additional bind-address entry to your MariaDB configuration file, which is typically located at either /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf. Simply add the following line after the existing bind-address line: