Bulk Add CSV does not handle "file://" URLs as a source

diamondsw commented 6 months ago

Server Software about: title: "TestBooru" theme: "danbooru2"

versions: shimmie: "2.10.0-beta2-20240116-5cc1a6f" schema: 21 php: "8.2.7" db: "pgsql PostgreSQL 11.22 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit" os: "Linux 6bed8b85390c 5.10.0-21-amd64 #1 SMP Debian 5.10.162-1 (2023-01-21) x86_64" server: "Unit/1.31.1"

extensions: core: ["admin","alias_editor","bbcode","comment","download","et","ext_manager","four_oh_four","handle_pixel","help_pages","image","index","media","mime","replace_file","setup","static_files","system","tag_edit","tag_list","upgrade","upload","user","user_config","view"] extra: ["auto_tagger","autocomplete","bulk_actions","bulk_add_csv","emoticons_list","et_server","favorites","home","pools","post_titles","rating","regen_thumb","relationships","rule34","tag_categories","tag_tools","tagger_xml"] handled_mimes: ["image/jpeg","image/gif","image/png","image/webp"]

stats: images: 65 comments: 0 users: 2

media: memory_limit: "32MB" disk_use: "127GB" disk_total: "197GB"

thumbnails: engine: "convert" quality: 75 width: 190 height: 300 scaling: 100 mime: "image/jpeg"

Client Software (please complete the following information)

Device: Mac Desktop
Browser: Safari

What steps trigger this bug

Create a CSV for the "Bulk Add CSV" extension, with a "file://" URL in the Source column
Import this via the admin interface

What did you expect to happen? Images to be added with associated tags, with the source link set to be a file URL to a local filesystem object.

What actually happened? All imports are marked as "failed" with the error "Invalid source URL". However, the result is even weirder - everything partially succeeded.

Thumbnails were generated for all posts
Tags were properly imported for all posts
The "file://" URLs are imported and are as expected on the Source link (!)
Images were not imported; "broken image" placeholders on all posts, and no images in the filesystem (subfolders created in images/, but all of them empty)
Ratings were not set on any posts - probably because it was a column past the source URL

Manually uploading an image works, so it's not permissions, and removing the offending "file://" URLs results in the import working as expected (just without any sources).

Why are you using file:// URLs in the first place? I'm using the tagging features to organize media on my drives (music, comics, etc), and when I find what I'm looking for, the source link can open up the original item. Yep, completely abusing software for a purpose it's not intended!

shish commented 6 months ago

"Invalid source URL" comes from the rule34 extension, which has a bunch of random odds-and-ends that one particular site requested, but aren't expected to work or even make sense for anybody else - so disabling that should get rid of the exception at least. (may need to edit data/config/extensions.conf.php directly, since that's a hidden extension)

That said, even with an exception in the middle of the upload process, images shouldn't end up half-uploaded...

diamondsw commented 6 months ago

