When upgrading from v3 according to the instructions, all old public URLs (albums, pictures) stop working

[mhellwig]

Detailed description of the problem [REQUIRED]

I have followed the instructions regarding upgrade from v3 and after a bit of slowness on my part re understanding everything I needed to do, I have the current git (as of 2022-09-24) master running and showing my albums correctly.

BUT!

In various places around the web and in emails, I had linked to albums and/or individual photographs in my v3 Lychee gallery. In an extremely annoying detail that wasn't mentioned in the upgrade docs, all these URLs no longer work! Breaking published/linked URLs is something we rightfully chastise owners of big public sites (newspapers, municipal sites, whatever) for, because it leads to link-rot and breaks the web! This should not happen!

Steps to reproduce the issue

Steps to reproduce the behavior:

• Have v3 galleries and publish links to them in other places
• upgrade to v4 according to instructions
• old links are now broken

Output of the diagnostics [REQUIRED]

    Diagnostics
    -------
    Warning: Dropbox import not working. dropbox_key is empty.
    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: Using 32 bit PHP, recommended upgrade to 64 bit

    System Information
    --------------
    Lychee Version (git):            master (c0a90b7) -- Up to date (35 minutes ago).
    DB Version:                      4.6.0

    composer install:                --no-dev
    APP_ENV:                         production
    APP_DEBUG:                       false

    System:                          Linux
    PHP Version:                     8.1.10
    PHP User agent:                  Lychee/4 (https://lycheeorg.github.io/)
    Timezone:                        Europe/Paris
    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:                         040600
    check_for_updates:               0
    sorting_photos_col:              taken_at
    sorting_photos_order:            ASC
    sorting_albums_col:              created_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
    layout:                          1
    image_overlay_type:              desc
    default_license:                 CC-BY-NC-ND-4.0
    compression_quality:             93
    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:                   Michael Hellwig
    landing_title:                   Michael
    landing_subtitle:                Photography
    landing_facebook:                
    landing_flickr:                  
    landing_twitter:                 https://www.twitter.com/das_aug
    landing_instagram:               
    landing_youtube:                 
    landing_background:              
    site_title:                      Lychee v4
    site_copyright_enable:           1
    site_copyright_begin:            2018
    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:               0
    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:                      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
    legacy_id_redirection:           1
    zip_deflate_level:               6

Browser and system

Browser (probably not relevant) Firefox
System: Debian Bullseye

[mhellwig]

addendum:

as far as I can see when looking into the mysql db, the old data is still there in tables with the lychee_ prefix. The links for the albums were constructed according to a pattern https://website/html/Lychee/#id_from_table_lychee_albums_goes_here
whereas the single photos were of the form https://website/html/Lychee/#album_id/#id_from_lychee_photos_table

sssooo.. I'm guessing it should be possible to have some kind of redirector that catches this kind of url and redirects to the new url for the same photo?

[nagmat84]

I joined the project after v3 had already been abandoned and v4 was the current major version. I skimmed the old v3 code and as far as I can tell there should not have been a change in the URLs. Moreover, there have been a lot of users who already have migrated from v3 to v4 and no one has complained about broken URLs.

The URL pattern seems to have always been and still is https://your.domain.tld/<album-id>/<photo-id> given the assumption that Lychee is served from the web root. Hence, the pattern is nearly the same as the one which you report except for the html/Lychee part in the middle which you see.

However, this string can nowhere be found inside Lychee and has never been part of the Lychee routing engine. It rather looks like something which had been added by your setup which had were serving your old v3 installation. If I might guess, your web root pointed to some directory and your v3 installation resided in a sub-directory html/Lychee below that. Are you sure, you haven't changed anything else during the update from v3 to v4 like changing the we root or adding/removing rewrite rules to/from the configuration of your webserver?

Using redirection to solve the problem might be a good idea, but this is something which needs to be added by your webserver config, not Lychee. But first I would suggest checking your current configuration if nothing has changed compared to the previous setup.

[nagmat84]

What webserver are you running?

If you post your webserver config here, the .env file (of course without credentials) and the .htaccess of your web root, maybe we can tell you what you need to change.

[mhellwig]

there is a misunderstanding, sorry. My old lychee installation lived at https://url/html/Lychee and the new one does too. That is not the issue. The issue is that the upgrade from v3 to v4 changed the album and photo ids! So now the URLs are different. The old ids, according to mysql, have datatype bigint(14) unsigned according to

describe lychee_albums;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| id           | bigint(14) unsigned | NO   | PRI | NULL    |       |
| title        | varchar(100)        | NO   |     |         |       |
| description  | varchar(1000)       | YES  |     |         |       |
| sysstamp     | int(11)             | NO   |     | NULL    |       |
| public       | tinyint(1)          | NO   |     | 0       |       |
| visible      | tinyint(1)          | NO   |     | 1       |       |
| downloadable | tinyint(1)          | NO   |     | 0       |       |
| password     | varchar(100)        | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

whereas the new table looks like this:

escribe albums;
+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| id               | char(24)            | NO   | PRI | NULL    |       |
| parent_id        | char(24)            | YES  | MUL | NULL    |       |
| license          | varchar(20)         | NO   |     | none    |       |
| cover_id         | char(24)            | YES  | MUL | NULL    |       |
| track_short_path | varchar(255)        | YES  |     | NULL    |       |
| _lft             | bigint(20) unsigned | NO   | MUL | 0       |       |
| _rgt             | bigint(20) unsigned | NO   |     | 0       |       |
+------------------+---------------------+------+-----+---------+-------+

and indeed the old ids are purely numerical whereas the new ones are numbers and letters and stuff

[mhellwig]

note: it is quite possible that my v3 installation was quite old even in v3 terms. I had been putting off the "finally do an upgrade" for quite some time once I had seen that there were bigger changes when v4 came along.

[nagmat84]

You must not look at the table albums but at the table base_albums. The table base_albums still contains the old IDs in a column legacy_id and the configuration option legacy_id_redirection is enabled in your configuration. So in theory you should be fine.

What do you see in the logs when you try to access an album or photo by the old ID? Normally there should be one out of two possible messages: either xyz requested legacy ID xyz, but redirection is disabled or xyz requested legacy ID xyz, redirecting to abc. Unless there is some error, of course.

[mhellwig]

I'm looking at base_albums now and it indeed has a field legacy_id. So for reference I am checking a specific album, lets use real urls now, it is (in the new site) https://hammer.in-ulm.de/html/Lychee/#bQ-JGf53lx5h2uiaiGiicU_C. In my old Lychee installation, this album had the url https://hammer.in-ulm.de/html/Lychee/#15789100912789. If I look for this album in the old lychee_albums table I get this:

select * from lychee_albums where id like '%15789100912789%';
+----------------+-----------------+-------------------------------------------------------------------------------------------+------------+--------+---------+--------------+----------+
| id             | title           | description                                                                               | sysstamp   | public | visible | downloadable | password |
+----------------+-----------------+-------------------------------------------------------------------------------------------+------------+--------+---------+--------------+----------+
| 15789100912789 | #project52 2020 | posting (and shooting) one photo for every week of 2020. Also see the 2019 and 2018 album | 1578910091 |      1 |       1 |            0 | NULL     |
+----------------+-----------------+-------------------------------------------------------------------------------------------+------------+--------+---------+--------------+----------+
1 row in set (0.001 sec)

Following your message, I should find this album in the base_albums table under the same legacy_id, so let's try that:

select * from base_albums where legacy_id like '%15789100912789%';
Empty set (0.001 sec)

so that didn't work, which explains why the redirect doesn't work either. Let's poke around and shorten the id:

select * from base_albums where legacy_id like '%1578%';
+--------------------------+------------+----------------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+----------+-----------+-------------------+---------------+-----------------+-------------------------+---------+----------+-------------+---------------+
| id                       | legacy_id  | created_at                 | updated_at                 | title           | description                                                                               | owner_id | is_public | grants_full_photo | requires_link | is_downloadable | is_share_button_visible | is_nsfw | password | sorting_col | sorting_order |
+--------------------------+------------+----------------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+----------+-----------+-------------------+---------------+-----------------+-------------------------+---------+----------+-------------+---------------+
| bQ-JGf53lx5h2uiaiGiicU_C | 1578910091 | 2020-01-13 10:08:11.000000 | 2022-09-24 21:43:09.000000 | #project52 2020 | posting (and shooting) one photo for every week of 2020. Also see the 2019 and 2018 album |        0 |         1 |                 1 |             0 |               0 |                       1 |       0 | NULL     | NULL        | NULL          |
+--------------------------+------------+----------------------------+----------------------------+-----------------+-------------------------------------------------------------------------------------------+----------+-----------+-------------------+---------------+-----------------+-------------------------+---------+----------+-------------+---------------+
1 row in set (0.001 sec)

ah-ha! so this is a truncated id. It is the entry not from the id field in the old lychee_albums table but rather the entry that was in the sysstamp column! Let's see if that gets us to a displayable album at https://hammer.in-ulm.de/html/Lychee/#1578910091.

Yes! I do! So. It seems for some reason the upgrade didn't use the id field but rather the sysstamp field when creating the new tables. I'm guessing the same mechanism applies for photos... Let's see for a specific one:

The new url is https://hammer.in-ulm.de/html/Lychee/#bQ-JGf53lx5h2uiaiGiicU_C/hjsemwUOEbxYFyy1-Zrrmqwv. The old url was https://hammer.in-ulm.de/html/Lychee/#15789100912789/16186935866665. So old table lychee_photos:

select * from lychee_photos where id like '%16186935866665%';
+----------------+-------+-------------------------------+--------------------------------------+------+--------+------------+-------+--------+------+-----+----------+-------+----------------------+---------+-------+------------+------+---------------------------------------+----------------+------------------------------------------+--------+
| id             | title | description                   | url                                  | tags | public | type       | width | height | size | iso | aperture | make  | model                | shutter | focal | takestamp  | star | thumbUrl                              | album          | checksum                                 | medium |
+----------------+-------+-------------------------------+--------------------------------------+------+--------+------------+-------+--------+------+-----+----------+-------+----------------------+---------+-------+------------+------+---------------------------------------+----------------+------------------------------------------+--------+
| 16186935866665 | 44    | we had pudding for Halloween! | 7248eb8c961e36bdae25b1fd8d60c1ca.jpg |      |      0 | image/jpeg |  6960 |   4640 | 9 MB | 800 | f/2.0    | Canon | Canon EOS M6 Mark II | 1/60 s  | 22 mm | 1604177409 |    0 | 7248eb8c961e36bdae25b1fd8d60c1ca.jpeg | 15789100912789 | e83efbfa8949bb4c98e5fba3866267ad2e6cc28e |      1 |
+----------------+-------+-------------------------------+--------------------------------------+------+--------+------------+-------+--------+------+-----+----------+-------+----------------------+---------+-------+------------+------+---------------------------------------+----------------+------------------------------------------+--------+

so that works. Checking the new table photos:

select * from photos where legacy_id like '%16186935866665%';
Empty set (0.010 sec)

similar problem. Let's see if it's again a truncated id:

select * from photos where legacy_id like '%16186935%';
+--------------------------+------------+----------------------------+----------------------------+----------+--------------------------+-------+-------------------------------+------+---------+-----------+------------+------+-------+----------------------+------+----------+---------+-------+----------+-----------+----------+---------------+----------+----------------------------+------------------+------------+----------+------------------------------------------+------------------------------------------+-----------------------+-----------------------+---------------------+
| id                       | legacy_id  | created_at                 | updated_at                 | owner_id | album_id                 | title | description                   | tags | license | is_public | is_starred | iso  | make  | model                | lens | aperture | shutter | focal | latitude | longitude | altitude | img_direction | location | taken_at                   | taken_at_orig_tz | type       | filesize | checksum                                 | original_checksum                        | live_photo_short_path | live_photo_content_id | live_photo_checksum |
+--------------------------+------------+----------------------------+----------------------------+----------+--------------------------+-------+-------------------------------+------+---------+-----------+------------+------+-------+----------------------+------+----------+---------+-------+----------+-----------+----------+---------------+----------+----------------------------+------------------+------------+----------+------------------------------------------+------------------------------------------+-----------------------+-----------------------+---------------------+
| hjsemwUOEbxYFyy1-Zrrmqwv | 1618693586 | 2021-04-17 21:06:26.000000 | 2022-09-24 21:42:53.000000 |        0 | bQ-JGf53lx5h2uiaiGiicU_C | 44    | we had pudding for Halloween! | NULL | none    |         0 |          0 | 800  | Canon | Canon EOS M6 Mark II | NULL | f/2.0    | 1/60 s  | 22 mm |     NULL |      NULL |     NULL |          NULL | NULL     | 2020-10-31 21:50:09.000000 | Europe/Paris     | image/jpeg |        0 | e83efbfa8949bb4c98e5fba3866267ad2e6cc28e | e83efbfa8949bb4c98e5fba3866267ad2e6cc28e | NULL                  | NULL                  | NULL                |
+--------------------------+------------+----------------------------+----------------------------+----------+--------------------------+-------+-------------------------------+------+---------+-----------+------------+------+-------+----------------------+------+----------+---------+-------+----------+-----------+----------+---------------+----------+----------------------------+------------------+------------+----------+------------------------------------------+------------------------------------------+-----------------------+-----------------------+---------------------+

it is. So it should be possible to get to it via https://hammer.in-ulm.de/html/Lychee/#1578910091/1618693586. And it indeed is! So. Apparently the code is there and is doing what it is supposed to do with the legacy_id fields. It's just that these fields have been filled with the wrong data.

[mhellwig]

addendum: I can't see that some wrong column was used when migrating from lychee_photos to photos, it apparently just used the first 10 or so digits

[d7415]

In an extremely annoying detail that wasn't mentioned in the upgrade docs, all these URLs no longer work!

Just to be clear, this is clearly described in the v4.5.3 release notes.

it apparently just used the first 10 or so digits

This feels like a 32-bit issue, and you are using 32-bit PHP. There have been a few steps in this transition and I'm not sure off-hand whether it's the last step (the migration to the newest IDs and storing legacy IDs) that will have caused this or an earlier one. I suspect it may have been the initial conversion from what I believe was a string to an integer in v4.

Yes, it's the initial migration from v3 to v4 IDs. Unfortunately I don't think is fixable on a 32-bit system without migrating the v3 IDs directly into the v4.5.3 IDs and modifying the redirection code. This would in turn remove many of the benefits of the new IDs. Alternatively legacy_id could be a string, but that would be non-trivial.

Sorry for the many edits here..

[mhellwig]

In an extremely annoying detail that wasn't mentioned in the upgrade docs, all these URLs no longer work!

Just to be clear, this is clearly described in the v4.5.3 release notes.

That may very well be, OTOH forgive me if I see a link that says "upgrading is easy, just follow these instructions" and indeed go ahead and follow the instructions without digging for release notes for intermediate versions.

it apparently just used the first 10 or so digits

This feels like a 32-bit issue, and you are using 32-bit PHP. There have been a few steps in this transition and I'm not sure off-hand whether it's the last step (the migration to the newest IDs and storing legacy IDs) that will have caused this or an earlier one. I suspect it may have been the initial conversion from what I believe was a string to an integer in v4.

Yes, it's the initial migration from v3 to v4 IDs. Unfortunately I don't think is fixable on a 32-bit system without carefully adjusting earlier migrations to skip a step and migrate directly from v3 IDs to v4.5.3 IDs.

crud. I mean this is a VM (indeed one that I set up ages ago) but it is a i686 debian install, not 64 bit. Getting things to 64 bit would, afaict, entail setting up a new vm and moving all the relevant config and data over.. and frankly that's a bit more than I can attempt right now what with having limited free time (as in about an hour to go, and then that'll be it for the next few months..)

