Photon / NodeJS / MySQL Process Stopping

Hello all,
I’m new here, so sorry if I’m posting in the wrong spot, etc…

I have a Photon setup taking distance measurements every 10 minutes, those are published and then I have a nodeJS script running as a daemon to get that data and insert it into a MySQL DB.

It’s all working fine, but for some reason the process just seems to fail after a few hours or so. I’m not really sure on the time period yet before it fails.

Here are my code samples:

Particle firmware:

//
//    Trash Sensor
//    Purpose: Using HC-SR04 sensor to collect distance data to determine if trash cans are full and need to be emptied
//
//    Author: David Cool
//    Version: 1.0

#include <HC-SR04.h>

STARTUP(System.enableFeature(FEATURE_RETAINED_MEMORY));

/******************************
 * define ranges for each can */
 // unknow device "default"
 int min0 = 10;
 int max0 = 50;
 
 // tashi-iot-1
 int min1 = 10;
 int max1 = 60;
 
 // tashi-iot-2
 int min2 = 10;
 int max2 = 200;
 
 // tashi-iot-3
 int min3 = 5;
 int max3 = 85;

/*
 The HC-SR04 device is a 5V device. So, VIN (which is 5V when connected to a USB power supply)
 is used to power it. Also, the "Echo" pin will present a 5V pulse, which can be connected
 to any of the D* GPIO pins, as they are 5V tolerant. However, they cannot be connected to
 non-5V tolerant pins, like the A* pins, even if in digitial mode.

 This example expects the wiring to be as follows:
    Photon  HC-SR04
    GND     GND
    VIN     VCC
    A0      Trig
    D0      Echo
*/

// trigger / echo pins
const int triggerPin = A0;
const int echoPin = D0;
HC_SR04 rangefinder = HC_SR04(triggerPin, echoPin);
int data;

// used to store device name
char dev_name[32] = "";
bool publishName = false;

unsigned long firstAvailable = 0;
int counter;
retained int retainedCounter = 0;

void handler(const char *topic, const char *data) {
  strncpy(dev_name, data, sizeof(dev_name)-1);
  Serial.printlnf("received %s: %s", topic, dev_name);
  publishName = true;
}

void setup()
{
    // start serial monitor
    Serial.begin(9600);
    
    // setup the distance sensor
    rangefinder.init();
    
    // get device name from cloud
    Particle.subscribe("particle/device/name", handler);
    Particle.publish("particle/device/name");  // <-- ask the cloud for the name to be sent to you
}

void loop()
{
    
    bool wifiReady = WiFi.ready();
	bool cloudReady = Particle.connected();

	Serial.printlnf("wifi=%s cloud=%s counter=%d retainedCounter=%d", (wifiReady ? "on" : "off"), (cloudReady ? "on" : "off"),
			counter++, retainedCounter++);
			
    if (wifiReady) {
		if (firstAvailable == 0) {
			firstAvailable = millis();
		}
		if (millis() - firstAvailable > 20000) {
			// After we've been up for 20 seconds, go to sleep. The delay is so the serial output gets written out before
			// sleeping.
			Serial.println("calling System.sleep(SLEEP_MODE_DEEP, 600)");
			delay(2);
            
            // SLEEP_MODE_DEEP timinig is in SECONDS, NOT microseconds!!!
			System.sleep(SLEEP_MODE_DEEP, 600);

			// The rest of the code here is not reached. SLEEP_MODE_DEEP causes the code execution to stop,
			// and when wake up occurs, it's like a reset where you start again with setup(), all variables are
			// cleared, etc.
			Serial.println("returned from sleep, should not occur");
		}
		
		if (publishName) {
            // Publish an event every ? minutes. The event is JSON formatted and has a
    		// value "distance" that updates each time.
    		
    		// Process distance values based on specific trash can installations and which device is installed there
    		if (dev_name == "tashi-iot-1") {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min1) * 100) / (max1 - min1) );
    		}
    		else if (dev_name == "tashi-iot-2") {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min2) * 100) / (max2 - min2) );
    		}
    		else if (dev_name == "tashi-iot-3") {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min3) * 100) / (max3 - min3) );
    		}
    		else {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min0) * 100) / (max0 - min0) );
    		}
    		
    		char trash_data[64];
    		snprintf(trash_data, sizeof(trash_data), "{\"distance\":\"%d\",\"name\":\"%s\"}", data, dev_name);
    
    		Particle.publish("trash_data", trash_data, PRIVATE);
            delay(1000); // to ensure adhering to rate limit
            publishName = false;
        }
		

	}
	else {
		firstAvailable = 0;
	}

	delay(1000);
    
}

nodeJS server:

var config = require('./config.json'); 

