Bug: Relation `fileinfo` not found

[stypr]

Steps to reproduce the behavior

1. Install the latest focalboard with the guides provided in https://www.focalboard.com/download/personal-edition/ubuntu/
   • installed with postgresql
   • focalboard v7.11.3 fresh installation
2. Create account, create board, ...
3. Add any file on Attachment on board
4. Delete file on Attachment

Expected behavior

File needs to be deleted and queries need to be executed as expected.

Current behavior

File does not get deleted. Cards with attachments will never be deleted.

The log show that the relation `fileinfo` does not exist..

$ systemctl status focalboard.service
...
Sep 29 00:16:43 apps focalboard-server[65981]: debug [2023-09-29 00:16:43.394 +09:00] Broadcast block change                   caller="ws/server.go:554" teamID=0 blockID=(block_id) remoteAddr="[::1]:38372"
Sep 29 00:16:43 apps focalboard-server[65981]: debug [2023-09-29 00:16:43.394 +09:00] Broadcast block change                   caller="ws/server.go:554" teamID=0 blockID=(block_id) remoteAddr="[::1]:37072"
Sep 29 00:16:43 apps focalboard-server[65981]: debug [2023-09-29 00:16:43.395 +09:00] POST Blocks                              caller="api/blocks.go:318" block_count=1 disable_notify=false
Sep 29 00:16:43 apps focalboard-server[65981]: debug [2023-09-29 00:16:43.398 +09:00] Block change event                       caller="notifylogger/logger_backend.go:48" action=add board="board" card="test" block_id=(block_id)
Sep 29 00:16:43 apps focalboard-server[65981]: debug [2023-09-29 00:16:43.522 +09:00] attachSession                            caller="api/auth.go:333" single_user=false
Sep 29 00:17:21 apps focalboard-server[65981]: debug [2023-09-29 00:17:21.121 +09:00] attachSession                            caller="api/auth.go:333" single_user=false
Sep 29 00:17:21 apps focalboard-server[65981]: error [2023-09-29 00:17:21.148 +09:00] pq: relation "fileinfo" does not exist   caller="api/api.go:183"
Sep 29 00:17:21 apps focalboard-server[65981]: error [2023-09-29 00:17:21.148 +09:00] API ERROR                                caller="api/api.go:200" code=500 error="pq: relation "fileinfo" does not exist" api=/api/v2/boards/(board_name)/blocks/(block_id)

Edition and Platform

• Edition: Personal Server
• Version: v7.11.3
• Browser and OS: Debian Bookworm

Additional context

