Ranking filter suddenly not working

[Kapowpenguin1]

I restarted the webui and the ranking filter suddenly seemed to stop working. Even after newly giving an image a rank, after moving it to favorites, the image still doesn't appear under the selected ranking; no images appear

newly setting a ranking, without moving, works fine tho... did the ranking database get reset somehow?...

[AlUlkesh]

It shouldn't, unless this is a fresh installation of course. Did you perhaps move or change directory names?

[Kapowpenguin1]

i moved the directory into a different folder previously, but i didn't use the webui until i moved it back

[AlUlkesh]

You can put this in the extension's folder:

https://github.com/AlUlkesh/debug-helper/blob/main/ranking_list.py

and then start it from there. You should see then what is saved in the db.

If it's a path issue you can try to fix it on the Maintenance tab, "Reapply ranking after moving files".

If that doesn't work for some reason, you can also try "Update directory names in database".

[Kapowpenguin1]

i ran the python code, and it's only reporting the most recent ranking i did to check the system.

1. i just realized, i might've deleted the sqlite3 file previously in trying to diagnose why image browser wasn't starting...
2. "reapply ranking after moving files" fixed the issue i was having after moving an image to favorites

looks like it's time to try my hand at file recovery...

[Kapowpenguin1]

new problem: i seem to have successfully recovered the wib.sqlite3 file i deleted, but now

1. i'm getting some sort of error when i try to reapply ranking 2023-07-16-17:26:49 image_browser.py: curr_path: D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\outputs\txt2img-images Traceback (most recent call last): File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\routes.py", line 394, in run_predict output = await app.get_blocks().process_api( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 1075, in process_api result = await self.call_function( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 884, in call_function prediction = await anyio.to_thread.run_sync( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio\to_thread.py", line 31, in run_sync return await get_asynclib().run_sync_in_worker_thread( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 937, in run_sync_in_worker_thread return await future File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 867, in run result = context.run(func, *args) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\image_browser.py", line 648, in reapply_ranking (alternate_hash,) = alternate_hash TypeError: cannot unpack non-iterable NoneType object

2. update directory names doesn't give me an error, but when i try to use the ranking filter, i get another error: 2023-07-16-17:26:49 image_browser.py: curr_path: D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\outputs\txt2img-images Traceback (most recent call last): File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\routes.py", line 394, in run_predict output = await app.get_blocks().process_api( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 1075, in process_api result = await self.call_function( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 884, in call_function prediction = await anyio.to_thread.run_sync( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio\to_thread.py", line 31, in run_sync return await get_asynclib().run_sync_in_worker_thread( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 937, in run_sync_in_worker_thread return await future File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 867, in run result = context.run(func, args) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\image_browser.py", line 648, in reapply_ranking (alternate_hash,) = alternate_hash TypeError: cannot unpack non-iterable NoneType object 2023-07-16-17:28:55 image_browser.py: get_image_page 2023-07-16-17:28:55 image_browser.py: get_all_images 2023-07-16-17:28:55 image_browser.py: curr_path: D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\log\images 2023-07-16-17:28:55 image_browser.py: cache_exif: 0/1308 cache_aes: 0/1308 0.0 seconds Traceback (most recent call last): File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\routes.py", line 394, in run_predict output = await app.get_blocks().process_api( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 1075, in process_api result = await self.call_function( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 884, in call_function prediction = await anyio.to_thread.run_sync( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio\to_thread.py", line 31, in run_sync return await get_asynclib().run_sync_in_worker_thread( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 937, in run_sync_in_worker_thread return await future File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 867, in run result = context.run(func, args) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\image_browser.py", line 923, in get_image_page filenames = get_all_images(img_path, sort_by, sort_order, keyword, tab_base_tag_box, img_path_depth, ranking_filter, ranking_filter_min, ranking_filter_max, aes_filter_min, aes_filter_max, score_type, exif_keyword, negative_prompt_search, use_regex, case_sensitive) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\image_browser.py", line 809, in get_all_images wib_db.transaction_end(conn, cursor) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\wib\wib_db.py", line 694, in transaction_end cursor.execute("COMMIT") sqlite3.OperationalError: database is locked

