SQLITE CONSTRAINT Error

General
1

Good Day Everone,

I have an unfamiliar issue that appears out of the blue and I have no reason why.

When we have built the flow last night, it all worked fine, and this morning after redstart the flow just seems not to right to the SQL DB table due to an SQLITE_CONSTRAINT_Error .but nothing has changed ( NOTHING )

I have deleted the flow and imported the backup confirmed working flow that passed the data successfully to the db last night.

the Error from the SQLite node :

Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: accreq.DATE

here with the Function node code / values that right to the...SQLite node

tvar now = (new Date()).toISOString().slice(0,-5);
var dateParts = now.split("T");

var door_id= msg.payload["psa001/d1/user_input "]["Door ID"];
var door_otp= msg.payload["psa001/d1/user_input "].OTP;
var hashkey= msg.payload.accreq;
var _msgid= msg._msgid;
var date = dateParts[0];
var time = dateParts[1];


//create SQL command
msg.topic = `INSERT INTO ACCREQ (
  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY
) values (
   '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,'${hashkey}');`
return msg;ype or paste code here

What I don't understand is why it worked perfectly last night and now it complains about a missing date value that is clearly passed to the SQL node or am I missing something hare ??

Here is the complete flow ( Or the ones that matter )

[{"id":"2c2bad1c.7298d2","type":"mqtt in","z":"6aaa3f67.0cbdf","name":"PSA001 - QR Value","topic":"accreq","qos":"0","datatype":"auto","broker":"57c985f0.a39e8c","x":270,"y":300,"wires":[["a759afa8.e4e22"]]},{"id":"f94cb424.90bce8","type":"mqtt in","z":"6aaa3f67.0cbdf","name":"PSA001 - OTP","topic":"psa001/d1/user_input ","qos":"0","datatype":"json","broker":"57c985f0.a39e8c","x":260,"y":460,"wires":[["a759afa8.e4e22"]]},{"id":"ce08ea1f.168018","type":"function","z":"6aaa3f67.0cbdf","name":"PSA DB - Insert - ACCREQ","func":"var now = (new Date()).toISOString().slice(0,-5);\nvar dateParts = now.split(\"T\");\n\nvar door_id= msg.payload[\"psa001/d1/user_input \"][\"Door ID\"];\nvar door_otp= msg.payload[\"psa001/d1/user_input \"].OTP;\nvar hashkey= msg.payload.accreq;\nvar _msgid= msg._msgid;\nvar date = dateParts[0];\nvar time = dateParts[1];\n\n\n//create SQL command\nmsg.topic = `INSERT INTO ACCREQ (\n  DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n   '${date}', '${time}', '${_msgid}', ${door_id}, ${door_otp} ,'${hashkey}');`\nreturn msg;","outputs":1,"noerr":0,"x":860,"y":380,"wires":[["5d519068.a4712","2673434c.d6c74c"]]},{"id":"a759afa8.e4e22","type":"join","z":"6aaa3f67.0cbdf","name":"Wait and Join ","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":580,"y":380,"wires":[["ce08ea1f.168018"]]},{"id":"5d519068.a4712","type":"debug","z":"6aaa3f67.0cbdf","name":"Insert data debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1210,"y":300,"wires":[]},{"id":"2673434c.d6c74c","type":"sqlite","z":"6aaa3f67.0cbdf","mydb":"9db34194.66aca","sqlquery":"msg.topic","sql":"","name":"PSA-DB ","x":1180,"y":380,"wires":[["f90aaa9d.95c438"]]},{"id":"f90aaa9d.95c438","type":"debug","z":"6aaa3f67.0cbdf","name":"Response from DB ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1430,"y":380,"wires":[]},{"id":"57c985f0.a39e8c","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"localhost ","port":"1883","clientid":"psa003","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"}]

I would really appreciate your help, Thank you in advance

2

Hi again.

Looks like you have a primary key or UNIQUE set on the date field & tried to write data with a same DATE as is already in the database.

See here

The solution is to understand what you need to be primary or unique and fix your database (node-red is not likely to be the issue here)

1 Like
3

Hi Steve,

Thanks for your response . i am sure its not Node red ,

so , you saying the " Primary Key value " is already exciting , thus it dont allow a duplicated ?

4

Primary key == new data row must be different

I cant help you here as I am not 100% on sqlite

If you explain what uniqueness or constraints on which field(s) you need - I am certain someone else can assist. And if you want a quick reply, you would be wise to also post your database table schema like this...

CREATE TABLE myTable (
    ...,
    DATE type UNIQUE,
    ... etc 
    ... etc 
);

so that people who know SQLITE will be easily able to help you.

1 Like
5

HI Steve ,

OK , again you where right as always. It was the PRIMARY KEY that was set to the Data collum ,

so i have dropped the table and created a new table exactly but changed the Primary key to the msg_id ,

Thanks again for your help
and now its working like a charm , every time after time . have done a couple of tests and all good so far

1 Like
6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.