Node-Red Newbie with Questions: Help with error logs

Hello,

This is my first post in this forum and I've learned enough about Node-Red on my own to be dangerous. I've gone down a delightfully convoluted rabbit hole to basically try to graph out various information coming out of my Hubitat hub. I certainly didn't design it, I'm just following footsteps and trying to debug along the way.

The project overview is here: Hubitat > NodeRed > MySQL > Grafana (LONG READ) - Integrations - Hubitat perhaps somebody here is familiar with this project or perhaps somebody here is familiar with a less invasive solution but at the end of the day this is a wonderful learning opportunity for me that I've enjoyed.

The flow I used:

[{
		"id": "97f64ca.d04b7b",
		"type": "tab",
		"label": "Hubitat Logging",
		"disabled": false,
		"info": ""
	},
	{
		"id": "a76fb58c.e649d8",
		"type": "websocket in",
		"z": "97f64ca.d04b7b",
		"name": "Hubitat Logs WS",
		"server": "",
		"client": "a188bb97.72e918",
		"x": 120,
		"y": 80,
		"wires": [
			[
				"ea94de21.c963d",
				"b03a9a19.0765e8"
			]
		]
	},
	{
		"id": "78ade93b.b1bac8",
		"type": "mysql",
		"z": "97f64ca.d04b7b",
		"mydb": "b443bf10.3a478",
		"name": "Logging DB",
		"x": 1270,
		"y": 80,
		"wires": [
			[]
		]
	},
	{
		"id": "b03a9a19.0765e8",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create Logs INSERT",
		"func": "var query = \"INSERT INTO logs(`name`,`msg`,`id`,`time`,`type`,`level`) VALUES(\";\n//var name = msg.name.replace(/'/g, \"\\'\");\n//var msg = msg.msg.replace(/'/g, \"\\'\");\n\nquery += \"'\" + msg.name + \"',\";\nquery += \"'\" + msg.msg + \"',\";\nquery += msg.id + ',';\nquery += \"'\" + msg.time + \"',\";\nquery += \"'\" + msg.type + \"',\";\nquery += \"'\" + msg.level + \"')\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 640,
		"y": 80,
		"wires": [
			[
				"78ade93b.b1bac8"
			]
		]
	},
	{
		"id": "1b825032.f16ee",
		"type": "websocket in",
		"z": "97f64ca.d04b7b",
		"name": "Hubitat Events WS",
		"server": "",
		"client": "384bf512.4d2332",
		"x": 130,
		"y": 200,
		"wires": [
			[
				"19d853b5.dd045c",
				"f6d78ae6.9b33c8",
				"837d6d2b.cd3d4"
			]
		]
	},
	{
		"id": "837d6d2b.cd3d4",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create Events INSERT",
		"func": "var query = \"INSERT INTO events(`source`,`name`,`displayName`,`value`,`unit`,`deviceId`,`hubId`,`locationId`,`installedAppId`,`descriptionText`) VALUES(\";\n//var name = msg.name.replace(/'/g, \"\\'\");\n//var msg = msg.msg.replace(/'/g, \"\\'\");\n\nquery += \"'\" + msg.source + \"',\";\nquery += \"'\" + msg.name + \"',\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"',\";\nquery += \"'\" + msg.unit + \"',\";\nquery += msg.deviceId           === null ? 'null,' : msg.deviceId + ',';\nquery += msg.hubId              === null ? 'null,' : msg.hubId + ',';\nquery += msg.locationId         === null ? 'null,' : msg.locationId + ',';\nquery += msg.installedAppId     === null ? 'null,' : msg.installedAppId + ',';\nquery += msg.descriptionText    === null ? 'null)' : \"'\" + msg.descriptionText + \"')\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 640,
		"y": 160,
		"wires": [
			[
				"78ade93b.b1bac8"
			]
		]
	},
	{
		"id": "e39467d7.6dc3e8",
		"type": "catch",
		"z": "97f64ca.d04b7b",
		"name": "",
		"scope": [
			"1b825032.f16ee",
			"a76fb58c.e649d8",
			"78ade93b.b1bac8"
		],
		"x": 70,
		"y": 720,
		"wires": [
			[
				"5b6da7c4.6ec298"
			]
		]
	},
	{
		"id": "5b6da7c4.6ec298",
		"type": "change",
		"z": "97f64ca.d04b7b",
		"name": "Convert error.message to msg.payload",
		"rules": [{
			"t": "set",
			"p": "payload",
			"pt": "msg",
			"to": "HE Logging has thrown an error",
			"tot": "str"
		}],
		"action": "",
		"property": "",
		"from": "",
		"to": "",
		"reg": false,
		"x": 330,
		"y": 720,
		"wires": [
			[
				"c3c2bc48.01bc5"
			]
		]
	},
	{
		"id": "c3c2bc48.01bc5",
		"type": "debug",
		"z": "97f64ca.d04b7b",
		"name": "",
		"active": true,
		"tosidebar": true,
		"console": false,
		"tostatus": false,
		"complete": "true",
		"x": 590,
		"y": 720,
		"wires": []
	},
	{
		"id": "46208e9a.ca969",
		"type": "inject",
		"z": "97f64ca.d04b7b",
		"name": "",
		"topic": "",
		"payload": "",
		"payloadType": "date",
		"repeat": "900",
		"crontab": "",
		"once": true,
		"onceDelay": 0.1,
		"x": 110,
		"y": 420,
		"wires": [
			[
				"7990ff36.983b4"
			]
		]
	},
	{
		"id": "7891807e.90163",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create temp INSERT",
		"func": "var query = \"INSERT INTO temperatures(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.payload.id + \",\";\nquery += \"'\" + msg.payload.label + \"',\";\nquery += msg.payload.attributes['temperature'];\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 1080,
		"y": 420,
		"wires": [
			[
				"ce199a1d.01e3a8"
			]
		]
	},
	{
		"id": "7990ff36.983b4",
		"type": "http request",
		"z": "97f64ca.d04b7b",
		"name": "Get Devices with Capabilites",
		"method": "GET",
		"ret": "obj",
		"url": "",
		"tls": "",
		"x": 340,
		"y": 420,
		"wires": [
			[
				"b726ad7a.fa073"
			]
		]
	},
	{
		"id": "b726ad7a.fa073",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Find Temperature Devices",
		"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n    if(device.capabilities && device.capabilities[0] == 'TemperatureMeasurement')\n        devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 610,
		"y": 420,
		"wires": [
			[
				"f1a7c34d.89ce1"
			]
		]
	},
	{
		"id": "f1a7c34d.89ce1",
		"type": "splitter",
		"z": "97f64ca.d04b7b",
		"name": "Split payload array",
		"property": "payload",
		"x": 850,
		"y": 420,
		"wires": [
			[
				"7891807e.90163"
			]
		]
	},
	{
		"id": "ce199a1d.01e3a8",
		"type": "mysql",
		"z": "97f64ca.d04b7b",
		"mydb": "b443bf10.3a478",
		"name": "Logging DB",
		"x": 1270,
		"y": 420,
		"wires": [
			[]
		]
	},
	{
		"id": "ed81fd66.e69b7",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Find Battery Devices",
		"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n    if(device.capabilities && device.capabilities[1] == 'Battery')\n        devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 600,
		"y": 560,
		"wires": [
			[
				"7f6d0f15.9d5fa"
			]
		]
	},
	{
		"id": "7f6d0f15.9d5fa",
		"type": "splitter",
		"z": "97f64ca.d04b7b",
		"name": "Split payload array",
		"property": "payload",
		"x": 850,
		"y": 560,
		"wires": [
			[
				"36f8d654.2bf7ba"
			]
		]
	},
	{
		"id": "36f8d654.2bf7ba",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create battery INSERT",
		"func": "var query = \"INSERT INTO batteries(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.payload.id + \",\";\nquery += \"'\" + msg.payload.label + \"',\";\nquery += msg.payload.attributes['battery'];\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 1080,
		"y": 560,
		"wires": [
			[
				"fd413858.152d38"
			]
		]
	},
	{
		"id": "fd413858.152d38",
		"type": "mysql",
		"z": "97f64ca.d04b7b",
		"mydb": "b443bf10.3a478",
		"name": "Logging DB",
		"x": 1270,
		"y": 560,
		"wires": [
			[]
		]
	},
	{
		"id": "9a69f4a5.deaff8",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create Motion INSERT",
		"func": "var query = \"INSERT INTO motions(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.deviceId + \",\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"'\";\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 640,
		"y": 200,
		"wires": [
			[
				"78ade93b.b1bac8"
			]
		]
	},
	{
		"id": "19d853b5.dd045c",
		"type": "switch",
		"z": "97f64ca.d04b7b",
		"name": "Name switch",
		"property": "name",
		"propertyType": "msg",
		"rules": [{
				"t": "eq",
				"v": "motion",
				"vt": "str"
			},
			{
				"t": "eq",
				"v": "switch",
				"vt": "str"
			}
		],
		"checkall": "true",
		"repair": false,
		"outputs": 2,
		"x": 410,
		"y": 220,
		"wires": [
			[
				"9a69f4a5.deaff8"
			],
			[
				"cbbf29b1.b06698"
			]
		]
	},
	{
		"id": "cbbf29b1.b06698",
		"type": "function",
		"z": "97f64ca.d04b7b",
		"name": "Create Switch INSERT",
		"func": "var query = \"INSERT INTO switches(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.deviceId + \",\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"'\";\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
		"outputs": 1,
		"noerr": 0,
		"x": 640,
		"y": 240,
		"wires": [
			[
				"78ade93b.b1bac8"
			]
		]
	},
	{
		"id": "20f2d767.c7b5a8",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Use websockets to log all events and logs",
		"info": "",
		"x": 200,
		"y": 40,
		"wires": []
	},
	{
		"id": "904730a4.46b0b",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Switches on msg.name to log motions and switches",
		"info": "",
		"x": 950,
		"y": 220,
		"wires": []
	},
	{
		"id": "1935fea3.313a31",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Calls Maker API every 15 minutes to log temperatures. (Fixes issue with temps/batteries only updating when they want to via websockets)",
		"info": "",
		"x": 480,
		"y": 380,
		"wires": []
	},
	{
		"id": "58f3eccf.6ebb34",
		"type": "inject",
		"z": "97f64ca.d04b7b",
		"name": "",
		"topic": "",
		"payload": "",
		"payloadType": "date",
		"repeat": "21600",
		"crontab": "",
		"once": true,
		"onceDelay": 0.1,
		"x": 110,
		"y": 560,
		"wires": [
			[
				"13d86cdc.bb08a3"
			]
		]
	},
	{
		"id": "13d86cdc.bb08a3",
		"type": "http request",
		"z": "97f64ca.d04b7b",
		"name": "Get Devices with Capabilites",
		"method": "GET",
		"ret": "obj",
		"url": "",
		"tls": "",
		"x": 340,
		"y": 560,
		"wires": [
			[
				"ed81fd66.e69b7"
			]
		]
	},
	{
		"id": "4960662b.bc34c8",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Calls Maker API every 6 hours to log batteries. (Fixes issue with temps/batteries only updating when they want to via websockets)",
		"info": "",
		"x": 450,
		"y": 520,
		"wires": []
	},
	{
		"id": "ea94de21.c963d",
		"type": "logger",
		"z": "97f64ca.d04b7b",
		"name": "Logs File Logger",
		"filename": "/var/log/hubitat/logs.json",
		"maxsize": "500",
		"maxfiles": 10,
		"complete": "payload",
		"console": false,
		"file": true,
		"debug": false,
		"zip": true,
		"logtype": "info",
		"x": 310,
		"y": 120,
		"wires": []
	},
	{
		"id": "f6d78ae6.9b33c8",
		"type": "logger",
		"z": "97f64ca.d04b7b",
		"name": "Events File Logger",
		"filename": "/var/log/hubitat/events.json",
		"maxsize": "500",
		"maxfiles": 10,
		"complete": "payload",
		"console": false,
		"file": true,
		"debug": false,
		"zip": true,
		"logtype": "info",
		"x": 310,
		"y": 280,
		"wires": []
	},
	{
		"id": "311185f1.e9d61a",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Log all entries to file on server for backup",
		"info": "",
		"x": 580,
		"y": 120,
		"wires": []
	},
	{
		"id": "e5b4b2c1.92991",
		"type": "comment",
		"z": "97f64ca.d04b7b",
		"name": "Log all entries to file on server for backup",
		"info": "",
		"x": 580,
		"y": 280,
		"wires": []
	},
	{
		"id": "a188bb97.72e918",
		"type": "websocket-client",
		"z": "",
		"path": "ws://192.168.1.110/logsocket",
		"tls": "",
		"wholemsg": "true"
	},
	{
		"id": "b443bf10.3a478",
		"type": "MySQLdatabase",
		"z": "",
		"host": "192.168.1.100",
		"port": "3306",
		"db": "hubitat_logging",
		"tz": ""
	},
	{
		"id": "384bf512.4d2332",
		"type": "websocket-client",
		"z": "",
		"path": "ws://192.168.1.110/eventsocket",
		"tls": "",
		"wholemsg": "true"
	}
]