i would assume the sqlite3 file got corrupted, but the ranking_list.py program seemed to parse it just fine...

[AlUlkesh]

For the second issue, I suspect it got locked by the first issue occurring.

With a1111 stopped, you can try this:

Copy your database file to some other location. Replace the database with the copied database. This will dereference all processes which were accessing your database file.

[Kapowpenguin1]

ok it turns out the recovered sqlite3 file is a bit more funked up than i was anticipating. i opened the file in an online viewer and

1. all of the file paths reference the old location of the directory, even tho it still references images that had been generated after i moved the directory
2. renamed the sqlite3 file to allow the image browser to generate a new and functional file... but the time stamps in the webui, for the images, don't match the timestamp shown in the file

so the good news is that the file seems relatively up to date, so probably not missing any rankings. the bad news is, it's looking like i'll have to manually correct the paths for the images... somehow.

[Kapowpenguin1]

For the second issue, I suspect it got locked by the first issue occurring.

With a1111 stopped, you can try this:

Copy your database file to some other location. Replace the database with the copied database. This will dereference all processes which were accessing your database file.

i just realized i completely missed the point of what you wanted me to do, but the ranking filters still don't work, even after i restart the webui, update directory names, and set the filter

i also just realized that "update directory names" complains about "from" being empty. i tried using the old paths i got from the online viewer and updating them to the new paths, but that just gave me another error: Traceback (most recent call last): File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\routes.py", line 394, in run_predict output = await app.get_blocks().process_api( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 1075, in process_api result = await self.call_function( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\gradio\blocks.py", line 884, in call_function prediction = await anyio.to_thread.run_sync( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio\to_thread.py", line 31, in run_sync return await get_asynclib().run_sync_in_worker_thread( File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 937, in run_sync_in_worker_thread return await future File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\venv\lib\site-packages\anyio_backends_asyncio.py", line 867, in run result = context.run(func, *args) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\image_browser.py", line 609, in exif_update_dirs wib_db.update_exif_data_mult(cursor, maint_update_dirs_from, maint_update_dirs_to) File "D:\Executables\Stable Diffusion WebUI\stable-diffusion-webui-gpu\extensions\stable-diffusion-webui-images-browser\scripts\wib\wib_db.py", line 592, in update_exif_data_mult cursor.execute(''' sqlite3.IntegrityError: UNIQUE constraint failed: exif_data.file, exif_data.key

[AlUlkesh]

I think there's a mixture of newly generated entries and old entries for some tables now and so it fails to change the old entries since it would lead to the same keys.

I just added some new options:

Try the update again, but only tick "Ranking".

[Kapowpenguin1]

I went through a few paths from one directory, which updated fine, but then i started updating paths from a different directory, and got a "sqlite3.IntegrityError: UNIQUE constraint failed: ranking.file" error. is this something i can fix on my end, or will this take another code update?

[AlUlkesh]

You can download this tool: https://sqlitebrowser.org/dl/

and then open the db with it.

When you go to "Browse Data" and choose the table "ranking", you can check your data and change or delete things.

[Kapowpenguin1]

i'm still getting the "UNIQUE constraint failed" error on some entries. any idea why?

the db browser log is showing "UPDATE "main"."ranking" SET "file"=? WHERE "_rowid_"='964'" if that helps

[AlUlkesh]

That probably means two entries would have the same file name after the update.

For example, if you have two entries:

1. x:\test\picture.png
2. x:\testing\picture.png

And you try updating x:\test to x:\testing that would happen.

You can narrow it down with the db browser:

Go to "Execute SQL" and paste and run this:

select *, count(*) as amount
from ranking
group by name
having count(*) > 1

This will show you, which filenames are the same in different directories.

[Kapowpenguin1]

i'm realizing what happened now. since i moved the webui directory twice, the database seems to have a separate entry for each directory the webui was in. in trying to fix the paths, i corrected x:\picture.png to z:\picture.png, but since i was going down the list indiscriminately correcting the paths, i was also trying to correct a separate entry for y:\picture.png to z:\picture.png too, which would've conflicted with the other z:\picture.png. is there some way to trigger the image browser to create more new entries for the new directory? i'm assuming that's what "reapply ranking" is supposed to do, but that still gives me the "nonetype" error, and "update directory names" seems to want to replace the previous path with a new path, unless that's what i'm supposed to do?

[AlUlkesh]

Yeah, that makes sense.

Without seeing the data, it's hard to suggest something.

You can try the following:

Same example:

1. x:\test\picture.png
2. x:\testing\picture.png

First check with db browser, which entry has the correct ranking.

Let's say in this example 1. has the correct ranking, but 2. is the current path.

Then you could

• Update x:\testing to a new name, for example x:\repair
• Update x:\test to x:\testing

After that the current path name should have the correct ranking. But check first, which version currently has the correct ranking in your case.

[Kapowpenguin1]

you can try the following

sounds like updating the paths is my best bet then. tho that makes me curious, how does image browser usually handle directory changes? it seems like it usually creates a new entry for the directory, but what triggers that? does it happen when the webui is initialized in a new directory?

[AlUlkesh]

First going to an image browser tab or hitting refresh or choosing a directory in the Others tab causes a directory read. It then reads the db/cache to see if there's an entry for it (using the full path name).

If you move a directory "behind the scenes", the browser has no way of knowing that. So when it next reads a directory and can't find the filename (again using the full path), it assumes it's new and creates a new entry in the db/cache.

This is normally not a problem, since all the info is in the pnginfo. But this is not always true for the ranking information.

That's why there is a Maintenance option "Reapply ranking after moving files". This looks at the entries in the database and checks if ranking is not set. If that is the case, it next tries to find a ranking by file name and hash only. If it finds it with a different directory name, it then next checks if the found file currently exists on the drive. If it does, the ranking from the found entry is copied to or created for the current filename. If it does not, the old entry (that has a ranking) is changed so it contains the current filename now.

[Kapowpenguin1]

bc i'm stubborn, i made some modifications to the python scripts to help me debug "reapply ranking". it seems like the script is struggling with a nonetype error bc it's looking for "x:\picture.png" in the filehash table, when the filehash table only has an entry for "y:\picture.png". but the ranking table has entries for both "x:\picture.png" and "y:\picture.png". i'm assuming there's no way to change this behavior to look for "y:\picture.png" entries instead, other than modifying the code?

[AlUlkesh]

Stubbornness and lazyness are two important qualities in a programmer.

Good find. I put in a new function on the mainenance tab:

Try doing that and afterwards "reapply ranking".

[Kapowpenguin1]

unfortunately, that didn't seem to do anything for me. however, i realized that "image_browser.py" had a block of code underneath "if alternate_ranking is not None" for "if alternate_hash is None", so i changed "alternate_ranking" to "alternate_hash" ["if alternate_hash is not None"] and that ended up solving my problem. this issue was a lot more involved than i thought it was going to be, so i appreciate your assistance in helping me with this

[Kapowpenguin1]

i ran the python code, and it's only reporting the most recent ranking i did to check the system.

1. i just realized, i might've deleted the sqlite3 file previously in trying to diagnose why image browser wasn't starting...

2. "reapply ranking after moving files" fixed the issue i was having after moving an image to favorites

looks like it's time to try my hand at file recovery...

oh, but the "move to favorites" issue is still there. i have to manually reapply ranking after moving to favorites, and i'm assuming this isn't intended behavior? but i can open a different case for that if it's necessary

[AlUlkesh]

Ah good, glad to hear you have your rankings back.

I opened this for the move issue: #217

[Kapowpenguin1]

alright. i also submitted a pull request with the change i made