misskey
misskey copied to clipboard
misskey-dev
Can't clear cached remote files
💡 Summary
I can't clear cached remote files
🥰 Expected Behavior
I can clear cached remote files
🤬 Actual Behavior
The clear action fails with this log:
INFO 1 [queue clean-remote-files] Deleting cached remote files...
...
ERR 1 [queue objectStorage] failed(QueryFailedError: canceling statement due to statement timeout
at PostgresQueryRunner.query (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async SelectQueryBuilder.loadRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2192:25)
at async SelectQueryBuilder.executeEntitiesAndRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2040:26)
at async SelectQueryBuilder.getRawAndEntities (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:684:29)
at async SelectQueryBuilder.getMany (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:750:25)
at async CleanRemoteFilesProcessorService.process (file:///misskey/packages/backend/built/queue/processors/CleanRemoteFilesProcessorService.js:40:27)
at async Worker.processJob (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:445:28)
at async Worker.retryIfFailed (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:634:24)) id=41 {
job: Job {
queue: Worker {
_events: [Object: null prototype],
_eventsCount: 5,
_maxListeners: undefined,
name: 'objectStorage',
opts: [Object],
closed: false,
connection: [RedisConnection],
qualifiedName: 'shitpost.poridge.club:queue:objectStorage:objectStorage',
keys: [Object],
toKey: [Function (anonymous)],
scripts: [Scripts],
abortDelayController: null,
blockUntil: 0,
drained: true,
extendLocksTimer: Timeout {
_idleTimeout: 7500,
_idlePrev: [TimersList],
_idleNext: [Timeout],
_idleStart: 748694312,
_onTimeout: [AsyncFunction (anonymous)],
_timerArgs: undefined,
_repeat: null,
_destroyed: false,
[Symbol(refed)]: true,
[Symbol(kHasPrimitive)]: false,
[Symbol(asyncId)]: 142965676,
[Symbol(triggerId)]: 142963869
},
limitUntil: 0,
waiting: [Promise],
running: true,
id: 'fad28a33-e6f1-4abe-b2c1-2113e1d4aec7',
processFn: [Function (anonymous)],
blockingConnection: [RedisConnection],
stalledCheckTimer: Timeout {
_idleTimeout: 30000,
_idlePrev: [Timeout],
_idleNext: [Timeout],
_idleStart: 748670411,
_onTimeout: [AsyncFunction (anonymous)],
_timerArgs: undefined,
_repeat: null,
_destroyed: false,
[Symbol(refed)]: true,
[Symbol(kHasPrimitive)]: false,
[Symbol(asyncId)]: 142962897,
[Symbol(triggerId)]: 0
},
asyncFifoQueue: [AsyncFifoQueue],
[Symbol(shapeMode)]: false,
[Symbol(kCapture)]: false
},
name: 'cleanRemoteFiles',
data: {},
opts: {
attempts: 0,
delay: 0,
removeOnFail: true,
removeOnComplete: true,
backoff: undefined
},
id: '41',
progress: 0,
returnvalue: null,
stacktrace: [
'QueryFailedError: canceling statement due to statement timeout\n' +
' at PostgresQueryRunner.query (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)\n' +
' at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n' +
' at async SelectQueryBuilder.loadRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2192:25)\n' +
' at async SelectQueryBuilder.executeEntitiesAndRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2040:26)\n' +
' at async SelectQueryBuilder.getRawAndEntities (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:684:29)\n' +
' at async SelectQueryBuilder.getMany (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:750:25)\n' +
' at async CleanRemoteFilesProcessorService.process (file:///misskey/packages/backend/built/queue/processors/CleanRemoteFilesProcessorService.js:40:27)\n' +
' at async Worker.processJob (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:445:28)\n' +
' at async Worker.retryIfFailed (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:634:24)'
],
attemptsStarted: 1,
attemptsMade: 1,
delay: 0,
repeatJobKey: undefined,
timestamp: 1725793449948,
parentKey: undefined,
parent: undefined,
toKey: [Function: bound ],
scripts: Scripts { queue: [Worker], moveToFinishedKeys: [Array] },
queueQualifiedName: 'shitpost.poridge.club:queue:objectStorage:objectStorage',
processedOn: 1725793449949,
failedReason: 'canceling statement due to statement timeout',
token: 'fad28a33-e6f1-4abe-b2c1-2113e1d4aec7:148194',
finishedOn: 1725793459953
},
e: {
stack: 'QueryFailedError: canceling statement due to statement timeout\n' +
' at PostgresQueryRunner.query (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)\n' +
' at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n' +
' at async SelectQueryBuilder.loadRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2192:25)\n' +
' at async SelectQueryBuilder.executeEntitiesAndRawResults (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2040:26)\n' +
' at async SelectQueryBuilder.getRawAndEntities (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:684:29)\n' +
' at async SelectQueryBuilder.getMany (/misskey/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/typeorm/query-builder/SelectQueryBuilder.js:750:25)\n' +
' at async CleanRemoteFilesProcessorService.process (file:///misskey/packages/backend/built/queue/processors/CleanRemoteFilesProcessorService.js:40:27)\n' +
' at async Worker.processJob (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:445:28)\n' +
' at async Worker.retryIfFailed (/misskey/node_modules/.pnpm/[email protected]/node_modules/bullmq/dist/cjs/classes/worker.js:634:24)',
message: 'canceling statement due to statement timeout',
name: 'QueryFailedError'
}
}
And in PostgreSQL:
2024-09-08 10:59:31.040 UTC [1245943] ERROR: canceling statement due to statement timeout
2024-09-08 10:59:31.040 UTC [1245943] STATEMENT: SELECT "MiDriveFile"."id" AS "MiDriveFile_id", "MiDriveFile"."userId" AS "MiDriveFile_userId", "MiDriveFile"."userHost" AS "MiDriveFile_userHost", "MiDriveFile"."md5" AS "MiDriveFile_md5", "MiDriveFile"."name" AS "MiDriveFile_name", "MiDriveFile"."type" AS "MiDriveFile_type", "MiDriveFile"."size" AS "MiDriveFile_size", "MiDriveFile"."comment" AS "MiDriveFile_comment", "MiDriveFile"."blurhash" AS "MiDriveFile_blurhash", "MiDriveFile"."properties" AS "MiDriveFile_properties", "MiDriveFile"."storedInternal" AS "MiDriveFile_storedInternal", "MiDriveFile"."url" AS "MiDriveFile_url", "MiDriveFile"."thumbnailUrl" AS "MiDriveFile_thumbnailUrl", "MiDriveFile"."webpublicUrl" AS "MiDriveFile_webpublicUrl", "MiDriveFile"."webpublicType" AS "MiDriveFile_webpublicType", "MiDriveFile"."accessKey" AS "MiDriveFile_accessKey", "MiDriveFile"."thumbnailAccessKey" AS "MiDriveFile_thumbnailAccessKey", "MiDriveFile"."webpublicAccessKey" AS "MiDriveFile_webpublicAccessKey", "MiDriveFile"."uri" AS "MiDriveFile_uri", "MiDriveFile"."src" AS "MiDriveFile_src", "MiDriveFile"."folderId" AS "MiDriveFile_folderId", "MiDriveFile"."isSensitive" AS "MiDriveFile_isSensitive", "MiDriveFile"."maybeSensitive" AS "MiDriveFile_maybeSensitive", "MiDriveFile"."maybePorn" AS "MiDriveFile_maybePorn", "MiDriveFile"."isLink" AS "MiDriveFile_isLink", "MiDriveFile"."requestHeaders" AS "MiDriveFile_requestHeaders", "MiDriveFile"."requestIp" AS "MiDriveFile_requestIp" FROM "drive_file" "MiDriveFile" WHERE ((NOT("MiDriveFile"."userHost" IS NULL)) AND ("MiDriveFile"."isLink" = $1)) ORDER BY "MiDriveFile"."id" ASC LIMIT 8
I've tried removing the statement timeout from postgres.ts https://github.com/misskey-dev/misskey/blob/8d23122fd664564dc069ca8e8e337f4d4a1727fc/packages/backend/src/postgres.ts#L215 but it didn't help much. Now It says it cleared the cache in logs but in reality it is not:
INFO 1 [queue clean-remote-files] Deleting cached remote files...
query is slow: SELECT "MiDriveFile"."id" AS "MiDriveFile_id", "MiDriveFile"."userId" AS "MiDriveFile_userId", "MiDriveFile"."userHost" AS "MiDriveFile_userHost", "MiDriveFile"."md5" AS "MiDriveFile_md5", "MiDriveFile"."name" AS "MiDriveFile_name", "MiDriveFile"."type" AS "MiDriveFile_type", "MiDriveFile"."size" AS "MiDriveFile_size", "MiDriveFile"."comment" AS "MiDriveFile_comment", "MiDriveFile"."blurhash" AS "MiDriveFile_blurhash", "MiDriveFile"."properties" AS "MiDriveFile_properties", "MiDriveFile"."storedInternal" AS "MiDriveFile_storedInternal", "MiDriveFile"."url" AS "MiDriveFile_url", "MiDriveFile"."thumbnailUrl" AS "MiDriveFile_thumbnailUrl", "MiDriveFile"."webpublicUrl" AS "MiDriveFile_webpublicUrl", "MiDriveFile"."webpublicType" AS "MiDriveFile_webpublicType", "MiDriveFile"."accessKey" AS "MiDriveFile_accessKey", "MiDriveFile"."thumbnailAccessKey" AS "MiDriveFile_thumbnailAccessKey", "MiDriveFile"."webpublicAccessKey" AS "MiDriveFile_webpublicAccessKey", "MiDriveFile"."uri" AS "MiDriveFile_uri", "MiDriveFile"."src" AS "MiDriveFile_src", "MiDriveFile"."folderId" AS "MiDriveFile_folderId", "MiDriveFile"."isSensitive" AS "MiDriveFile_isSensitive", "MiDriveFile"."maybeSensitive" AS "MiDriveFile_maybeSensitive", "MiDriveFile"."maybePorn" AS "MiDriveFile_maybePorn", "MiDriveFile"."isLink" AS "MiDriveFile_isLink", "MiDriveFile"."requestHeaders" AS "MiDriveFile_requestHeaders", "MiDriveFile"."requestIp" AS "MiDriveFile_requestIp" FROM "drive_file" "MiDriveFile" WHERE ((NOT("MiDriveFile"."userHost" IS NULL)) AND ("MiDriveFile"."isLink" = $1)) ORDER BY "MiDriveFile"."id" ASC LIMIT 8 -- PARAMETERS: [false]
execution time: 27049
DONE 1 [queue clean-remote-files] All cached remote files has been deleted.
The remote files didn't delete, I still have 100GB of files and around 9GB of the info about them in DB. It would be very helpful if you could point what can I do to clear these files.
📝 Steps to Reproduce
- With you admin account go to /admin/files and press the trash icon or use API with POST request to /api/admin/drive/clean-remote-files with your admin credentials
💻 Frontend Environment
* Model and OS of the device(s):
* Browser: Mozilla Firefox 131.0
* Server URL: https://shitpost.poridge.club
* Misskey:2024.9.0
🛰 Backend Environment (for server admin)
* Installation Method or Hosting Service: Docker Compose
* Misskey: 2024.9.0
* Node: v20.16.0
* PostgreSQL: 15.8
* Redis: 7.4.0
* OS and Architecture: Debian GNU/Linux 12 (bookworm)
Do you want to address this bug yourself?
- [ ] Yes, I will patch the bug myself and send a pull request
