Unable to Fill Gravity Table

Problem with Beta 5.0:
I’ve been running the PiHole v5 beta for about the past month. I went to run an update today to update to the most recent version of the beta code, and was greeted with an error message when attempting to update Gravity at the end of the process. This error also repeats itself any time I call an update to Gravity itself.

The terminal reads:

Unable to fill gravity table in database /etc/pihole/gravity_temp.db
Error: /tmp/tmp.461xDziPgw.gravity line 53869: expected 2 columns of data but found 3

I encountered this issue last week when I attempted to update as well (on the same machine). I tried updating my OS (CentOS 7 on VMware) and performing a database rebuild per documentation that I found elsewhere on the forum, and that didn’t seem to help. I ended up rolling back to a backup because the repair operation completely hosed my group configuration as well.

The PiHole still appears to be blocking correctly from the little bit that I’ve been table to test it so far, and I can query URLs on the blocklists from the web interface; however, the blacklisted domain count is -2, and the updates to gravity don’t appear to be completing successfully (on account of the database error).

I’d like to provide the opportunity to work through this before I roll back to my snapshot (or last backup). Any help would be appreciated!

Debug Token (edit):
jx3qa02hyb

That -2 is standard on the first import of the blocklist. It should give the previous value after the next import.

It can’t seem to get beyond that, though, as it continues to throw that database error while updating Gravity.
I’ve tried logging in with a fresh browser, too, and the domain count remains the same. The tooltip shows that the database was last updated more than two weeks ago, too.

I tried updating Gravity just now, and this is what it’s showing:
2020-02-13 20_45_43-GravityError

Hopefully this is some useful information.

One of the lists in your adlist causes this problem on import. This was not caught by the current cleaning filters so you really have determine which list is causing.

The -2 and old date are thing that are being worked or even solved but not yet implemented in the current beta.

As you mention yourself you have to run the debug and post the token.

I can’t look in that data but support can.

@patconn87 Could you tell me what blocking lists you’re using? I will try to reproduce this.

sqlite3 /etc/pihole/gravity.db "SELECT address FROM vw_adlist;"

should tell you.

@DL6ER, here is the output of the query:

https://raw.githubusercontent.com/StevenBlack/hosts/master/hosts
https://mirror1.malwaredomains.com/files/justdomains
http://sysctl.org/cameleon/hosts
https://s3.amazonaws.com/lists.disconnect.me/simple_tracking.txt
https://s3.amazonaws.com/lists.disconnect.me/simple_ad.txt
https://hosts-file.net/ad_servers.txt
https://raw.githubusercontent.com/durablenapkin/scamblocklist/master/hosts.txt
https://gitlab.com/ookangzheng/dbl-oisd-nl/raw/master/dbl.txt

I’ve also uploaded a debug token after having updated gravity, jx3qa02hyb. Hope this helps!

Just set mine up to use the same lists, and not seeing any issues…

  [i] Upgrading gravity database from version 10 to 11
  [✓] Deleting existing list cache
  [i] Neutrino emissions detected...
  [✓] Pulling blocklist source list into range

  [✓] Preparing new gravity database
  [i] Target: https://raw.githubusercontent.com/StevenBlack/hosts/master/hosts
  [✓] Status: Retrieval successful

  [i] Target: https://mirror1.malwaredomains.com/files/justdomains
  [✓] Status: Retrieval successful

  [i] Target: http://sysctl.org/cameleon/hosts
  [✓] Status: Retrieval successful

  [i] Target: https://s3.amazonaws.com/lists.disconnect.me/simple_tracking.txt
  [✓] Status: Retrieval successful

  [i] Target: https://s3.amazonaws.com/lists.disconnect.me/simple_ad.txt
  [✓] Status: Retrieval successful

  [i] Target: https://hosts-file.net/ad_servers.txt
  [✓] Status: Retrieval successful

  [i] Target: https://raw.githubusercontent.com/durablenapkin/scamblocklist/master/hosts.txt
  [✓] Status: Retrieval successful

  [i] Target: https://gitlab.com/ookangzheng/dbl-oisd-nl/raw/master/dbl.txt
  [✓] Status: Retrieval successful

  [✓] Storing downloaded domains in new gravity database
  [✓] Building tree
  [✓] Swapping databases
  [✓] Flushing DNS cache
  [i] Number of gravity domains: 1127519 (1007398 unique domains)
  [i] Number of exact blacklisted domains: 1
  [i] Number of regex blacklist filters: 0
  [i] Number of exact whitelisted domains: 0
  [i] Number of regex whitelist filters: 0
  [✓] Cleaning up stray matter

  [✓] DNS service is running
  [✓] Pi-hole blocking is Enabled

  [i] The install log is located at: /etc/pihole/install.log
