Is there a way to force Joplin to run the unused resources cleanup?

tessus · 2019-10-04T00:08:26.011Z

bsouthern:

I’m on Windows, do you know if anyone has run your script successfully on Windows?

I haven't used Windows for 20 years, but I know that Windows now comes with a Linux subsystem. I believe a lot of users on this forum use it to run bash scripts and/or other linux code.

bsouthern:

I assume that I needed to downloaded the sqlite tools so I did and installed the sqlite3.exe tools so I did (you should add this to your dependency list).

Hmm, on Windows executables use the .exe extension, but the script just calls sqlite3. Also, they should be added to the Windows PATH or fully referenced (with an absolute path) in the script.

Maybe the perl script is easier to run on Windows. I am very sorry, but I am not much of a help on Windows.

bsouthern · 2019-10-04T00:10:01.783Z

I actually solved this, I just had to do this: sudo apt install sqlite3 from withing the Windows linux subsystem ubuntu shell.

I do have the list of records now but it is roughly double what I know is orphaned. I have 815 records that were just created but I am getting 1500 reported from the tool. What exactly is it looking at when report the number of records?

tessus · 2019-10-04T00:21:44.820Z

It retrieves the list of ids from the database with the following command

select resource_id from note_resources where is_associated = 0

Are all notes and resources synced on that machine or is there still a sync operation in progress? Laurent also introduced an on demand download of attachments. My script was written before that was implemented. @laurent, if a note has an attachment, but is not downloaded, is there a record in the database that shows that resource as not associated? but in that case it can’t be on the local file system anyway, because it hasn’t been downloaded. so wouldn’t an attempt to delete such a resource via the joplin api fail anyway?

bsouthern · 2019-10-04T00:29:52.801Z

So I use the webclipper to clip a webpage with roughly 1000 svg files. Immediately I realized my error and deleted the note. The resources however are not deleted, they are all local on this machine yes. I can see them in the resources folder there are 815 exactly. The sync started, I quickly cancelled it and extended the sync time to 24 hours to give me time.

So a sync process has started yes. If I look at my sync screen here is what I see:

sync%20problem

A few notes have already synced but if you subtract the first number from the second one it is 800 files.

I would like to know what to do next. I don’t want to let it sync to dropbox because it will be a disaster and files from my dropbox aren’t being deleted either so there won’t be a way to clean them up. I tried changing the setting to Manual however it still starts syncing them to dropbox as soon as I start the sync.

The script gave me roughly 1600 records which seems like almost double but it can’t be correct because it would leave me with just a few hundred resources.

tessus · 2019-10-04T00:39:17.220Z

This is exactly what I don’t understand. Where are these additional 800 resources coming from? How long have you been using Joplin? If you did something similar a while back or deleted notes with many resources, you could of course have 1600 orphaned resources laying around.

Theoretically you can still sync to the target, because after my script will have run, it will remove the files from the target as well at the next sync.

However, I would not run the script, if you do not know that these resources are really orphaned. You have to figure out what’s up with those 800 extra files.

tessus · 2019-10-04T00:45:14.738Z

Something else came to mind, which needs clarification from @laurent.

Let’s say you have 4 revisions of a note. In revision 2 a file was attached, but now in the latest revision the reference to that file is no longer there. So technically that resource is no longer associated with a note. But in the database that resource must not be set to is_associated = 0.

bsouthern · 2019-10-04T00:49:05.118Z

I think I may know what is going on. I changed the synchronization target to file system and it is encrypting the files. This makes sense- roughly double means one encrypted and one unencrypted. Do you know if the the database considers each file and its matching encrypted version to be two different resources?

Regarding the orphaned entries, I don’t think I have 1600 orphaned items. I just restored my database from a jex file last night and I am pretty sure that when you backup and restore from jex it doesn’t copy the orphaned resources. Maybe laurent can confirm this as well but I think from my testing this is how it works.

If this is right then maybe I just need to let the sync finish now and then try the script after.

tessus · 2019-10-04T00:59:02.225Z

Unfortunately these are questions I cannot answer.

I do not use encryption, nor note history, nor do I set anything other than always as the attachment download behavior. Thus I have never had the chance to play around with such a database.

Let's wait for Laurent's input. But whatever you do - do not run the script without making a backup first!

bsouthern · 2019-10-04T01:07:59.728Z

Yes I am looking for ways to recover. I was hoping I could recover from a jex file I made last night and then re import today’s work before the webclip problem by running a second instance of Joplin and but it looks like that option has been disabled or is not implemented yet.

