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 10002ms\n' +
' (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.
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 :
// Database connection configuration
const pool = mariadb.createPool({
host: '127.0.0.1',
user: 'query',
password: 'query_pw',
database: 'weather',
connectionLimit: 5,
});