Help request, trying to batch write with writeprecision::MS

[aardbei]

I use influxdbV1 with batch writing, Writepresision::MS (or NS/US for that matter) influxdb client library V3.2 ESP8266 board 2.7.1

And a HX711 with the latest library on pins const int LOADCELL_DOUT_PIN = 13; const int LOADCELL_SCK_PIN = 12;

My output in Grafana looks like this, data gets swapped

2020-06-14 18:52:50 0 2020-06-14 18:52:50 0 2020-06-14 18:52:50 0 2020-06-14 18:52:49 500 2020-06-14 18:52:49 499 2020-06-14 18:52:49 500 2020-06-14 18:52:49 500 2020-06-14 18:52:49 0 2020-06-14 18:52:49 12 2020-06-14 18:52:49 499 2020-06-14 18:52:49 500 2020-06-14 18:52:49 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 499 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:48 500 2020-06-14 18:52:47 245 2020-06-14 18:52:47 162 2020-06-14 18:52:47 0 2020-06-14 18:52:47 0 2020-06-14 18:52:47 500 2020-06-14 18:52:47 476 2020-06-14 18:52:47 464 2020-06-14 18:52:47 449 2020-06-14 18:52:47 379 2020-06-14 18:52:46 0 2020-06-14 18:52:46 0

The output from println is okay so I'm trying to write in the right order

18:52:46.867 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153566 18:52:46.970 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153567 18:52:47.176 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153567 18:52:47.278 -> Data written to InfluxDB :-) Massa: 162.00 Tijd: 1592153567 18:52:47.381 -> Data written to InfluxDB :-) Massa: 245.00 Tijd: 1592153567 18:52:47.483 -> Data written to InfluxDB :-) Massa: 379.00 Tijd: 1592153567 18:52:47.586 -> Data written to InfluxDB :-) Massa: 449.00 Tijd: 1592153567 18:52:47.688 -> Data written to InfluxDB :-) Massa: 464.00 Tijd: 1592153567 18:52:47.791 -> Data written to InfluxDB :-) Massa: 476.00 Tijd: 1592153567 18:52:47.895 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153567 18:52:47.998 -> Data written to InfluxDB :-) Massa: 499.00 Tijd: 1592153568 18:52:48.100 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.204 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.306 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.408 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.512 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.615 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.718 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.821 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.926 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153568 18:52:48.994 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153569 18:52:49.096 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153569 18:52:49.269 -> Data written to InfluxDB :-) Massa: 499.00 Tijd: 1592153569 18:52:49.373 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153569 18:52:49.476 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153569 18:52:49.578 -> Data written to InfluxDB :-) Massa: 500.00 Tijd: 1592153569 18:52:49.680 -> Data written to InfluxDB :-) Massa: 499.00 Tijd: 1592153569 18:52:49.784 -> Data written to InfluxDB :-) Massa: 12.00 Tijd: 1592153569 18:52:49.886 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153569 18:52:49.987 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153570 18:52:50.091 -> Data written to InfluxDB :-) Massa: 0.00 Tijd: 1592153570

If I try to manually set the time to resolve this (dPoint.setTime( tnow )) or dPoint.setTime( tijd ) the following happens:

InfluxDB write failed: {"error":"partial write: points beyond retention policy dropped=20"}

It even goes wrong when I set

define MAX_BATCH_SIZE 1

define WRITE_BUFFER_SIZE 1

effectively disabling batch-write :-S

What am I doing wrong?

My code is straightforward:

