Delete Duplicate / Invalid Sensor Variable

[seant100]

Hi

I have 2 Sensor Variables of type Volume on the same Sensor. I am not sure how this happened but I would like to delete the extra one as its causing confusion. I see the trash icon is for Purge only and purging all data does not delete the actual Sensor Vairable itself. So how can I now delete this Sensor Variable?

[jkandasa]

@seant100 When you edit the sensor, you can select unselect sensor variables. But it is not possible to create more than one type on the same sensor. Can you check the type by editing sensor variables?

[seant100]

@jkandasa Both the Sensor Variables are type Volume. I checked on the Edit page for each Sensor Variable. I did what you said above and deselected "Volume" on Sensor edit page but now its lost all data for the sensor variable it was logging to ! This was what I was trying to avoid as I wanted to keep a history of my water usage

[jkandasa]

@seant100 restore from your last backup. to retrieve your data. I need your database to fix this issue. you may email to jkandasa at gmail dot com

[seant100]

@jkandasa don't worry. i will be ok with data loss for this sensor for now.

[jkandasa]

@seant100 ok, thank you! It is a corner case. I do not know how to reproduce this issue. If you send your database(with this issue) I may check is there any issue with MyController.

[seant100]

@jkandasa Sent via email a copy of old database with the issue in it still. As per email there is another sensor with duplicate sensor variables as well. Hope it helps figure out what causes this problem.

[jkandasa]

@seant100 I see that lot of database unique checks removed from your database. I do not know how?

this statement not available on your database.

ALTER TABLE PUBLIC.SENSOR_VARIABLE ADD CONSTRAINT PUBLIC.CONSTRAINT_491 UNIQUE(SENSORDBID, VARIABLETYPE);

And I do not see these entries,

ALTER TABLE PUBLIC.METRICS_BINARY_TYPE_DEVICE ADD CONSTRAINT PUBLIC.CONSTRAINT_47 UNIQUE(SENSORVARIABLEID, TIMESTAMP);         
ALTER TABLE PUBLIC.RULE_OPERATION_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_7 UNIQUE(RULEDEFINITIONID, OPERATIONID);
ALTER TABLE PUBLIC.EXTERNALSERVER ADD CONSTRAINT PUBLIC.CONSTRAINT_9 UNIQUE(NAME);             
ALTER TABLE PUBLIC.ROLE_NODE_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_48 UNIQUE(ROLEID, NODEID);   
ALTER TABLE PUBLIC.SENSOR_VARIABLE ADD CONSTRAINT PUBLIC.CONSTRAINT_491 UNIQUE(SENSORDBID, VARIABLETYPE);      
ALTER TABLE PUBLIC.GATEWAY ADD CONSTRAINT PUBLIC.CONSTRAINT_1 UNIQUE(NAME);    
ALTER TABLE PUBLIC.METRICS_DOUBLE_TYPE_DEVICE ADD CONSTRAINT PUBLIC.CONSTRAINT_4B UNIQUE(SENSORVARIABLEID, TIMESTAMP, AGGREGATIONTYPE);        
ALTER TABLE PUBLIC.ROOM ADD CONSTRAINT PUBLIC.CONSTRAINT_2678D UNIQUE(NAME, PARENTID);         
ALTER TABLE PUBLIC.SENSOR_VARIABLES_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_D41 UNIQUE(SENSORTYPE, VARIABLETYPE); 
ALTER TABLE PUBLIC.FIRMWARE_VERSION ADD CONSTRAINT PUBLIC.CONSTRAINT_D1 UNIQUE(VERSION);       
ALTER TABLE PUBLIC.RULE_DEFINITION ADD CONSTRAINT PUBLIC.CONSTRAINT_CA UNIQUE(NAME);           
ALTER TABLE PUBLIC.SETTINGS ADD CONSTRAINT PUBLIC.CONSTRAINT_842 UNIQUE(USERID, KEY, SUBKEY);  
ALTER TABLE PUBLIC.FORWARD_PAYLOAD ADD CONSTRAINT PUBLIC.CONSTRAINT_41 UNIQUE(SOURCEID, DESTINATIONID);        
ALTER TABLE PUBLIC.FIRMWARE_DATA ADD CONSTRAINT PUBLIC.CONSTRAINT_C UNIQUE(FIRMWAREID);        
ALTER TABLE PUBLIC.ROLE_USER_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_F UNIQUE(ROLEID, USERID);    
ALTER TABLE PUBLIC.FIRMWARE ADD CONSTRAINT PUBLIC.CONSTRAINT_D8 UNIQUE(TYPEID, VERSIONID);     
ALTER TABLE PUBLIC.FIRMWARE_TYPE ADD CONSTRAINT PUBLIC.CONSTRAINT_C52 UNIQUE(NAME);            
ALTER TABLE PUBLIC.SENSOR ADD CONSTRAINT PUBLIC.CONSTRAINT_919 UNIQUE(SENSORID, NODEID);       
ALTER TABLE PUBLIC.USER_SETTINGS ADD CONSTRAINT PUBLIC.CONSTRAINT_9AD UNIQUE(USER_ID, KEY);    
ALTER TABLE PUBLIC.ROLE_SENSOR_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_21 UNIQUE(ROLEID, SENSORID);               
ALTER TABLE PUBLIC.ROLE_MQTT_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_66 UNIQUE(ROLEID);           
ALTER TABLE PUBLIC.NODE ADD CONSTRAINT PUBLIC.CONSTRAINT_24 UNIQUE(EUI, GATEWAYID);            
ALTER TABLE PUBLIC.USER ADD CONSTRAINT PUBLIC.CONSTRAINT_27 UNIQUE(USERNAME);  
ALTER TABLE PUBLIC.ROLE ADD CONSTRAINT PUBLIC.CONSTRAINT_26 UNIQUE(NAME);      
ALTER TABLE PUBLIC.METRICS_GPS_TYPE_DEVICE ADD CONSTRAINT PUBLIC.CONSTRAINT_C2 UNIQUE(SENSORVARIABLEID, TIMESTAMP, AGGREGATIONTYPE);           
ALTER TABLE PUBLIC.RESOURCE ADD CONSTRAINT PUBLIC.CONSTRAINT_1A5 UNIQUE(RESOURCETYPE, RESOURCEID);             
ALTER TABLE PUBLIC.TIMER ADD CONSTRAINT PUBLIC.CONSTRAINT_4C2 UNIQUE(NAME);    
ALTER TABLE PUBLIC.UID_TAG ADD CONSTRAINT PUBLIC.CONSTRAINT_10 UNIQUE(UID);    
ALTER TABLE PUBLIC.METRICS_COUNTER_TYPE_DEVICE ADD CONSTRAINT PUBLIC.CONSTRAINT_11 UNIQUE(SENSORVARIABLEID, TIMESTAMP, AGGREGATIONTYPE);       
ALTER TABLE PUBLIC.SYSTEM_JOBS ADD CONSTRAINT PUBLIC.CONSTRAINT_269 UNIQUE(NAME);              
ALTER TABLE PUBLIC.ROLE_GATEWAY_MAP ADD CONSTRAINT PUBLIC.CONSTRAINT_93F UNIQUE(ROLEID, GATEWAYID);

[seant100]

@jkandasa I am not sure why those would be missing. I am mostly running the latest snapshot release and update that version every now and then when i look and see a new version is available. Should I run all those statements on my database here?

[jkandasa]

@seant100 No, do not run these statements. I will fix #349, soon. so that we can import export from one database to another database easily.

[seant100]

@jkandasa Ok. Thank you. I will wait then for the import/export feature