DB Migration issues: Invalid path error on certain directories & my-drive homepage wouldn't load

[xd003]

Ever since migrating to the PGroonga database along with all its recent changes, i have noticed that atleast 10% of my directories display this error when trying to open them. The size column displays 0B for such directories. All of the other files and directories show up totally fine. Its happening only in certain directories/paths

db: groonga/pgroonga:latest-debian-16 (previously on postgres 16 latest image which uses debain by default) teldrive: v1.4.11

[divyam234]

Path will be invalid only if its not present.select * from teldrive.get_file_from_path('/test', tguserid) you can run this to see if path is valid replace tguserid with telegram your user id.

[xd003]

select * from teldrive.get_file_from_path('/test', tguserid)

Getting this response from SQL - SQL Error [P0001]: ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] Where: PL/pgSQL function teldrive.get_file_from_path(text,bigint) line 43 at RAISE

Here's the whole log from teldrive when opening that directory if it helps

  2024/07/15 13:30:48 goose: no migrations to run. current version: 20240715001936
15/07/2024 01:30 PM  INFO  Started server http://localhost:8080
15/07/2024 01:32 PM  INFO    {"status": 200, "method": "GET", "path": "/api/files", "query": "page=1&limit=500&order=asc&sort=name&path=%2FMedia%2FAnimated+Movies", "ip": "103.44.106.30", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0", "latency": 0.110719102, "time": "2024-07-15T13:32:59Z"}
15/07/2024 01:33 PM  ERROR  [DB] github.com/divyam234/teldrive/pkg/services/file.go:329 ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] (SQLSTATE P0001)
[38.313ms] [rows:-] WITH "ranked_scores" AS (SELECT "name",count(*) OVER () as total,ROW_NUMBER() OVER (ORDER BY name ASC) AS rank FROM "teldrive"."files" WHERE user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) SELECT *,(select total from ranked_scores limit 1) as total FROM "teldrive"."files" WHERE name >= (SELECT name FROM ranked_scores WHERE rank = 1) AND (user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) ORDER BY "name" LIMIT 500
15/07/2024 01:33 PM  ERROR  record not found
15/07/2024 01:33 PM  INFO    {"status": 404, "method": "GET", "path": "/api/files", "query": "page=1&limit=500&order=asc&sort=name&path=%2FMedia%2FAnimated+Movies%2FMortal+Kombat+Legends+Battle+of+the+Realms+%282021%29+%5Btmdbid-841755%5D", "ip": "103.44.106.30", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0", "latency": 0.03868104, "time": "2024-07-15T13:33:05Z"}
15/07/2024 01:33 PM  ERROR  [DB] github.com/divyam234/teldrive/pkg/services/file.go:329 ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] (SQLSTATE P0001)
[39.331ms] [rows:-] WITH "ranked_scores" AS (SELECT "name",count(*) OVER () as total,ROW_NUMBER() OVER (ORDER BY name ASC) AS rank FROM "teldrive"."files" WHERE user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) SELECT *,(select total from ranked_scores limit 1) as total FROM "teldrive"."files" WHERE name >= (SELECT name FROM ranked_scores WHERE rank = 1) AND (user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) ORDER BY "name" LIMIT 500
15/07/2024 01:33 PM  ERROR  record not found

These are not new files/directories but pretty old and their files are still present on the telegram channel, only started happening after the recent update

[divyam234]

This is fine you have missing folders in database for this path /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] try to check for every folder separetely for same userid.

[DevRush94]

select * from teldrive.get_file_from_path('/test', tguserid)

Getting this response from SQL - SQL Error [P0001]: ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] Where: PL/pgSQL function teldrive.get_file_from_path(text,bigint) line 43 at RAISE

Here's the whole log from teldrive when opening that directory if it helps