var Particle = require('particle-api-js');
var particle = new Particle();

var mysql = require('mysql');

// Make sure you update the required MySQL server settings in config.json!
var con = mysql.createConnection({
        host:config.mysql_host,
        user:config.mysql_user,
        password:config.mysql_password,
        database:config.mysql_database});


con.connect(function(err) {
        if (err) throw err;
        
        console.log("Connected to database");

        // "sse-examples-01" in the Particle event to filter on. It's a prefix, so all events beginning with this
        // name are stored in the database.
        particle.getEventStream({ deviceId:config.deviceFilter, auth:config.authToken, name:'trash_data' }).then(
                        function(stream) {
                                stream.on('event', function(event) {
                                        console.log("event: ", event);
                                        
                                        // This assumes that the data in the event is valid JSON!
                                        var data = JSON.parse(event.data);

                                        
                                        // The "distance" value in the JSON is stored in the distance column in the SQL d$
                                        
                                        // create table example02 (id INT NOT NULL AUTO_INCREMENT, device VARCHAR(34), ts$
                                        var sql = "INSERT INTO trash_sensor (device, name, ts, distance) VALUES ('" + eve$
                                          con.query(sql, function (err, result) {
                                            if (err) throw err;
                                            console.log("inserted: " + sql);
                                          });
                                });
                        },
                        function(err) {
                                console.log("Failed to getEventStream: ", err);
                        });

});