[mhellwig]

hummm. reading through the above-mentioned release notes for 4.5.3 at https://github.com/LycheeOrg/Lychee/releases/tag/v4.5.3, I do have to say I strongly disagree with the implied "this is temporary and might stop working in the future" subtext as expressed in the part where it says

This gives you time to migrate any external reference to the new IDs. We also generate a log entry every time the redirection service is used. This log contains the legacy and new ID as well as information where the request came from. This may help you with the migration of your external links.

That's just... wrong. Cool URIs don't change! https://www.w3.org/Provider/Style/URI I sure hope I am misreading the above and the subtext I get from it isn't actually implied.

[mhellwig]

Dang. Thought that maybe things were easy (are they ever) and tried just changing one example legacy_id to the old long form by doing

update base_albums set legacy_id = 15789100912789 where id = 'bQ-JGf53lx5h2uiaiGiicU_C'

but that didn't lead to a workable redirect. Changing it back to the truncated legacy_id makes it work again though. So I can't even just write a quick script that goes over the ids in the old lychee_ tables and changes the ones in the new tables to be long.

So. uh.. I still don't understand why the old ids need to be truncated on 32bit systems? I mean this has been a 32 bit system all along and it used to have long album- and photo-ids just fine? So why is there a need to truncate them?

[d7415]