2024/07/15 13:30:48 goose: no migrations to run. current version: 20240715001936
15/07/2024 01:30 PM  INFO  Started server http://localhost:8080
15/07/2024 01:32 PM  INFO    {"status": 200, "method": "GET", "path": "/api/files", "query": "page=1&limit=500&order=asc&sort=name&path=%2FMedia%2FAnimated+Movies", "ip": "103.44.106.30", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0", "latency": 0.110719102, "time": "2024-07-15T13:32:59Z"}
15/07/2024 01:33 PM  ERROR  [DB] github.com/divyam234/teldrive/pkg/services/file.go:329 ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] (SQLSTATE P0001)
[38.313ms] [rows:-] WITH "ranked_scores" AS (SELECT "name",count(*) OVER () as total,ROW_NUMBER() OVER (ORDER BY name ASC) AS rank FROM "teldrive"."files" WHERE user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) SELECT *,(select total from ranked_scores limit 1) as total FROM "teldrive"."files" WHERE name >= (SELECT name FROM ranked_scores WHERE rank = 1) AND (user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) ORDER BY "name" LIMIT 500
15/07/2024 01:33 PM  ERROR  record not found
15/07/2024 01:33 PM  INFO    {"status": 404, "method": "GET", "path": "/api/files", "query": "page=1&limit=500&order=asc&sort=name&path=%2FMedia%2FAnimated+Movies%2FMortal+Kombat+Legends+Battle+of+the+Realms+%282021%29+%5Btmdbid-841755%5D", "ip": "103.44.106.30", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:128.0) Gecko/20100101 Firefox/128.0", "latency": 0.03868104, "time": "2024-07-15T13:33:05Z"}
15/07/2024 01:33 PM  ERROR  [DB] github.com/divyam234/teldrive/pkg/services/file.go:329 ERROR: file not found for path: /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] (SQLSTATE P0001)
[39.331ms] [rows:-] WITH "ranked_scores" AS (SELECT "name",count(*) OVER () as total,ROW_NUMBER() OVER (ORDER BY name ASC) AS rank FROM "teldrive"."files" WHERE user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) SELECT *,(select total from ranked_scores limit 1) as total FROM "teldrive"."files" WHERE name >= (SELECT name FROM ranked_scores WHERE rank = 1) AND (user_id = 6362481266 AND status = 'active' AND parent_id in (SELECT id FROM teldrive.get_file_from_path('/Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755]', 6362481266))) ORDER BY "name" LIMIT 500
15/07/2024 01:33 PM  ERROR  record not found

These are not new files/directories but pretty old and their files are still present on the telegram channel, only started happening after the recent update

+1

[xd003]

This is fine you have missing folders in database for this path /Media/Animated Movies/Mortal Kombat Legends Battle of the Realms (2021) [tmdbid-841755] try to check for every folder separetely for same userid.

Just checked many of them, not all folders with 0B size has this issue. Half of them contain files inside them, its just not updated in the outer folder size column for some reason. For the rest folders, i can reproduce this issue with each of them ( maybe like 5% can say). What do i do now then ? Delete all these folders from the web and reupload ?

[divyam234]

Are you able to list files by parentId of the folder?

[xd003]

Are you able to list files by parentId of the folder?

i definitely can list files by parentId, zero no issues with that. This is very same file/directory from above error

[divyam234]

Are you able to list files by parentId of the folder?

i definitely can list files by parentId, zero no issues with that. This is very same file/directory from above error

Are you able to enter inside first folder?

[xd003]

Are you able to enter inside first folder?

Yes works, inside that folder in attached screenshot

[divyam234]

WITH RECURSIVE dir_hierarchy AS (
        SELECT
            root.id,
            root.name,
            root.parent_id,
            0 AS depth,
            '' as path
        FROM
            teldrive.files as root
        WHERE
            root.parent_id = 'root' AND root.user_id = uid

        UNION ALL

        SELECT
            f.id,
            f.name,
            f.parent_id,
            dh.depth + 1 AS depth,
            dh.path || '/' || f.name
        FROM
            teldrive.files f
        JOIN
            dir_hierarchy dh ON dh.id = f.parent_id
        WHERE f.type = 'folder' AND f.user_id = uid
    )
select * from dir_hierarchy;

