Error setting up MySQL database in ctrlX CORE Node-RED App

[ahmadKhairi]

Hello,

I have installed node-red-node-mysql and have installed MariaDB (version 10.2.6) and HeidiSQL.

Previously I have done the steps to create MySQL database with the PR21 IoT Gateway.

Here is my reference: https://connect.bosch.com/blogs/fd06bcde-5a87-4016-8979-a675625c1791/entry/IoT_Gateway_1_4_Sneak_pre...

So now I am attempting to do the same with ctrlX CORE.

In HeidiSQL, I have created the database as below MySQL statements which I run in the Query:

CREATE DATABASE ctrlX;

USE ctrlX;

CREATE TABLE endpoint (

PID varchar(120) NOT NULL,

NAME varchar(100) DEFAULT NULL,

UNIT varchar(10) DEFAULT NULL,

DATA longtext DEFAULT NULL,

PRIMARY KEY (PID)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE endpoint_value (

ID int(11) NOT NULL AUTO_INCREMENT,

VALUE varchar(200) DEFAULT NULL,

TIMESTAMP timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE

current_timestamp(),

QUALITY varchar(20) DEFAULT NULL,

PID varchar(120) DEFAULT NULL,

PRIMARY KEY (ID),

KEY PID (PID),

CONSTRAINT endpoint_value_ibfk_1 FOREIGN KEY (PID) REFERENCES endpoint (PID)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE USER 'ctrlxCore'@'localhost' IDENTIFIED BY 'password';

CREATE USER 'ctrlxCore'@'%' IDENTIFIED BY 'password';

Apart from the above, I have also allowed access to Global privileges for the user 'ctrlxCore'.

In Node-RED I have added the mysql node onto my flow and created the MySQL database using the database 'ctrlX' created above.

The input is coming directly from a mqtt subscribe node and I want to save the data into the database.

The mqtt output is just a float number from an ambient temperature sensor. It doesn't come in JSON object format.

However when I Deploy in Node-RED, these errors pop up:

1) "Error: connect ECONNREFUSED 127.0.0.1:3306"

2) "Database not connected"

I know that probably it isn't a good idea to just use the SQL statements from the tutorial because it was meant for PR21 IoT Gateway but does anyone have tried this method before and succeeded to store data via MySQL from Node-RED app?

[ahmadKhairi · Accepted solution]

I already found a solution to my own question.

The issues I had:

1. In HeidiSQL, the user created is not linked to ctrlX IP address.

2. In ctrlX Node-RED MySQL node, the database is not pointed to where the database was located.

So I did this instead:

- Inside HeidiSQL Query:

CREATE USER 'ctrlxCore'@'192.168.0.234' IDENTIFIED BY 'password';

Where 192.168.0.234 is my ctrlX Core device

- Inside Node-RED MySQL database node:

Host: 192.168.0.132 (my computer IP where database was created/installed)
port: 3306
user: ctrlxCore
password: password
database: ctrlx
Charset: UTF8

In HeidiSQL, I dropped the previously created TABLES and created a new and simple TABLE to get values from mqtt-in node:

CREATE TABLE humidity (
VALUE VARCHAR(100),
TIMESTAMP timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE
);

You can refer to my Node-RED flow as per attached screenshots.

Thank you

[StreetCruiser]

Hello @ahmadKhairi. 

I found your post as I look up the term "MySQL". Just wondering if you happen to ever install ORACLE MySQL Database in the ctrlX Core?

I intend to install it, but not sure if it would work. I know that ORACLE MySQL can be installed in Ubuntu, but not sure in Ubuntu CORE...

Any feedback from you is highly appreciated. Thanks.