So. uh.. I still don't understand why the old ids need to be truncated on 32bit systems? I mean this has been a 32 bit system all along and it used to have long album- and photo-ids just fine? So why is there a need to truncate them?

• v3 stored IDs (which were numbers) as strings.
• v4 before v4.5.3 used integers. In general, this makes sense when storing numbers, but on 32-bit systems the upper limit is 4294967295, hence the 10 digit truncation.
• v4.5.3 uses strings again (the IDs are no longer just numbers), but the legacy redirection assumes the earlier v4 IDs are in use, not the v3 ones.

[mhellwig]

so there should be a way for me (since I haven't uploaded or done anything to the gallery yet) to modify the database migration script in such a way that it just copies over my old ids to the legacy_id column?

[mhellwig]

So. uh.. I still don't understand why the old ids need to be truncated on 32bit systems? I mean this has been a 32 bit system all along and it used to have long album- and photo-ids just fine? So why is there a need to truncate them?

* v3 stored IDs (which were numbers) as strings.

* v4 before v4.5.3 used integers. In general, this makes sense when storing numbers, but on 32-bit systems the upper limit is 4294967295, hence the 10 digit truncation.

* v4.5.3 uses strings again (the IDs are no longer just numbers), but the legacy redirection assumes the earlier v4 IDs are in use, not the v3 ones.

but wait.. describe lychee_albums shows id to be of type bigint(14) unsigned. And describe base_albums gives me legacy_id as bigint(20) unsigned. So these aren't strings as you seem to be saying? Or are they used as strings in the php code?

[mhellwig]

so there should be a way for me (since I haven't uploaded or done anything to the gallery yet) to modify the database migration script in such a way that it just copies over my old ids to the legacy_id column?

and adding to that, that won't help me if I don't also change the php code for the redirector to not assume it is dealing with early v4 legacy_ids. Correct?

[d7415]

so there should be a way for me (since I haven't uploaded or done anything to the gallery yet) to modify the database migration script in such a way that it just copies over my old ids to the legacy_id column?

From what you posted here it looks like your DB will support the 64 bit IDs but your PHP will not.

The redirection code expects an integer, so I suspect it (and the table) would need modifying to use a string instead. (Looks like you caught this while I was typing)

So these aren't strings as you seem to be saying?

hjsemwUOEbxYFyy1-Zrrmqwv looks like a string to me.

[mhellwig]

hjsemwUOEbxYFyy1-Zrrmqwv looks like a string to me.

oh. Misunderstanding then. I thought you meant the v3 id and the new v4 legacy_id field.

[d7415]

oh. Misunderstanding then. I thought you meant the v3 id and the new v4 legacy_id field.

No, the v3 id and the v4 id are strings (though the v3 is numeric, /[0-9]+/). The legacy_id (and id for v3 < x < v4.5.3) is an integer.

I can't remember whether there were other design reasons at the time, but v3's number-in-a-string allowed it to use "numbers" greater than the 32-bit max on 32-bit systems.

[mhellwig]

so. To recap. There is a redirector but it doesn't help me. So I would claim that this is a bug, just in a small number of edge-cases. Question is, what do I do? My course of action would need to be, as far as I understand it:

• change the database migration code to not truncate
• rerun the migration
• change the redirector php code to correctly understand my legacy_ids.

Does that look correct? With my current "can't actually program php, just read enough to understand some of it" (did (some) perl 20 years ago or so) and "don't actually have any time" this doesn't look too feasible to attempt myself.

[mhellwig]

since I still have my Lychee-v3 directory lying around I just checked and it indeed makes the choice of using strings in the php code and bigint in the database code. Not sure whether that knowledge helps me in any way, though.

[mhellwig]

so. To recap. There is a redirector but it doesn't help me. So I would claim that this is a bug, just in a small number of edge-cases. Question is, what do I do? My course of action would need to be, as far as I understand it:

* change the database migration code to not truncate

* rerun the migration

* change the redirector php code to correctly understand my `legacy_id`s.

Does that look correct? With my current "can't actually program php, just read enough to understand some of it" (did (some) perl 20 years ago or so) and "don't actually have any time" this doesn't look too feasible to attempt myself.

alternative, I guess:

• change this system/create new VM that runs 64 bit
• copy image files over, create lychee database from sql dump
• rerun migration Not sure which one is worse in terms of time required

[kamil4]

From what I remember, Laravel (the PHP framework that we use in v4) couldn't properly deal with bigints on 32-bit systems, unlike Lychee v3 which didn't use any framework. We just couldn't get it to work (believe me, we tried), so in the end we were forced to truncate those ids. That code dates back to before version 4.0 was released; the latest big database migration from 4.5.3 is completely innocent here.

Given the struggles we faced in the past trying to get long ids to work in v4, I strongly recommend upgrading to a 64-bit PHP setup instead. Yes, it may be a significant initial effort, but once you get over that hurdle, Lychee v4 should work the way you want without any custom tweaks.

You've got some lovely images in that sample album you linked, BTW :smiley:.

[mhellwig]

So I finally ended up spinning up a new VM with proper 64 bit and transferring everything over. I think everything is working now.

[nagmat84]

Hence, I assume this issue can be closed?