• Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular

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

    Troubleshooting
    2
    12
    217
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • D
      Daniele last edited by

      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 1 Reply Last reply Reply Quote 0
      • jkandasa
        jkandasa @Daniele last edited by

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

        1 Reply Last reply Reply Quote 0
        • D
          Daniele last edited by

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

          1 Reply Last reply Reply Quote 0
          • D
            Daniele last edited by

            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.

            1 Reply Last reply Reply Quote 0
            • D
              Daniele last edited by

              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?

              jkandasa 1 Reply Last reply Reply Quote 0
              • jkandasa
                jkandasa @Daniele last edited by

                @Daniele Can you post your sketch?

                1 Reply Last reply Reply Quote 0
                • D
                  Daniele last edited by

                  /*
                   * 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 1 Reply Last reply Reply Quote 0
                  • jkandasa
                    jkandasa @Daniele last edited by

                    @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
                    1 Reply Last reply Reply Quote 0
                    • D
                      Daniele last edited by

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

                      1 Reply Last reply Reply Quote 1
                      • D
                        Daniele last edited by

                        @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 1 Reply Last reply Reply Quote 0
                        • jkandasa
                          jkandasa @Daniele last edited by

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

                          1 Reply Last reply Reply Quote 0
                          • D
                            Daniele last edited by

                            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.

                            1 Reply Last reply Reply Quote 0
                            • First post
                              Last post

                            0
                            Online

                            647
                            Users

                            506
                            Topics

                            3.3k
                            Posts

                            Copyright © 2015-2022 MyController.org | Contributors | Localization