Delete Account Fails:

[jlpoolen]

In the administrative interface, I am trying to delete an account and get an error "Server error or API not found"

Steps to reproduce the issue

Steps to reproduce the behavior:

1. Log into the administrative account
2. Select Users
3. Click the "Delete" button on the row of the user you want to delete
4. See error

Screenshots

Output of the diagnostics [REQUIRED]

Diagnostics
    -----------
    Warning: Dropbox import not working. dropbox_key is empty.
    Info: Latest version of PHP is 8.1

    System Information
    ------------------
    Lychee Version (git):            master (75aa06d) - Data not in Cache
    DB Version:                      4.5.1
    
    composer install:                --no-dev
    APP_ENV:                         production
    APP_DEBUG:                       false
    
    System:                          Linux
    PHP Version:                     8
    PHP User agent:                  Lychee/4 (https://lycheeorg.github.io/)
    Timezone:                        America/Los_Angeles
    Max uploaded file size:          500M
    Max post size:                   500M
    Max execution time:              200
    SQLite Version:                  3.38.1
    
    Imagick:                         1
    Imagick Active:                  1
    Imagick Version:                 1808
    GD Version:                      bundled (2.1.0 compatible)

    Config Information
    ------------------
    version:                         040501
    check_for_updates:               0
    sorting_Photos_col:              taken_at
    sorting_Photos_order:            ASC
    sorting_Albums_col:              max_taken_at
    sorting_Albums_order:            ASC
    imagick:                         1
    skip_duplicates:                 0
    small_max_width:                 0
    small_max_height:                360
    medium_max_width:                1920
    medium_max_height:               1080
    lang:                            en
    layout:                          1
    image_overlay_type:              desc
    default_license:                 none
    compression_quality:             90
    full_photo:                      1
    delete_imported:                 0
    Mod_Frame:                       1
    Mod_Frame_refresh:               30
    thumb_2x:                        1
    small_2x:                        1
    medium_2x:                       1
    landing_page_enable:             0
    landing_owner:                   John Poole
    landing_title:                   John Poole
    landing_subtitle:                
    landing_facebook:                
    landing_flickr:                  
    landing_twitter:                 
    landing_instagram:               
    landing_youtube:                 
    landing_background:              dist/cat.jpg
    site_title:                      Salemdata Photos
    site_copyright_enable:           1
    site_copyright_begin:            2022
    site_copyright_end:              2022
    additional_footer_text:          
    display_social_in_gallery:       0
    public_search:                   0
    SL_enable:                       0
    SL_for_admin:                    0
    public_recent:                   0
    recent_age:                      1
    public_starred:                  0
    downloadable:                    0
    photos_wraparound:               1
    map_display:                     0
    zip64:                           1
    map_display_public:              0
    map_provider:                    Wikimedia
    force_32bit_ids:                 0
    map_include_subalbums:           0
    update_check_every_days:         3
    has_exiftool:                    1
    share_button_visible:            0
    import_via_symlink:              0
    has_ffmpeg:                      1
    location_decoding:               0
    location_decoding_timeout:       30
    location_show:                   1
    location_show_public:            0
    rss_enable:                      0
    rss_recent_days:                 7
    rss_max_items:                   100
    prefer_available_xmp_metadata:   0
    editor_enabled:                  1
    lossless_optimization:           0
    swipe_tolerance_x:               150
    swipe_tolerance_y:               250
    local_takestamp_video_formats:   .avi|.mov
    log_max_num_line:                1000
    unlock_password_photos_with_url_param: 0
    nsfw_visible:                    1
    nsfw_blur:                       0
    nsfw_warning:                    0
    nsfw_warning_admin:              0
    map_display_direction:           1
    album_subtitle_type:             oldstyle
    upload_processing_limit:         4
    public_photos_hidden:            1
    new_photos_notification:         1
    legacy_id_redirection:           1

Browser and system

Firefox 100.9.2 on Win7

[nagmat84]

This is odd. You are on the master branch and you have the most recent commit. However, there has been a big re-factoring which changed the whole error handling. In particular, the legacy error message "Server error or API not found" has been nuked, because it did not provide any useful information. Instead, with version 4.5.1 you should either see the original message of the exception thrown in the backend or - if something fails fundamentally - the message "Unknown error".

Do you have CLI access to your server? Can you confirm that your working directory is clean and in-sync with the upstream repository? At the moment I suspect a mismatch between the backend and the frontend version.

[jlpoolen]

Yes, I run my own Gentoo server.

janus /usr/local/src/Lychee # git status
On branch master
Your branch is up to date with 'origin/master'.

nothing to commit, working tree clean
janus /usr/local/src/Lychee # git log -n 1
# gives:
commit 75aa06db3a9b2894018539b7a921240755d6660a (HEAD -> master, origin/master, origin/HEAD)
Author: qwerty287 <80460567+qwerty287@users.noreply.github.com>
Date:   Wed Apr 27 23:41:50 2022 +0200

    Add locale variable (#1291)

    * Add locale variable
    * use App\Facades\Lang;

    Co-authored-by: ildyria <beviguier@gmail.com>
# end of log
janus /usr/local/src/Lychee # git pull
Already up to date.
janus /usr/local/src/Lychee # date
Mon May 23 13:06:54 PDT 2022
janus /usr/local/src/Lychee #

[nagmat84]

Are you sure that the remote GIT repository you are pulling from is our GIT repo? The latest commit in your log is in April, but since then there have been a lot of other commits. The latest was only 4 hours ago by @qwerty287. Nonetheless, your GIT installation says "up-to-date".

What is your configured remote repo? I believe git remote show -v is the command you need. Maybe also git remote list -v. I can never remember that.

[jlpoolen]

Ahah.

janus /usr/local/src/Lychee # git remote get-url --all origin https://github.com/jlpoolen/Lychee.git janus /usr/local/src/Lychee #
I'll pull in your changes and update later today or tomorrow.

Sorry... I guess I created a fork and forgot.

[jlpoolen]

Problem still exists in revised version.

(I was sick, so I was unable to attend within the timeframe promised above.)
I updated my fork on 5/23.

janus /usr/local/src/Lychee # date; git status
Wed May 25 11:59:25 PDT 2022
On branch master
Your branch is up to date with 'origin/master'.

nothing to commit, working tree clean
janus /usr/local/src/Lychee #

janus /usr/local/src/Lychee # git log -n 1
    commit c77a809508f591708e346e8e6c4acdffab55ec2f (HEAD -> master, origin/master, origin/HEAD)
    Merge: 75aa06db 68d60df4
    Author: John Poole <jlpoole56@gmail.com>
    Date:   Mon May 23 13:44:21 2022 -0700

        Merge pull request #1 from LycheeOrg/master

        Catch up

As of 5/25/2022 at 12:00 PM PDT, I was one commit behind, the commit being:

[Fixes](https://github.com/jlpoolen/Lychee/commit/ae955e1687eb456d75b05edc67c6d6f0120cce62) https://github.com/LycheeOrg/Lychee/issues/1330 [(](https://github.com/jlpoolen/Lychee/commit/ae955e1687eb456d75b05edc67c6d6f0120cce62)https://github.com/LycheeOrg/Lychee/pull/1339[)](https://github.com/jlpoolen/Lychee/commit/ae955e1687eb456d75b05edc67c6d6f0120cce62)

* Cleaned up album test file

* Added test for multi-delete (fails as expected)

* Made test more robust to clean up its own mess.

* Fixed multi-delete problem.

@[nagmat84](https://github.com/jlpoolen/Lychee/commits?author=nagmat84)
nagmat84 committed 23 hours ago

I restarted Apache:

Wed May 25 11:54:44 PDT 2022
 * Stopping apache2 ...                                                                                                                                   [ ok ]
 * Starting apache2 ... 
 

Log shows:

2022-05-25 18:55:52 UTC -- error   -- PDOStatement::execute -- 526 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
2022-05-25 18:55:52 UTC -- error   -- App\Models\User::delete -- 88 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed; caused by
2022-05-25 18:55:52 UTC -- error   -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (SQL: delete from "users" where "id" = 3); caused by
2022-05-25 18:55:51 UTC -- error   -- App\Models\User::delete -- 88 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (SQL: delete from "users" where "id" = 3); caused by
2022-05-25 18:55:51 UTC -- error   -- App\Exceptions\ModelDBException::create -- 50 -- Deleting user failed; caused by
2022-05-25 18:55:44 UTC -- error   -- PDOStatement::execute -- 526 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
2022-05-25 18:55:44 UTC -- error   -- App\Models\User::delete -- 88 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed; caused by
2022-05-25 18:55:44 UTC -- error   -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (SQL: delete from "users" where "id" = 3); caused by
2022-05-25 18:55:44 UTC -- error   -- App\Models\User::delete -- 88 -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (SQL: delete from "users" where "id" = 3); caused by
2022-05-25 18:55:43 UTC -- error   -- App\Exceptions\ModelDBException::create -- 50 -- Deleting user failed; caused by
2022-05-25 18:55:34 UTC -- notice  -- App\ModelFunctions\SessionFunctions::log_as_admin -- 183 -- User (jlpoole) has logged in from [REDACTED by jlpoole]

Here's my attempt to look into the database, but I'm not familiar with foreign key constraints in the context of sqlite.

janus /usr/local/src/Lychee # cp /var/db/lychee/lychee.db  /tmp
janus /usr/local/src/Lychee # chmod 777 /tmp/lychee.db
janus /usr/local/src/Lychee # sqlite3 /tmp/lychee.db
SQLite version 3.38.1 2022-03-12 13:37:29
Enter ".help" for usage hints.
sqlite> .tables
albums                 notifications          sym_links
base_albums            page_contents          tag_albums
configs                pages                  user_base_album
logs                   photos                 users
migrations             size_variants          web_authn_credentials
sqlite> select * from users;
0|2022-04-29 11:07:01|2022-04-29 04:09:27.035252|$2y$10$TeWDefR/l3wikMF1qMx6cudP1Y1OTdKiOMyN22A0JGLirNx7W8npu|$2y$10$t59l8nGT5bNsasY4YQH10umqThRqa/pe0wr6GFnHbA7PkJF5kXIk6||0|0|
1|2022-04-29 04:17:02.651531|2022-04-30 05:10:34.262647|JohnPoole|$2y$10$1SlZ8HhaH1FCpBi6vaDgaOkoZ5ON.TpdWhPwBRLrzpOmHqGkoGPpS|jlpoole56@gmail.com|1|0|
2|2022-04-30 00:35:10.219590|2022-05-23 03:42:57.229091|JuliInman|$2y$10$iy9PQVtBXcI4RV5dU4eg2ePIg0LXxc6OnscBkHrNMEh/pLIhctQVq||1|0|
3|2022-05-23 03:38:42.311069|2022-05-23 19:39:00.978705|asarlak|$2y$10$CgVS5r.58BzxuiF1swbzJ.ZeWRkBpYKuXL.PkhM86QBYPlO9mf6Uq||1|1|
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: /tmp/lychee.db
sqlite> .schema users
CREATE TABLE IF NOT EXISTS "users" ("id" integer not null primary key autoincrement, "created_at" datetime not null, "updated_at" datetime not null, "username" varchar not null, "password" varchar, "email" varchar, "may_upload" tinyint(1) not null default '0', "is_locked" tinyint(1) not null default '0', "remember_token" varchar);
CREATE UNIQUE INDEX "users_username_unique" on "users" ("username");
CREATE UNIQUE INDEX "users_email_unique" on "users" ("email");
sqlite>

I'm wondering why there is not text in the log message following "caused by". Might this have to do with the presence of albums owned by the user that perhaps need to be deleted prior to deleting the user? Also, CASCADE on delete looks tempting, but I wonder how that might affect shared ownership, if there is such a principle in this scheme.

[nagmat84]

Problem still exists in revised version.

Too bad. I thought I had found and fixed all places, but obviously I missed some. I will have a look into it the next weekend.

Log shows:

[...]

I'm wondering why there is not text in the log message following "caused by".

That's easy. The log is sorted in anti-chronological order with the most recent entry on the top. So you have to read the log from bottom-to-top. If you see a message which ends with "caused by", you have to read the message one line above. This is the message which caused the line below.

In other words, the error message "User::delete failed" was caused by "Foreign key violation" which makes sense.

Here's my attempt to look into the database, but I'm not familiar with foreign key constraints in the context of sqlite.

[...]

Might this have to do with the presence of albums owned by the user that perhaps need to be deleted prior to deleting the user?

Exactly.

Also, CASCADE on delete looks tempting, but I wonder how that might affect shared ownership, if there is such a principle in this scheme.

Please don't, because this way you might accidentally delete photos which you want to keep. My idea/solution was to re-assign ownership of all albums and photos to the admin user and then delete the user.

But obviously I missed something.

In the long-run it would be nice to offer a choice in the front-end which allows the admin to select whether all photos and albums of the user shall be deleted, too, or whether ownership shall be re-assigned. But I didn't feel like implementing this option into the frontend.

[jlpoolen]

The "works-for-me" will be that I have locked the account. Thanks.

[nagmat84]

Please note that a locked user can still log into Lychee and use it normally. Locked users only cannot change their password. Hence a locked user is not comparable to a deleted user.

[jlpoolen]

Good point. I'll change the password which effectively locks the user out. Thank you.