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



  • 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
    


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



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



  • 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.



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

    aae78055-695c-4606-b1f2-ca855e29ff39-image.png

    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?



  • @Daniele Can you post your sketch?



  • /*
     * 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();
    
    }
    


  • @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


  • @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!


Log in to reply
 

Suggested Topics

10
Online

562
Users

456
Topics

2.8k
Posts