search

LycheeOrg / Lychee

A great looking and easy-to-use photo-management-system you can run on your server, to manage and share photos.
https://lycheeorg.github.io/
MIT License
3.31k stars 296 forks source link

Migration fails partway (after update) #1513

Closed devincowan closed 2 years ago

devincowan commented 2 years ago

Detailed description of the problem [REQUIRED]

I was previously on version 040007 (in the configs table of the database, I think that equates to v4.0.7?) Did the following:

git pull
composer install --no-dev
php artisan migrate

the migration ran into the following issue:

... Migrating: 2021_12_04_181200_refactor_models Info: Checking consistency of DB

In Connection.php line 712: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list' (SQL: select id, owner_id from albums where owner_id not in (select id from users))

In Connection.php line 368: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list'

Steps to reproduce the issue

I haven't reproduced it myself. Not sure if cloning v4.0.7, uploading a bunch of images, and then running the update/migration would reproducibly cause this?

Screenshots Screen Shot 2022-09-08 at 2 15 44 PM

Output of the diagnostics [REQUIRED]

Diagnostics
-----------
Error: sorting_photos_col not set in database
Error: sorting_albums_col not set in database
Error: Wrong property for image_overlay_type, expected exif or desc or date or none, got takedate.
Warning: You may experience problems when uploading a photo of large size. Take a look in the FAQ for details.
Warning: You may experience problems when uploading a photo of large size. Take a look in the FAQ for details.
Warning: You may experience problems when uploading a photo of large size or handling many/large albums. Take a look in the FAQ for details.
Warning: zend.assertions is disabled although Lychee is in debug mode. For easier debugging code generation for assertions should be enabled.
Info: Latest version of PHP is 8.1
Warning: Default timezone not properly set; you might experience strange results when importing photos without explicit EXIF timezone

System Information
------------------
Lychee Version (git):            master (60cb9f2) -- Could not get commits
DB Version:                      4.4.0

composer install:                --no-dev
APP_ENV:                         local
APP_DEBUG:                       true

