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

General
1

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

image
image694Ă—776 31.8 KB

The PHPmyAdmin where I successfully inserted data using the myPHPAdmin interface

image
image1250Ă—1216 143 KB

User privileges using commandline

MariaDB [(none)]> SELECT User,Host FROM mysql.user WHERE User='erasmi';
+--------+--------------+
| User | Host |
+--------+--------------+
| erasmi | 6mtchurchill |
| erasmi | localhost |
+--------+--------------+
2 rows in set (0.006 sec)

MariaDB [(none)]>

User privileges from phpMyAdmin

image
image1101Ă—878 109 KB

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)
2

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

3

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

1 Like
4

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

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

5

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

7

Thanks for checking,

Yes, I did.

8

@dynamicdave ,

This is how have the connection configured:

image
image718Ă—780 32.7 KB

Is this what you meant?

9

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?

10

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!!

11

image
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"
12

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

13

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
14

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

Problem SOLVED!!

15

Pleased you got it working.

16

Cough cough :wink:

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 :man_shrugging:

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

17

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