`/**

• Secure Write Example code for InfluxDBClient library for Arduino
• Enter WiFi and InfluxDB parameters below
• Demonstrates connection to any InfluxDB instance accesible via:
• • unsecured http://...
• • secure https://... (appropriate certificate is required)
• • InfluxDB 2 Cloud at https://cloud2.influxdata.com/ (certificate is preconfigured)
• Measures signal level of all visible WiFi networks including signal level of the actually connected one
• This example demonstrates time handling, how to write measures with different priorities, batching and retry
• Data can be immediately seen in a InfluxDB 2 Cloud UI - measurements wifi_status and wifi_networks **/

if defined(ESP32)

include

WiFiMulti wifiMulti;

define DEVICE "ESP32"

elif defined(ESP8266)

include

ESP8266WiFiMulti wifiMulti;

define DEVICE "ESP8266"

define WIFI_AUTH_OPEN ENC_TYPE_NONE

endif

include

include

// Setup the loadcell stuff

include // HX711 library for the scale

define calibration_factor 923.53 //-7050.0 //This value is obtained using the SparkFun_HX711_Calibration sketch tbv china loadcell

// #define calibration_factor 701.77 //-7050.0 //This value is obtained using the SparkFun_HX711_Calibration sketch tbv dual 3kg tedea

define calibration_factor2 3694.12 //-7050.0 //This value is obtained using the SparkFun_HX711_Calibration sketch tbv china loadcell

const int LOADCELL_DOUT_PIN = 13; const int LOADCELL_SCK_PIN = 12;

HX711 scale;

// weight values float weight = 0.0; // measured weight int weightout = 0; // integer weight out float dyntar = 0.0; // dynamic tar const int freq = 80; // HX711 supports 10 and 80 Hertz, default = 10, hardware mod necessary uint64_t tijd = 0; // test with usec unixtime, didn't work int t = 0; // save measurement interval millis incrementals

define THRESHOLD 2.1 // Measures only if the weight is greater than 2 gram.

// WiFi AP SSID

define WIFI_SSID ****

// WiFi password

define WIFI_PASSWORD ****

// Influxdb1 @home

define INFLUXDB_URL ****

define INFLUXDB_DB_NAME ****

define INFLUXDB_USER ****

define INFLUXDB_PASSWORD ****

// #define INFLUXDB_BUCKET "Weegschaal"

// Influxdb2 connect // InfluxDB client instance with preconfigured InfluxCloud certificate // InfluxDBClient client(INFLUXDB_URL, INFLUXDB_ORG, INFLUXDB_BUCKET, INFLUXDB_TOKEN, InfluxDbCloud2CACert);

// Influxdb1 connect // InfluxDB client instance for InfluxDB 1 InfluxDBClient client(INFLUXDB_URL, INFLUXDB_DB_NAME); // Zie ook in de setup loop!

// Set timezone string according to https://www.gnu.org/software/libc/manual/html_node/TZ-Variable.html // Examples: // Pacific Time: "PST8PDT" // Eastern: "EST5EDT" // Japanesse: "JST-9" // Central Europe: "CET-1CEST,M3.5.0,M10.5.0/3" //#define TZ_INFO "CET-1CEST,M3.5.0,M10.5.0/3"

define TZ_INFO "CET-1CEST-2,M3.5.0/2,M10.5.0/3"

define WRITE_PRECISION WritePrecision::MS

define MAX_BATCH_SIZE 10

define WRITE_BUFFER_SIZE 30

define MAX_TIME 60

define HTTP_KEEP true

// Data point Point sensorStatus("wifi_status");

// Number for loops to sync time using NTP int iterations = 0;

void timeSync() { // Synchronize UTC time with NTP servers // Accurate time is necessary for certificate validaton and writing in batches configTime(0, 0, "0.nl.pool.ntp.org", "3.nl.pool.ntp.org", "nl.pool.ntp.org" ); // Set timezone setenv("TZ", TZ_INFO, 1);

// Wait till time is synced Serial.print("Syncing time"); int i = 0; while (time(nullptr) < 1000000000ul && i < 1000) { Serial.print("."); delay(100); i++; } Serial.println();

// Show time time_t tnow = time(nullptr); Serial.print("Synchronized time: "); Serial.println(String(ctime(&tnow))); }

void setup() { Serial.begin(115200);

// Setup the scale scale.begin(LOADCELL_DOUT_PIN, LOADCELL_SCK_PIN); scale.set_scale(calibration_factor); //This value is obtained by using the SparkFun_HX711_Calibration sketch delay(1000); scale.tare(); //Assuming there is no weight on the scale at start up, reset the scale to 0

// Setup wifi WiFi.mode(WIFI_STA); wifiMulti.addAP(WIFI_SSID, WIFI_PASSWORD);

Serial.print("Connecting to wifi"); while (wifiMulti.run() != WL_CONNECTED) { Serial.print("."); delay(100); } Serial.println();

// Add tags // sensorStatus.addTag("device", DEVICE); // sensorStatus.addTag("SSID", WiFi.SSID());

// Sync time for certificate validation timeSync();

// Check server connection if (client.validateConnection()) { Serial.print("Connected to InfluxDB: "); Serial.println(client.getServerUrl()); } else { Serial.print("InfluxDB connection failed: "); Serial.println(client.getLastErrorMessage()); }

// Set InfluxDB 1 authentication params client.setConnectionParamsV1(INFLUXDB_URL, INFLUXDB_DB_NAME, INFLUXDB_USER, INFLUXDB_PASSWORD);

//Enable messages batching and retry buffer client.setWriteOptions(WRITE_PRECISION, MAX_BATCH_SIZE, WRITE_BUFFER_SIZE, MAX_TIME, HTTP_KEEP); }

void loop() { // Sync time for batching once per hour if ( (millis()/1000/3600) > iterations ) { timeSync(); iterations++; }

if (millis() > t + (1000/freq*2)) {
weight = (scale.get_units()); // dynamic tar if below threshold if ( (weight + dyntar) < THRESHOLD ) { weight = -dyntar; } weightout = int (weight + dyntar); writeData(weightout); t = millis(); } delay(100); }

void writeData(float weight) {

time_t tnow = time(nullptr);

tijd = xx_time_get_time();

Point dPoint("Massa");  
dPoint.addField("gram", weight);  

// dPoint.setTime( tijd ); //set the time // dPoint.setTime( tnow ); //set the time Serial.print("Massa: ");
Serial.print(weight);
Serial.print(" Tijd: "); Serial.println(tnow); // Serial.print(" Tijd2: "); // print((long)tijd); if(!client.writePoint(dPoint)) { Serial.print("InfluxDB write failed: "); Serial.println(client.getLastErrorMessage()); } else { Serial.print("Data written to InfluxDB :-) "); } }

/**

include

void print(uint64_t value) { const int NUM_DIGITS = log10(value) + 1; char sz[NUM_DIGITS + 1]; sz[NUM_DIGITS] = 0; for ( size_t i = NUM_DIGITS; i--; value /= 10) { sz[i] = '0' + (value % 10); } Serial.println(sz); } **/

int64_t xx_time_get_time() { struct timeval tv; gettimeofday(&tv, NULL); return (tv.tv_sec * 1000LL + (tv.tv_usec / 1000LL)); }`

