Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

UNIQUE constraint failed: Devices.dev_MAC #23

Closed
roberthawdon opened this issue Jan 13, 2021 · 3 comments
Closed

UNIQUE constraint failed: Devices.dev_MAC #23

roberthawdon opened this issue Jan 13, 2021 · 3 comments
Assignees
@pucherot
Labels
bug Something isn't working fixed Bug fixed

Comments

@roberthawdon
Copy link

roberthawdon commented Jan 13, 2021 •
edited
Loading

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
Copy link
Owner

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"

@pucherot pucherot self-assigned this Jan 14, 2021
@pucherot pucherot added the bug Something isn't working label Jan 14, 2021
@roberthawdon
Copy link
Author

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 added a commit that referenced this issue Jan 15, 2021
@pucherot
v2.56
f8564f4 
BUGFIX #23
@pucherot
Copy link
Owner

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

@pucherot pucherot added the fixed Bug fixed label Jan 15, 2021
jokob-sk referenced this issue in jokob-sk/NetAlertX Jul 17, 2022
@pucherot
v2.56
f9b1ea7 
BUGFIX #23
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@pucherot pucherot

Labels
bug Something isn't working fixed Bug fixed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@roberthawdon @pucherot