search

mycontroller-org / mycontroller-v1-legacy

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

Nodes with duplicate ID's #469

Closed seant100 closed 5 years ago

seant100 commented 6 years ago

I occasionally get new nodes appearing that have the same EUI as an existing node. See attached screenshot of example. image

jkandasa commented 6 years ago

@seant100 If you do not mind, can you send your database backup to my email? jkandasa at gmail dot com

seant100 commented 6 years ago

@jkandasa I added the missing unique key constraints in my database and updated to latest snapshot. Will inform if this happens again. Thank you

seant100 commented 6 years ago

@jkandasa It appears logs have many issues trying to read data and then that fails and so it tries to insert a new node. Here is a snippet from the logs where I suspect it is doing this .... Obviously now the fake sensors are not appearing as the new node fails to insert now due to the unique constraint. But this does seem to indicate some underlying issue.

2018-10-27 18:04:38,438 ERROR [mc-th-pool-23] [org.mycontroller.standalone.db.dao.NodeDaoImpl:67] unable to get Node. gatewayId:1, nodeEui:6
org.h2.jdbc.JdbcSQLException: General error: "java.lang.NullPointerException" [50000-194]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.message.DbException.convert(DbException.java:295)
    at org.h2.message.DbException.toSQLException(DbException.java:268)
    at org.h2.message.TraceObject.logAndConvert(TraceObject.java:352)
    at org.h2.jdbc.JdbcResultSet.findColumn(JdbcResultSet.java:188)
    at com.j256.ormlite.jdbc.JdbcDatabaseResults.findColumn(JdbcDatabaseResults.java:103)
    at com.j256.ormlite.field.FieldType.resultToJava(FieldType.java:826)
    at com.j256.ormlite.stmt.mapped.BaseMappedQuery.mapRow(BaseMappedQuery.java:61)
    at com.j256.ormlite.stmt.StatementExecutor.queryForFirst(StatementExecutor.java:109)
    at com.j256.ormlite.dao.BaseDaoImpl.queryForFirst(BaseDaoImpl.java:239)
    at com.j256.ormlite.stmt.QueryBuilder.queryForFirst(QueryBuilder.java:380)
    at org.mycontroller.standalone.db.dao.NodeDaoImpl.get(NodeDaoImpl.java:65)
    at org.mycontroller.standalone.provider.ExecuterAbstract.getNode(ExecuterAbstract.java:475)
    at org.mycontroller.standalone.provider.ExecuterAbstract.isNodeRegistered(ExecuterAbstract.java:612)
    at org.mycontroller.standalone.provider.ExecuterAbstract.execute(ExecuterAbstract.java:98)
    at org.mycontroller.standalone.provider.EngineAbstract.auditQueue(EngineAbstract.java:260)
    at org.mycontroller.standalone.provider.EngineAbstract.run(EngineAbstract.java:131)
    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.lang.NullPointerException: null
    at org.h2.util.StringUtils.toUpperEnglish(StringUtils.java:106)
    at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3144)
    at org.h2.jdbc.JdbcResultSet.findColumn(JdbcResultSet.java:186)
    ... 15 common frames omitted
2018-10-27 18:04:38,441 ERROR [mc-th-pool-23] [org.mycontroller.standalone.db.dao.NodeDaoImpl:69] PrepareStatement:[SELECT * FROM `node` WHERE (`gatewayId` = 1 AND `eui` = '6' ) ]
2018-10-27 18:04:38,454 ERROR [mc-th-pool-23] [org.mycontroller.standalone.db.dao.BaseAbstractDaoImpl:247] unable to add new item:[Node(id=null, eui=6, gatewayTable=GatewayTable(id=1, enabled=null, name=null, networkType=null, timestamp=null, state=UNAVAILABLE, statusMessage=null, statusSince=null, type=null, properties=null), name=null, version=null, type=null, libVersion=null, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1540656278443, rssi=null, properties={}, parentNodeEui=null, registrationState=REGISTERED, smartSleepEnabled=false)]
java.sql.SQLException: Unable to run insert stmt on object Node(id=null, eui=6, gatewayTable=GatewayTable(id=1, enabled=null, name=null, networkType=null, timestamp=null, state=UNAVAILABLE, statusMessage=null, statusSince=null, type=null, properties=null), name=null, version=null, type=null, libVersion=null, batteryLevel=null, eraseConfig=null, firmware=null, state=UP, lastSeen=1540656278443, rssi=null, properties={}, parentNodeEui=null, registrationState=REGISTERED, smartSleepEnabled=false): INSERT INTO `node` (`id` ,`eui` ,`gatewayId` ,`name` ,`version` ,`type` ,`libVersion` ,`batteryLevel` ,`eraseConfig` ,`firmwareId` ,`state` ,`lastSeen` ,`rssi` ,`properties` ,`parentNodeEui` ,`registrationState` ,`smartSleepEnabled` ) 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:244)
    at org.mycontroller.standalone.provider.ExecuterAbstract.getNode(ExecuterAbstract.java:488)
    at org.mycontroller.standalone.provider.ExecuterAbstract.isNodeRegistered(ExecuterAbstract.java:612)
    at org.mycontroller.standalone.provider.ExecuterAbstract.execute(ExecuterAbstract.java:98)
    at org.mycontroller.standalone.provider.EngineAbstract.auditQueue(EngineAbstract.java:260)
    at org.mycontroller.standalone.provider.EngineAbstract.run(EngineAbstract.java:131)
    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: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "CONSTRAINT_24_INDEX_2 ON PUBLIC.NODE(EUI, GATEWAYID) VALUES ('6', 1, 209)"; SQL statement:
INSERT INTO `node` (`id` ,`eui` ,`gatewayId` ,`name` ,`version` ,`type` ,`libVersion` ,`batteryLevel` ,`eraseConfig` ,`firmwareId` ,`state` ,`lastSeen` ,`rssi` ,`properties` ,`parentNodeEui` ,`registrationState` ,`smartSleepEnabled` ) 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)
    ... 11 common frames omitted
jkandasa commented 6 years ago

@seant100 I guess this issue related to https://github.com/mycontroller-org/mycontroller/issues/484, fixes this issue too.