Running this in a docker-container

[pluim003]

I run Pihole and Unbound in a Docker-container, so I guess I have to put the script in the docker-image as well and let it run from there. I'll check out how I can add this to it. Might indeed be convenient. Added a 2nd pihole in another network a few days ago, but adding manually all the lists is one hell of a job, so I quit after a few. :-)

[pluim003]

Update: I added it to the creation of the pihole-unbound-image. Created a volume which will remain untouched upon recreation of the image/container which maps to the my-pihole-lists-repository. It takes some manual steps the first time and hopefully by using git config credential.helper store I don't have to enter credentials anymore after recreating an image/container.

My first initpull on my second pihole resulted in:

During pihole_cloudsync --initpull

[i] Creating new gravity databases... [✗] Unable to copy data from /etc/pihole/gravity.db to /etc/pihole/gravity.db_temp Parse error near line 15: foreign key mismatch - "domainlist_by_group" referencing "domainlist" Parse error near line 19: foreign key mismatch - "adlist_by_group" referencing "adlist" [✓] Building tree [✓] Swapping databases [✓] The old database remains available. [i] Number of gravity domains: 0 (0 unique domains) [i] Number of exact blacklisted domains: 32 [i] Number of regex blacklist filters: 1 [i] Number of exact whitelisted domains: 10 [i] Number of regex whitelist filters: 0 [✓] Flushing DNS cache [✓] Cleaning up stray matter

with an empty gravity.db. When running pihole -g again I had a new gravity.db but the webinterface still said I had 0 domains on the adlist. After another bounce of the container everything looked normal again.

[pluim003]

Another update: updating the container with a new image gave me again to enter credentials. As I mapped a volume to the my-pihole-lists-directory I entered in .git/config hardcoded in the url username and password (or better the key). It's not the safest way but can't find another way at the moment.

Then I would like to add something to the cron, but that should be based on whether it's a primary or secundairy pihole. Maybe just a file with the cron-entry which should be copied through another cron-job (which can be made hardcoded). Are there better solutions?

[pluim003]

Think I found what's causing the issue. As I try it manually I notice the following:

root@pihole2:/etc/pihole# sqlite3 gravity.db "DROP table adlist;" root@pihole2:/etc/pihole# sqlite3 gravity.db "DROP table domainlist;" root@pihole2:/etc/pihole# sqlite3 gravity.db ".import /usr/local/bin/my-pihole-lists/adlist.csv adlist" root@pihole2:/etc/pihole# sqlite3 gravity.db ".import /usr/local/bin/my-pihole-lists/domainlist.csv domainlist" /usr/local/bin/my-pihole-lists/domainlist.csv:12: expected 1 columns but found 2 - extras ignored root@pihole2:/etc/pihole#

Then I checked csv and table and specifically line 12:

csv:

d,type,domain,enabled,date_added,date_modified,comment 1,0,api.honeywell.com,1,1639114561,1639114561,"Honeywell API voor Home Assistant" 2,0,kinesis.us-east-1.amazonaws.com,1,1639120354,1639120354,"Added from Query Log" 3,0,www.awin1.com,1,1639669994,1639669994,"Added from Query Log" 4,0,lcprd1.samsungcloudsolution.net,1,1642966367,1642966367,"Added from Query Log" 5,0,googleadservices.com,1,1643191831,1643191831,"zoeken in Google en dan doorklikken gaat niet goed" 6,0,dartsearch.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 7,0,www.googletagmanager.com,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 8,0,www.googletagservices.com,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 9,0,ad.doubleclick.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 10,0,clickserve.dartsearch.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 15,3,(.|^)tweakers.nl$,1,1655474830,1655474830, 18,1,google.com.tr,1,1655542288,1655542288,"Added from Query Log" 20,1,google.com.gr,1,1655542295,1655542295,"Added from Query Log"

table:

root@pihole2:/etc/pihole# sqlite3 gravity.db "select * from domainlist;" 1,0,api.honeywell.com,1,1639114561,1639114561,"Honeywell API voor Home Assistant" 2,0,kinesis.us-east-1.amazonaws.com,1,1639120354,1639120354,"Added from Query Log" 3,0,www.awin1.com,1,1639669994,1639669994,"Added from Query Log" 4,0,lcprd1.samsungcloudsolution.net,1,1642966367,1642966367,"Added from Query Log" 5,0,googleadservices.com,1,1643191831,1643191831,"zoeken in Google en dan doorklikken gaat niet goed" 6,0,dartsearch.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 7,0,www.googletagmanager.com,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 8,0,www.googletagservices.com,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 9,0,ad.doubleclick.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 10,0,clickserve.dartsearch.net,1,1643191952,1643191952,"Zoeken met Google en doorklikken" 15,3,(. 18,1,google.com.tr,1,1655542288,1655542288,"Added from Query Log" 20,1,google.com.gr,1,1655542295,1655542295,"Added from Query Log"

This regex-thing is causing trouble and requires a second pihole -g-run

[pluim003]

adding -- ascii to the import-command solves it and the row gets nicely imported.

[pluim003]

And after another run still trouble. Tried it with adding ".separator ','" to the import-lines and -v (for verbose logging) and without -ascii. Still trouble with respect to foreign keys:

[i] Creating new gravity databases... [✗] Unable to copy data from /etc/pihole/gravity.db to /etc/pihole/gravity.db_temp Parse error near line 15: foreign key mismatch - "domainlist_by_group" referencing "domainlist" Parse error near line 19: foreign key mismatch - "adlist_by_group" referencing "adlist"

[pluim003]

And after another run still trouble. Tried it with adding ".separator ','" to the import-lines and -v (for verbose logging) and without -ascii. Still trouble with respect to foreign keys:

[i] Creating new gravity databases... [✗] Unable to copy data from /etc/pihole/gravity.db to /etc/pihole/gravity.db_temp Parse error near line 15: foreign key mismatch - "domainlist_by_group" referencing "domainlist" Parse error near line 19: foreign key mismatch - "adlist_by_group" referencing "adlist"

[pluim003]

Finally managed to solve the foreign key stuff. Even though foreign keys are enabled it was still complaining. Solved it by also exporting the adlist_by_group and domainlist_by_group-tables, dropping them and importing them. Now pihole -g runs without error.