Unique index or primary key violation: "CONSTRAINT_4B_INDEX_4 ON PUBLIC.METRICS_DOUBLE_TYPE_DEVICE

Daniele

I'm getting this error message in the log, it's repeating every few minutes.
Could someone help my fixing it?

2020-03-23 10:10:57,081 ERROR [mc-th-pool-2] [org.mycontroller.standalone.provider.ExecuteMessageDependentTask:67] Exception, [timestamp:1584954609955, sensorVariableId:308, payload:21.2]
org.mycontroller.standalone.exceptions.McDatabaseException: java.sql.SQLException: Unable to run insert stmt on object MetricsDoubleTypeDevice(sensorVariable=SensorVariable(id=308, sensor=Sensor(id=291, sensorId=2, type=S_TEMP, name=P0 Temperatura Studio, lastSeen=1584954609993, node=Node(id=115, eui=3, gatewayTable=GatewayTable(id=36, enabled=true, name=P0_Ethernet_GW2, networkType=MY_SENSORS, timestamp=null, state=UP, statusMessage=Connected Successfully, statusSince=1584954466699, type=ETHERNET, properties={p=5003, streamAckEnabled=false, af=1800, ackEnabled=true, h=192.168.1.22, txDelay=200, ackWaitTime=1000, failedRetryCount=1, reconnectDelay=120}), name=P0 - PIR Studio, version=1.1, type=S_ARDUINO_NODE, libVersion=2.3.1, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1584954610013, rssi=null, properties={hbTx=1584954462121}, parentNodeEui=0, registrationState=REGISTERED, smartSleepEnabled=false), room=Room(id=2, name=Studio, description=null, parentId=null, icon=null, fullPath=Studio), variableTypes=[Temperature]), variableType=V_TEMP, metricType=DOUBLE, timestamp=1584954609955, value=21.2, previousValue=21.2, unitType=U_TEMPERATURE, readOnly=true, offset=0.0, priority=100, properties={marginRight=20, color=#ff7f0e, interpolate=linear, subType=line, marginBottom=60, useGlobal=true, type=lineChart, marginTop=5, marginLeft=65}), timestamp=1584954609955, samples=1, min=21.2, max=21.2, avg=21.2, aggregationType=RAW, start=null, end=null): INSERT INTO `metrics_double_type_device` (`sensorVariableId` ,`timestamp` ,`samples` ,`min` ,`max` ,`avg` ,`aggregationType` ) VALUES (?,?,?,?,?,?,?)
	at org.mycontroller.standalone.db.dao.BaseAbstractDaoImpl.create(BaseAbstractDaoImpl.java:250)
	at org.mycontroller.standalone.metrics.engine.MetricEngineMyController.post(MetricEngineMyController.java:87)
	at org.mycontroller.standalone.provider.ExecuteMessageDependentTask.executeDependentTask(ExecuteMessageDependentTask.java:60)
	at org.mycontroller.standalone.provider.ExecuteMessageDependentTask.run(ExecuteMessageDependentTask.java:84)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Unable to run insert stmt on object MetricsDoubleTypeDevice(sensorVariable=SensorVariable(id=308, sensor=Sensor(id=291, sensorId=2, type=S_TEMP, name=P0 Temperatura Studio, lastSeen=1584954609993, node=Node(id=115, eui=3, gatewayTable=GatewayTable(id=36, enabled=true, name=P0_Ethernet_GW2, networkType=MY_SENSORS, timestamp=null, state=UP, statusMessage=Connected Successfully, statusSince=1584954466699, type=ETHERNET, properties={p=5003, streamAckEnabled=false, af=1800, ackEnabled=true, h=192.168.1.22, txDelay=200, ackWaitTime=1000, failedRetryCount=1, reconnectDelay=120}), name=P0 - PIR Studio, version=1.1, type=S_ARDUINO_NODE, libVersion=2.3.1, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1584954610013, rssi=null, properties={hbTx=1584954462121}, parentNodeEui=0, registrationState=REGISTERED, smartSleepEnabled=false), room=Room(id=2, name=Studio, description=null, parentId=null, icon=null, fullPath=Studio), variableTypes=[Temperature]), variableType=V_TEMP, metricType=DOUBLE, timestamp=1584954609955, value=21.2, previousValue=21.2, unitType=U_TEMPERATURE, readOnly=true, offset=0.0, priority=100, properties={marginRight=20, color=#ff7f0e, interpolate=linear, subType=line, marginBottom=60, useGlobal=true, type=lineChart, marginTop=5, marginLeft=65}), timestamp=1584954609955, samples=1, min=21.2, max=21.2, avg=21.2, aggregationType=RAW, start=null, end=null): INSERT INTO `metrics_double_type_device` (`sensorVariableId` ,`timestamp` ,`samples` ,`min` ,`max` ,`avg` ,`aggregationType` ) VALUES (?,?,?,?,?,?,?)
	at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:25)
	at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:137)
	at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:458)
	at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:328)
	at org.mycontroller.standalone.db.dao.BaseAbstractDaoImpl.create(BaseAbstractDaoImpl.java:246)
	... 6 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_4B_INDEX_4 ON PUBLIC.METRICS_DOUBLE_TYPE_DEVICE(SENSORVARIABLEID, TIMESTAMP, AGGREGATIONTYPE) VALUES (308, 1584954609955, 0, 1471576)"; SQL statement:
INSERT INTO `metrics_double_type_device` (`sensorVariableId` ,`timestamp` ,`samples` ,`min` ,`max` ,`avg` ,`aggregationType` ) VALUES (?,?,?,?,?,?,?) [23505-194]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:103)
	at org.h2.mvstore.db.MVSecondaryIndex.checkUnique(MVSecondaryIndex.java:231)
	at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:190)
	at org.h2.mvstore.db.MVTable.addRow(MVTable.java:707)
	at org.h2.command.dml.Insert.insertRows(Insert.java:156)
	at org.h2.command.dml.Insert.update(Insert.java:114)
	at org.h2.command.CommandContainer.update(CommandContainer.java:101)
	at org.h2.command.Command.executeUpdate(Command.java:258)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160)
	at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146)
	at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:183)
	at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91)
	... 9 common frames omitted