With the latest build and the "hidden" rule34 extension removed, the import "succeeds", but it stripped the colon from "file:///mypath/", turning it into "file///mypath/" (which obviously doesn't work).

shish commented 6 months ago

That is weird, it was working for me :S Are you able to check your database directly, and see if what's stored in the database is correct? (Trying to narrow down if this is being stripped during upload or during display)

diamondsw commented 6 months ago

Heh, after going into the database and confirming the data there is fine (it is), I discovered the web page has prepended an "http://" before the "file://", making it: <br>Source:<a href='http://file:///Volumes/... (whitespace removed for posting here).

shish commented 6 months ago

Ah, this was a theme-specific issue in ways that I hadn't spotted (I checked that TagEditTheme::format_source() was fixed and there were no overrides for that - but the danbooru theme added a different override at a higher level)

diamondsw commented 6 months ago

Sorry to reopen this, but I ran into it again this evening, but worse (SQL errors). I also reproduced it on a fresh/empty copy of the booru that I had backed up previously. I can provide both the self-contained backup as well as the import directory contents if needed.

13759.jpg ([REDACTED])... failed:
Invalid source URL
31045.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
36322.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
37646.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
81082.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(6) is not present in table "tags".
102638.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
107613.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(6) is not present in table "tags".
121364.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
125877.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
131535.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(27) is not present in table "tags".
139945.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
141547.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
145666.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(71) is not present in table "tags".
192030.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
192032.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
266304.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(124) is not present in table "tags".
321392.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
336563.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
394373.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
464880.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
479806.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(154) is not present in table "tags".
527425.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
573204.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
576324.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
608878.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
629929.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
690109.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
695897.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
770772.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
854395.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
897400.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
909726.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
963614.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
973877.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
991389.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(27) is not present in table "tags".
995871.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1046069.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1060059.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1071633.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1078515.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(25) is not present in table "tags".
1079149.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1084431.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(25) is not present in table "tags".
1115595.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1234390.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(241) is not present in table "tags".
1262636.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1292777.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(192) is not present in table "tags".
1316116.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1333351.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1369896.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(27) is not present in table "tags".
1384885.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1410598.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(29) is not present in table "tags".
1442430.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1501788.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1610953.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1642342.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1652844.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1661485.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1672397.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
1687837.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1765706.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1802415.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
1854170.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1859774.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(211) is not present in table "tags".
1872547.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
1952881.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
1972381.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2000515.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2091491.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2091512.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2091609.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2104690.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2122329.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2142516.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(30) is not present in table "tags".
2143251.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2209822.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(102) is not present in table "tags".
2263607.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(180) is not present in table "tags".
2332038.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2335857.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2342169.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2368631.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2370569.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2399306.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2426838.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
2426844.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(427) is not present in table "tags".
2426861.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(427) is not present in table "tags".
2426865.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(427) is not present in table "tags".
2426879.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(427) is not present in table "tags".
2542225.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2573529.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2605140.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2640514.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(57) is not present in table "tags".
2672583.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2672584.jpg ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(437) is not present in table "tags".
2675790.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(3) is not present in table "tags".
2682329.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2702663.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(1) is not present in table "tags".
2723573.png ([REDACTED])... failed:
SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "image_tags" violates foreign key constraint "image_tags_tag_id_fkey" DETAIL: Key (tag_id)=(433) is not present in table "tags".

Running the latest Docker image; system info:

about:
  title: "TestBooru"
  theme: "danbooru2"
  url: "http://[REDACTED]/index.php?q="

versions:
  shimmie: "2.11.0-alpha-20240122-a7c50a0"
  schema: 21
  php: "8.2.7"
  db: "pgsql PostgreSQL 11.22 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit"
  os: "Linux 418b9e20a2a1 6.1.0-17-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.69-1 (2023-12-30) x86_64"
  server: "Unit/1.31.1"

extensions:
  core: ["admin","alias_editor","bbcode","comment","download","et","ext_manager","four_oh_four","handle_pixel","help_pages","image","index","media","mime","replace_file","setup","static_files","system","tag_edit","tag_list","upgrade","upload","user","user_config","view"]
  extra: ["auto_tagger","autocomplete","bulk_add_csv","emoticons_list","et_server","favorites","home","pools","post_titles","rating","regen_thumb","relationships","rule34","tag_categories","tag_tools","tagger_xml"]
  handled_mimes: ["image/jpeg","image/gif","image/png","image/webp"]

stats:
  images: 0
  comments: 0
  users: 2

media:
  memory_limit: "32MB"
  disk_use: "126GB"
  disk_total: "197GB"

thumbnails:
  engine: "convert"
  quality: 75
  width: 190
  height: 300
  scaling: 100
  mime: "image/jpeg"

diamondsw commented 5 months ago

FYI, I did a few more tests, and this seems to be a regression on non-standard URL handling:

If I leave the source URLs as "file:///foo/bar" then the import fails, regardless of which files/tags/URLs
If I remove all source URLs then all files import properly
If I replace all URLs with an http URL (i.e. "https://www.google.com/") then all files import properly with the source link intact

Hope this helps!

shish commented 5 months ago

Looks like two problems happening at the same time there:

the first image fails with "invalid source URL"
latter images fail because they were using the same tags as the first image... but the first image failed, and so its tags were never saved to the database

Re: "Invalid source URL" - that is an expected side-effect of enabling the rule34 extension (that particular site has requested that any non-http source URLs be banned)

diamondsw commented 5 months ago

Re: "Invalid source URL" - that is an expected side-effect of enabling the rule34 extension (that particular site has requested that any non-http source URLs be banned)

Smacks forehead. Of course that wasn't in my backup image, and I forgot it despite it being in this very issue thread.

shish commented 5 months ago

Separated the other part into #1020 :3

diamondsw commented 5 months ago

Might need another issue split off from this. Ran into two semi-related problems:

Any changes to the active extensions will silently add rule34 back into the mix. Since it's hidden, there's no way of knowing this has happened.
Thanks to the rule34 extension being reactivated, attempting to edit a post's tags failed - and catastrophically. Not only did it fail with an internal error, but the booru crashed and refused to restart. I had to restore from backup (fortunately I had just made one a few minutes earlier).

On a booru instance that previously bulk-added such URLs properly, attempting to subsequently edit the post (e.g. change tags) results in:

Internal Error

Message: Invalid source URL

Version: 2.11.0-alpha (on 8.2.7)

Stack Trace:

#0 /app/core/send_event.php(161): Shimmie2\Rule34->onSourceSet()
#1 /app/ext/tag_edit/main.php(229): Shimmie2\send_event()
#2 /app/core/send_event.php(161): Shimmie2\TagEdit->onImageInfoSet()
#3 /app/ext/view/main.php(82): Shimmie2\send_event()
#4 /app/core/send_event.php(161): Shimmie2\ViewPost->onPageRequest()
#5 /app/index.php(91): Shimmie2\send_event()
#6 {main}

Although after this error occurred it appears that the booru died and cannot be restarted via docker compose down && docker compose up -d. Nothing too interesting in docker logs, other than perhaps:

/app/.docker/entrypoint.sh: Stopping Unit daemon after initial configuration...
2024/02/05 16:28:08 [notice] 28#28 process 32 exited with code 0
/app/.docker/entrypoint.sh: Waiting for control socket to be removed...
2024/02/05 16:28:08 [notice] 28#28 process 33 exited with code 0
2024/02/05 16:28:08 [notice] 40#40 app process 42 exited with code 0
2024/02/05 16:28:08 [alert] 40#40 sendmsg(13, -1, -1, 2) failed (32: Broken pipe)
2024/02/05 16:28:08 [notice] 40#40 app process 41 exited with code 0
2024/02/05 16:28:08 [alert] 40#40 sendmsg(13, -1, -1, 2) failed (32: Broken pipe)
2024/02/05 16:28:08 [notice] 28#28 process 40 exited with code 0

I don't pretend to know the background of the rule34 extension and how it's intertwined with the code, but its nature as a hidden quasi-required chunk of code seems like a problem.

shish commented 5 months ago

Any changes to the active extensions will silently add rule34 back into the mix

Argh, that should be fixed by 88530f5f6 (HTML checkboxes have a value of "on" or null, I had set hidden extensions to be "off", which was interpreted the same as "on"...)

shish / shimmie2

Bulk Add CSV does not handle "file://" URLs as a source #1015