error - Server Error in Diagnotics section after migration 4.4.0 to 4.6.0

[MrLivre]

Hello. I made a manual migration from 4.4.0 to 4.6.0 all seem to working fine except the diagnostics section wich show error - Server Error message and blank page.

logs report that error :

2022-09-09 17:52:39 UTC -- error   -- PDO::prepare -- 368 -- SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list'
2022-09-09 17:52:39 UTC -- error   -- App\Actions\Diagnostics\Checks\MissingUserCheck::check -- 13 -- SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list'; caused by
2022-09-09 17:52:39 UTC -- error   -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list' (SQL: select `owner_id` from `albums` group by `owner_id`); caused by
2022-09-09 17:52:39 UTC -- error   -- App\Actions\Diagnostics\Checks\MissingUserCheck::check -- 13 -- SQLSTATE[42S22]: Column not found: 1054 Unknown column 'owner_id' in 'field list' (SQL: select `owner_id` from `albums` group by `owner_id`); caused by

Found another issue when uploading pictures

2022-09-09 18:32:35 UTC -- error   -- PDOStatement::execute -- 501 -- SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1662748355' for key 'photos_legacy_id_unique'
2022-09-09 18:32:35 UTC -- error   -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1662748355' for key 'photos_legacy_id_unique'; caused by
2022-09-09 18:32:35 UTC -- error   -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1662748355' for key 'photos_legacy_id_unique' (SQL: insert into `photos` (`legacy_id`, `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`, `updated_at`, `created_at`, `id`) values (1662748355, 0, Pnr3zasQ422JGorllNRj1NPv, 19-IMG_4059, ?, ?, none, 0, 0, 125, Canon, Canon PowerShot G12, ?, f/4.0, 1/160 s, 6 mm, ?, ?, ?, ?, ?, 2016-04-21 10:27:25.000000, UTC, image/jpeg, 0, 5548b3c4029835b873c7efd0920378aa2618741c, 5548b3c4029835b873c7efd0920378aa2618741c, ?, ?, ?, 2022-09-09 18:32:35.926919, 2022-09-09 18:32:35.926919, 5h8lSNYrbDjjh8sFEIb7QtRq)); caused by
2022-09-09 18:32:35 UTC -- error   -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1662748355' for key 'photos_legacy_id_unique' (SQL: insert into `photos` (`legacy_id`, `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`, `updated_at`, `created_at`, `id`) values (1662748355, 0, Pnr3zasQ422JGorllNRj1NPv, 19-IMG_4059, ?, ?, none, 0, 0, 125, Canon, Canon PowerShot G12, ?, f/4.0, 1/160 s, 6 mm, ?, ?, ?, ?, ?, 2016-04-21 10:27:25.000000, UTC, image/jpeg, 0, 5548b3c4029835b873c7efd0920378aa2618741c, 5548b3c4029835b873c7efd0920378aa2618741c, ?, ?, ?, 2022-09-09 18:32:35.926919, 2022-09-09 18:32:35.926919, 5h8lSNYrbDjjh8sFEIb7QtRq)); caused by
2022-09-09 18:32:35 UTC -- error   -- App\Exceptions\ModelDBException::create -- 50 -- Updating photo failed; caused by

I use MariaDB 10.3.36, PHP 8.1

[kamil4]

Your first errors from the logs (about owner_id) indicate that your installation tree is messed up. You mentioned that you upgraded from 4.4.0 to 4.6.0 manually, and I'm guessing you still have some obsolete files from version 4.4.0 left. In particular, Lychee/app/Actions/Diagnostics/Checks/MissingUserCheck.php should not exist in version 4.6.0. So you need to clean that up first.

Now, regarding the errors about duplicate entry 1662748355. What you are seeing there are duplicate legacy IDs being generated; in principle this could only happen with 32-bit PHP installations and when adding new photos very quickly.

• Were you uploading multiple files at a time? See if uploading them one-at-a-time is successful.
• Are you using a 32-bit PHP? Once you fix your diagnostics page (see above), go there and see if diagnostics warns you about 32-bit PHP. If it doesn't, check the state of the force_32bit_ids config variable and if it's set to 1, try resetting it to 0.

In principle, we have code in place that should transparently deal with duplicate entries like that, but that code has recently been refactored and perhaps a bug managed to creep in...

[kamil4]

I confirm the second issue (with duplicate entries). @nagmat84, it appears that in the HasRandomIDAndLegacyTimeBasedID trait we now fail to catch the exception in performInsert, possibly because the exception type is now ModelDBException rather than QueryException? This is easy to reproduce by setting force_32bit_ids to 1 and uploading several small photos at once over a fast link.

[MrLivre]

