Node red on RPi refuse to connect to MySQL DB running on another RPi on local LAN

fritserasmus · 27 March 2023 15:57

I have my Node-RED on a RPi 192.168.1.23 and MySQL on another RPi 192.168.1.24

I used to have Node-RED, MQTT server and MySQL DB server on one RPi , but it struggled so I am splitting it up to two RPi with Node-RED and MQTT running on a RPi 4B 8 GB - Works perfect with more connections now.

My next phase is to connect to a newly created DB on a separate RPi but I just cannot figure out how to change privileges to allow the connection.

My injection appears to be just fine:-

\var temperature = msg.payload.DS18B20.Temperature;
var string1 = "INSERT INTO `TestTemp`(`index`, `create_date`, `main_source`, `source`, `reading`)"; // string1:
var string2 = " VALUES (NULL, current_timestamp(),'1856',"; // string2
var string3 = "'Office',"; // string3
var string4 = temperature; // string4
var string5 = ")"; // string5
msg.topic = string1 + string2 + string3 + string4 + string5; // Concatenate strings
return msg;

But the connection node fails with this message:

3/27/2023, 5:48:51 PMnode: MySQL on 24
msg : string[22]
"Database not connected"
3/27/2023, 5:48:57 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"

The MySQL connection

The PHPmyAdmin where I successfully inserted data using the myPHPAdmin interface

User privileges using commandline

MariaDB [(none)]>

User privileges from phpMyAdmin

I need more simplistic guidence than these detailed websites as it confuses me just more

javascript - Node.js Error: connect ECONNREFUSED - Stack Overflow

https://community.progress.com/s/article/Connection-Error-ECONNREFUSED-Connection-refused-by-the-server

Running the command: sudo service mysql status

 mariadb.service - MariaDB 10.5.15 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2023-03-24 11:17:12 SAST; 3 days ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 507 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 524 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 535 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-enviro>
    Process: 653 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 655 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 609 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 14 (limit: 1595)
        CPU: 1min 6.376s
     CGroup: /system.slice/mariadb.service
             └─609 /usr/sbin/mariadbd

Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[661]: There is no need to run mysql_upgrade again for 10.5.15-MariaDB.
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[661]: You can use --force if you still want to run mysql_upgrade
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[673]: Checking for insecure root accounts.
Mar 24 11:17:12 MySQLPi /etc/mysql/debian-start[679]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Mar 25 12:51:44 MySQLPi mariadbd[609]: 2023-03-25 12:51:44 571 [Warning] Access denied for user 'erasmi'@'localhost' (using password: YES)

marcus-j-davies · 27 March 2023 16:39

Hi @fritserasmus

ECONNREFUSED means the port 3306 is not offering any services on the IP Address of 192.168.1.24

MySQL is not configured to listen on 192.168.1.24 (check my.ini)
Some OS/Router level firewall setting, that actively rejects the connection.

This is quite different to ACCESS DENIED - this is a network level refusal to gain access to the MySQL instance

jbudd · 27 March 2023 16:43

Have you made any changes to /etc/mysql/mariadb.conf.d/50-server.cnf to allow connections from other devices?

marcus-j-davies · 27 March 2023 16:45

Is that the my.ini equivalent? - I have only ever used MySQL - but know they are almost the same thing right?

EDIT:
Oh hang-on my.ini is windows only

dynamicdave · 27 March 2023 17:27

I found I had to enter the IP address of the remote RPi that is accessing the dB on the main RPi.
I couldn't get it to recognise the 'host-name'. Hope this helps?
db_access

fritserasmus · 27 March 2023 18:40

Thanks for checking,

Yes, I did.

fritserasmus · 27 March 2023 18:54

@dynamicdave ,

This is how have the connection configured:

Is this what you meant?

fritserasmus · 27 March 2023 19:00

I still get the error:

3/27/2023, 8:59:23 PMnode: MySQL on 24
msg : string[22]
"Database not connected"
3/27/2023, 8:59:30 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"

Any further suggestions?

dynamicdave · 27 March 2023 19:12

I think you are showing me the settings in the Node-RED mysql node.
I was refering to the User accounts overview inside 'phpmyadmin' you showed near the start of the thread.
I think you need an entry in the User Accounts for 192.168.1.23 in order for it to access 192.168.1.24

Although I use the command line $sudo mysql -u root -p to manage my users/privileges/passwords, I'm sure you can do the same thing using 'phpmyadmin' from a web browser.

For what its worth - I seem to remember I went through a lot of trial-and-error before I got things to work!!

fritserasmus · 27 March 2023 20:30

Got that now

 SELECT User,Host FROM mysql.user WHERE User='erasmi';
+--------+--------------+
| User   | Host         |
+--------+--------------+
| erasmi | 192.168.1.23 |
| erasmi | 6mtchurchill |
| erasmi | localhost    |
+--------+--------------+
3 rows in set (0.005 sec)

But still no connection.

3/27/2023, 10:30:57 PMnode: MySQL_RPi
msg : error
"Error: connect ECONNREFUSED 192.168.1.24:3306"
3/27/2023, 10:31:03 PMnode: MySQL on 24
msg : string[22]
"Database not connected"

fritserasmus · 27 March 2023 20:33

BTW:

the User name, is the user I use to log onto the DB, is that correct?

fritserasmus · 28 March 2023 02:30

Just to make the feedback more complete by adding first few lines of "50-server.cnf
":-

#
# * Basic Settings
#

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.

bind-address            = 192.168.1.23

fritserasmus · 28 March 2023 02:36

I changed bind-address to 0.0.0.0 and Node-RED connected successfully.

Problem SOLVED!!

dynamicdave · 28 March 2023 07:25

Pleased you got it working.

marcus-j-davies · 28 March 2023 07:39

Cough cough

glad you got to the bottom of it!

Although, your trying to connect to 192.168.1.24 not 192.168.1.23 - I mean its working so

You may need to adjust PMA to connect to the lan address, instead of localhost - I'm not sure.

system · 11 April 2023 07:40

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Trouble connecting to MariaDB using node-red-node-mysql Error: connect ECONNREFUSED 127.0.0.1:3306 General	6	1734	5 August 2022
NODE-RED MYSQL NODE connection refused General	27	13592	16 February 2021
Mssql node-red raspberry pi General	9	720	22 May 2020
Connecting to Mariadb server on another computer General	5	833	21 February 2022
Error in connection with mariadb General	8	3554	22 April 2020

Node red on RPi refuse to connect to MySQL DB running on another RPi on local LAN

Related topics