The other bit of this is I’m using pm2 (http://pm2.keymetrics.io/) to daemon-ize the nodeJS script.

It’s all working great the first few? hours but then no data is inserted into the SQL DB. If I monitor the process with pm2 it shows it as up and running, and nothing sticks out in the logs. If I restart the process it works again as expected but then fails in a few hours again.

Is there anything that would time out, etc on the Particle side of things causing this problem?

I’m not sure how to track this down. Any ideas are most welcome!

Cheers

What do you mean with the process failing?

  • is it on the Photon or server side
  • does the device not wake again
  • does it not reconnect to WiFi
  • does it not reconnect to the cloud
  • does it not send data
  • does it send wrong data
  • does it send the correct data but your server can’t get it
  • does the server receive data but can’t insert it into your DB

However, there are a few things you may want to change even though they won’t necessarily tackle your issue.

  • you cannot compare char array strings like this if (dev_name == "tashi-ot-1"), you need to use strcmp() (or similar functions)
  • make sure only to publish when cloudReady - wifiReady is not enough

If the SSE connection is broken (between your server and the Particle cloud), under certain circumstances the connection will not be reopened, causing events to be lost. It’s a bug in the particle-api-js implementation and should be fixed in the next month or so. Since the SSE stream is not Particle-specific you could use a different SSE implementation until then, as well.

@ScruffR

  • I don’t think it’s on the Photon side, at least the firmware, that’s publishing fine to the cloud and shows up in the event log on the Particle site. So I think it might be related to either the Particle API or something else on my server side with the process manager pm2. It’s hard to tell.
  • The device is waking from deep sleep just fine
  • The WiFi is reconnecting just fine
  • It’s connecting to the cloud fine
  • It sends data fine
  • Correct data is sent
  • The data shows as a Particle Event but the Particle API running that nodeJS script as a daemon doesn’t insert the values to MySQL. the pm2 process manager shows the script as up and running, but when you monitor it the insert doesn’t happen, but all the logs look clean with no errors… That’s why I was thinking something is getting lost or timing out in the Particle API?
  • The server always inserts the data correctly, but only when that nodeJS script responds…

I’ll look into the strcmp() but for now the method I used is working okay. Will I run into problems if special characters are used or something like that?
I’ll also look into adding the cloudReady as well, though it always works correctly at present.

Thanks for your suggestions and ideas!

1 Like

@rickkas7 Ah, good to know! This “feels” like what might be going on.

Is there any way to programmatically test for this break or failure? It would be great to catch that in a log or something.

Do you have any suggestions for alternate SSE implementations?

Thanks!

I’m quite surprised that this works.
I’d have to test myself whether the currently used C++ compiler got really smart about strings, but AFAIK this kind of equality check would compare the two pointers whether they point to the same location or not. But since a string liteal (e.g. "tashi-ot-1") resides in flash memory and your dev_name sits in RAM it’s highly unlikely that they will ever be the same.
I’d expect that your current code will always fall through to the else branch of that conditional block.

To test my theory add some Serial.print() statements in each of the branches and see whether you ever get anything else than the message printed in the else branch.

@ScruffR Yes, you’re right… I bet it’s always going to the else so I’m assuming it’s just working! I’ll fix that up today and test to be certain.

Thanks again!!!

1 Like

@ScruffR Yes, the strcmp() fixed that up. I also added the cloudReady as well. My updated code is here for other travelers that might be interested:

//
//    Trash Sensor
//    Purpose: Using HC-SR04 sensor to collect distance data to determine if trash cans are full and need to be emptied
//
//    Author: David Cool
//    Version: 1.0

#include <HC-SR04.h>

STARTUP(System.enableFeature(FEATURE_RETAINED_MEMORY));

/******************************
 * define ranges for each can */
 // unknow device "default"
 int min0 = 10;
 int max0 = 50;
 
 // tashi-iot-1
 int min1 = 10;
 int max1 = 60;
 
 // tashi-iot-2
 int min2 = 10;
 int max2 = 200;
 
 // tashi-iot-3
 int min3 = 5;
 int max3 = 85;

/*
 The HC-SR04 device is a 5V device. So, VIN (which is 5V when connected to a USB power supply)
 is used to power it. Also, the "Echo" pin will present a 5V pulse, which can be connected
 to any of the D* GPIO pins, as they are 5V tolerant. However, they cannot be connected to
 non-5V tolerant pins, like the A* pins, even if in digitial mode.

 This example expects the wiring to be as follows:
    Photon  HC-SR04
    GND     GND
    VIN     VCC
    A0      Trig
    D0      Echo
*/

// trigger / echo pins
const int triggerPin = A0;
const int echoPin = D0;
HC_SR04 rangefinder = HC_SR04(triggerPin, echoPin);
int data;

// used to store device name
char dev_name[32] = "";
bool publishName = false;

unsigned long firstAvailable = 0;
int counter;
retained int retainedCounter = 0;

void handler(const char *topic, const char *data) {
  strncpy(dev_name, data, sizeof(dev_name)-1);
  Serial.printlnf("received %s: %s", topic, dev_name);
  publishName = true;
}

void setup()
{
    // start serial monitor
    Serial.begin(9600);
    
    // setup the distance sensor
    rangefinder.init();
    
    // get device name from cloud
    Particle.subscribe("particle/device/name", handler);
    Particle.publish("particle/device/name");  // <-- ask the cloud for the name to be sent to you
}

void loop()
{
    
    bool wifiReady = WiFi.ready();
	bool cloudReady = Particle.connected();

	Serial.printlnf("wifi=%s cloud=%s counter=%d retainedCounter=%d", (wifiReady ? "on" : "off"), (cloudReady ? "on" : "off"),
			counter++, retainedCounter++);
			
    if (wifiReady && cloudReady) {
		if (firstAvailable == 0) {
			firstAvailable = millis();
		}
		if (millis() - firstAvailable > 20000) {
			// After we've been up for 20 seconds, go to sleep. The delay is so the serial output gets written out before
			// sleeping.
			Serial.println("calling System.sleep(SLEEP_MODE_DEEP, 600)");
			delay(2);
            
            // SLEEP_MODE_DEEP timinig is in SECONDS, NOT microseconds!!!
			System.sleep(SLEEP_MODE_DEEP, 600);

			// The rest of the code here is not reached. SLEEP_MODE_DEEP causes the code execution to stop,
			// and when wake up occurs, it's like a reset where you start again with setup(), all variables are
			// cleared, etc.
			Serial.println("returned from sleep, should not occur");
		}
		
		if (publishName) {
            // Publish an event every ? minutes. The event is JSON formatted and has a
    		// value "distance" that updates each time.
    		
    		// Process distance values based on specific trash can installations and which device is installed there
    		if (strcmp(dev_name, "tashi-iot-1") == 0) {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min1) * 100) / (max1 - min1) );
    		}
    		else if (strcmp(dev_name, "tashi-iot-2") == 0) {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min2) * 100) / (max2 - min2) );
    		}
    		else if (strcmp(dev_name, "tashi-iot-3") == 0) {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min3) * 100) / (max3 - min3) );
    		}
    		else {
    		  // get current distance sensor reading in inches
    		  data = rangefinder.distInch();
              data = 100 - ( ((data - min0) * 100) / (max0 - min0) );
    		}
    		
    		char trash_data[64];
    		snprintf(trash_data, sizeof(trash_data), "{\"distance\":\"%d\",\"name\":\"%s\"}", data, dev_name);
    
    		Particle.publish("trash_data", trash_data, PRIVATE);
            delay(1000); // to ensure adhering to rate limit
            publishName = false;
        }
		

	}
	else {
		firstAvailable = 0;
	}

	delay(1000);
    
}

Also, I’ve been running the Particle API nodeJS script since last night and it hasn’t stopped as of this morning. So it’s definitely not a specific time frequency as it’s been up for 8-10 hours now.

Thanks again for your eyes and insights!