I'm fairly handy with this kind of stuff and some light guidance will go a long way with me. I'm sure I've come to the correct place of enthusiasts to help me :slight_smile:

This being my first post here, I did read some of the tips for making a valuable post. I hope I provided enough info to at least convey to somebody where I am at and the statement of the problem in the correct format. If there's better ways I should be providing information, let me know; I'm flexible.

At the end of the day, what I am asking for is help with how to read/act upon the errors I'm seeing.

Welcome to the forum @SergeantPup

First point, for the future, please see this post for details of how to post flows and code, currently your flow is not importable (but I don't need to import it anyway yet). How to share code or flow json

Most errors that appear in the debug pane will have the name of the node in the first line. Also if you click on that line it should take you to the node generating the error.

The most important information you have provided is the error message in the message you have posted. If you look in there you will see that one of the values that appears in the SQL is just the text undefined. That is what the database is complaining about. If you add a debug node showing what is going into the node that generates the query and another showing what is coming out of that node then that may give you a better idea of what is going on. I imagine that the data going into the function is missing one field.
If you cannot see why the function is failing then post the debug output of what is going in and out and just the code in that function so we can help with determining the problem.

1 Like

That's definitely the post I referenced when making this thread. I must've missed a step that didn't allow it to format correctly. I'll be more careful with that in the future.

These are great suggestions and gives me enough information to chase down more info. I'll work on it over the weekend and come back here if I hit any barriers. Each time I approach this problem, I get significantly further. I'm so close to having all this talk correctly to eachother.

If you go back and edit the post (the pen symbol at the end) then you will see that you just have single backticks round the flow rather than triple backticks on separate lines. If you used one of the buttons then perhaps you clicked the wrong one, it should be the </> button. You can correct the backticks and save the change, then it should format correctly. Again for the future, if when exporting the flow you select Compact (down at the bottom of the dialog) then it will put it all on one line, so it takes less space.

Somehow I made the post worse lol. I'm just gonna leave it alone until the next time I need to post. But I DID see what you meant by the 3 ''' on their own lines before/after and I definitely messed that up to begin with.

1 Like

Hello Colin,

You gave me such valuable information to where I was able to isolate every error (and subsequent errors) and resolve all the known issues. I found SEVERAL more problems since I last responded and all of them have been resolved. The bottom line was there was definitely some bad setup (and UNIQUE choices) in the original flow I imported and without the clarity of step by step instructions, the debugging helped me fill in the gaps.

I am pleased as punch right now and I learned so much and had fun in the process. Thanks!

1 Like

Glad to be of help :slight_smile:

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