Can you run this query ans see whether path is listed or not at 3 depth .try for all possible user id if yo have multiple accounts

[xd003]

Can you run this query ans see whether path is listed or not at 3 depth .try for all possible user id if yo have multiple accounts

The problematic paths causing error are not listed with this script. i have always had single user in jwt -> allowed-users. This particular path in web contains 114 folders ( including the single problematic one wherein i cannot enter) while the script only listed 113 folders excluding it

[xd003]

@divyam234 I would like to point a slight difference i noticed. If i roll back to the previous version 1.4.10, i no longer see these problematic directories in the web normally. Only way i can find them is from a search, browsing by their parentID's. In the current version, they are visible on the web but i can't open them as mentioned in this thread

[divyam234]

@xd003 can browse the folder in rclone

[xd003]

@xd003 can browse the folder in rclone

i can view that folder with rclone lsd in its parent folder With rclone ls directly on that folder, it returns -

 ERROR : : error listing: directory not found
 Failed to ls with 2 errors: last error was: directory not found

In rclone mount, it appears as an empty folder

[xd003]

@divyam234 i thought i would just delete one of these problematic directories and recreate it to see if it gets fixed. But that folder is not getting deleted at all, i deleted it from the normal web listing and also after searching for it explicitly and deleting it but it just doesn't get deleted/removed. I have succesfully deleted the file inside it by searching for that folder and deleting the file browsing by parentID

if i try uploading the same folder again, it gives me error - ERROR: duplicate key value violates unique constraint "unique_folder" (SQLSTATE 23505) So I am unable to neither delete nor recreate the folder. Attempting to rename that folder from the web interface also has no effect.

[xd003]

Don't know what's really going on here, randomly nothing would show up on the /my-drive homepage. /recent and /storage show up totally fine.

Teldrive docker doesn't report any error. Thankfully, I had taken a database backup before migrating to pgroonga + teldrive update. Will roll back both the db and teldrive version to older one for now

[SingingDalong]

In my case, the problem occurred when I used the 127.0.0.1 address. I changed the URL address to localhost and the problem was solved.

[xd003]

In my case, the problem occurred when I used the 127.0.0.1 address. I changed the URL address to localhost and the problem was solved.

Good for you, i am running this on a remote server so not sure what would fix it. Uploading still seems to work fine when this happens

[kreuber]

I'm also generally hit by this. When switching to PGroonga I receive this on the main page /my-drive

In the Postgres Log it looks like this:

2024-07-18 09:04:07.262 UTC [769] ERROR:  file not found for path:
2024-07-18 09:04:07.262 UTC [769] CONTEXT:  PL/pgSQL function get_file_from_path(text,bigint) line 43 at RAISE
2024-07-18 09:04:07.262 UTC [769] STATEMENT:  select * from teldrive.get_file_from_path($1, $2)

And the Teldrive log has:

18/07/2024 09:04 AM ERROR   [DB] github.com/divyam234/teldrive/pkg/services/file.go:326 ERROR: file not found for path:  (SQLSTATE P0001)
[53.231ms] [rows:-] select * from teldrive.get_file_from_path('/', xxxxx)
18/07/2024 09:04 AM ERROR   ERROR: file not found for path:  (SQLSTATE P0001)

I also tested the DB with TD 1.4.9, 1.4.10 and 1.4.11 so it seems to be DB related? When switching back to 1.4.9 and Bitnami PG Docker it's fine.

[kreuber]

1.4.12:

[xd003]

1.4.12:

can confirm, i am also facing the same issue even on 1.4.12. /my-drive just shows up empty like earlier

[xd003]

@divyam234 Apart from the above error that /my-drive would just up empty, i can confirm that the original issue ie. certain directories show up with invalid path is still present for me on latest v1.4.12

[divyam234]

@xd003 I can't reproduce it I need dump of folders entries to see where the problem lies.You can dump json or csv of only folders from dbeaver.

[xd003]

@xd003 I can't reproduce it I need dump of folders entries to see where the problem lies.You can dump json or csv of only folders from dbeaver.

