Error on loading graph: database disk image is malformed

[kbakk]

Hi,

Thanks for developing and maintaining this project 👏

I've been using goatcounter with a small site, and I'm having some issues. I managed to resolve it by dumping the DB and modifying the dump before loading it into sqlite again.

When loading certain dates in the UI I get an error: Error: <errors.errorString Value>

Screenshot

From the logs:

Dec  2 08:34:50 dashboard: ERROR: 1 errors:
HitLists.List hit_stats: zdb.Select: database disk image is malformed
 {http_form="" http_headers="Accept-Encoding: gzip, deflate, br, zstd · Accept-Language: nb-NO,nb;q=0.9,no;q=0.8,nn;q=0.7,en-US;q=0.6,en;q=0.5 · Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7 · Cache-Control: no-cache · Connection: keep-alive · Cookie: key=<removed> · Dnt: 1 · Pragma: no-cache · Referer: http://localhost:443/?hl-period=&period-start=2023-08-16&period-end=2023-08-16&filter=&daily=on · Sec-Ch-Ua-Mobile: ?0 · Sec-Ch-Ua-Platform: \"macOS\" · Sec-Ch-Ua: \"Chromium\";v=\"131\", \"Not_A Brand\";v=\"24\" · Sec-Fetch-Dest: document · Sec-Fetch-Mode: navigate · Sec-Fetch-Site: same-origin · Sec-Fetch-User: ?1 · Upgrade-Insecure-Requests: 1 · User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36" http_host="localhost:443" http_method="GET" http_url="/?hl-period=&period-start=2023-08-16&period-end=2023-08-16&filter=&daily=off"}

When trying to run a db migration (with 2.5.0), I get an error:

$ goatcounter-v2.5.0 db migrate -db sqlite3+./goatcounter.sqlite3 2022-11-17-1-open-at
# (success)
$ goatcounter-v2.5.0 db migrate -db sqlite3+./goatcounter.sqlite3 2023-05-16-1-hits
goatcounter-v2.5.0: zdb.Migrate.Run: running "2023-05-16-1-hits": zdb.Exec: database disk image is malformed

Error seen with goatcounter version: v2.4 (I'm uncertain, but I don't think I've run the DB with any other versions.)

Migrations :

list and pending

$ goatcounter db migrate -db sqlite3+./goatcounter.sqlite3 list
2021-04-01-1-store-warn
2021-04-07-1-billing-anchor
2021-06-27-1-public
2021-12-02-1-languages
2021-12-02-2-language-enable
2021-12-08-1-set-chart-text
2021-12-09-1-email-reports
2021-12-13-2-superuser
2022-01-13-1-unfk
2022-01-14-1-idx
2022-02-16-1-rm-billing
2022-03-06-1-campaigns
2022-10-17-1-campaigns
2022-10-21-1-apitoken-lastused
2022-11-03-1-uncount
2022-11-03-2-ununique
2022-11-05-1-paths-title
2022-11-15-1-correct-hit-stats

$ goatcounter db migrate -db sqlite3+./goatcounter.sqlite3 pending
no pending migrations

I managed to work around this by following these steps:

sqlite3 goatcounter.sqlite3 ".dump" > dump.sql

# Trying to restore from dump gives a hint of the issue
sqlite3 test.sqlite3 < dump.sql
# output:  Runtime error near line 59719: UNIQUE constraint failed: user_agents.ua (19)

# Remove index causing issues
sed -e '/^CREATE UNIQUE INDEX "user_agents#ua" on user_agents(ua);$/ s/^/-- /' dump.sql > dump_update.sql
sqlite3 new_goatcounter.sqlite3 < dump_update.sql
# It works

# Identify duplicates
sqlite3 new_goatcounter.sqlite3 'SELECT ua, COUNT(*) AS cnt
FROM user_agents
GROUP BY ua
HAVING cnt > 1;' 
# output: 
#"~Z (~W NT 10.0; Win64; x64) ~a537.36 ~G ~c67.0.3396.99 ~s537.36|2
#StatusCake_Pagespeed_Indev|5
#facebookexternalhit/1.1 (+http://www.facebook.com/externalhit_uatext.php)|2
# (... 116 rows total)

# Remove duplicates from user_agents table
sqlite3 new_goatcounter.sqlite3 'DELETE FROM user_agents
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM user_agents
    GROUP BY ua
);'

# Recreate the unique index
sqlite3 new_goatcounter.sqlite3 'CREATE UNIQUE INDEX "user_agents#ua" ON user_agents(ua);'

# Now migration succeeds
goatcounter-v2.5.0 db migrate -db sqlite3+./new_goatcounter.sqlite3 2023-05-16-1-hits