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.