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)]> 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
I need more simplistic guidence than these detailed websites as it confuses me just more
javascript - Node.js Error: connect ECONNREFUSED - Stack Overflow
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)