jkandasa

@Daniele How often are you sending the temperature value from your node? Looks like receive multiple messages in a single milliseconds?

Daniele

As a max once per second, but usually every some minute

Daniele

By the way, looking at the previous errors I can see many different sensor ids, so it seems like something general, not related to a specific sensor.

Daniele

This morning I was checking the resource logs , and I noticed some odd behaviour I cannot explain:

The same temperature is transmitted twice at the same timestamp, but my sketch reads the temperature once per second and sends it only when the absolute difference from the previous value is greater than 0.1°C.
This setup is in place from more than 1 year, and I never saw this beahaviour...
Any idea?

jkandasa

@Daniele Can you post your sketch?

Daniele

/*
 * MySBootloader 1.3.0 16 MHz
 * 
 * 9-13 NRF24 (standard mysensors)
 * 1 PIR
 * 2 temperatura (DS18B20)
 */

#define MY_DEBUG
#define MY_DEBUG_DETAIL

#define MY_NODE_ID 3

#define CHILD_ID_PIR 1
#define CHILD_ID_TEMP 2

#define pirPin 8
#define TempPin 2

#include <MyRF24_P0_GW2.h>
#include <MySensors.h>

#include <MyCircularBuffer.h>
#include <OneWire.h>
#include <DallasTemperature.h>

#define wdt_check_min 10;
#define wdt_check_max 150;
#include <MyWDT.h>

OneWire oneWire(TempPin);
DallasTemperature sensors(&oneWire);
DeviceAddress ds18Addr;

MyMessage msgPIR(CHILD_ID_PIR, V_TRIPPED);
MyMessage msgTEMP(CHILD_ID_TEMP, V_TEMP);

int nNoUpdatesPIR = 0, nNoUpdatesTEMP = 0;
unsigned const int FORCE_UPDATE_N_READS = 300;




unsigned int calibrationTime = 10;
unsigned int p;
unsigned int p_tot;

float temp, last_temp = 0, tempTot = 0;

unsigned int msg;
unsigned int last_msg = 9; //per forzare primo invio

unsigned const int loop_cycles = 60;
unsigned const int loop_wait = 1; //secondi tra ogni rilevazione del PIR

/*
unsigned int wdt_failures = 0;
unsigned int wdt_check_interval = 30;
unsigned int wdt_loops = 0;
unsigned int wdt_uplink = 0;
*/