Thank you so much! I made a big clean up and turned force_32bit_ids to 0 (don't know why it was on 1) all is working fine an smooth know. Big thanks to you again.

[kamil4]

Reopening as there is a bug that we need to fix for 32-bit PHP users, but I'm glad that it doesn't affect you.

[MrLivre]

huho... I've uploaded 200 photos without problem and now even a single photo crash with this error while importing from server

2022-09-10 08:11:33 UTC -- error   -- PDOStatement::execute -- 501 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1
2022-09-10 08:11:33 UTC -- error   -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1; caused by
2022-09-10 08:11:33 UTC -- error   -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1 (SQL: insert into `photos` (`updated_at`, `created_at`, `title`, `description`, `tags`, `type`, `iso`, `aperture`, `make`, `model`, `lens`, `shutter`, `focal`, `taken_at`, `taken_at_orig_tz`, `latitude`, `longitude`, `altitude`, `img_direction`, `location`, `live_photo_content_id`, `is_public`, `is_starred`, `album_id`, `owner_id`, `original_checksum`, `checksum`, `id`, `legacy_id`) values (2022-09-10 08:11:33.643545, 2022-09-10 08:11:33.643545, 05-IMG_0204, ?, ?, image/jpeg, 125, f/1.8, Canon, Canon PowerShot G7 X Mark II, ?, 1/1250 s, 9 mm, 2016-06-08 06:55:01.000000, Europe/Paris, ?, ?, ?, ?, ?, ?, 0, 0, ?, 0, e79f8bdf61214b3698a4cfdb42fd325777b92287, e79f8bdf61214b3698a4cfdb42fd325777b92287, dlQMIskxc-AAdgUokJZaLkdZ, 16627974939618)); caused by
2022-09-10 08:11:33 UTC -- error   -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1 (SQL: insert into `photos` (`updated_at`, `created_at`, `title`, `description`, `tags`, `type`, `iso`, `aperture`, `make`, `model`, `lens`, `shutter`, `focal`, `taken_at`, `taken_at_orig_tz`, `latitude`, `longitude`, `altitude`, `img_direction`, `location`, `live_photo_content_id`, `is_public`, `is_starred`, `album_id`, `owner_id`, `original_checksum`, `checksum`, `id`, `legacy_id`) values (2022-09-10 08:11:33.643545, 2022-09-10 08:11:33.643545, 05-IMG_0204, ?, ?, image/jpeg, 125, f/1.8, Canon, Canon PowerShot G7 X Mark II, ?, 1/1250 s, 9 mm, 2016-06-08 06:55:01.000000, Europe/Paris, ?, ?, ?, ?, ?, ?, 0, 0, ?, 0, e79f8bdf61214b3698a4cfdb42fd325777b92287, e79f8bdf61214b3698a4cfdb42fd325777b92287, dlQMIskxc-AAdgUokJZaLkdZ, 16627974939618)); caused by
2022-09-10 08:11:33 UTC -- error   -- App\Exceptions\ModelDBException::create -- 50 -- Updating photo failed; caused by

EDIT: Manual import do the same error

[nagmat84]

I am on it.

I confirm the second issue (with duplicate entries). [...] it appears that in the HasRandomIDAndLegacyTimeBasedID trait we now fail to catch the exception in performInsert, possibly because the exception type is now ModelDBException rather than QueryException?

Is this just a vague thought or or a substantiated analysis? I stepped through the code (not in the debugger yet) and deep down Illuminate\Database\Connection::run still throws a QueryException. However, I haven't made a thorough analyzes yet. I will report back.

[nagmat84]

I have a fix in place. The problem was not the type of exception, but a change from integer values to string values for error codes. Could anybody, please, please, please tell the Laravel guys that PHP supports type annotation for some years now and that type safety is a good thing. 🤬

[MrLivre]

Hello again I still have to set force_32bit_ids to 1 otherwise I have this error
2022-09-10 19:06:30 UTC -- error -- PDOStatement::execute -- 501 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1
2022-09-10 19:06:30 UTC -- error -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1; caused by
2022-09-10 19:06:30 UTC -- error -- Illuminate\Database\Connection::runQueryCallback -- 712 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1 (SQL: insert into photos (legacy_id, 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, updated_at, created_at, id) values (16628367908983, 0, rYAh2HyGBalkjwm0lw5Z5fem, 16 mai 2014 010, ?, ?, none, 0, 0, 80, Canon, Canon PowerShot G12, ?, f/4.0, 1/640 s, 10 mm, ?, ?, ?, ?, ?, 2014-05-16 10:55:22.000000, UTC, image/jpeg, 0, 31f93155c593cbae36ec56853a6b620559830e8f, 31f93155c593cbae36ec56853a6b620559830e8f, ?, ?, ?, 2022-09-10 19:06:30.898186, 2022-09-10 19:06:30.898186, E5Da5rOXqOh9MbPrr3Xtevay)); caused by
2022-09-10 19:06:30 UTC -- error -- App\Models\Photo::performInsert -- 106 -- SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'legacy_id' at row 1 (SQL: insert into photos (legacy_id, 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, updated_at, created_at, id) values (16628367908983, 0, rYAh2HyGBalkjwm0lw5Z5fem, 16 mai 2014 010, ?, ?, none, 0, 0, 80, Canon, Canon PowerShot G12, ?, f/4.0, 1/640 s, 10 mm, ?, ?, ?, ?, ?, 2014-05-16 10:55:22.000000, UTC, image/jpeg, 0, 31f93155c593cbae36ec56853a6b620559830e8f, 31f93155c593cbae36ec56853a6b620559830e8f, ?, ?, ?, 2022-09-10 19:06:30.898186, 2022-09-10 19:06:30.898186, E5Da5rOXqOh9MbPrr3Xtevay)); caused by
2022-09-10 19:06:30 UTC -- error -- App\Exceptions\ModelDBException::create -- 50 -- Updating photo failed; caused by

My PHP is 64bits

[nagmat84]

The question is what is your MySQL? The error ("numeric value out of range") indicates that your PHP version is 64 bit (otherwise it wouldn't be able to create that huge numbers in the first place) but that MySQL is only 32bit and thus cannot handle the 64bit values which PHP is throwing at it.

This is also the reason why you need to set force_32bit explicitly, because the auto-detection only takes PHP into account.

[MrLivre]

SHOW VARIABLES LIKE "%version%"; return :

in_predicate_conversion_threshold | 1000
innodb_version | 10.3.36
protocol_version | 10
slave_type_conversions |  
system_versioning_alter_history | ERROR
system_versioning_asof | DEFAULT
version | 10.3.36-MariaDB
version_comment | MariaDB Server
version_compile_machine | x86_64
version_compile_os | Linux
version_malloc_library | system
version_source_revision | faddcf3c395da640b760c3f701f5bc1f3baae6c4
version_ssl_library | OpenSSL 1.0.2k-fips  26 Jan 2017
wsrep_patch_version | wsrep_25.24

So 64 bit everywhere no?

[kamil4]

Looks like it indeed. Is this is a shared hosting setup? We've seen some weird issues like this in such environments.

Anyway, what's the output of:

describe photos legacy_id;

Is the type bigint unsigned or something else?

[kamil4]

Very weird that it worked for you yesterday and you were able to upload 200 photos but now it just fails... Has anything changed in the meantime?

[MrLivre]

Yes, it's really weird, and yes it's a shared hosting environment so I don't control parameters. bigint is unsigned :( :

legacy_id | bigint(20) unsigned | NO | UNI | NULL |  

Can I change do something or I stay with force_32bit_ids?

Thanks

[nagmat84]

That looks fine and the width of the column (20) is also correct. I am running out of idea here.

Just another wild guess: Does your PHP process communicate with the MySQL daemon correctly or are there other things in between like a load balancer, DB proxy etc., pp? Is PHP and the MySQL daemon running in different Docker images, VMs etc. or do they share the same execution environment? Could there anything in between which might be unable to handle 64bit? I have never heard about anybody using such things for real, but who knows.

Can I change do something or I stay with force_32bit_ids?

Unless we find out what is having problems with 64bit integers, there isn't much you can change. And even if we would find out I doubt that you could do much about it as your are on a shared hoster . I would recommend to stay with force_32bit_ids being enabled.

The setting is only relevant for generating the legacy IDs which have been used by Lychee 4.4.x and below. They are not used anymore. We only have kept generating them in case if there was a fundamental problem with 4.5 and we had to ask users to migrate down from 4.5 to 4.4.x again. We decided to keep generating these IDs for about a year which will end spring next year. I guess at that point we will stop generating the legacy IDs and then the option force_32bit_ids will be gone.

[MrLivre]

Thanks a lot. I don't think they're gonna give me all this informations as I choose shared hosting (my bad). Next time I'll take a dedicated server. But for that project it's only a personal photos gallery (15000+) I thought shared hosting was enough. Keep doing great job with Lychee it's a really powerful application.
Regards

[ildyria]

I wish. 😢 ⁣Télécharger BlueMail pour Android ​ Le 10 sept. 2022, 12:16, à 12:16, Matthias Nagel ***@***.***> a écrit:

…

I have a fix in place. The problem was not the type of exception, but a change from integer values to string values for error codes. Could anybody, please, please, please tell the Laravel guys that PHP supports type annotation for some years now and that type safety is a good thing. 🤬 -- Reply to this email directly or view it on GitHub: #1514 (comment) You are receiving this because you are subscribed to this thread. Message ID: ***@***.***>