Update Complete!

  Current Pi-hole version is v4.3.2-401-g082cfb2
  Current AdminLTE version is v4.3.2-406-gee8b8932
  Current FTL version is vDev-06b5f3c

I just did a version update, and am still getting the same error message. No changes to lists, either.

  [i] Storing downloaded domains in new gravity database...
  [✗] Unable to fill gravity table in database /etc/pihole/gravity_temp.db
  Error: /tmp/tmp.840OCxWfPw.gravity line 101404: expected 2 columns of data but found 3
  [✓] Cleaning up stray matter
  [✓] DNS service is running
  [✓] Pi-hole blocking is Enabled

  Unable to complete update, please contact Pi-hole Support

Output of pihole -v:

Pi-hole version is v4.3.2-401-g082cfb2 (Latest: v4.3.2)
AdminLTE version is v4.3.2-412-g1dc359d (Latest: v4.3.2)
FTL version is vDev-ff840d0 (Latest: v4.3.1)

Can you do head -n 101400 /tmp/tmp.840OCxWfPw.gravity | tail 10 and we can look at the file in that area.

Here’s the output:

zanox-affiliate.de,5
zanox.com,5
zantracker.com,5
zde-affinity.edgecaching.net,5
zedo.com,5
zeepmedia.com,5
zencudo.co.uk,5
zenkreka.com,5
zenzuu.com,5
zeus.developershed.com,5

This doesn’t look like three columns?..

I’m not fully sure those line numbers are accurate. But I don’t see anything that would trigger the response. Patconn87, do you still have that file? Could we get a copy of it?

Can you run this command on the file, if it still exists?

awk -F',' '{print $3}' /tmp/tmp.840OCxWfPw.gravity

I believe you would need to do “head -n 101410” to get the last 10 lines you wanted with tail (101401 - 101410)

1 Like

Yes, you are quite right, mine grabbed the first 101400 lines and then trimmed the last 10 of that. So 101390 to 101400.

Can you run this command on the file, if it still exists?

awk -F’,’ ‘{print $3}’ /tmp/tmp.840OCxWfPw.gravity

The file still exists on my system; however, when I run that command, I don’t get any output (just a bunch of blank lines).

I believe you would need to do “head -n 101410” to get the last 10 lines you wanted with tail (101401 - 101410)

Here is the output of the corrected command:

zeusclicks.com,5
zintext.com,5
zmedia.com,5
zumobi.com,5005.free-counter.co.uk,6    <-----
006.free-adult-counters.x-xtra.com,6
006.free-counter.co.uk,6
007.free-counter.co.uk,6
007.go2cloud.org,6
0075-7112-e7eb-f9b9.reporo.net,6
008.free-counter.co.uk,6

I think I spotted the trouble spot. I can save a copy of the file elsewhere on my system so it doesn’t get purged. Where would you like me to upload it so you can check it over? It’s about 26MB.

I don’t think we need the file. I think the problem is one list ends (and might not have a newline at the end?) and the next list is concatenating.

The output should be

zmedia.com,5 
zumobi.com,5
005.free-counter.co.uk,6

Edit: These are the two lists that are involved.

https://s3.amazonaws.com/lists.disconnect.me/simple_ad.txt
https://hosts-file.net/ad_servers.txt

Edit2: Yep,

https://s3.amazonaws.com/lists.disconnect.me/simple_ad.txt

has no newline at the end of the file.

dschaper@Mariner-10:~/Projects/Pi-hole/core$ curl https://s3.amazonaws.com/lists.disconnect.me/simple_ad.txt
...
zeus.developershed.com
zeusclicks.com
zintext.com
zmedia.com
zumobi.comdschaper@Mariner-10:~/Projects/Pi-hole/core$ 

Ahhhhh, got it. I’ll have to disable that list, update gravity, and see what happens (I’ll check my ‘production’ PiHole, too, to see if it’s reporting the same issue). Isn’t that one of the default lists that’s shipped with PiHole?

Yes

We are actually all affected by this, however, we were not seeing this as gravity hides SQL warnings and only complains when SQL errors are encountered.

I see (when not muting the errors):

/tmp/tmp.biRZucgQ9D.gravity:101445: expected 2 columns but found 3 - extras ignored

Have a look at the last part.

Now to the point why do our Pi-holes behave differently. What is your local SQLite3 version?

# sqlite3 --version
3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a820

I will likely enable the output of warnings as well?

This should get fixed by

However, we should still compare our sqlite3 versions.