search

pi-hole / FTL

The Pi-hole FTL engine
https://pi-hole.net
Other
1.36k stars 194 forks source link

DBimport false sql error #1617

Closed regae closed 1 month ago

regae commented 1 year ago

latest development-v6

2023-08-04 20:31:29.771 [1361M] ERR: SQLite3 message: abort at 36 in [INSERT INTO disk.query_storage SELECT FROM query_storage WHERE id > ? AND timestamp < ?]: UNIQUE constraint failed: query_storage.id (1555) 2023-08-04 20:31:29.771 [1361M] ERR: export_queries_to_disk(): Failed to export queries: constraint failed 2023-08-04 20:31:29.772 [1361M] INFO: SQL query was: "INSERT INTO disk.query_storage SELECT FROM query_storage WHERE id > ? AND timestamp < ?" 2023-08-04 20:31:29.772 [1361M] INFO: with parameters: id = 0, timestamp = 1691155889.768082

happened during exit, and only if DBimport is false. is it reproducible or just me?

DL6ER commented 1 year ago

I'm running the development-v6 branch for a long time on my Pi-hole can cannot find a similar error in my most recent logs files.

parameters: id = 0, timestamp = 1691155889.768082

suggests that it tries to store queries beginning with ID = 0. If there is already something in the database, this will obviously cause said uniqueness constraint violation.

Some questions:

  1. Are there any database related errors further up in your log? I'd expect something like this during or at least rather soon (say within in the first 1-2 minutes) after an FTL restart.
  2. What does FTL say how many queries it imported from the database?

Further ideas:

  1. It would otherwise be interesting if you could set debug.database = true and watch for lines like

    Storing queries on disk WHERE id > XXX (max is XXX) and timestamp < XXX

  2. Please show us the output of

    sqlite3 /etc/pihole/pihole-FTL.db 'SELECT MIN(id),MAX(id) FROM query_storage;'

  3. Do you have lines like Failed to get MAX(id) from query_storage in your FTL log file?

This all should help us in deciding how to go forward with fixing the issue you are seeing.

DL6ER commented 1 year ago

Ah, no. Ignore (most of) what I said above, I read

only if DBimport is false

but failed to fully appreciate what this does - I think we might not be handing this configuration correctly.

I'll come back to you soon with a proposed solution.

DL6ER commented 1 year ago