MyCircularBuffer<byte,loop_cycles> pirBuffer;
MyCircularBuffer<float,10> TempBuffer;
//CircularBuffer<byte,3> wdtBuffer;


void setup() {

  wdt_disable();
  
  pinMode(pirPin, INPUT);
  digitalWrite(pirPin, LOW);

  //Fase di calibrazione
  Serial.print("Calibrating sensor ");
  for(int i = 0; i < calibrationTime; i++){
    Serial.print(".");
    p = digitalRead(pirPin);
    pirBuffer.push(p);
    delay(1000);
  }
  Serial.println(" done");
  Serial.println("SENSOR ACTIVE");
  delay(50);

  sensors.begin();
  if (!sensors.getAddress(ds18Addr, 0)) 
    Serial.println("Unable to find address for Device 0"); 

  sensors.setResolution(ds18Addr, 11);

  wdt_enable(WDTO_4S);

}



void presentation()
{
  // Send the sketch version information to the gateway and Controller
  sendSketchInfo("P0 - PIR Studio", "1.1");

  present(CHILD_ID_PIR, S_MOTION, "P0 PIR Studio");
  present(CHILD_ID_TEMP, S_TEMP, "P0 Temperatura Studio");
}




void loop() {

  p = digitalRead(pirPin);
  pirBuffer.push(p);

  p_tot = 0;
  for(int i=0; i<pirBuffer.size(); i++){
    p_tot += pirBuffer[i];  
  }

  if(p_tot > loop_cycles/4){
    msg = 1;
  }
  else {
    msg = 0;
  }


  if (msg != last_msg || nNoUpdatesPIR == FORCE_UPDATE_N_READS) {
    last_msg = msg;
    nNoUpdatesPIR = 0;
    send(msgPIR.set(msg), true);

    #ifdef MY_DEBUG
    Serial.print("P: ");
    Serial.println(msg);
    #endif
  } else {
    nNoUpdatesPIR++;
  }


  //check external temp
  sensors.requestTemperatures();
  wait(1000);
  //temp = sensors.getTempCByIndex(0);
  temp = sensors.getTempC(ds18Addr);
  if(temp != DEVICE_DISCONNECTED_C)
    TempBuffer.push(temp);



  tempTot = 0;
  for(int i=0; i<TempBuffer.size(); i++){
    tempTot += TempBuffer[i];  
  }
  tempTot = tempTot / TempBuffer.size();

  if (abs(tempTot - last_temp) > 0.1 || nNoUpdatesTEMP == FORCE_UPDATE_N_READS) {
    last_temp = tempTot;
    nNoUpdatesTEMP = 0;
    send(msgTEMP.set(tempTot,1), true);

    #ifdef MY_DEBUG
    Serial.print("T avg: ");
    Serial.println(tempTot);
    #endif
  } else {
    nNoUpdatesTEMP++;
  }


  #ifdef MY_DEBUG_DETAIL
  Serial.print("T: ");
  Serial.print(temp);
  Serial.print(" - P:");
  Serial.println(p);
  #endif

  wait(loop_wait * 1000);


  //wdt uplink check
  wdt_check();

}

jkandasa

@Daniele Hi, Sorry for the delayed response.
Your sketch looks ok. I do not see any issue to repeat a message

Possible causes:

Your gateway response might not reach easily to your temperature node, hence your temperature node keeps repeating the same message and gateway sends to MyController.
Restarting MyController operating system might fix this

Daniele

@jkandasa it's probably related to some gateway issue, probably connected to the issue I asked here

I made a hard restart of the gateway and now the error disappeared.
Thank you!

Daniele

@jkandasa sorry to re-open an hold discussion.
the same error appeared again, and this time the gateway hard reset did not solve it.
do you have any idea on how to better isolate the issue?
thanks
Daniele

jkandasa

@Daniele This error happens when we receive multiple messages in the same timeframe. The first one will be inserted into the database and other messages will be dropped with this error.
If you do not see impact on your data, just ignore [or] find a duplication situation and avoid it.

Daniele

If others will arrive here searching for a similar issue: in my case the final solution has been changing the Arduino Uno I was using as ethernet gateway.

Unique index or primary key violation: "CONSTRAINT_4B_INDEX_4 ON PUBLIC.METRICS_DOUBLE_TYPE_DEVICE

1

549

524

3.4k