server descriptor query results are wrong and/or duplicated
Here is an interesting issue. :) I was looking up data for Hobbit18 (fingerprint 2720C39019EDD2037599B0D7F06C83570F92FB17). Nothing too fancy: published server descs, nickname, fingerprint, OR address, contact info, exit policy, bw and burst rate.
Given that the fields are in different tables I had to use some JOINs like so:
SELECT DISTINCT sc.published, nickname, sc.fingerprint, address, contact,
exit_policy, bandwidth_avg, bandwidth_burst
FROM server_descriptor_core AS sc
INNER JOIN server_descriptor_details AS sd ON sc.fingerprint = sd.fingerprint
INNER JOIN server_descriptor_bw AS sb ON sc.fingerprint = sb.fingerprint
WHERE sc.fingerprint = '2720C39019EDD2037599B0D7F06C83570F92FB17' AND
sc.published >= %(start)s AND sc.published <= %(end)s AND is_bridge = False
ORDER BY sc.published ASCThe result I get when quering from 2025-05-27 to 2025-12-05 look like:
| sc.published | nickname | sc.fingerprint | address | contact | exit_policy | bandwidth_avg | bandwidth_burst |
|---|---|---|---|---|---|---|---|
| 2025-05-27 10:20:18 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-05-27 10:20:18 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | 62914560 |
| 2025-05-28 04:20:22 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-05-28 04:20:22 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | |
| 2025-09-17 22:20:23 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-09-17 22:20:23 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | 62914560 |
| 2025-09-18 16:20:27 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-09-18 16:20:27 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | 62914560 |
| 2025-09-19 09:45:56 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-09-19 09:45:56 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | 62914560 |
| 2025-09-20 03:46:00 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject *:*'] | 41943040 | 62914560 |
| 2025-09-20 03:46:00 | Hobbit18 | 2720C39019EDD2037599B0D7F06C83570F92FB17 | 179.43.134.19 | Hobbit hobbit18@proton.me | ['reject 0.0.0.0/8:*', 'reject 169.254.0.0/16:*', 'reject 127.0.0.0/8:*', 'reject 192.168.0.0/16:*', 'reject 10.0.0.0/8:*', 'reject 172.16.0.0/12:*', 'reject 179.43.134.19:*', 'reject *:25', 'reject *:119', 'reject *:135-139', 'reject *:445', 'reject *:465', 'reject *:563', 'reject *:587', 'reject *:1214', 'reject *:4661-4666', 'reject *:6346-6429', 'reject *:6699', 'reject *:6881-6999', 'accept *:*'] | 41943040 | 62914560 |
I omitted a lot of lines and added only the ones important for my point. So, duplicated entries started right from the beginning when the relay showed up, what's going on here? Moreover looking on relay-search one can easily see that the relay changed from a non-exit to an exit node between 2025-09-17 and 2025-09-20, yet there is no such change visible in the DB results above. However, it is visible in the respective server descriptors: before there is no exit policy visible, after is.