UNIQUE constraint failed: Devices.dev_MAC

[roberthawdon]

I'm getting the following error when running a scan:

Pi.Alert 2.54 (2021-01-13)
---------------------------------------------------------
Scan Devices
    ScanCycle: 1
    Timestamp: 2021-01-13 23:27:00

Scanning...
    arp-scan Method...
    Pi-hole Method...
    DHCP Leases Method...

Procesising scan results...
    Devices Detected.......: 22
        arp-scan Method....: 22
        Pi-hole Method.....: +0
        New Devices........: 0

    Devices in this cycle..: 20
        Down Alerts........: 0
        New Down Alerts....: 0
        New Connections....: 0
        Disconnections.....: 37
        IP Changes.........: 0

Updating DB Info...
    Sessions Events (connect / discconnect) ...
    Creating new devices...
Traceback (most recent call last):
  File "/home/pi/pialert/back/pialert.py", line 1339, in <module>
    sys.exit(main())
  File "/home/pi/pialert/back/pialert.py", line 75, in main
    res = scan_network()
  File "/home/pi/pialert/back/pialert.py", line 381, in scan_network
    create_new_devices ()
  File "/home/pi/pialert/back/pialert.py", line 722, in create_new_devices
    (startTime, startTime) )
sqlite3.IntegrityError: UNIQUE constraint failed: Devices.dev_MAC

This is from a fresh install from the main branch. (v 2.54)

[pucherot]

Ohhh, it's a bit weird, the INSERT SQL statement checks that the MAC doesn't already exist in the list of existing devices.

Please, can you execute this command?

sqlite3 ~/pialert/db/pialert.db "SELECT * FROM Devices"
sqlite3 ~/pialert/db/pialert.db "SELECT * FROM DHCP_Leases"

[roberthawdon]

