search

mycontroller-org / mycontroller-v1-legacy

The Open Source Controller
http://www.MyController.org
Apache License 2.0
147 stars 90 forks source link

ERROR log: Unique index or primary key violation #255

Closed gbrd closed 7 years ago

gbrd commented 8 years ago

I have this error in logs : 

2016-08-24 17:32:15,783 ERROR [Thread-930275] [org.mycontroller.standalone.db.dao.BaseAbstractDaoImpl:187] unable to add new item:[MetricsBinaryTypeDevice(sensorVariable=SensorVariable(id=7, sensor=Sensor(id=8, sensorId=4, type=S_BINARY, name=Status salon, lastSeen=1472052735701, node=Node(id=4, eui=2, gatewayTable=GatewayTable(id=1, enabled=true, name=g1, networkType=MY_SENSORS, timestamp=null, state=UP, statusMessage=Connected Successfully, statusSince=1471813237223, type=SERIAL, properties={br=115200, rdr=pi4j, rf=120, dr=Auto, pn=/dev/ttyUSB0}), name=Thermostat, version=1.1, type=S_ARDUINO_REPEATER_NODE, libVersion=2.0.0, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1472052735707, rssi=null, properties={}, parentNodeEui=null, registrationState=REGISTERED), room=null, variableTypes=[Status]), variableType=V_STATUS, metricType=BINARY, timestamp=1472052735738, value=0, previousValue=0, unitType=U_NONE, readOnly=false, offset=0.0, priority=100, graphProperties={color=#ff7f0e, interpolate=linear, subType=line, useGlobal=true, type=lineChart}), timestamp=1472052735738, state=false, timestampFrom=null, timestampTo=null)]
java.sql.SQLException: Unable to run insert stmt on object MetricsBinaryTypeDevice(sensorVariable=SensorVariable(id=7, sensor=Sensor(id=8, sensorId=4, type=S_BINARY, name=Status salon, lastSeen=1472052735701, node=Node(id=4, eui=2, gatewayTable=GatewayTable(id=1, enabled=true, name=g1, networkType=MY_SENSORS, timestamp=null, state=UP, statusMessage=Connected Successfully, statusSince=1471813237223, type=SERIAL, properties={br=115200, rdr=pi4j, rf=120, dr=Auto, pn=/dev/ttyUSB0}), name=Thermostat, version=1.1, type=S_ARDUINO_REPEATER_NODE, libVersion=2.0.0, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1472052735707, rssi=null, properties={}, parentNodeEui=null, registrationState=REGISTERED), room=null, variableTypes=[Status]), variableType=V_STATUS, metricType=BINARY, timestamp=1472052735738, value=0, previousValue=0, unitType=U_NONE, readOnly=false, offset=0.0, priority=100, graphProperties={color=#ff7f0e, interpolate=linear, subType=line, useGlobal=true, type=lineChart}), timestamp=1472052735738, state=false, timestampFrom=null, timestampTo=null): INSERT INTO `metrics_binary_type_device` (`sensorVariableId` ,`timestamp` ,`state` ) VALUES (?,?,?)
    at com.j256.ormlite.misc.SqlExceptionUtil.create(SqlExceptionUtil.java:22) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:135) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at com.j256.ormlite.stmt.StatementExecutor.create(StatementExecutor.java:450) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at com.j256.ormlite.dao.BaseDaoImpl.create(BaseDaoImpl.java:310) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.mycontroller.standalone.db.dao.BaseAbstractDaoImpl.create(BaseAbstractDaoImpl.java:184) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.mycontroller.standalone.message.McMessageEngine.recordSetTypeData(McMessageEngine.java:848) [mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.mycontroller.standalone.message.McMessageEngine.execute(McMessageEngine.java:116) [mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.mycontroller.standalone.message.McMessageEngine.run(McMessageEngine.java:962) [mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_65]
Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_INDEX_47 ON PUBLIC.METRICS_BINARY_TYPE_DEVICE(SENSORVARIABLEID, TIMESTAMP) VALUES ( /* key:26871 */ 7, 1472052735738, null)"; SQL statement:
INSERT INTO `metrics_binary_type_device` (`sensorVariableId` ,`timestamp` ,`state` ) VALUES (?,?,?) [23505-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.message.DbException.get(DbException.java:178) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.message.DbException.get(DbException.java:154) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:103) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtree.find(PageBtree.java:121) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:151) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:104) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:206) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:206) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:97) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:88) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.table.RegularTable.addRow(RegularTable.java:120) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.command.dml.Insert.insertRows(Insert.java:157) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.command.dml.Insert.update(Insert.java:115) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.command.CommandContainer.update(CommandContainer.java:79) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.command.Command.executeUpdate(Command.java:254) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:158) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:144) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:170) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91) ~[mycontroller-dist-standalone-0.0.3.Alpha2-single.jar:0.0.3.Alpha2]
    ... 7 common frames omitted
cimba007 commented 8 years ago

Hello gbrd, can u provide a full log file or copy the content of "~/mycontroller/logs/mycontroller.log" to a site like http://pastebin.com/

To not make the data public on pastebin select "Paste Exposure: Unlisted"

jkandasa commented 8 years ago

@gbrd When we send payload(from same sensor variable) multiple times in a millisecond this error happens. Sometimes MQTT broker holds set of payloads and send to MyController with in a millisecond this error occurs.

Are you seeing this error often?

In database timestamp AND sensor variable id should be unique.

gbrd commented 8 years ago

understood ! I seems not to be a big issue (maybe you could downgrade from ERROR to WARN or replace "insert" with some kind of "replace" ?)

I don't use a MQTT broker. Not reproduced since a few days, next time I will try to understood why I receive several values in the same milliseconds for the same sensor.

jkandasa commented 8 years ago

@gbrd This type of issue should be ERROR, because you can see the trace it is top level method to create entry for all tables. And we may not perform createOrUpdate, this will create huge performance issue. I feel we can leave this as is.

jkandasa commented 7 years ago

@gbrd I have included timestamp on message which is on queue. I hope this issue will not occur again.