I think the focalboard was using fileinfo relation in the past. but now it seems to be replaced to file_info. (Correct me if I'm wrong) I don't really know why fileinfo is still used when file_info exists.

psql log relation list

$ sudo --login --user postgres
postgres@apps:~$ psql
psql (15.3 (Debian 15.3-0+deb12u1))

# \dt
                  List of relations
 Schema |         Name          | Type  |   Owner
--------+-----------------------+-------+------------
 public | blocks                | table | focalboard
 public | blocks_history        | table | focalboard
 public | board_members         | table | focalboard
 public | board_members_history | table | focalboard
 public | boards                | table | focalboard
 public | boards_history        | table | focalboard
 public | categories            | table | focalboard
 public | category_boards       | table | focalboard
 public | db_lock               | table | focalboard
 public | file_info             | table | focalboard
 public | notification_hints    | table | focalboard
 public | preferences           | table | focalboard
 public | schema_migrations     | table | focalboard
 public | sessions              | table | focalboard
 public | sharing               | table | focalboard
 public | subscriptions         | table | focalboard
 public | system_settings       | table | focalboard
 public | teams                 | table | focalboard
 public | users                 | table | focalboard
(19 rows)

[stypr]

I found the solution, but this issue still needs to be fixed.

The problem was that the fileinfo table was originally from Mattermost, and for some reason, it's still referencing fileinfo relation on file deletion.

As for a temporary fix, I've used the migration sql from Mattermost's Migrations -- it works nicely for postgres, but needs to checked if the fix works for mysql.

[Update] Even with the temporary fix, the file doesn't get deleted from the storage. it's just the record that gets deleted from the database..

CREATE TABLE IF NOT EXISTS fileinfo (
    id VARCHAR(26) PRIMARY KEY,
    creatorid VARCHAR(26),
    postid VARCHAR(26),
    createat bigint,
    updateat bigint,
    deleteat bigint,
    path VARCHAR(512),
    thumbnailpath VARCHAR(512),
    previewpath VARCHAR(512),
    name VARCHAR(256),
    extension VARCHAR(64),
    size bigint,
    mimetype VARCHAR(256),
    width integer,
    height integer,
    haspreviewimage boolean
);

CREATE INDEX IF NOT EXISTS idx_fileinfo_update_at ON fileinfo (updateat);
CREATE INDEX IF NOT EXISTS idx_fileinfo_create_at ON fileinfo (createat);
CREATE INDEX IF NOT EXISTS idx_fileinfo_delete_at ON fileinfo (deleteat);
CREATE INDEX IF NOT EXISTS idx_fileinfo_postid_at ON fileinfo (postid);
CREATE INDEX IF NOT EXISTS idx_fileinfo_extension_at ON fileinfo (extension);
CREATE INDEX IF NOT EXISTS idx_fileinfo_name_txt ON fileinfo USING gin(to_tsvector('english', name));

ALTER TABLE fileinfo ADD COLUMN IF NOT EXISTS minipreview bytea;
ALTER TABLE fileinfo ADD COLUMN IF NOT EXISTS content text;

CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON fileinfo USING gin(to_tsvector('english', content));
CREATE INDEX IF NOT EXISTS idx_fileinfo_name_splitted ON fileinfo USING gin (to_tsvector('english'::regconfig, translate((name)::text, '.,-'::text, '   '::text)));

ALTER TABLE fileinfo ADD COLUMN IF NOT EXISTS remoteid varchar(26);
ALTER TABLE fileinfo OWNER TO focalboard;

[stypr]

Found few fixes, someone can do a PR or just fix it locally.

1. split bugfix (/services/store/sqlstore/blocks.go) Without this the deletion never works because fileIDExists and attachmentIDExists will always exist on newer version. (it's not nil, it's "")

func retrieveFileIDFromBlockFieldStorage(id string) string {
    parts := strings.Split(id, ".")
    if len(parts) < 1 {
        return ""
    }
    return parts[0][1:]
}

needs to be changed to

func retrieveFileIDFromBlockFieldStorage(id string) string {
    if id == "" {
        return ""
    }
    parts := strings.Split(id, ".")
    if len(parts) < 1 {
        return ""
    }
    return parts[0][1:]
}

2. change FileInfo to file_info(/services/store/sqlstore/blocks.go)

-                       Update("FileInfo").
-                       Set("DeleteAt", model.GetMillis()).
+                       Update("file_info").
+                       Set("delete_at", model.GetMillis()).

[intersecato]

Hi, @stypr! I tested the fix linked to https://github.com/stypr/focalboard/commit/e13a2d2d08a4cbf76fcefdc5febe62a4c1d43ca0 but the attachment doesn't get deleted from the storage.

(I also made a diff check between our two blocks.go but they are the same)

[stypr]

Hi, @stypr!

I tested the fix linked to https://github.com/stypr/focalboard/commit/e13a2d2d08a4cbf76fcefdc5febe62a4c1d43ca0 but the attachment doesn't get deleted from the storage.

(I also made a diff check between our two blocks.go but they are the same)

Hi,

yes it's not going to be deleted because there doesnt seem to be an implementation for it.. I'm planning to add that some point but as for now the only way is to implement it by yourself..

[stypr]

Hi, @stypr! I tested the fix linked to stypr@e13a2d2 but the attachment doesn't get deleted from the storage.

(I also made a diff check between our two blocks.go but they are the same)

so I checked a bit more but I confirmed that there is no implementation for this. If you have to delete the file, you have a couple of options

1. delete manually
2. implement auto file deletion
3. make a cronjob to regularly delete files by checking delete_at > 0 from file_info table