Please try branch fix/DBimport using pihole checkout ftl fix/DBimport once the build is finished (can be watched here: https://github.com/pi-hole/FTL/actions/runs/5765033112). Note that all builds will have to be finished before the binaries become available.

If you are using Docker, this may be more tricky but we can work this out together if this is the case for you.

PromoFaux commented 1 year ago

this may be more tricky

Build the image locally with:

docker buildx build src/. --tag ph-development-v6 --no-cache --build-arg FTL_BRANCH=fix/DBimport

And then replace the reference to pihole/pihole:development-v6 with ph-development-v6 in your run/compose script to use the locally built image

edit: Same caveat applies about waiting for the action @DL6ER mentioned to complete first!

regae commented 1 year ago

confirmed, no more error / warning logs when DBimport is false.

built locally, forgot to mention

regae commented 1 year ago

instead opening a new issue, i'd like to ask here i have this config domain=lan,192.168.0.0/24,local

hostname.lan is still logged in query log, i am kind a lazy to test it with released FTL (because not really important), but as far as i remember before trying dev-v6 this wasn't happening,

maybe related #1247

DL6ER commented 1 year ago

Just to be sure:

One of your dnsmasq config files has

domain=lan,192.168.0.0/24,local

and your /etc/pihole/pihole.toml has

piholePTR = "HOSTNAMEFQDN"
regae commented 1 year ago

correct, piholePTR is set to HOSTNAMEFQDN

DL6ER commented 1 year ago

It seems like they have always been logged, I verified this also with a v5 Pi-hole. The issue you linked (rather the PR mentioned therein) only added support for PTR lookups of the IP address to reply with the "most appropriate" hostname for the interface a query is received on. Nothing more.

The check whether or not this is a special Pi-hole domain (that is also not logged) is found here: https://github.com/pi-hole/FTL/blob/1a11413385eee8992b12b662cd37c32e55da4f6d/src/dnsmasq_interface.c#L458-L460

The conditions are it has to be either pi.hole (possibly with a domain suffix) or the hostname of the machine (as in /etc/hostname, possibly with the same domain suffix). Support for domain=.... type of hostnames was never there and actually doesn't seem to be doable without a lot of code rewriting inside the dnsmasq code (something we generally try to avoid).

However, have a look at https://github.com/pi-hole/FTL/pull/1611 which may give you exactly what you are looking for - even when it means you would have to define these domains there in addition to your dnsmasq config file.

regae commented 1 year ago

ok then, back to original issue since it's been resolved, you may close this issur, or should i? thank you

DL6ER commented 1 year ago

The fix is merged so we can close this. Thank you very much for reporting it!

regae commented 1 year ago

i'm sorry, i got a new log after reenabling DBimport a lot of same log

2023-08-06 23:17:04.363 [20482M] INFO: 20000 queries parsed... 2023-08-06 23:17:04.365 [20482M] WARNING: Database: DOMAIN should never be NULL, 1691318626 2023-08-06 23:17:04.477 [20482M] WARNING: Database: CLIENT should never be NULL, 1691322173

i just noticed when i see the log size has fatted

@DL6ER

DL6ER commented 1 year ago

Well, this is indeed strange. Please post the output of

pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM queries WHERE timestamp BETWEEN 1691318626 AND 1691318627"
pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM queries WHERE timestamp BETWEEN 1691322173 AND 1691322174"

You are free to modify any of the content if you feel this is necessary to preserve your privacy, however, if you replace some test please be crystal clear where you replaced something so we don't start digging into the wrong direction.

Note to @DL6ER: Query timestamps are floating-point values, don't cast them to integer before printing!

DL6ER commented 1 year ago

I did just push the branch fix/query-table improving the warnings such that we get the exact query IDs these warnings belong to. Feel free to check them out and get a better hint to which queries have actually triggered them (even when I think you should be able to identify them from the commands above, too - there will just be more noise).

regae commented 1 year ago

2023-08-07 09:40:34.186 [1856M] INFO: 30000 queries parsed... 2023-08-07 09:40:34.277 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57715, timestamp = 1691366033.152300 2023-08-07 09:40:34.277 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57714, timestamp = 1691366033.176419 2023-08-07 09:40:34.278 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57713, timestamp = 1691366033.204493 2023-08-07 09:40:34.279 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57712, timestamp = 1691366033.359529 2023-08-07 09:40:34.279 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57711, timestamp = 1691366033.505675 2023-08-07 09:40:34.280 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57710, timestamp = 1691366033.578566 2023-08-07 09:40:34.281 [1856M] WARNING: Database: CLIENT should never be NULL, ID = 57708, timestamp = 1691366033.854058 2023-08-07 09:40:34.318 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 59813, timestamp = 1691370366.520848 2023-08-07 09:40:34.319 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 59812, timestamp = 1691370366.884319 2023-08-07 09:40:34.325 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 59956, timestamp = 1691370685.610311 2023-08-07 09:40:34.327 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60007, timestamp = 1691370796.705706 2023-08-07 09:40:34.327 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60006, timestamp = 1691370796.707861 2023-08-07 09:40:34.329 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60032, timestamp = 1691370881.289020 2023-08-07 09:40:34.329 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60031, timestamp = 1691370881.334828 2023-08-07 09:40:34.334 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60180, timestamp = 1691371561.585410 2023-08-07 09:40:34.335 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60187, timestamp = 1691371563.467970 2023-08-07 09:40:34.335 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60186, timestamp = 1691371563.630574 2023-08-07 09:40:34.336 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60192, timestamp = 1691371564.605913 2023-08-07 09:40:34.337 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60190, timestamp = 1691371564.761336 2023-08-07 09:40:34.338 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60193, timestamp = 1691371566.929544 2023-08-07 09:40:34.338 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60201, timestamp = 1691371568.206872 2023-08-07 09:40:34.339 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60204, timestamp = 1691371570.670408 2023-08-07 09:40:34.342 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60280, timestamp = 1691371847.726170 2023-08-07 09:40:34.349 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60486, timestamp = 1691372523.488124 2023-08-07 09:40:34.354 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60659, timestamp = 1691373096.116340 2023-08-07 09:40:34.355 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60658, timestamp = 1691373096.118144 2023-08-07 09:40:34.362 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 60878, timestamp = 1691373811.035742 2023-08-07 09:40:34.368 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61049, timestamp = 1691374139.534438 2023-08-07 09:40:34.369 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61048, timestamp = 1691374139.537179 2023-08-07 09:40:34.370 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61097, timestamp = 1691374151.200008 2023-08-07 09:40:34.371 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61096, timestamp = 1691374151.202224 2023-08-07 09:40:34.372 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61109, timestamp = 1691374152.064191 2023-08-07 09:40:34.372 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61108, timestamp = 1691374152.066421 2023-08-07 09:40:34.373 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61107, timestamp = 1691374152.157456 2023-08-07 09:40:34.374 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61099, timestamp = 1691374152.793139 2023-08-07 09:40:34.374 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61098, timestamp = 1691374152.796164 2023-08-07 09:40:34.375 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61113, timestamp = 1691374153.061540 2023-08-07 09:40:34.376 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61112, timestamp = 1691374153.064514 2023-08-07 09:40:34.376 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61111, timestamp = 1691374153.066567 2023-08-07 09:40:34.377 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61110, timestamp = 1691374153.068444 2023-08-07 09:40:34.378 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61132, timestamp = 1691374244.211552 2023-08-07 09:40:34.379 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61131, timestamp = 1691374244.213899 2023-08-07 09:40:34.381 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61169, timestamp = 1691374394.751253 2023-08-07 09:40:34.381 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61168, timestamp = 1691374394.755107 2023-08-07 09:40:34.383 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61201, timestamp = 1691374435.148453 2023-08-07 09:40:34.383 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61200, timestamp = 1691374435.149326 2023-08-07 09:40:34.384 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61199, timestamp = 1691374435.154628 2023-08-07 09:40:34.385 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61212, timestamp = 1691374515.947971 2023-08-07 09:40:34.386 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61214, timestamp = 1691374518.600558 2023-08-07 09:40:34.386 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61213, timestamp = 1691374518.601789 2023-08-07 09:40:34.388 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61242, timestamp = 1691374596.702981 2023-08-07 09:40:34.389 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61279, timestamp = 1691374677.603763 2023-08-07 09:40:34.390 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61278, timestamp = 1691374677.605519 2023-08-07 09:40:34.390 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61277, timestamp = 1691374677.608600 2023-08-07 09:40:34.392 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61298, timestamp = 1691374717.994442 2023-08-07 09:40:34.392 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61297, timestamp = 1691374717.996509 2023-08-07 09:40:34.394 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61327, timestamp = 1691374758.399547 2023-08-07 09:40:34.395 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61335, timestamp = 1691374839.194770 2023-08-07 09:40:34.395 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61342, timestamp = 1691374862.595591 2023-08-07 09:40:34.396 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61341, timestamp = 1691374862.598043 2023-08-07 09:40:34.397 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61340, timestamp = 1691374862.602390 2023-08-07 09:40:34.397 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61339, timestamp = 1691374862.603760 2023-08-07 09:40:34.399 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61372, timestamp = 1691374919.928958 2023-08-07 09:40:34.400 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61377, timestamp = 1691374960.288671 2023-08-07 09:40:34.400 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61376, timestamp = 1691374960.290282 2023-08-07 09:40:34.401 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61385, timestamp = 1691375000.744858 2023-08-07 09:40:34.402 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61384, timestamp = 1691375000.747038 2023-08-07 09:40:34.402 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61383, timestamp = 1691375000.752310 2023-08-07 09:40:34.404 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61411, timestamp = 1691375062.697875 2023-08-07 09:40:34.405 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61410, timestamp = 1691375062.699635 2023-08-07 09:40:34.406 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61422, timestamp = 1691375081.591077 2023-08-07 09:40:34.407 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61456, timestamp = 1691375162.595599 2023-08-07 09:40:34.408 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61474, timestamp = 1691375243.482640 2023-08-07 09:40:34.410 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61514, timestamp = 1691375369.748825 2023-08-07 09:40:34.410 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61513, timestamp = 1691375369.752807 2023-08-07 09:40:34.411 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61537, timestamp = 1691375370.483416 2023-08-07 09:40:34.412 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61536, timestamp = 1691375370.485045 2023-08-07 09:40:34.413 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61525, timestamp = 1691375370.818846 2023-08-07 09:40:34.413 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61524, timestamp = 1691375370.822468 2023-08-07 09:40:34.414 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61523, timestamp = 1691375370.827852 2023-08-07 09:40:34.415 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61522, timestamp = 1691375370.832340 2023-08-07 09:40:34.416 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61541, timestamp = 1691375371.083026 2023-08-07 09:40:34.416 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61540, timestamp = 1691375371.083780 2023-08-07 09:40:34.421 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61649, timestamp = 1691375468.806380 2023-08-07 09:40:34.422 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61646, timestamp = 1691375468.823542 2023-08-07 09:40:34.422 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61645, timestamp = 1691375468.903395 2023-08-07 09:40:34.423 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61644, timestamp = 1691375468.906335 2023-08-07 09:40:34.426 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61720, timestamp = 1691375630.604383 2023-08-07 09:40:34.426 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61719, timestamp = 1691375630.607421 2023-08-07 09:40:34.429 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61800, timestamp = 1691375638.154108 2023-08-07 09:40:34.429 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61799, timestamp = 1691375638.159350 2023-08-07 09:40:34.430 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61789, timestamp = 1691375638.288649 2023-08-07 09:40:34.431 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61788, timestamp = 1691375638.290874 2023-08-07 09:40:34.432 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61823, timestamp = 1691375643.181319 2023-08-07 09:40:34.432 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61822, timestamp = 1691375643.183044 2023-08-07 09:40:34.434 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61862, timestamp = 1691375665.077730 2023-08-07 09:40:34.435 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61861, timestamp = 1691375665.079704 2023-08-07 09:40:34.436 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61883, timestamp = 1691375671.114251 2023-08-07 09:40:34.436 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61882, timestamp = 1691375671.115662 2023-08-07 09:40:34.437 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61881, timestamp = 1691375671.118954 2023-08-07 09:40:34.440 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61964, timestamp = 1691375749.099933 2023-08-07 09:40:34.441 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61967, timestamp = 1691375750.785055 2023-08-07 09:40:34.441 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61966, timestamp = 1691375750.787498 2023-08-07 09:40:34.443 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61972, timestamp = 1691375751.816366 2023-08-07 09:40:34.443 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 61971, timestamp = 1691375751.956177 2023-08-07 09:40:34.445 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62016, timestamp = 1691375785.830131 2023-08-07 09:40:34.446 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62015, timestamp = 1691375785.831478 2023-08-07 09:40:34.446 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62014, timestamp = 1691375785.834589 2023-08-07 09:40:34.447 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62013, timestamp = 1691375785.836265 2023-08-07 09:40:34.448 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62034, timestamp = 1691375832.736041 2023-08-07 09:40:34.450 [1856M] WARNING: Database: DOMAIN should never be NULL, ID = 62100, timestamp = 1691375913.531875 2023-08-07 09:40:34.452 [1856M] INFO: Imported 37013 queries from the long-term database

/etc/pihole/pihole-FTL.db "SELECT * FROM queries WHERE timestamp BETWEEN 1691318626 AND 1691318627"

28437|1691318626.56092|2|2||192.168.9.30|127.0.0.1#5053||3|0.0475406646728516|0 28436|1691318626.56237|1|2||192.168.9.30|127.0.0.1#5053||3|0.0306684970855713|0 28435|1691318626.61428|2|3|rr7---sn-2uuxa3vh-jb3e.googlevideo.com|192.168.9.30|||1|0.000258445739746094|0 28434|1691318626.80932|2|17|www.youtube.com|192.168.9.30|127.0.0.1#5053||3|0.000238180160522461|0 28433|1691318626.81056|1|17|www.youtube.com|192.168.9.30|127.0.0.1#5053||3|0.000283002853393555|0

before this issue raised, just to be clear here

DBimport = true > DBimport = false > report it > got a fix > DBimport = true still using the same pihole-FTL.db

DL6ER commented 1 year ago

I just wanted to step in and tell you that I have not forgotten to check this out but I'm currently snowed under with work. Things should improve end of next week. I will then come back to you.

DL6ER commented 10 months ago

@regae Sorry for the long delay ... we made quite a few changes to the relevant code over the past weeks, and I was hoping you have good news that this is now with the latest development-v6 version? If not, I have some time to check this out in greater detail.

regae commented 10 months ago

after enabling dbimport, 

2023-11-23 18:39:32.107 [18271M] INFO: Imported 53742 queries from the on-disk database (it has 588850 rows)
2023-11-23 18:39:32.108 [18271M] INFO: Parsing queries in database
2023-11-23 18:39:32.534 [18271M] INFO:   10000 queries parsed...
2023-11-23 18:39:32.705 [18271M] WARNING: Database: DOMAIN should never be NULL, ID = 2003346, timestamp = 1700666738.715170
2023-11-23 18:39:32.705 [18271M] WARNING: Database: DOMAIN should never be NULL, ID = 2003345, timestamp = 1700666738.717183
2023-11-23 18:39:32.957 [18271M] INFO:   20000 queries parsed...
2023-11-23 18:39:33.345 [18271M] INFO:   30000 queries parsed...
2023-11-23 18:39:33.824 [18271M] INFO:   40000 queries parsed...
2023-11-23 18:39:34.379 [18271M] INFO:   50000 queries parsed...
2023-11-23 18:39:34.605 [18271M] INFO: Imported 53738 queries from the long-term database
2023-11-23 18:39:34.607 [18271M] INFO:  -> Total DNS queries: 53738
2023-11-23 18:39:34.607 [18271M] INFO:  -> Cached DNS queries: 34654
2023-11-23 18:39:34.607 [18271M] INFO:  -> Forwarded DNS queries: 14054
2023-11-23 18:39:34.607 [18271M] INFO:  -> Blocked DNS queries: 4418
2023-11-23 18:39:34.607 [18271M] INFO:  -> Unknown DNS queries: 0
2023-11-23 18:39:34.607 [18271M] INFO:  -> Unique domains: 2692
2023-11-23 18:39:34.607 [18271M] INFO:  -> Unique clients: 23
2023-11-23 18:39:34.607 [18271M] INFO:  -> Known forward destinations: 2

only those two logs were logged for running 10 minutes, will wait longer. i will update later.

DL6ER commented 10 months ago

Okay, could you please provide the output of

pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM query_storage WHERE id = 2003346;"

and

pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM domain_by_id WHERE id = (SELECT domain FROM query_storage WHERE id = 2003346);"

?

DL6ER commented 10 months ago

... and ... (I forgot one):

pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM domain_by_id WHERE domain IS NULL;"
regae commented 10 months ago 
# pihole-FTL sql -h /etc/pihole/pihole-FTL.db "SELECT * FROM query_storage WHERE id = 2003346;"
id       timestamp         type  status  domain  client  forward  additional_info  reply_type  reply_time         dnssec  regex_id
-------  ----------------  ----  ------  ------  ------  -------  ---------------  ----------  -----------------  ------  --------
2003346  1700666738.71517  2     2       2663    23      2        (null)           4           0.100451469421387  0       (null)

the other two returns empty. maybe for me just use the easy way, which is use a new database.

DL6ER commented 2 months ago

Is this still happening?

regae commented 1 month ago

Is this still happening?

i don't think so, did not appear in the log when i try to reproduce, the only thing i noticed is that when DBImport is false in web ui Query Log page is empty Dashboard page is still show the query for tops domains.

is it expected behavior? can you confirm what i noticed?

DL6ER commented 1 month ago

Yes, this is expected behavior. There is no history to restore when DBimport is false.