Node-red-node-mysql not behaving

General
1

I installed node-red on a laptop running Ubuntu 18.04. It was installed from the Snap Store so I am not relly sure how to change settings.js.

The flow goes like this:

node-red-node-openweathermap 0.2.1 access OpenWeatherMAPI API
|
Function Node converts some values and creates an SQL
| insert query in msgTopic.
node-red-node-mysql 0.0.19 Insert data to Database

The query works okay but the SQL node is throwing this error exactly every 20 seonds. I think that is the default timeout in the settings.js file. Data only comes from the OWM node around every ten minutes.

Error: ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO)

Problem comes then I try to retrieve the data with another flow in another tab. This flow consist of Inject > SQL > Function Node > Chart Node. The function Node formats the data for the Chart node.

The chart shows on the tab but it never shows data. Only the group label and chart label are shown - no X,Y axis or data. The inject node has the Select Query in. This node injects thhe query as I can see from a debug node. Adebug node after SQL node only shows a blank message.

On another note I tried SQLite node but can only create a :memory:. I cannot get it to connect to a database on the physical (loacal hard) drive. SQL DB Browser will connect to the the physical table. I will attach the two flows.

I am sorry for typos. I have bad tremors in both hand and arms.

Collect and insert flow.

[{"id":"16666702.703cc9","type":"tab","label":"OWMap","disabled":false,"info":""},{"id":"899919a9.ed9758","type":"openweathermap in","z":"16666702.703cc9","name":"OWM Conditions","wtype":"current","lon":"-94.9183","lat":"31.6591","city":"","country":"","language":"en","x":120,"y":60,"wires":[["a5ec3971.0fecd8","9ceedf81.4fd52"]]},{"id":"a5ec3971.0fecd8","type":"function","z":"16666702.703cc9","name":"","func":"\nvar mp = msg.payload;\n \n\nvar tempF = Number(mp.tempc) * 1.8 + 32;\nvar tempK = Number(mp.tempk);\nvar humidity = Number(mp.humidity);\n\nvar dewPtK = Math.pow(humidity/100, (1/8)) * (112 + 0.9 * tempK) + 0.1 * tempK - 112;\n\nvar dewPtF = (dewPtK-273.15) * 1.8 +32;\n\nvar visMi = Number(msg.data.visibility) * 3.28083 /5280;\n\nvar windSpMPH = Number(mp.windspeed) * 2.23694;\n\nvar sql = "INSERT INTO tblObs (dt, location, tempf, humidity, dewPt, pressure, clouds, visibility, ";\nsql = sql + "windSp, windDir) ";\n\nsql = sql + "VALUES (" + msg.data.dt + ", '" + msg.location.city + "', " + tempF + ", " + mp.humidity;\nsql = sql + ", " + dewPtF + ", " + mp.pressure + ", " + mp.clouds + ", " + visMi + ", ";\nsql = sql + windSpMPH + ", " + mp.winddirection + ");"\n\nmsg.topic = sql;\n\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":60,"wires":[["4c8f8e92.9034e"]]},{"id":"4c8f8e92.9034e","type":"mysql","z":"16666702.703cc9","mydb":"40d9596e.d88058","name":"","x":500,"y":60,"wires":[]},{"id":"6f1acf71.68ecf","type":"comment","z":"16666702.703cc9","name":"Open weather map collector","info":"","x":160,"y":20,"wires":},{"id":"9ceedf81.4fd52","type":"debug","z":"16666702.703cc9","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":320,"y":140,"wires":},{"id":"40d9596e.d88058","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"OWMap","tz":""}]

Retreive and Graph Flow.

[{"id":"310f7da9.ca9662","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"db713f49.e4dfc","type":"comment","z":"310f7da9.ca9662","name":"Temp, Dewpt, & Humidity","info":"","x":150,"y":60,"wires":},{"id":"3342e14e.28400e","type":"inject","z":"310f7da9.ca9662","name":"","topic":"Select dt, tempF, humidity, dewPt, clouds from tblObs Where dt >= DATE_SUB(Now(), INTERVAL 2 DAY) Order By dt","payload":"","payloadType":"str","repeat":"240","crontab":"","once":true,"onceDelay":"","x":90,"y":100,"wires":[["47abcf48.c708","7deda106.86dd1"]]},{"id":"38055ba1.35de04","type":"function","z":"310f7da9.ca9662","name":"M Graph","func":"\nvar d0 = ;\nvar d1 = ;\nvar d2 = ;\nvar d3 = ;\n\nlabels = [""];\n\nfor (var i = 0; i< msg.payload.length; i++){\n \n var timestamp = new Date(msg.payload[i].dt); \n \n //Adjust time to show on graph as local\n var to = timestamp.getTimezoneOffset();\n timestamp = timestamp - to * 60 * 1000;\n \n if (i === 0) {var tFirst = timestamp}\n \n var temp = (msg.payload[i].tempF) * (9/5) + 32;\n d0.push({"x":timestamp, "y":tempF}); \n \n var dewpt = (msg.payload[i].dewPt) * (9/5) + 32;\n d1.push({"x":timestamp, "y":dewPt}); \n\n \n var t = parseFloat(msg.payload[i].temp);\n var dp = parseFloat(msg.payload[i].dewpt);\n \n \n d2.push({"x":timestamp, "y":humidity}); \n\n}\n\nvar tLast = timestamp;\n\nt = 32; \nd3.push({"x":tFirst, "y":t});\nd3.push({"x":tLast, "y":t});\n\nmsg.payload = [{"series":["Temperature", "Humidity", "Dewpt","Freezing"],"data": [d0, d1, d2,d3], labels}];\n\nreturn msg;\n\n","outputs":1,"noerr":0,"x":400,"y":100,"wires":[["6b1034c1.4406cc"]]},{"id":"6b1034c1.4406cc","type":"ui_chart","z":"310f7da9.ca9662","name":"","group":"ef4ed59f.7bd018","order":0,"width":0,"height":0,"label":"Temperature, Humidty & Dewpoint","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"0","ymax":"100","removeOlder":"2","removeOlderPoints":"","removeOlderUnit":"86400","cutout":0,"useOneColor":false,"colors":["#ff8000","#80ff00","#0000ff","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":620,"y":100,"wires":[]},{"id":"47abcf48.c708","type":"mysql","z":"310f7da9.ca9662","mydb":"40d9596e.d88058","name":"","x":240,"y":100,"wires":[["38055ba1.35de04","6024c326.00032c"]]},{"id":"7deda106.86dd1","type":"debug","z":"310f7da9.ca9662","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":200,"y":180,"wires":},{"id":"6024c326.00032c","type":"debug","z":"310f7da9.ca9662","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":430,"y":180,"wires":},{"id":"ef4ed59f.7bd018","type":"ui_group","z":"","name":"Graphs","tab":"c7061180.fe759","order":1,"disp":true,"width":"8","collapse":false},{"id":"40d9596e.d88058","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"OWMap","tz":""},{"id":"c7061180.fe759","type":"ui_tab","z":"","name":"Current Conditions","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Query to create table.

CREATE TABLE tblObs (
dt INTEGER NOT NULL,
location TEXT,
tempF NUMERIC,
humidity NUMERIC,
dewPt NUMERIC,
pressure NUMERIC,
clouds NUMERIC,
visibility NUMERIC,
windSp NUMERIC,
windDir NUMERIC
);

Thank you,

Richard Watkins

2

Are you sure you have mysql configured correctly to begin with, as per (for example) ...NODE-RED MYSQL NODE connection refused

3

If you are using the snap it runs by default in restricted mode so may not have access to anything outside of its container, so depending on how mongo is configured they may not be able to talk. If Node-RED is the only snap I would recommend you either run it either in "classic" mode which will give it full access - or not use the snap and use default Node-RED install script instead.

4

Honestly, I am unhappy running it under Snap. As far I can tell they have compressed somewhere that I really can't get to. In the past I have used node red on a Rasbrry Pi2 or 3. I wanted it on the laptopp because it has 1 TB storage andthe pi 16 GBs. On the Pi I always ran a script. Prior to loading with snap I used this command:

sudo npm install -g --unsafe-perm node-red

I know that this installs globally. II don't know exactly what that means. I know that installing with a script on the PI that the install is in the \home\pi.node-red. Iam not sure where the global install is but I am pretty sure it isn't in /home/username.

The flow was throwing the same errors in the global install. I think next I still unstall the snapversion and re-install with the above command as a global.

I am beginning to wonder if this is a node-red 1.0.2. I notice last night that I can no longer enable or disable a flow by double clicking the tab annd clicking the switch. It must have gotten moved sometime in version 1. I am starting to think something in version 1.0.2 is the problem with database nodes. I could only get SQLite to run with a :memory: DB. Trying to use it a physial database although they will readable by the the DB Browser. I would guess it is a premissions error between Node Red and Ubuntu. If you know something or figure it out plese let me know. If need be I guess I will store the data in a fllat file :).

Richard

5

This is the connfiguration.

image
image1920×2130 530 KB

The insert query writes. There is new record every 10 minuutes (over 80 records sine last night). Below is the Select Query the Inject node:

Select dt, tempF, humidity, dewPt, clouds from tblObs Where dt >= DATE_SUB(Now(), INTERVAL 2 DAY) Order By dt

The queery worked for a long time on a PI bu suddenly stopped. Reloaded from a backup but never worked again.

So the short answer is yes I think it is configured correctly.

Richard

6

Hello Richard,

As a newbie, pardon me for asking. Have you check command line access to the database using mysql from the same environment?

Kind regards.

7

Today I completely reloaded the computer. Ubunto 18.04, Lamp Stack (with MariaDB),and Node-Red. The errors are gone. I have no idea but I am glad they did.

Richard

8

Glad to hear that you are back on track. Thanks for closing the loop with your feedback. Helps us to remember one more resolution option during troubleshooting.

Kind regards.