Missing entries in statistics table causes error only when logged in

[samotar]

Lychee version

v7.10.0

Did you check the latest Lychee version?

Yes, I did

Which PHP version are you using?

PHP 8.4

Detailed description of the problem

Disclaimer: This post has been written entirely by me, a human. Some of the troubleshooting that lead to my findings were carried out AI-Assisted (using Sonnet 5.5 Thinking)

Sharing in case it may be helpful.
I noticed that when logged in with my own user, for 2 of my albums, none of their photos were shown. ("Nothing to see here")
Without being logged in, they were shown as expected. This has been the case for quite a while, even way back in Lychee 6, but only now have I gotten around to troubleshoot it.

Browser console error

AxiosError: Request failed with status code 500

Detailed server response (APP_DEBUG=true)

App\Http\Resources\Models\PhotoRatingResource::fromModel(): Argument #1 ($stats) must be of type App\Models\Statistics, null given, called in /usr/www/users/samotar/lychee/app/Http/Resources/Models/PhotoResource.php on line 107

SQL statement exposing the affected photos

SELECT p.id, p.title FROM photos p LEFT JOIN statistics s ON s.photo_id = p.id WHERE s.photo_id IS NULL;

A total of 67 photos were missing entries in table statistics. All of them were part of the 2 affected albums. (Both albums contained more than 67 photos each, for what it's worth)

SQL statement adding the missing rows

INSERT INTO statistics (photo_id) SELECT p.id FROM photos p LEFT JOIN statistics s ON s.photo_id = p.id WHERE s.photo_id IS NULL;

For good measure

php artisan optimize:clear

This seems to have fixed the error. The photos are now being displayed regardless of being logged in or not. I'm not sure what had caused the rows to be missing in the first place. The only conspicuous thing I have found was that the affected photos and their albums have been created around the same time.

But I do think it was rather weird to experience that error only when logged in. (Impression: Anonymous users are able to see photos that privileged users can't see)

Steps to reproduce the issue

1. Manage to upload photos with missing entries in table statistics somehow (or delete rows manually, I guess)
2. Access an album that the photos are part of while logged in as any user (owner or not)
3. Access the same album without any login (anonymous)

Diagnostics [REQUIRED]

Lychee Version (release):                  7.10.0
DB Version:                                7.10.0
Docker:                                    false
composer install:                          --no-dev
APP_ENV:                                   production
APP_DEBUG:                                 false
APP_URL:                                   set
APP_DIR:                                   default
LOG_VIEWER_ENABLED:                        false
System:                                    Linux
PHP Version:                               8.4.26
PHP User agent:                            Lychee/6 (https://lycheeorg.dev/)
int size                                   64 bits 8
Timezone:                                  Europe/Berlin
Max uploaded file size:                    15M
Max post size:                             15M
Chunk size:                                15.00 MB
Max execution time:                        240
MySQL Version:                             10.11.18-MariaDB-0+deb12u1
exec() Available:                          yes
Imagick Available:                         1
Imagick Enabled:                           yes
Imagick Version:                           1691
GD Version:                                bundled (2.1.0 compatible)
Number of foreign key:                     55 found.

		

Space
Configuration

version:                                   071000
check_for_updates:                         0
sorting_photos_col:                        title
sorting_photos_order:                      ASC
sorting_albums_col:                        max_taken_at
sorting_albums_order:                      DESC
imagick:                                   1
skip_duplicates:                           0
small_max_width:                           0
small_max_height:                          360
medium_max_width:                          1920
medium_max_height:                         1080
lang:                                      en
image_overlay_type:                        desc
default_license:                           CC-BY-NC-ND-4.0
compression_quality:                       90
grants_full_photo_access:                  1
delete_imported:                           1
mod_frame_enabled:                         1
mod_frame_refresh:                         10
thumb_2x:                                  1
small_2x:                                  1
medium_2x:                                 1
landing_page_enable:                       0
search_public:                             1
hide_version_number:                       1
recent_age:                                10
grants_download:                           0
photos_wraparound:                         0
map_display:                               1
zip64:                                     1
map_display_public:                        1
map_provider:                              RRZE
map_include_subalbums:                     0
update_check_every_days:                   3
has_exiftool:                              0
share_button_visible:                      1
import_via_symlink:                        0
has_ffmpeg:                                0
location_decoding:                         0
location_decoding_timeout:                 30
location_show:                             0
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
local_takestamp_video_formats:             .avi|.mov|.mp4|.mkv|.webm
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
nsfw_banner_override:                      
map_display_direction:                     1
album_subtitle_type:                       takedate
upload_processing_limit:                   4
new_photos_notification:                   0
zip_deflate_level:                         6
default_album_protection:                  private
allow_username_change:                     1
album_decoration:                          all
album_decoration_orientation:              row
auto_fix_orientation:                      1
random_album_id:                           highlighted
use_last_modified_date_when_no_exif_date:  0
layout:                                    square
date_format_photo_thumb:                   j M Y G:i
date_format_photo_overlay:                 j M Y G:i:s T
date_format_sidebar_uploaded:              j M Y G:i:s T
date_format_sidebar_taken_at:              j M Y G:i:s T
date_format_hero_min_max:                  F Y
date_format_hero_created_at:               j M Y G:i:s T
date_format_album_thumb:                   M Y
upload_chunk_size:                         0 B
nsfw_banner_blur_backdrop:                 0
search_result_limit:                       300
search_minimum_length_required:            4
photo_layout_justified_row_height:         320
photo_layout_masonry_column_width:         300
photo_layout_grid_column_width:            250
photo_layout_square_column_width:          200
photo_layout_gap:                          12
display_thumb_album_overlay:               always
display_thumb_photo_overlay:               never
default_album_thumb_aspect_ratio:          1/1
use_album_compact_header:                  1
back_button_enabled:                       1
back_button_text:                          Return to example.org
SA_random_thumbs:                          1
login_required:                            0
enable_unsorted:                           1
enable_highlighted:                        1
enable_recent:                             1
enable_on_this_day:                        1
login_required_root_only:                  1
dark_mode_enabled:                         1
show_keybinding_help_popup:                0
show_keybinding_help_button:               1
disable_se_call_for_actions:               0
enable_se_preview:                         0
slideshow_timeout:                         5
keep_original_untouched:                   1
low_quality_image_placeholder:             1
timeline_photos_enabled:                   0
timeline_photos_public:                    1
timeline_photos_granularity:               day
timeline_photos_order:                     taken_at
timeline_photos_layout:                    square
timeline_photos_pagination_limit:          200
timeline_albums_enabled:                   1
timeline_albums_public:                    1
timeline_albums_granularity:               year
timeline_left_border_enabled:              1
timeline_photo_date_format_year:           Y
timeline_photo_date_format_month:          M Y
timeline_photo_date_format_day:            j M Y
timeline_photo_date_format_hour:           g:i
timeline_album_date_format_year:           Y
timeline_album_date_format_month:          M Y
timeline_album_date_format_day:            j M
hide_nsfw_in_smart_albums:                 1
hide_nsfw_in_frame:                        1
hide_nsfw_in_map:                          1
hide_nsfw_in_search:                       1
search_photos_layout:                      square
hide_nsfw_in_rss:                          1
hide_nsfw_in_timeline:                     1
number_albums_per_row_mobile:              3
cache_enabled:                             0
cache_ttl:                                 86400
low_number_of_shoots_per_day:              10
medium_number_of_shoots_per_day:           50
high_number_of_shoots_per_day:             100
maintenance_processing_limit:              5000
disable_recursive_permission_check:        1
exif_disabled_for_all:                     0
file_name_hidden:                          0
client_side_favourite_enabled:             0
default_old_settings:                      0
default_expert_settings:                   0
default_all_settings:                      0
disable_thumb_download:                    1
disable_thumb2x_download:                  1
disable_small_download:                    0
disable_small2x_download:                  0
disable_medium_download:                   0
disable_medium2x_download:                 0
temporary_image_link_enabled:              0
temporary_image_link_when_logged_in:       0
temporary_image_link_when_admin:           0
temporary_image_link_life_in_seconds:      86400
secure_image_link_enabled:                 0
SA_override_visibility:                    0
TA_override_visibility:                    0
enable_colour_extractions:                 0
colour_extraction_driver:                  farzai
home_page_default:                         gallery
flow_enabled:                              1
flow_public:                               0
flow_base:                                 
flow_max_items:                            10
flow_strategy:                             auto
flow_include_sub_albums:                   0
flow_include_photos_from_children:         0
flow_open_album_on_click:                  0
flow_display_open_album_button:            0
flow_highlight_first_picture:              1
flow_min_max_enabled:                      1
flow_display_statistics:                   1
flow_compact_mode_enabled:                 1
flow_image_header_enabled:                 1
flow_image_header_cover:                   cover
flow_image_header_height:                  24
flow_carousel_enabled:                     1
flow_carousel_height:                      6
date_format_flow_published:                M j, Y, g:i:s A e
date_format_flow_min_max:                  F Y
flow_blur_nsfw_enabled:                    1
hide_nsfw_in_flow:                         1
skip_duplicates_early:                     1
sync_delete_missing_photos:                0
sync_delete_missing_albums:                0
sync_dry_run:                              1
gallery_header_enabled:                    1
gallery_header_logged_in_enabled:          1
gallery_header:                            
gallery_header_bar_transparent:            1
gallery_header_bar_gradient:               1
import_via_url_forbidden_localhost:        1
import_via_url_forbidden_local_ip:         1
import_via_url_require_https:              1
import_via_url_forbidden_ports:            1
is_scroll_to_navigate_photos_enabled:      1
is_swipe_vertically_to_go_back_enabled:    1
timeline_page_enabled:                     0
timeline_quick_access_date_format_year:    Y
timeline_quick_access_date_format_month:   M
timeline_quick_access_date_format_day:     j M
timeline_quick_access_date_format_hour:    h M, g:i
details_links_enabled:                     0
details_links_public:                      0
photo_thumb_tags_enabled:                  0
hide_nsfw_in_tag_albums:                   1
hide_nsfw_in_tag_listing:                  1
sorting_pinned_albums_col:                 created_at
sorting_pinned_albums_order:               DESC
copyright_text:                            
watermark_enabled:                         0
watermark_photo_id:                        
watermark_public:                          1
watermark_logged_in_users_enabled:         0
renamer_enabled:                           0
renamer_enforced:                          0
renamer_enforced_before:                   0
renamer_enforced_after:                    0
renamer_photo_title_enabled:               1
renamer_album_title_enabled:               1
extract_zip_on_upload:                     1
webshop_currency:                          EUR
webshop_default_description:               No description provided.
webshop_allow_guest_checkout:              1
webshop_terms_url:                         
webshop_privacy_url:                       
webshop_default_price_cents:               0
webshop_default_license:                   personal
webshop_default_size:                      medium
enable_untagged:                           1
photos_pagination_limit:                   500
deduplicate_pinned_albums:                 1
desktop_dock_full_transparency_enabled:    0
mobile_dock_full_transparency_enabled:     0
import_via_url_block_redirect:             1
enable_smart_album_per_owner:              0
rating_enabled:                            1
rating_public:                             0
rating_show_only_when_user_rated:          0
rating_show_avg_in_details:                1
rating_photo_view_mode:                    hover
rating_show_avg_in_photo_view:             0
rating_album_view_mode:                    hover
rating_show_avg_in_album_view:             1
album_layout:                              grid
albums_per_page:                           200
albums_pagination_ui_mode:                 infinite_scroll
albums_infinite_scroll_threshold:          10
photos_per_page:                           100
photos_pagination_ui_mode:                 infinite_scroll
photos_infinite_scroll_threshold:          10
shared_albums_visibility_default:          show
enable_unrated:                            0
enable_1_star:                             0
enable_2_stars:                            0
enable_3_stars:                            0
enable_4_stars:                            0
enable_5_stars:                            0
enable_best_pictures:                      1
best_pictures_count:                       100
enable_propagate_unlock_option:            0
enable_photo_details_always_open:          0
enable_my_rated_pictures:                  1
enable_my_best_pictures:                   1
my_best_pictures_count:                    50
gps_coordinate_display:                    1
gps_coordinate_display_public:             1
photos_star_visibility:                    editor
album_enhanced_display_enabled:            0
album_header_size:                         half_screen
album_header_landing_title_enabled:        0
watermark_optout_disabled:                 0
raw_download_enabled:                      0
contact_form_enabled:                      0
contact_form_security_question:            
contact_form_security_answer:              
contact_form_custom_consent_required:      0
contact_form_custom_privacy_url:           
contact_form_header:                       
contact_form_headline:                     
contact_form_contact_method:               
contact_form_message_label:                
contact_form_message_answer:               
contact_form_custom_consent_text:          
contact_form_custom_submit_button_text:    
contact_form_thank_you_message:            
landing_background_landscape_mode:         latest_album_cover
landing_background_portrait_mode:          latest_album_cover
search_colour_distance:                    30
default_user_trust_level:                  trusted
guest_upload_trust_level:                  check
download_archive_chunked:                  0
download_archive_chunk_size:               300
download_archive_drop_extension_enabled:   1
is_embed_enabled:                          0
use_admin_dashboard:                       1
timeline_albums_root_enabled:              0
disable_swipe_effect:                      0
close_upload_on_success:                   0
folder_upload_enabled:                     1
folder_upload_max_depth:                   5
site_logo:                                 
landing_logo:                              
landing_header_logo:                       
ai_vision_enabled:                         0
ai_vision_face_enabled:                    0
ai_vision_face_permission_mode:            restricted
ai_vision_face_selfie_confidence_threshold: 0.8
ai_vision_face_person_is_searchable_default: 1
ai_vision_face_allow_user_claim:           1
ai_vision_face_overlay_enabled:            1
ai_vision_face_overlay_default_visibility: visible
ai_vision_face_recognition_warning:        1
sm_card_album_source:                      header
sm_card_image_url:                         
ai_vision_nsfw_enabled:                    0
ai_vision_nsfw_preset:                     default
ai_vision_nsfw_check_block_action:         block
ai_vision_nsfw_monitor_block_action:       moderate
ai_vision_nsfw_trust_but_verify_block_action: moderate
ai_vision_nsfw_trust_block_action:         approve
ai_vision_nsfw_sensitive_album_action:     mark_album
ai_vision_nsfw_sensitive_no_album_action:  skip
ai_vision_nsfw_scan_trusted_users:         0
ai_vision_nsfw_monitor_hide_on_scan:       0
ai_vision_nsfw_trust_but_verify_hide_on_scan: 0
ai_vision_nsfw_trust_hide_on_scan:         0
PA_override_visibility:                    0
PA_override_searchability:                 0
hide_nsfw_in_person_albums:                1
breadcrumb_enabled:                        1
rss_title:                                 
rss_description:                           
rounded_corners_enabled:                   1
album_border_enabled:                      0
selection_border_enabled:                  0
selection_overlay_enabled:                 1
photo_share_card_enabled:                  1
zip_bomb_max_total_size:                   10GB
zip_bomb_max_file_size:                    5GB
zip_bomb_max_entries:                      10000
zip_bomb_max_ratio:                        200
zip_bomb_delete_rejected_file:             1
flags_enabled:                             1
photo_flags_enabled:                       1
cover_id_flag_enabled:                     1
header_id_flag_enabled:                    1
highlighted_flag_enabled:                  1
validated_flag_enabled:                    1
smart_album_flags_enabled:                 1
album_flags_enabled:                       1
public_hidden_flag_enabled:                1
public_visible_flag_enabled:               1
password_flag_enabled:                     1
sensitive_flag_enabled:                    1
primary_color:                             
expert_album_settings:                     0
managed_cache_enabled:                     1
managed_cache_ttl:                         3600
managed_cache_albums_enabled:              1
contact_form_enabled_on_gallery:           0
contact_form_enabled_on_album:             0
contact_form_enabled_for_logged_in:        0
sm_pinterest_url:                          
sm_deviantart_url:                         
sm_tumblr_url:                             
sm_500px_url:                              
sm_pixelfeed_url:                          
sm_discord_url:                            
sm_reddit_url:                             
landing_layout:                            classic
landing_intro_screen_enabled:              1
landing_hero_text_position:                center
landing_hero_text_color:                   
landing_hero_text_opacity:                 100
landing_animation_preset:                  classic_fade
landing_about_enabled:                     0
landing_about_text:                        
landing_featured_items_enabled:            0
landing_featured_items_mode:               automatic
landing_featured_items_count:              24
landing_cta_text:                          
landing_cta_position:                      bottom
landing_cta_shift_type:                    relative
landing_cta_shift_x:                       0
landing_cta_shift_x_direction:             right
landing_cta_shift_y:                       30
landing_cta_shift_y_direction:             up
landing_meridian_explore_offset:           42
landing_meridian_contact_offset:           70
landing_meridian_explore_line_position:    33
landing_meridian_contact_line_position:    67
landing_login_position:                    side
landing_backdrop_opacity:                  0
TA_albums_listing_enabled:                 1
PA_albums_listing_enabled:                 1
video_thumbnail_frame_mode:                middle
video_thumbnail_frame_seconds:             1
photo_ken_burns_on_hover_enabled:          1
photo_ken_burns_on_hover_scale:            50
photo_ken_burns_on_hover_duration:         15
embed_metadata_in_files_enabled:           0
embed_metadata_update_checksum_enabled:    0
title_bucket_mode:                         date_prefix
title_bucket_prefix_length:                1
photo_title_bucket_mode:                   date_prefix
photo_title_bucket_prefix_length:          1
show_cover_of_locked_albums:               0
show_selected_cover_on_locked_albums:      0
timeline_lens_height:                      230
timeline_lens_falloff:                     17
timeline_lens_magnification:               55
photo_viewer_highest_quality_enabled:      0
album_date_scrubber_enabled:               1
size_variant_format:                       original
gallery_password_cookie_lifetime:          30
video_loop_enabled:                        0

Browser & System [REQUIRED]

Firefox 140.16.0esr (64-bit)
Debian unstable (sid)

Please confirm (incomplete submissions will not be addressed)

• I have provided easy and step-by-step instructions to reproduce the bug.
• I understand my bug report will be removed if I haven't met the criteria above.
• I understand that if I am requested to provide more information, I must do so within 14 days or the bug report will be closed.

[ildyria]

Yeah that is a weird one.
Also, the statistics table is supposed to do upsert when opened etc...

INSERT INTO statistics (photo_id) SELECT p.id FROM photos p LEFT JOIN statistics s ON s.photo_id = p.id WHERE s.photo_id IS NULL;

That can easily be solved by creating a single migration which applies this.