I am attaching the dump of folders along with this post. i would also like to mention the /my-drive issue atleast seems random, it shows up at times on its own. At the time of exporting this dump, /my-drive was loading fine for me folders_dump.json

[xd003]

if you need the name of some of the problematic folders explicitly, do lmk

[divyam234]

I can list all directories from your data without issues also the problematic folders. The are no problematic folders as count of dir_hierarchy is equal to no folders i.e 5055.

[divyam234]

You might have messed up data restore process.Do one thing rerun the migrations on new db and restore only data not schema when using pg_restore.

pg_restore --dbname="" --format=directory --no-acl --no-owner -a -n teldrive -t files

Use restore this way -a to copy only data after running migrations and restore users , channels first in order and other tables afterwards or you can disable foriegn key checks for all tables then copy data.

[xd003]

You might have messed up data restore process.Do one thing rerun the migrations on new db and restore only data not schema when using pg_restore.

Just to confirm, did we had to do pg_restore on newly created pgroonga db ? I did take a backup for safety with pg backup but didn't used pg restore, just changed the docker image in compose and recreated the container.

[divyam234]

No its not needed but not sure why its not working for you.

[xd003]

No its not needed but not sure why its not working for you.

I see, i will try restore process as you told and see how it goes ? I am assuming the random my-drive showing empty for me and another user is also part of this db migration issue ? If i directly open any folder it loads fine. Eg my-drive/some_dir

[xd003]

i can confirm that all of the aforemention issues are related to database migration, still unsure what caused it in first place but my old postgres backup with v1.4.9 is working perfectly fine

Will update thread as i have more inputs/findings

[xd003]

After resotoring my backup from postgres, i just changed the docker image to pgroonga and rebuilt the container. I started teldrive only after this and the migrations ran well and it seems to be fine now, not facing those issues atm. Not sure what caused it in first place earlier but closing it for now

[phanirithvij]

Make sure you back up your database first!!

This worked for me for v1.4.13 (updated from 1.4.9 likely, I don't know)

After the update, I noticed a new root entry in the files table. I dropped the one where size was NULL (there were two root rows, and the other one had a valid size). Then, I ran REINDEX DATABASE teldrive; and everything worked fine.

Be careful which root row you drop!

[aniel300]

@phanirithvij how and where do i run REINDEX DATABASE teldrive;

[phanirithvij]

Might be outdated since the db I see has different id format (uuid). But no harm in re-indexing if you don't remove anything so up to you.

I personally used beekeeper-studio and connected to the postgres db localhost:5432 with teldrive user, password provided in the docker compose file, POSTGRES_PASSWORD=***, and teldrive db.

Alternatively you can use docker exec -t teldrive_db psql -U teldrive and do it there but for some reason it didn't work for me.

[aniel300]

am stuck on 1.4.10 because of this. i was banned from their discord so i can't really ask for help. here is my docker compose in case it is of any help and u can help me. (dont know how to share the compose file in here in the right format)

` teldrive: container_name: teldrive hostname: teldrive image: ghcr.io/divyam234/teldrive:1.4.10 #:1.3.0 #1.0.15 depends_on: teldrive_db: condition: service_healthy ports:

• 8080:8080 environment:
• PUID=$PUID
• PGID=$PGID
• TZ=$TIME_ZONE volumes:
• $PERSIST/teldrive/session.db:/session.db:rw

• $PERSIST/teldrive/config.toml:/config.toml restart: unless-stopped

  teldrive_db: hostname: teldrive_db image: groonga/pgroonga:latest-debian-15 container_name: teldrive_db restart: always environment:

• PUID=$PUID
• PGID=$PGID
• TZ=$TIME_ZONE
• POSTGRES_USER=xxx
• POSTGRES_PASSWORD=xxx volumes:
• $PERSIST/teldrive_db:/var/lib/postgresql/data healthcheck: test: ["CMD", "pg_isready", "-U", "xxx"] interval: 10s start_period: 30s`

[aniel300]

sent