[vlastahajek]

@aardbei, the problem lies in the time you are setting. time_t contains only seconds. If you are using this for the setting timestamp, you have to set WritePrecision::S, otherwise InfluxDB will consider this is the time in the past.

Although value from xx_time_get_time looks correct, from my experience, Arduino cannot handle correctly more than 32bit values. Try to print them, you have to see 14 digits to have correct milliseconds representation.

If you would like to get rid of timestamp headache, let this library to handle timestamp for you:

1. Use point.setTime(WRITE_PRECISION), which will use the current time to set timestamp in desired precision.
2. You don't need to set the time at all. When a write precision is set, library will set timestamp at the time of writing.

[aardbei]

@vlastahajek, hi, yes I suspected it's a time issue Added your suggestion, unfortunately the result is the same

Old: Point dPoint("Massa");
dPoint.addField("gram", weight);

New: Point dPoint("Massa");
dPoint.setTime(WRITE_PRECISION); dPoint.addField("gram", weight);

Any other suggestions?

[vlastahajek]

@aardbei , enable debug and attach the serial output from couple of loops, please.

[aardbei]

@vlastahajek hi, attached a debug of a V3.13 attempt with the problem and an error the compiler threw me while compiling with debugging enabled on V3.20 Hope this helps?

debugcompilerror3.20.txt debugwithlib3.13.txt

[vlastahajek]

@aardbei, great idea with reverting to 3.13 (I'm looking into the compile problem, I can reproduce it). Your output shows all is correctly written. Can you check also output from InfluxDB, not Grafana, to see results?

1. use the influx cmd tool for connecting to the server
2. type use <your-database-name>
3. type select * from Massa order by time desc limit 20, or some other number of recent records

[aardbei]

@vlastahajek hi,

It's already faulty in the database

I executed:

SELECT "gram" FROM "Massa" WHERE time >= 1592419804780ms and time <= 1592419808354ms

See attached file

I also looked at one line in the debug specifically, and to me it looks like the time parameter is not incrementing in the way I would suspect?

For instance this group: 20:40:41.572 -> Massa gram=0.00 1592419230555 20:40:41.778 -> Massa gram=0.00 1592419230763 20:40:41.984 -> Massa gram=0.00 1592419230972 which is followed by 20:40:42.188 -> Massa gram=340.00 1592419230170

Isn't 1592419230170 earlier in time than 1592419230555?

database_select.txt debug_analyze.txt

[vlastahajek]

@aardbei, thanks for the analysis. You've discovered a bug. The Point::setTime() wrongly correlate time_t and the millis(). I will prepare a fix, in the meantime use the following as a workaround, please (a slightly modified your version of creating timestamp):

...
point.setTime(getTimestamp());
....

String getTimestamp() {
  struct timeval tv;
  gettimeofday(&tv, NULL);
  uint64_t tsVal = (tv.tv_sec * 1000LL + (tv.tv_usec / 1000LL));
  char buff[20];
  sprintf(buff, "%llu", tsVal);
  return String(buff);
}

[vlastahajek]

You can also skip setting a timestamp on the client. When no precision is set (default), server will set the current time as a point timestamp. This is, of course, usable only if you immediately write the value, without batching.

[aardbei]

@vlastahajek, thank you for the workaround, works flawless! I want to use batching because I have a lot of measurements in a short period of time and I don't want to strain my server too much

[kmf909]

hi i also have issue to verify batch code. the IDE error as below. is the work around apply to this?

/tmp/arduino_modified_sketch_744549/SecureBatchWrite.ino: In function 'void loop()': SecureBatchWrite:99:14: error: too few arguments to function 'void timeSync(const char*, const char*, const char*, const char*)'timeSync(); ^ In file included from /home/nirsai/Arduino/libraries/InfluxDB-Client-for-Arduino-master/src/InfluxDbClient.h:34:0, from /tmp/arduino_modified_sketch_744549/SecureBatchWrite.ino:25: /home/nirsai/Arduino/libraries/InfluxDB-Client-for-Arduino-master/src/util/helpers.h:35:6: note: declared here void timeSync(const char *tzInfo, const char* ntpServer1, const char* ntpServer2 = nullptr, const char* ntpServer3 = nullptr); ^ exit status 1 too few arguments to function 'void timeSync(const char*, const char*, const char*, const char*)'

[vlastahajek]

@kmf909, please check the issue #73, where I wrote a workaround.