Hi, see below (I've hashed out part of my public IP)

pi@pihole:~ $ sqlite3 ~/pialert/db/pialert.db "SELECT * FROM Devices"
Internet|Internet|House|Router|Orange / Vodafone / Movistar ....|0|Always on||2021-01-01 00:00:00|2021-01-01 00:00:00|151.231.###.###|0|0|1|0|0|0|2021-01-01 00:00:00.000000|1
d0:58:fc:8e:92:5a|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.215|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
3c:89:94:25:25:39|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.1|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
54:e0:19:6e:1a:53|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.2|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
54:e0:19:5e:4d:fe|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.14|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
3c:89:94:0d:ed:f1|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.18|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:29:5d:4a|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.19|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
6c:4a:85:31:87:16|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.56|0|15|1|1|0|0|2021-01-13 23:25:44.032263|1
64:4b:f0:13:b6:30|(unknown)|(unknown)||CalDigit, Inc|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.84|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
f8:0f:f9:93:80:ec|(unknown)|(unknown)||Google, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.41|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
e8:9e:b4:be:92:4b|(unknown)|(unknown)||Hon Hai Precision Ind. Co.,Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.92|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:2d:d4:af|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.108|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
14:59:c0:54:de:6a|(unknown)|(unknown)||NETGEAR|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.130|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
ac:63:be:3a:ed:90|(unknown)|(unknown)||Amazon Technologies Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.152|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
d0:58:fc:a6:c1:d2|(unknown)|(unknown)||BSkyB Ltd|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.216|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
48:e1:e9:2d:d1:e9|(unknown)|(unknown)||Chengdu Meross Technology Co., Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.180|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
0c:96:e6:53:1a:7c|(unknown)|(unknown)||Cloud Network Technology (Samoa) Limited|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.201|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
a8:6b:ad:31:b5:19|(unknown)|(unknown)||Hon Hai Precision Ind. Co.,Ltd.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.211|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
38:f9:d3:64:7d:a4|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.114|0|15|1|1|0|0|2021-01-13 23:25:44.032263|1
86:65:d0:0e:40:50|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:25:00|2021-01-13 23:25:00|192.168.0.112|0|1|1|1|0|0|2021-01-13 23:25:44.032263|1
5e:3f:a0:d0:16:5b|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.160|0|1|1|1|0|0||1
c6:31:2d:f3:98:f8|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.159|0|1|1|1|0|0||1
00:1f:c5:f0:15:57|(unknown)|(unknown)||Nintendo Co., Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.109|0|1|1|1|0|0||1
ea:c8:01:9b:49:34|(unknown)|(unknown)||(Unknown)|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.168|0|1|1|1|0|0||1
7c:a1:ae:39:ed:0f|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.149|0|1|1|1|0|0||1
9c:b6:d0:de:6d:5d|ian-arch-XPS15-9560.lan|(unknown)||Rivet Networks|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.55|0|1|1|1|0|0||1
3a:b6:ec:e9:05:2e|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.192|0|1|1|1|0|0||1
c0:a5:3e:a2:6f:5c|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.156|0|1|1|1|0|0||1
d0:16:b4:b4:df:6d|(unknown)|(unknown)||Huawei Technologies Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.87|0|1|1|1|0|0||1
34:c3:d2:d1:65:e3|(unknown)|(unknown)||Fn-Link Technology Limited|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|-|0|1|1|1|0|0||1
d8:0d:17:74:38:eb|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.64|0|1|1|1|0|0||1
d8:0d:17:74:43:cd|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.49|0|1|1|1|0|0||1
d8:55:75:81:78:2f|Galaxy-A10.lan|(unknown)||Samsung Electronics Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.68|0|1|1|1|0|0||1
68:ff:7b:35:22:ad|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.65|0|1|1|1|0|0||1
cc:32:e5:1e:8d:fb|HS100.lan|(unknown)||Tp-Link Technologies Co.,Ltd.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.105|0|1|1|1|0|0||1
70:81:eb:67:bc:f4|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.31|0|1|1|1|0|0||1
40:98:ad:21:24:99|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.42|0|1|1|1|0|0||1
00:e1:51:b0:00:9f|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.155|0|1|1|1|0|0||1
00:17:fa:52:3b:0c|(unknown)|(unknown)||Microsoft Corporation|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.111|0|1|1|1|0|0||1
74:9e:af:40:00:8b|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.26|0|1|1|1|0|0||1
f4:06:16:4a:5e:4a|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.193|0|1|1|1|0|0||1
a8:a1:98:06:9b:09|(unknown)|(unknown)||TCT mobile ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.184|0|1|1|1|0|0||1
c4:84:66:91:a5:6d|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.81|0|1|1|1|0|0||1
b8:27:eb:85:e9:5f|(unknown)|(unknown)||Raspberry Pi Foundation|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.253|0|1|1|1|0|0||1
f8:6f:c1:33:70:2b|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.146|0|1|1|1|0|0||1
d8:96:95:88:1e:d3|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.136|0|1|1|1|0|0||1
68:9c:70:ab:8d:47|(unknown)|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.40|0|1|1|1|0|0||1
d0:3f:aa:2b:22:0a|iPhone.lan|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.78|0|1|1|1|0|0||1
e6:8f:8a:ec:19:79|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|fd58:84d1:7017:0:5070:749f:a05a:f267|0|1|1|1|0|0||1
34:2e:b6:a1:4d:11|(unknown)|(unknown)||Huawei Technologies Co.,Ltd|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:362e:b6ff:fea1:4d11|0|1|1|1|0|0||1
1e:97:ef:84:88:b1|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.34|0|1|1|1|0|0||1
7e:27:2a:f5:9d:78|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:78ba:5007:cce8:f112|0|1|1|1|0|0||1
66:13:48:bd:a5:fd|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|fe80::1cc4:80a2:723:cc63|0|1|1|1|0|0||1
6a:c3:5f:8c:0e:01|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.167|0|1|1|1|0|0||1
ba:64:5d:52:c5:31|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.142|0|1|1|1|0|0||1
54:e0:19:f7:04:af|(unknown)|(unknown)||Ring LLC|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|2a02:c7f:98c2:2200:56e0:19ff:fef7:4af|0|1|1|1|0|0||1
26:7c:91:f0:bf:8c|RobertspleWatch.lan|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.79|0|1|1|1|0|0||1
d4:c9:4b:d4:8c:f0|(unknown)|(unknown)||Motorola Mobility LLC, a Lenovo Company|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.133|0|1|1|1|0|0||1
06:50:52:b4:17:f5|(unknown)|(unknown)|||0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.176|0|1|1|1|0|0||1
78:e3:de:5d:79:32|iPad.lan|(unknown)||Apple, Inc.|0|(unknown)||2021-01-13 23:15:00|2021-01-13 23:15:00|192.168.0.38|0|1|1|1|0|0||1

pi@pihole:~ $ sqlite3 ~/pialert/db/pialert.db "SELECT * FROM DHCP_Leases"
1610747993|38:f9:d3:64:7d:a4|192.168.0.114|BGGLOBAL2050|01:38:f9:d3:64:7d:a4
1610742790|9c:b6:d0:de:6d:5d|192.168.0.55|ian-arch-XPS15-9560|01:9c:b6:d0:de:6d:5d
1610747993|64:4b:f0:13:b6:30|192.168.0.84|*|01:64:4b:f0:13:b6:30
1610744702|48:e1:e9:29:5d:4a|192.168.0.19|*|*
1610742524|5e:3f:a0:d0:16:5b|192.168.0.160|RobertHnsiPhone|01:5e:3f:a0:d0:16:5b
1610746057|48:e1:e9:2d:d1:e9|192.168.0.180|*|*
1610708150|48:e1:e9:2d:d4:af|192.168.0.108|*|*
1610715261|54:e0:19:5e:4d:fe|192.168.0.14|ChimePro-fe|00:05:54:e0:19:5e:4d:fe
1610748572|c6:31:2d:f3:98:f8|192.168.0.159|iPhone|01:c6:31:2d:f3:98:f8
1610742661|26:7c:91:f0:bf:8c|192.168.0.79|RobertspleWatch|01:26:7c:91:f0:bf:8c
1610713374|54:e0:19:6e:1a:53|192.168.0.2|54E0196E1A53-mysimplelink|*
1610734972|ea:c8:01:9b:49:34|192.168.0.168|HayleyspleWatch|01:ea:c8:01:9b:49:34
1610731899|14:59:c0:54:de:6a|192.168.0.130|*|*
1610722208|f8:0f:f9:93:80:ec|192.168.0.41|Google-Nest-Mini|*
1610715740|3c:89:94:0d:ed:f1|192.168.0.18|*|01:3c:89:94:0d:ed:f1
1610721375|6c:4a:85:31:87:16|192.168.0.56|Living-Room|01:6c:4a:85:31:87:16
1610734959|e8:9e:b4:be:92:4b|192.168.0.92|*|01:e8:9e:b4:be:92:4b
1610748555|00:1f:c5:f0:15:57|192.168.0.109|Wii|01:00:1f:c5:f0:15:57
1610714224|86:65:d0:0e:40:50|192.168.0.112|iPad|01:86:65:d0:0e:40:50
1610736868|ac:63:be:3a:ed:90|192.168.0.152|*|01:ac:63:be:3a:ed:90
1610747994|2|fd58:84d1:7017::1a8|*|00:01:00:01:24:27:bc:e4:38:f9:d3:64:7d:a4
1610742792|3134327015|2a02:c7f:98c2:2200::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742792|3134327015|fd58:84d1:7017::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742774|1134290498|2a02:c7f:98c2:2200::1d3|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742774|1134290498|fd58:84d1:7017::1d3|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610722209|4187193580|fd58:84d1:7017::1cd|Google-Nest-Mini|00:03:00:01:f8:0f:f9:93:80:ec
1610722209|4187193580|2a02:c7f:98c2:2200::1cd|Google-Nest-Mini|00:03:00:01:f8:0f:f9:93:80:ec

[pucherot]

Hi Robert, thank you for your time.

A new version v2.56 is available. This version resolve this problem (one MAC with more than one DHCP_Leases):

1610742792|3134327015|2a02:c7f:98c2:2200::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe
1610742792|3134327015|fd58:84d1:7017::1f8|ian-arch-XPS15-9560|00:04:b6:13:d6:79:86:58:9f:ad:18:6d:20:f0:6e:45:cd:fe