System:                          Linux
PHP Version:                     8.0.22
PHP User agent:                  Lychee/4 (https://lycheeorg.github.io/)
Timezone:                        UTC
Max uploaded file size:          2M
Max post size:                   8M
Max execution time:              30
MySQL Version:                   10.5.15-MariaDB-0+deb11u1

exec() Available:                yes
Imagick Available:               1
Imagick Enabled:                 1
Imagick Version:                 1691
GD Version:                      2.3.3

Config Information
------------------
version:                         040400
check_for_updates:               1
sorting_Photos_col:              taken_at
sorting_Photos_order:            DESC
sorting_Albums_col:              min_taken_at
sorting_Albums_order:            DESC
imagick:                         1
skip_duplicates:                 1
small_max_width:                 0
small_max_height:                360
medium_max_width:                1920
medium_max_height:               1080
lang:                            en
layout:                          1
image_overlay_type:              takedate
default_license:                 none
compression_quality:             90
full_photo:                      1
delete_imported:                 0
Mod_Frame:                       0
Mod_Frame_refresh:               30
landing_page_enable:             0
landing_owner:             
landing_title:                   TITLE
landing_subtitle:                Ask if it's beautiful
landing_facebook:                
landing_flickr:                  
landing_twitter:                 
landing_instagram:               
landing_youtube:                 
landing_background:              dist/cat.jpg
thumb_2x:                        1
small_2x:                        0
medium_2x:                       0
site_title:                      TITLE
site_copyright_enable:           0
site_copyright_begin:            2019
site_copyright_end:              2022
public_search:                   0
additional_footer_text:          
display_social_in_gallery:       0
public_recent:                   0
recent_age:                      1
SL_enable:                       0
SL_for_admin:                    0
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:                    0
share_button_visible:            0
has_ffmpeg:                      1
import_via_symlink:              0
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:         0
devincowan commented 2 years ago 
$ php artisan migrate:status
+------+-------------------------------------------------------------+-------+
| Ran? | Migration                                                   | Batch |
+------+-------------------------------------------------------------+-------+
| Yes  | 2014_10_12_000000_create_users_table                        | 1     |
| Yes  | 2018_08_03_110935_create_albums_table                       | 1     |
| Yes  | 2018_08_03_110936_create_photos_table                       | 1     |
| Yes  | 2018_08_03_110942_create_configs_table                      | 1     |
| Yes  | 2018_08_03_111324_create_logs_table                         | 1     |
| Yes  | 2018_08_10_134924_move_settings                             | 6     |
| Yes  | 2018_08_15_102039_move_albums                               | 1     |
| Yes  | 2018_08_15_103716_move_photos                               | 1     |
| Yes  | 2018_10_30_135411_sharing                                   | 1     |
| Yes  | 2019_02_21_114356_create_pages_table                        | 1     |
| Yes  | 2019_02_21_114408_create_page_contents_table                | 1     |
| Yes  | 2019_06_21_180451_create_sym_links_table                    | 1     |
| Yes  | 2019_09_28_171753_config_fix                                | 3     |
| Yes  | 2019_09_28_190822_photos_fix                                | 3     |
| Yes  | 2019_10_01_add_livephoto_cols                               | 3     |
| Yes  | 2019_10_02_1400_config_map_display_public                   | 3     |
| Yes  | 2019_10_03_214750_frame_refresh_in_sec                      | 3     |
| Yes  | 2019_10_06_1400_config_map_providers                        | 3     |
| Yes  | 2019_10_06_152017_add_force_32bit_ids                       | 3     |
| Yes  | 2019_10_07_0900_config_map_include_sub_albums               | 3     |
| Yes  | 2019_10_09_233402_config_map_mod                            | 3     |
| Yes  | 2019_10_11_093442_config_check_update_every                 | 3     |
| Yes  | 2019_12_02_2100_config_exiftool                             | 3     |
| Yes  | 2019_12_15_0700_add_share_button_visible_option             | 3     |
| Yes  | 2019_12_15_1000_config_check_update_every_cat_fix           | 3     |
| Yes  | 2019_12_25_0600_config_exiftool_ternary                     | 3     |
| Yes  | 2020_01_018_2300_config_import_via_symlink                  | 4     |
| Yes  | 2020_01_04_1200_config_has_ffmpeg                           | 3     |
| Yes  | 2020_01_26_1200_config_public_sorting                       | 4     |
| Yes  | 2020_01_28_133201_composer_update                           | 4     |
| Yes  | 2020_02_14_0600_location_decoding                           | 4     |
| Yes  | 2020_03_11_124417_increase_length_photo_type                | 4     |
| Yes  | 2020_03_17_200000_unhide_configs                            | 4     |
| Yes  | 2020_04_19_122905_bump_version                              | 5     |
| Yes  | 2020_04_22_155712_bump_version040002                        | 5     |
| Yes  | 2020_04_29_000250_bump_version040003                        | 5     |
| Yes  | 2020_05_12_114228_rss                                       | 6     |
| Yes  | 2020_05_12_161427_bump_version040005                        | 6     |
| Yes  | 2020_05_19_174233_config_prefer_available_xmp_metadata      | 6     |
| Yes  | 2020_05_26_135052_bump_version040006                        | 6     |
| Yes  | 2020_06_04_104605_config_editor_enabled                     | 7     |
| Yes  | 2020_07_11_104605_config_lossless_optimization              | 7     |
| Yes  | 2020_07_11_184605_update_licences                           | 7     |
| Yes  | 2020_07_26_085322_config_swipe_tolerance                    | 7     |
| Yes  | 2020_07_29_132731_config_local_takestamp                    | 7     |
| Yes  | 2020_08_21_123622_add_smart_tag_album_cols                  | 7     |
| Yes  | 2020_10_09_130043_bump_version040007                        | 7     |
| Yes  | 2020_10_15_104504_add_log_max_num_line                      | 8     |
| Yes  | 2020_10_15_161346_sort_image_per_album                      | 8     |
| Yes  | 2020_11_12_183345_config_password_url_param_for_smart_album | 8     |
| Yes  | 2020_11_19_231553_bump_version040008                        | 8     |
| Yes  | 2020_12_12_203153_migrate_admin_user                        | 9     |
| Yes  | 2020_12_12_203831_create_web_authn_tables                   | 9     |
| Yes  | 2020_12_18_162100_bump_version040009                        | 9     |
| Yes  | 2020_12_18_162155_add_nsfw_album                            | 9     |
| Yes  | 2020_12_18_163800_bump_version040010                        | 9     |
| Yes  | 2020_12_24_022307_bump_version040100                        | 9     |
| Yes  | 2020_12_26_153220_nested_set_for_albums                     | 9     |
| Yes  | 2021_01_09_163715_remove_max_min_takestamps                 | 9     |
| Yes  | 2021_01_12_122546_bump_version040200                        | 9     |
| Yes  | 2021_01_18_103729_add_album_cover                           | 9     |
| Yes  | 2021_01_20_113912_bump_version040201                        | 9     |
| Yes  | 2021_01_24_231904_fix-rotation                              | 9     |
| Yes  | 2021_01_27_085903_config_map_display_direction              | 9     |
| Yes  | 2021_01_30_111736_display_takedate                          | 9     |
| Yes  | 2021_02_12_222948_config_upload_processing_limit            | 9     |
| Yes  | 2021_02_13_132245_bump_version040202                        | 9     |
| Yes  | 2021_02_18_232639_config_public_photos_hidden               | 9     |
| Yes  | 2021_03_03_175555_config_remove_image_overlay               | 9     |
| Yes  | 2021_04_17_135924_bump_version040300                        | 9     |
| Yes  | 2021_05_02_174300_add_filesize_raw_col                      | 9     |
| Yes  | 2021_05_12_185726_bump_version040301                        | 9     |
| Yes  | 2021_05_13_140700_refactor_size_variants                    | 9     |
| Yes  | 2021_05_16_171615_bump_version040302                        | 9     |
| Yes  | 2021_05_25_160600_post_revert_fixes                         | 9     |
| Yes  | 2021_05_31_201000_convert_filesize_to_bigint                | 9     |
| Yes  | 2021_06_01_181900_refactor_timestamps_anew                  | 9     |
| Yes  | 2021_06_01_182000_bump_version040304                        | 9     |
| Yes  | 2021_06_06_151613_fix-takedate                              | 9     |
| Yes  | 2021_06_23_105939_create_notifications_table                | 9     |
| Yes  | 2021_06_30_121651_add_email_to_users_table                  | 9     |
| Yes  | 2021_06_30_122229_config_new_photos_notification            | 9     |
| Yes  | 2021_07_19_134617_bump_version040305                        | 9     |
| Yes  | 2021_10_27_133121_fix_confidentiality                       | 9     |
| Yes  | 2021_11_16_162058_bump_version040306                        | 9     |
| Yes  | 2021_12_03_201242_bump_version040400                        | 9     |
| No   | 2021_12_04_181200_refactor_models                           |       |
| No   | 2022_01_13_183131_bump_version040500                        |       |
| No   | 2022_01_16_181337_optimize_tables                           |       |
| No   | 2022_02_02_203008_filesize_size_variants                    |       |
| No   | 2022_02_22_194700_fix_sorting_config                        |       |
| No   | 2022_04_06_091900_drop_objectionable_indices                |       |
| No   | 2022_04_13_094611_add_track_short_path_to_album_table       |       |
| No   | 2022_04_16_170724_add_missing_indices                       |       |
| No   | 2022_04_16_174503_bump_version040501                        |       |
| No   | 2022_04_18_150400_add_index_for_delete                      |       |
| No   | 2022_04_18_174417_fix_live_photo_short_path                 |       |
| No   | 2022_07_13_174800_permission_test                           |       |
| No   | 2022_07_24_102214_bump_version040502                        |       |
| No   | 2022_08_03_184746_add_zip_options                           |       |
| No   | 2022_08_06_205701_bump_version040503                        |       |
| No   | 2022_08_06_210757_bump_version040600                        |       |
| No   | 2022_08_27_103010_drop_page_support                         |       |
| No   | 2022_08_27_110209_drop_admin_user_config                    |       |
| Yes  | 2064_12_25_0000_generate_installed_log                      | 4     |
+------+-------------------------------------------------------------+-------+
devincowan commented 2 years ago

It seems like renameTables() part of 2021_12_04_181200_refactor_models migration got run, because 

> show tables;
+---------------------------+
| Tables_in_lychee          |
+---------------------------+
| albums                    |
| albums_tmp                |
| base_albums               |
| configs                   |
| logs                      |
| logs_tmp                  |
| migrations                |
| notifications             |
| page_contents             |
| page_contents_tmp         |
| pages                     |
| pages_tmp                 |
| photos                    |
| photos_tmp                |
| size_variants             |
| sym_links                 |
| tag_albums                |
| user_album                |
| user_base_album           |
| users                     |
| users_tmp                 |
| web_authn_credentials     |
| web_authn_credentials_tmp |
+---------------------------+
23 rows in set (0.001 sec)

You can see that the "..._tmp" tables exist

So I either need help reverting the partially run migration, or I need help completing the migration :( Would really appreciate it if you have a solution! Thanks...

ildyria commented 2 years ago

Not sure if cloning v4.0.7, uploading a bunch of images, and then running the update/migration would reproducibly cause this?

Difficult because v4.0.7 was running on php7.4.

Can you check the content of the tables with _tmp and without ? Can you check the structure of the tables with _tmp and without ?

In theory, from the migration file https://github.com/LycheeOrg/Lychee/blob/master/database/migrations/2021_12_04_181200_refactor_models.php you should be safe by just dropping the associated tables without _tmp and rename the ones with _tmp to without.

I strongly advise you to do a back up before doing that. Otherwise, If you could always zip your db and send it to us so we can try to load it locally and see what is breaking in the migration. :)

devincowan commented 2 years ago

@ildyria thanks for taking a look! All of the "_tmp" tables had my data and all of the other tables were empty. So I took your advice. Then re-ran the migration and everything went fine.

Not sure what happened, but I really appreciate your help! Also, I LOVE Lychee--hope to find a way to contribute at some point!

j1b3n commented 1 year ago

Hello, I have the same issue and not really sure what i am supposed to do with the https://github.com/LycheeOrg/Lychee/blob/master/database/migrations/2021_12_04_181200_refactor_models.php file. Could you explain me a bit pls ? Actually i try to migrate my DB from 4.4.0 to 4.5.3

thx a lot !

devincowan commented 1 year ago

@j1b3n My process was:

  1. backup database
  2. dropping the associated tables without _tmp
  3. rename the ones with _tmp (removing the _tmp)
  4. deleting the entry in the migrations table that corresponds to the migration that created issues
  5. Running migrations

But that was pretty specific to my case. as @ildyria recommended, you can check and see if all the "_tmp" tables have data and none of the normal table names have any. That would be the use case for the above 5 steps...

j1b3n commented 1 year ago

Hey, ty for ur awesome advices, I just figured out wher was my issue. In fact I had an sql duplicate entry error, just had to delete a log in my db and all was ok !!

See u

Le 30 sept. 2022 à 18:34, Devin Cowan @.***> a écrit :

 @j1b3n My process was:

backup database dropping the associated tables without _tmp rename the ones with _tmp (removing the _tmp) deleting the entry in the migrations table that corresponds to the migration that created issues Running migrations But that was pretty specific to my case. as @ildyria recommended, you can check and see if all the "_tmp" tables have data and none of the normal table names have any. That would be the use case for the above 5 steps...

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.