[BUG] Server Error while querying duplicates for selection

[TadeSF]

Fider Cloud or Self Hosted Self Hosted v0.22.0

Describe the bug When I try to query for duplicate entries, I get a 500 server error back.

To Reproduce

1. Create a post
2. Create another post
3. Assign it the status duplicate
4. Search for the first post

Expected behavior The first post should appear.

Server Logs

INFO [2024-09-20T09:51:39Z] [WEB] GET [https://fb-delegator.dmun.de/api/v1/posts?query=asd&limit=6](https://fb-delegator.dmun.de/api/v1/posts?query=asd&limit=6%3C/span%3E) started
ERROR [2024-09-20T09:51:39Z] [WEB] Error Trace: 
- app/middlewares/setup.go:133
- goroutine 43 [running]:
runtime/debug.Stack()
    /usr/local/go/src/runtime/debug/stack.go:24 +0x5e
github.com/getfider/fider/app/pkg/errors.Panicked({0x1afe440?, 0xc0005a46c0?})
    /server/app/pkg/errors/errors.go:40 +0xb4
github.com/getfider/fider/app/cmd.routes.WebSetup.func17.1.2()
    /server/app/middlewares/setup.go:133 +0x45
panic({0x1afe440?, 0xc0005a46c0?})
    /usr/local/go/src/runtime/panic.go:770 +0x132
github.com/getfider/fider/app/pkg/dbx.(*Trx).MustRollback(...)
    /server/app/pkg/dbx/dbx.go:309
github.com/getfider/fider/app/pkg/web.(*Context).Rollback(0xc000548750)
    /server/app/pkg/web/context.go:143 +0x8b
github.com/getfider/fider/app/cmd.routes.WebSetup.func17.1(0xc000548750)
    /server/app/middlewares/setup.go:141 +0x33b
github.com/getfider/fider/app/cmd.routes.Session.func13.1(0xc000548750)
    /server/app/middlewares/session.go:21 +0xcc
github.com/getfider/fider/app/cmd.routes.Compress.func9.1(0xc000548750)
    /server/app/middlewares/compress.go:36 +0x1bf
github.com/getfider/fider/app/cmd.routes.CSRF.func8.1(0xc000280570?)
    /server/app/middlewares/security.go:40 +0x8a
github.com/getfider/fider/app/cmd.routes.Secure.func7.1(0xc000548750)
    /server/app/middlewares/security.go:27 +0x1de
github.com/getfider/fider/app/cmd.routes.CatchPanic.func5.1(0xc000413260?)
    /server/app/middlewares/panic.go:20 +0x53
github.com/getfider/fider/app/pkg/web.(*Engine).handle.func1({0x1eec770, 0xc0001580e0}, 0xc0002627e0, {0xc0005a67e0, 0x1, 0x1ee7740?})
    /server/app/pkg/web/engine.go:269 +0x116
github.com/julienschmidt/httprouter.(*Router).Handle.(*Router).saveMatchedRoutePath.func2({0x1eec770, 0xc0001580e0}, 0xc0002627e0, {0x0?, 0x429d3b?, 0x0?})
    /go/pkg/mod/github.com/julienschmidt/httprouter@v1.3.1-0.20200921135023-fe77dd05ab5a/router.go:240 +0x1bc
github.com/julienschmidt/httprouter.(*Router).ServeHTTP(0xc000118480, {0x1eec770, 0xc0001580e0}, 0xc0002627e0)
    /go/pkg/mod/github.com/julienschmidt/httprouter@v1.3.1-0.20200921135023-fe77dd05ab5a/router.go:474 +0x1de
net/http.serverHandler.ServeHTTP({0xc0005d3c20?}, {0x1eec770?, 0xc0001580e0?}, 0x6?)
    /usr/local/go/src/net/http/server.go:3142 +0x8e
net/http.(*conn).serve(0xc00013a240, {0x1eede20, 0xc0004df410})
    /usr/local/go/src/net/http/server.go:2044 +0x5e8
created by net/http.(*Server).Serve in goroutine 12
    /usr/local/go/src/net/http/server.go:3290 +0x4b4
 (app/pkg/errors/errors.go:40)
- github.com/getfider/fider/app/pkg/dbx.(*Trx).MustRollback:309 (app/pkg/errors/errors.go:39)
- failed to rollback transaction (app/pkg/dbx/dbx.go:319)
- driver: bad connection
INFO [2024-09-20T09:51:39Z] [WEB] GET https://fb-delegator.dmun.de/api/v1/posts?query=asd&limit=6 finished with 500 in 115ms (panicked)
WARN [2024-09-20T09:51:39Z] [WEB] window.unhandledrejection: Failed to GET /api/v1/posts?query=asd&limit=6 with body '<empty>'

Database Logs

2024-09-20 10:01:27.871 UTC [1] LOG:  server process (PID 7037) was terminated by signal 11: Segmentation fault
2024-09-20 10:01:27.871 UTC [1] DETAIL:  Failed process was running: 
                    SELECT * FROM ( WITH 
                                                        agg_tags AS ( 
                                                            SELECT 
                                                                    post_id, 
                                                                    ARRAY_REMOVE(ARRAY_AGG(tags.slug), NULL) as tags
                                                            FROM post_tags
                                                            INNER JOIN tags
                                                            ON tags.ID = post_tags.TAG_ID
                                                            AND tags.tenant_id = post_tags.tenant_id
                                                            WHERE post_tags.tenant_id = $1

                                                            GROUP BY post_id 
                                                        ), 
                                                        agg_comments AS (
                                                                SELECT 
                                                                        post_id, 
                                                                        COUNT(CASE WHEN comments.created_at > CURRENT_DATE - INTERVAL '30 days' THEN 1 END) as recent,
                                                                        COUNT(*) as all
                                                                FROM comments 
                                                                INNER JOIN posts
                                                                ON posts.id = comments.post_id
                                                                AND posts.tenant_id = comments.tenant_id
                                                                WHERE posts.tenant_id = $1
                                                                AND comments.deleted_at IS NULL
                                                                GROUP BY post_id
                                                        ),
                                                        agg_votes AS (
                                                                SELECT 

2024-09-20 10:01:27.871 UTC [1] LOG:  terminating any other active server processes
2024-09-20 10:01:27.872 UTC [7034] WARNING:  terminating connection because of crash of another server process
2024-09-20 10:01:27.872 UTC [7034] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2024-09-20 10:01:27.872 UTC [7034] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2024-09-20 10:01:27.874 UTC [7038] WARNING:  terminating connection because of crash of another server process
2024-09-20 10:01:27.874 UTC [7038] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2024-09-20 10:01:27.874 UTC [7038] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2024-09-20 10:01:27.883 UTC [1] LOG:  all server processes terminated; reinitializing
2024-09-20 10:01:27.919 UTC [7040] LOG:  database system was interrupted; last known up at 2024-09-20 09:59:30 UTC
2024-09-20 10:01:27.919 UTC [7041] FATAL:  the database system is in recovery mode
2024-09-20 10:01:28.061 UTC [7040] LOG:  database system was not properly shut down; automatic recovery in progress
2024-09-20 10:01:28.067 UTC [7040] LOG:  redo starts at 0/22E1D68
2024-09-20 10:01:28.067 UTC [7040] LOG:  invalid record length at 0/22E5088: wanted 24, got 0
2024-09-20 10:01:28.067 UTC [7040] LOG:  redo done at 0/22E5050
2024-09-20 10:01:28.070 UTC [7042] FATAL:  the database system is in recovery mode
2024-09-20 10:01:28.099 UTC [1] LOG:  database system is ready to accept connections

Screenshots

[mattwoberts]

Does this happen every time? I'm struggling to recreate anything here

[TadeSF]

Yes, it happens every time. And I just discovered that the "similar posts" search does also not work when you are submitting a new post. On iOS/Safari, someone even gets an error when typing any character:

Do you need anything else from me?

[TadeSF]

Looks like I completely fried the database now. Once I recreated the container I got weird "exited with 139" messages, which – with a little research – indicate SIGSEGV problems (?). I restarted my server afterwards and now the database is not accessible anymore... But I did a manual backup before – now my question would be:

How do I restore a zip backup? I did not find anything in the docs.

[mattwoberts]

@TadeSF I'm not sure how you restore from the zip - it would depend how you did the backup, and what you backed up exactly. Presumably you'll need to copy the backup into the docker container and then restore it from there.

I'm not sure what's causing your issue. I don't think it's a code issue - I've got loads of instances and I can't recreate anything. Your database is not very happy - your original server log shows it segfaulting when you do the search, which is odd.

I wonder if there is a recent post or comment that contains some characters that wasn't sanitised properly so it's tripping up the database.

Also another odd thing I've spotted is that your URL contains some odd text (https://fb-delegator.dmun.de/api/v1/posts?query=asd&limit=6%3C/span%3E) - that bit after limit=6 doesn't look quite right to me, not sure what it's doing there

[TadeSF]

This is the backup I did

[TadeSF]

Yeah, thats also very odd. I suspected special characters or something similar as well... I had umlaute (ä, ü, ö) in tags and ofc. in the posts themselves, but those cannot really be an issue, can they? If it helps I can clean up and redact the backed json files.