I am letting it sync now and I will take a backup then run it when it is finished.

Thanks for the script!

bsouthern · 2019-10-04T02:19:34.430Z

I have decided the cleanest way to recover is to export a jex backup then import into a new instance then sync again. Brute force but it is the safest method until I gain a better understanding of how Joplin works.

Thanks for your help tonight tessus. I learned a lot.

I would like to see what Laurent says about these issues as well.

laurent · 2019-10-04T07:09:41.974Z

tessus:

Are all notes and resources synced on that machine or is there still a sync operation in progress? Laurent also introduced an on demand download of attachments. My script was written before that was implemented. @laurent, if a note has an attachment, but is not downloaded, is there a record in the database that shows that resource as not associated? but in that case it can’t be on the local file system anyway, because it hasn’t been downloaded. so wouldn’t an attempt to delete such a resource via the joplin api fail anyway?

Even if the physical file is not there, the metadata has been downloaded so for almost everything that deals with resources it shouldn't make a difference. If you delete it, it would succeed as it will delete the metadata.

Let’s say you have 4 revisions of a note. In revision 2 a file was attached, but now in the latest revision the reference to that file is no longer there. So technically that resource is no longer associated with a note. But in the database that resource must not be set to is_associated = 0 .

For that reason, the resource expiration delay is the same as the note revision expiration delay. If revisions are kept for 90 days, resources also will be kept for 90 days.

laurent · 2019-10-04T07:13:09.048Z

Note that you are a bit in dangerous territory here. There are some aspects of Joplin that aren’t well documented and may change from version to version, so be extra careful when you delete resources.

tessus · 2019-10-04T19:20:07.145Z

laurent:

Note that you are a bit in dangerous territory here.

Right, that's the reason for all my questions. I wrote the script before a few things were implemented in Joplin, so I'm not entirely sure, if the script is still safe.

laurent:

Even if the physical file is not there, the metadata has been downloaded so for almost everything that deals with resources it shouldn’t make a difference

Ok, but what does the column is_associated say for an attachment that has not been downloaded? Does it say 0 or 1? IMO it's associated, just not downloaded, but I didn't develop the schema and logic.

laurent:

For that reason, the resource expiration delay is the same as the note revision expiration delay.

Ok, but once again, what does it say in the column is_associated? According to your explanation, it should say 1 during those 90 days (even if the current revision is not associated with that resource), but it will say 0 after 90 days? Somehow this makes no sense. What if the resource was added on day 45 and deleted on day 70? Or do you mean after 90 days of real disassociation? In my example above after 90 days starting at day 70?

laurent · 2019-10-05T21:35:54.923Z

If a resource is associated with any note, is_associated is 1, it it’s not is_associated is 0. From the moment is_associated is 0, a timer starts and after x days the resource is deleted.

tessus · 2019-10-05T22:11:50.537Z

Thanks for the info, but in this case I would like to point out that resources will be deleted incorrectly, when people use different keep note history for settings across devices.

Scenario:

Device 1: keep history for 10 days, revision X on day 3 removes reference to resource
Device 2: keep history for 5 days, resource is deleted on day 8
Device 1: on day 9 one reverts to revision from day 2, resource gone, but reference still exists in note

Oh, wait, maybe not. I think in that case the history might be kept for 5 days only on all devices.

what does the column is_associated say for an attachment that has not been downloaded? Does it say 0 or 1 ?

I still don't know the answer to that one. Does the info in the database make a distinction betweeen not associated and not downloaded?
This is even more critical than the other issue, because the other one could be remedied by taking the timeframe into account.

But I think it is safe to say that my script (and/or @sciurius's perl script) should not be used with an enabled note history, because it currently doesn't take the note history's timeframe into account.

laurent · 2019-10-05T22:22:08.317Z

tessus:

what does the column is_associated say for an attachment that has not been downloaded? Does it say 0 or 1

1 if it's associated with a note

tessus · 2019-10-06T21:30:52.616Z

Ok, this means my script was not safe for environments with note history enabled.

I’ve updated the script so that no resources are deleted which are still in use by revisions.

tessus · 2019-10-06T21:51:02.822Z

@sciurius does your --weed option support note revisions? If not, you might want to update your script as well.

sciurius · 2019-10-07T06:11:29.655Z

Not yet. I’ll look into it when I get back from vacation.
Thanks for reminding.

chentao · 2023-08-12T20:50:11.233Z

There's also https://joplin-utils.rxliuli.com/joplin-batch-web/#getting-started, easy to use.