Missing thumbs on map, cover on albums and Show nothing in some albums (other albums are normal)

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.41k stars 301 forks source link

Missing thumbs on map, cover on albums and Show nothing in some albums (other albums are normal) #1251

Closed geeklihui closed 2 years ago

geeklihui commented 2 years ago

Detailed description of the problem [REQUIRED]

Issue 1: Missing thumbs on map. Right click on the thumb and click show picture in new tab, it showed "404 not found", and the URL is http://x.x.x.x/null.

Issue 2: Thumbs of album covers are sometimes missing and sometimes normal. After manually specifying a photo as the cover, the cover thumb can be displayed all the time.

Issue 3: Nothing in some albums, but other albums are normal.

Issue 4: Sometimes I got "Server error or API not found" error during importing photos from server, or just during I visist the website. It will disappear after a webpage reloading.

My thoughts: I'm wondering if it is because I uploaded too many photos. The album mentioned in issue 2 has about 1150 photos. And there are about 6500 photos in the whole server. I remember the thumbs on map were normally showing on the day before yesterday. I only uploaded 100 photos at that time. Yesterday I reinstall the server and then import all of my photos. So I don't know if it's the reason. Another possibility about issue 1: Because I'm using TrueNAS, I mounted extenal strorage to these paths:

../uploads/import
../uploads/big
../uploads/medium
../uploads/small
../uploads/raw
../uploads/thumbs

I don't know if thumbs on map are stored in ../uploads/thumbs, or somewhere else.

Steps to reproduce the issue

Steps to reproduce the behavior: Issue 1: Click on map. Issue 2: Just visit Lychee. Issue 3: Just open an album. Issue 4: Click "import from server" or just switch albums or click on settings, logs and so on.

Screenshots Issue 1:

Issue 2: Missing:

Normal after reloading webpage several times:

Issue 3: Problematic album: At first a blue process line showed on the top.

Then an video album with blank thumb appeared, then disappeard very soon (about 0.1s).

Then nothing here.

Normal albums:

Issue 4: Don't have chance to screenshot yet.

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 (e150930) - Data not in Cache
DB Version:                      4.5.0

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

System:                          FreeBSD
PHP Version:                     8
PHP User agent:                  Lychee/4 (https://lycheeorg.github.io/)
Timezone:                        Asia/Shanghai
Max uploaded file size:          10240M
Max post size:                   10240M
Max execution time:              300
MySQL Version:                   10.4.24-MariaDB

Imagick:                         1
Imagick Active:                  1
Imagick Version:                 1692
GD Version:                      2.3.1

Browser and system

Microsoft Edge 99.0.1150.55 on Windows 10

nagmat84 commented 2 years ago

We had a similar report recently (also with a NAS) and the problem vanished after the user reduced the limit of maximum parallel processing for photo import.

I know that it might be too much to ask and might take a while, but could you try that? Start with a fresh installation, set the limit to 1 and re-import everything into a new and empty DB?

Unfortunately, I don't know by heart where the setting is located. I only know that it defaults to 4. @kamil4 might help out.

At the moment we do not deal well with resource problems. A single request modifies more than one table, but without using transactions. If the process dies in the middle due to exhausted resources the database is left in an inconsistent state which might lead to a lot of funny results.

If the suggestion above solves your problem, then we should really start thinking about using proper transactions.

geeklihui commented 2 years ago

We had a similar report recently (also with a NAS) and the problem vanished after the user reduced the limit of maximum parallel processing for photo import.

I know that it might be too much to ask and might take a while, but could you try that? Start with a fresh installation, set the limit to 1 and re-import everything into a new and empty DB?

Unfortunately, I don't know by heart where the setting is located. I only know that it defaults to 4. @kamil4 might help out.

At the moment we do not deal well with resource problems. A single request modifies more than one table, but without using transactions. If the process dies in the middle due to exhausted resources the database is left in an inconsistent state which might lead to a lot of funny results.

If the suggestion above solves your problem, then we should really start thinking about using proper transactions.

I don't know where to set the limit either. Although re-upload photos do take a while, I'm willing to try that after I found the setting location. Perhap this time I should using command to upload because it takes me too long time uplodaing with webpage, I have to check it periodically because it may stop, the percentage of progress remains on one number. But I need "symbolic link" option, I found command on https://lycheeorg.github.io/docs/faq.html#can-i-set-up-lychee-to-watch-a-folder-for-new-images-and-automatically-add-them-to-albums to import, but I don't know how to add "symbolic link" option.

geeklihui commented 2 years ago

@nagmat84 Good news. Issue 1 and Issue 3may have some relevance. Just now I set some albums public, and create a user. Then I login with the user and I found that the thumbs on map are normally shown! Then I logout and login with admin user again to check the map, thumbs still keep blank. But when I open the album marked public, then click map, the thumbs on map are normally shown too. So I guess, when I click on map in the homepage, it contains all photos including those in the album metioned in issue 3. And, yes, if I open the problematic album and click map, thumbs don't show either. So may be the problem is exactly on that problematic album. But I still don't know if it's too many photos or something else. Because there is an album with much more photos than this one, but everything is normal. (Note: That album with more photos is divided into two dozen sub-albums. And the album with the problem has only one video sub-album, the rest of the 1100+ photos are in the root directory of this album.)

kamil4 commented 2 years ago

What's your PHP memory_limit? We really should be reporting it in the diagnostics but we currently don't :disappointed:. However, you can find it by appending /phpinfo to your Lychee URL (while logged in as admin) and searching the output for memory_limit. Given the symptoms you report, I would start by doubling it and see if that helps with anything (please verify after doubling it that Lychee sees the new setting; it's easy to, e.g., modify a wrong php.ini file).

The parallelism limit setting is called upload_processing_limit and you can find it in the advanced settings (click More at the bottom of the Settings screen). Note that it applies to uploads from local computer only, not to importing from server (which is always done one-at-a-time), so if you were importing, that issue would not apply to you.

Answering some of your other questions:

yes, the location map uses the uploads/thumb images.
to use symbolic links during import, you can check the right checkbox in the GUI if using Import from server, or you can set import_via_symlink to 1 in the advanced settings. If importing via the commandline instead, you can add the --import_via_symlink=1 option.

geeklihui commented 2 years ago

Hi Kamil, Thanks for your reply. Actually I adjusted the memory limit when I found these issues. It was 1024M before and I doubled it to 2048M. I don't see any improvement till now. And thanks for your answer about my questions. Since parallelism limit only apply to upload photo from local computer, I think it's no necessary to try that. I suspect memory problems too. But I check memory using, php-fpm only take about 500M. Then I check the memory in TrueNAS, Service takes 10.5G, ZFS Cache takes 1G, and 0.5G free. I think it's abnormal and reboot TrueNAS, now Service takes 3G, ZFS Cache takes 8G, and 1G free. But the problem still exsits, that album still show nothing. I don't know what to do next. Maybe imrpove memory limit to 4096M and try again? Do I need to delete that album and re-import photos? Another issue: I can't import videos in a folder at all. The process is always 0%, and retry many times didn't help. I don't know why. Other folders (including videos too) are all normal.

---Original--- From: "Kamil @.> Date: Wed, Mar 30, 2022 22:42 PM To: @.>; Cc: @.**@.>; Subject: Re: [LycheeOrg/Lychee] Missing thumbs on map, cover on albums and Show nothing in some albums (other albums are normal) (Issue #1251)

What's your PHP memory_limit? We really should be reporting it in the diagnostics but we currently don't 😞. However, you can find it by appending /phpinfo to your Lychee URL (while logged in as admin) and searching the output for memory_limit. Given the symptoms you report, I would start by doubling it and see if that helps with anything (please verify after doubling it that Lychee sees the new setting; it's easy to, e.g., modify a wrong php.ini file).

The parallelism limit setting is called upload_processing_limit and you can find it in the advanced settings (click More at the bottom of the Settings screen). Note that it applies to uploads from local computer only, not to importing from server (which is always done one-at-a-time), so if you were importing, that issue would not apply to you.

Answering some of your other questions:

yes, the location map uses the uploads/thumb images.

to use symbolic links during import, you can check the right checkbox in the GUI if using Import from server, or you can set import_via_symlink to 1 in the advanced settings. If importing via the commandline instead, you can add the --import_via_symlink=1 option.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

kamil4 commented 2 years ago

My memory_limit is 128M and I can open albums with a little over 1,000 photos just fine. So that can't be it then...

So is it just one particular album at this point that's giving you this weird behavior? It may indeed be the simplest to delete it, create a new one and re-import its contents, and see if that helps.

Are you seeing any errors in the JavaScript debug console of your web browser when you try to open that album (hit F12 and switch to the Console tab)?

nagmat84 commented 2 years ago

The described symptoms are typical, if the nested set properties of the album tree are corrupt or inconsistent. For each node in the tree we calculate a left and right boundary and we also use these values to find the cover thumbnail of an album (unless a specific cover is assigned, of course). For more details on nested sets see Wikipedia - Nested Set Model.

Adding or removing an album requires updating the left and right boundary not only of its direct parent or its siblings, but usually of the complete tree. If a request fails in between, then this update might only be half-baked. Or if there are concurrent requests and each updates the whole tree they might interfere with each other. Inconsistent left-right boundaries have a lot of funny effects like showing covers for albums from a photo which isn't even in that album, but in a sibling album and so on.

As you reported that you had some failing imports with "API not found", it might possibly be that your tree structure became corruped in the process.

In theory, a corrupted nested set model can be fixed without re-importing. The left-right boundaries can be completely recalculated from scratch based only on the "is-parent"-relation. However, we would need to write a console command like fix-tree for that first. (At the moment no such command exist.)

I would really be interested to find out if a broken tree is your problem. I encountered these problems myself, when I was working on the refactoring and toying around with @ildyria's example set and was importing "too fast". At the moment, we do not take care of concurrent write access to the tree structure at all and I thought something like "shit, this can really screw things up and we need to fix that with proper transaction handling and write locks". But as we never had any problem reports of this kind before, I did not follow this path any further, because it seems not to happen very frequently.

geeklihui commented 2 years ago

@kamil4 Acutally I have two albums with this weird behavior. One is described in issue 3, and another is desrcibed above:

Another issue: I can't import videos in a folder at all. The process is always 0%, and retry many times didn't help. I don't know why. Other folders (including videos too) are all normal.

Here is the screenshot: And clicking “Cancel” did not respond, I had to refresh the page to exit importing. What is very comfusing is that I have tried dozens of times before, the importing all failed, the progress is still not shown after waiting for more than 10 minutes. But just now, I switched the language to English in order to take a screenshot, then go to edit this post, and about after 5 minutes I back to Lychee, the importing has finished! Could this be due to language? If it's not possible at all, let's focus on the album that shows nothing. There were some errors that appeared when I just opened the console, the ones in the green box appeared after I clicked on the album showing nothing:

I haven't deleted that album yet because @nagmat84 seems to have some new ideas.

@nagmat84 Thanks for your detailed explanation. Your theory sounds very possible, what can I do to assist you in confirming this theory?

nagmat84 commented 2 years ago

Wrt. the non-functional progress bar

This is much likely due to the browser not receiving a streamed response during the process, but only a final response at the end. Typically this indicates a mis-configured web server. Many web-servers try to cache the output of a PHP script and send the response in blocks of 8kB or if the response has been finalized. While this usually a wise idea wrt. to TCP throughput, it breaks streaming. You should fix that in the configuration of your web server. If you don't, then you will encounter timeouts with imports, because the browser does not receive a response in time.

Wrt. to the JS errors

The errors are somewhat expected. They are not another error by themselves, but only another symptom, because the thumbnails are null and something like null.height is an undefined reference.

Wrt. to my new idea

There is nothing you could do right now. Someone (i.e. me or one of the other active developers) needs to write a console command first. I will see and try, if I can code something this evening (this means in approx. 7 hours wrt. to my timezone).

geeklihui commented 2 years ago

Thanks @nagmat84 . I'm looking forward to good news from you. I don't see items about "cache" in configuration of my nginx web server If you would like to help me check the configuration? I can send to you via email.

nagmat84 commented 2 years ago

I am no expert on Nginx at all. I have never used it. Note, that the option does not necessarily need to be called something like "cache". Sometimes only a certain combination of options might prevent streaming.

I believe we have a default config for Nginx in our docs which allows streaming. Maybe a comparison of that with your own config might give you a clue. This blog post Jeff Gerling: Streaming PHP – disabling output buffering in PHP, Apache, Nginx, and Varnish seems to be the most complete and accurate guide for various web servers and configuration combinations.

kamil4 commented 2 years ago

Wrt. the non-functional progress bar

I agree with what @nagmat84 wrote but the simplest way for now to ensure that you're getting all the output in a timely fashion is to import from the commandline instead of via the browser front-end.

Wrt. to the JS errors

The errors are somewhat expected. They are not another error by themselves, but only another symptom, because the thumbnails are null and something like null.height is an undefined reference.

Actually, I had a brief look into it and it must be original that is null, not the thumbnails. Can we recommend to @geeklihui an SQL query that would let him identify the broken photos and delete them? I'm sorry but nowadays, with all this info being scattered across multiple SQL tables, the challenge exceeds my meager SQL skills... I'm curious if purging the broken entries would get his Lychee instance into fully-working state or if other things may be broken as well (such as the nested set ids).

A few more questions to @geeklihui:

You didn't include your configuration in the original Diagnostics dump. I'm particularly interested whether you are using ffmpeg for videos? I.e., for those that import fine, do you get a thumbnail, or just an empty square with a play button?
Is there anything of interest in Lychee logs after a failed import attempt?
Just to confirm: for failing videos/folders, is it the case that the same videos import just fine into other folders?

d7415 commented 2 years ago

This blog post Jeff Gerling: Streaming PHP – disabling output buffering in PHP, Apache, Nginx, and Varnish seems to be the most complete and accurate guide for various web servers and configuration combinations.

It basically just says to set a header like we do here.

nagmat84 commented 2 years ago

Here is the PR for the CLI command to check and fix the tree: PR #1252

nagmat84 commented 2 years ago

It basically just says to set a header like we do

Right. I never looked at the Nginx part, because it was of no interest for me. So this blog does not help, because we already do everything what is suggested.

nagmat84 commented 2 years ago

Wrt. to the JS errors The errors are somewhat expected. They are not another error by themselves, but only another symptom, because the thumbnails are null and something like null.height is an undefined reference.

Actually, I had a brief look into it and it must be original that is null, not the thumbnails. Can we recommend to @geeklihui an SQL query that would let him identify the broken photos and delete them?

Right, the problem are missing originals which actually should never occur. (Thumbnails might be null).

Here we go. First a SELECT

SELECT p.id, p.title
FROM photos AS p
LEFT OUTER JOIN size_variants AS sv
ON (p.id = sv.photo_id AND sv.type = 0)
WHERE sv.id IS NULL;

In the optimal case, this query should return an empty set.

It works as follows: It selects ID and title from the photos table and joins the photos table with the size variants. An left outer join is required such that the result will contain even an entry for photos which don't have a matching size variant. That is crucial, because we actually want to get photos with a missing size variant. We restrict the join to size variants of type 0 which is the size variant for the original image. Finally, we restrict the whole output to rows with no size variant.

@geeklihui Could you run this query and check if the result seems plausible?

If yes, you could delete the offending photos with the following command. But please make sure, you have a backup!

DELETE FROM photos WHERE photos.id IN (
  SELECT p.id
  FROM photos AS p
  LEFT OUTER JOIN size_variants AS sv
  ON (p.id = sv.photo_id AND sv.type = 0)
  WHERE sv.id IS NULL
);

It runs a drop statement with the previous query as an inner query.

nagmat84 commented 2 years ago

@geeklihui The new command to check and fix the album tree has been merged. As you are on the master branch, a simple git pull should do it. You run the command from your Lychee root directory via ./artisan lychee:fix-tee. I am curious what the output will be.

geeklihui commented 2 years ago

Thanks all. @nagmat84 So according to @d7415 , I don't need to change the condiguration of web server, right? @kamil4

Yes I use ffmpeg. For videos I got normal thumbs.
Nothing special in logs. All logs are the information like: 2022-03-31 21:59:56 -- notice -- App\Image\ImagickHandler::scale -- 113 -- Saving thumb to......
That videos succssfully imported yesterday, and just now I try to import them to another album named test, also succssful. So I think the previous failures maybe due to the problem mentioned by @nagmat84 about condiguration of web server. But when I try to delete the test album, "server error or API not found" appears. And it looks all videos deleted but albums not.

The original files:

And logs:

 2022-04-01 10:02:35 -- notice  -- App\Models\Photo::delete -- 430 -- ZfKaKzREy4Bfr921lfwAG6DL is a duplicate, files are not deleted!
2022-04-01 10:02:35 -- notice  -- App\Models\Photo::delete -- 430 -- xL0bEgEAIX29D4SVunWA-DK_ is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- wmxBbUKibbDFw9DxTr3Zx0X4 is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- wChUhzmqtrJHjFKeKGEmCVGj is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- vdd7MqHiB1UfY7VGp6Soinmt is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- uuPykweIXnAdLxQL0K1QudGF is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- tQ09HOU5-1dV1Jm3V7q2GDN_ is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- tfvHJj3x9x0a9jj_cjHgTQbT is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- slNyCOEMpNaW80DKBm2dnTM9 is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- seosDq4B3PrzFpUkrIxCg5M8 is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- RM23ewNTkLUfL3PLEGgO9C4h is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- QAvMpY9LXcJE6OupMs6io6hn is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- O4YcCVkplTblvHTDM_wgtNCV is a duplicate, files are not deleted!
2022-04-01 10:02:34 -- notice  -- App\Models\Photo::delete -- 430 -- nWFgbp2lhGEJK2GlH-cZ19iA is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- Mj9WpOHFnZAOLvvf34xMtBIY is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- m-IhZvLy8z2Vl1U-tn6s1E0_ is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- LCeExpCcKCKcKR1q-G9MNMkj is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- l8cPhO0oMYFsh_cDxDXAEMZv is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- ku3Hib-f1zNkDFP7F3MhVOun is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- jZ8bUCub3cD14hN14rgC__Vo is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- ikq0en1mbnM6G3ZafDgNymIC is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- i3SkbLK34tcvq-wfX1I6OtyE is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- i3AxAjqAhw0doaaD3lBfEgVE is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- gbJbzfC7vm4lgvdnNgnn7D13 is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- E8XgNXArxCAl6WzP4L84MYk9 is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- dXt0MktVRQFNEteYjxO8H7xR is a duplicate, files are not deleted!
2022-04-01 10:02:33 -- notice  -- App\Models\Photo::delete -- 430 -- dl2SP1B7SeyVXJ-MTxemVDSC is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- d9wOQsHWCVeQ0iNli_y-NuAk is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- cRWw5uqDvfl2HcoaarG2YBL- is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- cPqn7EJwn7Af1hhf39GimkG- is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- BT5_l87euvjqO3g9uSrXVIkG is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- b2e2A4vBJQxaeBhoDdy9fUWu is a duplicate, files are not deleted!
2022-04-01 10:02:32 -- notice  -- App\Models\Photo::delete -- 430 -- AaqShHitwSBKm3XRwTpaZwcx is a duplicate, files are not deleted!
2022-04-01 10:02:31 -- notice  -- App\Models\Photo::delete -- 430 -- 6NIL5fcLgSBZocapMYvZSEd8 is a duplicate, files are not deleted!
2022-04-01 10:02:30 -- notice  -- App\Models\Photo::delete -- 430 -- 5L68diUjVwoeDiDobaWVMLGD is a duplicate, files are not deleted!
2022-04-01 10:02:30 -- notice  -- App\Models\Photo::delete -- 430 -- 4vaCiKCm1pdFqvb72pCvDCab is a duplicate, files are not deleted!
2022-04-01 10:02:30 -- notice  -- App\Models\Photo::delete -- 430 -- 4SvyTsZHEwYIIupSUVfS7Z_i is a duplicate, files are not deleted!
2022-04-01 10:02:29 -- notice  -- App\Models\Photo::delete -- 430 -- 3lTV-wCDUxwrqxfEmyTBXF_V is a duplicate, files are not deleted!
2022-04-01 10:02:29 -- notice  -- App\Models\Photo::delete -- 430 -- 2TT7CHkGOKo-PvL-GxBdtMY5 is a duplicate, files are not deleted!
2022-04-01 10:02:29 -- notice  -- App\Models\Photo::delete -- 430 -- 0WYTmu4ap5HrwCo72rWIBcjM is a duplicate, files are not deleted!
2022-04-01 10:02:29 -- notice  -- App\Models\Photo::delete -- 430 -- _5srr72ddx5tgNKbgyJs0L_u is a duplicate, files are not deleted!

SQL result:

It seemed nothing deleted.

Fix-tee result: It response "Command "lychee:fix-tee" is not defined", I think maybe you were saying ./artisan lychee:fix-tree?

kamil4 commented 2 years ago

Note that trying to re-import photos or videos that are already in the database will simply duplicate the metadata without performing all the regular importing steps. In other words, you can't test whether importing works correctly by reusing the same input files. You need to try importing files that are not yet in the database (deleting the photos/videos from Lychee and then re-importing is OK, though). I'm mentioning this based on your logs that indicate that many of the objects you were trying to delete had duplicates.

Now to the main issues: these are puzzling results. I don't know why you would get a server error in the step 3 above. I have no idea why the SQL queries didn't find any matches; based on your earlier results, I was sure they would. The nested tree set wasn't found to be broken either.

I don't know how else we can debug this remotely. I feel that we may need a dump of your Lychee database to try to figure out what might be going wrong there, although @nagmat84 should weigh in first with his thoughts...

nagmat84 commented 2 years ago

I feel that we may need a dump of your Lychee database to try to figure out what might be going wrong

Totally agreed. The results don't make sense to me. I guess we need to have a direct look at the DB ourselves. I am running out of ideas otherwise.

nagmat84 commented 2 years ago

After giving it a second thought, there might also be another option. I am not sure if looking at the DB will help us, as the DB seems to be fine (no photos without original size variant, no broken tree).

Maybe @geeklihui could switch to the branch consistent_json_api. Although this branch is not yet thoroughly tested among the developers, it cannot make things worse. It does not contain a DB migration, so it should not break the DB more than it may already be.

However, the branch should provide us with better error messages. In particular, if an exception occurs in the backend we see the message of the exception in the frontend and instead of this "Server API not found'. We also get a proper backtrace in the log.

geeklihui commented 2 years ago

After giving it a second thought, there might also be another option. I am not sure if looking at the DB will help us, as the DB seems to be fine (no photos without original size variant, no broken tree).

Maybe @geeklihui could switch to the branch consistent_json_api. Although this branch is not yet thoroughly tested among the developers, it cannot make things worse. It does not contain a DB migration, so it should not break the DB more than it may already be.

However, the branch should provide us with better error messages. In particular, if an exception occurs in the backend we see the message of the exception in the frontend and instead of this "Server API not found'. We also get a proper backtrace in the log.

Okay, could you please tell me how to switch to the branch consistent_json_api? Besides, I haven't deleted that album yet because I want to test @nagmat84 's new idea before. Thus it looks fine about tree, how about try to delete the album and re-import again? And I can provide a tunnel to login to my db remotely if you need.

nagmat84 commented 2 years ago

could you please tell me how to switch to the branch consistent_json_api?

Invoke git checkout consistent_json_api from the root folder of Lychee on the CLI.

how about try to delete the album and re-import again?

That would (hopefully) solve the issue ultimately, but I really would like to find out what got you there in the first place in order to prevent this kind of issue in the future.

And I can provide a tunnel to login to my db remotely if you need.

As it appears not to be a problem on the DB layer that would not help much, because there will be nothing to detect there. Instead a dump of the DB which we could import in our own installation would be preferred. This way we could step-debug the PHP code of the backend and see where it fails.

But first let's try the other branch.

geeklihui commented 2 years ago

@nagmat84 Got it, I'll try consistent_json_api branch ASAP.

geeklihui commented 2 years ago

@nagmat84 I think I'm on branch, right? 51e322febc06cd02f7febd3e11647f9

The album still show nothing, and logs shows: (It seems timezone has been reset to default. I'll fix it later.)

nagmat84 commented 2 years ago

Yes, you are on the right branch. Please

run ./artisan migrate from the root folder of Lychee, and
edit your .env file such that
- APP_ENV=development
- APP_DEBUG=true
run ./artisan cache:clear && ./artisan route:clear && ./artisan config:clear

Step one is necessary, because a config option has been renamed. Step two is necessary that the JSON response from the backend includes the complete exception stack trace. Step three ensures that the changed configuration is really used.

geeklihui commented 2 years ago

@nagmat84 I‘ve done these steps.

The album still shows nothing, and logs show nothing either, just one log: 2022-04-02 03:53:59 UTC -- notice -- App\ModelFunctions\SessionFunctions::log_as_admin -- 183 -- User (xxx) has logged in from x.x.x.x

geeklihui commented 2 years ago

@nagmat84 And I click the map, thumbs still keep blank, and right-click it to show in new tab, it showed: There are so many items on this page, I cannot screenshot it all. And logs here:

2022-04-02 04:01:25 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page]. 2022-04-02 04:01:24 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page]. 2022-04-02 04:01:22 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page]. 2022-04-02 04:01:20 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page]. 2022-04-02 04:01:20 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page]. 2022-04-02 04:01:18 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page].

A few more question, how can I delete the test album I created according to https://github.com/LycheeOrg/Lychee/issues/1251#issuecomment-1085338823? I tried to delete again just now, and I got these in log:

2022-04-02 04:01:52 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id)) 2022-04-02 04:01:52 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id)) (SQL: delete from albums where albums._lft between 304 and 388); caused by 2022-04-02 04:01:52 UTC -- error -- App\Exceptions\ModelDBException::create -- 50 -- Deletingalbum failed; caused by

nagmat84 commented 2 years ago

And I click the map, thumbs still keep blank

Please note, the whole point of the last couple of steps was not to bring your thumbnails back, but to find out what goes on.

And logs here:

2022-04-02 04:01:25 UTC -- error -- App\Http\Controllers\PageController::page -- 44 -- No query results for model [App\Models\Page].

Here we go, but why?! The page controller?! The page controller is invoked as a fall through route and a catch all address. That is definitely a bug.

I tried to delete again just now, and I got these in log:

The log says that it cannot delete an album, because it is still referenced by another album as its parent. If you delete an album, we take care of that, by recursively deleting all child albums first. For that we use the left-right boundaries of the nested set model. (The values 304 and 388 you can see in the logs.) From the left-right boundaries we can also infer that the album has 388-304-1 = 83 descendants which must be deleted first.

But your test album has no child albums at all, right? If so, this suggests that your nested set model is indeed broken, as I suspected earlier, but why did the CLI command not report any error? :thinking:

I guess this is what happens: When you try to delete the test album, the query selects the allegedly 83 descendants for deletion first. But these 83 albums are not really descendants of the album. At least one of these 83 "descendants" has at least another sub-album which is not picked by the query, because its left-right boundaries are outside the limits and thus the whole query fails. In theory, this is a good thing, because you probably don't want 83 albums to be accidentally deleted.

geeklihui commented 2 years ago

@nagmat84

But your test album has no child albums at all, right?

Acutually I do have child albums in that test album. I have 14 child albums and each child album has its child album and the child's child album also has a child album. That is to say I have about 42 ablums among the test album. All photos has been deleted, but albums all remained. But you have reminded me. I deleted the second level child ablum, and then I can delete the first level child album. ( I cannot delete the first level child album if the second level child album exists. But I can delete the second level child album when the third level child album exists. Why?) After I deleted all 42 child albums, I deleted the test album successfully. But why logs shows about duplicate file before? I wonder if it could be caused by the third level child album with an underscore at the top of its name?

nagmat84 commented 2 years ago

Unfortunately, you were too fast deleting the album manually. I had wanted to give you some SQL queries to test whether my assumption was right. Well, my fault that I had not thought of this earlier.

I cannot delete the first level child album if the second level child album exists. But I can delete the second level child album when the third level child album exists. Why?

Broken tree I would guess. Normally, you should be able to delete any album irrespective of the depth of the hierarchy. But now it is too late to confirm that.

geeklihui commented 2 years ago

@nagmat84 I think I can reproduce that test album. Give me a few minutes. By the way what about the album showing nothing? Any new idea?

nagmat84 commented 2 years ago

By the way what about the album showing nothing? Any new idea?

After we have enabled proper error reporting you should hopefully see some conclusive log entries. If any exception is thrown and caught along the call graph, then it should now appear in the logs.

geeklihui commented 2 years ago

@nagmat84 test album reproduced. It seems that this is a reproducible problem.😄 Here are logs when I click delete on the test album ( same as before):

2022-04-02 07:58:07 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id)) 2022-04-02 07:58:07 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id)) (SQL: delete from albums where albums._lft between 304 and 388); caused by 2022-04-02 07:58:06 UTC -- error -- App\Exceptions\ModelDBException::create -- 50 -- Deletingalbum failed; caused by 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- zs9pKQGMDtLw_cEAa52T_IYw is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- ZiaK2UcRE-8Q-k-Vd0ypicip is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- Z5bo_RUIhIgPFG6Tn2fwBnCx is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- Z_6HfXs06zbc22JHgl95grtr is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- Yyaw56YR0OWAf8wI5FXBon24 is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- yomcSFFh-ZIBNt1i6Dm0pJMw is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- xOGpbR3D19Tq-xkKmCV1dr7T is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- Wyt7FNDl0rZ2iVy-6ABdtt6y is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- VccpC4rPlmm9UiuPKflL9fa1 is a duplicate, files are not deleted! 2022-04-02 07:58:05 UTC -- notice -- App\Models\Photo::delete -- 462 -- U9qhd5C71_wcvMde57NI02wY is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- U7oIWQTGBYdhSjbqoqVc5uJP is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- U0HkXfOhHLsxFAwOve2qzAZk is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- TE7ieVAqDtTXzgnXDQ8wntYQ is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- QQshuHVF4wHUEfeUReOvWEV2 is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- PLmnqnGsRSM1ybpFWvcDXOt4 is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- OzzevL7pfVQ4O1nm3rxrsNU0 is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- osSwbNTh6Odw9XFMF_AbSf_i is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- o8hXktW80_gnorJKkU88RStb is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- nlVHgWB4SHDlce44Jg2xrwxo is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- nKNQHAj4wn9q7OhxAqHe5wNE is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- MDeWbu2URuDlHNvTd3DZqndV is a duplicate, files are not deleted! 2022-04-02 07:58:04 UTC -- notice -- App\Models\Photo::delete -- 462 -- KYNvzogOH8zordLbml3u6Z9y is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- KuPD4q8ES9wV-aqVUQwNvOoQ is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- Ia6XNdmQ4-m4bk6fkWAGXfZC is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- HCR8BJ4VTK9LY1hWmQ86FFzN is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- GtWXNp3RKWilw705faqIXfHH is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- erpqM2oPE5dITXa_MgMrIgSs is a duplicate, files are not deleted! 2022-04-02 07:58:03 UTC -- notice -- App\Models\Photo::delete -- 462 -- DBh7e8gZ3xl_34EMIe4I8WfD is a duplicate, files are not deleted! 2022-04-02 07:58:02 UTC -- notice -- App\Models\Photo::delete -- 462 -- CSOHRhJHoxcB5O-rf6ZfQ9Cb is a duplicate, files are not deleted! 2022-04-02 07:58:01 UTC -- notice -- App\Models\Photo::delete -- 462 -- BSohk9jbG5WH5hwOyvG-DrhG is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- Bbqynvtp6opDQsQZwq6LZr9g is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- AYqyehPPYj4duCAjTOh9edwP is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 9UTqFgTX78gElnRiBVd81QrM is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 9-WqJ6BydDdOC7pW23DdCBZp is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 8bo3ckdK-VPoEz8R9J-VkSot is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 6M4sLWCkjZWb-0RbgdeIQuj8 is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 3SlR99P-WTZ1S3N7eZjGi2cg is a duplicate, files are not deleted! 2022-04-02 07:58:00 UTC -- notice -- App\Models\Photo::delete -- 462 -- 3jXuGNFTJiCdd2PYmLa2ljMH is a duplicate, files are not deleted! 2022-04-02 07:57:59 UTC -- notice -- App\Models\Photo::delete -- 462 -- 2IQv_7cUov05kx7UEPjimGaP is a duplicate, files are not deleted! 2022-04-02 07:57:59 UTC -- notice -- App\Models\Photo::delete -- 462 -- 12sskAZU1STiHWve488g-86V is a duplicate, files are not deleted! 2022-04-02 07:57:59 UTC -- notice -- App\Models\Photo::delete -- 462 -- _9a46HR3uEMimqvIgyHj4y0i is a duplicate, files are not deleted!

nagmat84 commented 2 years ago

Sorry for the delay. Given this log

2022-04-02 07:58:07 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- Integrity constraint violation:
  Cannot delete or update a parent row: a foreign key constraint fails
  (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id))
2022-04-02 07:58:07 UTC -- error -- App\Models\Extensions\BaseAlbum::delete -- 136 -- Integrity constraint violation:
  Cannot delete or update a parent row: a foreign key constraint fails
  (lychee_db.albums, CONSTRAINT albums_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES albums (id))
  (SQL: delete from albums where albums._lft between 304 and 388);

I would like to ask you to run the following SQL command

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a._lft >= 304 AND a._rgt <= 388
ORDER BY a._lft ASC;

Does the output look plausible to you? You should see the sub-hierarchy of your problematic album in a depth-first tree walk with the problematic album at the top.
Could you post the result here? I would like to manually check whether the result is consistent, i.e. if the nested set model is valid, and if we indeed get a depth-first walk of the subtree.

If you don't want to post the title here (due to privacy concerns, you can leave the title out of the result:

SELECT a.id, a.parent_id, a._lft, a._rgt
FROM albums AS a
WHERE a._lft >= 304 AND a._rgt <= 388
ORDER BY a._lft ASC;

geeklihui commented 2 years ago

@nagmat84 Here is the reslut. I think "42 rows" means you are right, there do have 42 child albums.

[2022-04-02 19:38:09]  root@localhost [lychee_db]> SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
[2022-04-02 19:38:15]      -> FROM albums AS a
[2022-04-02 19:38:15]      -> JOIN base_albums AS ba
[2022-04-02 19:38:15]      -> ON (ba.id = a.id)
[2022-04-02 19:38:15]      -> WHERE a._lft >= 304 AND a._rgt <= 388
[2022-04-02 19:38:15]      -> ORDER BY a._lft ASC;
[2022-04-02 19:38:16]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 19:38:16]  | id                       | parent_id                | _lft | _rgt | title    |
[2022-04-02 19:38:16]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 19:38:16]  | O0WXENN69ljYUH7PlsEE5P2J | EB5bo1ggINfGKizLLE_fni50 |  304 |  309 | 201303   |
[2022-04-02 19:38:16]  | XSclEoP-CXQ9ihXZI3ghwBov | O0WXENN69ljYUH7PlsEE5P2J |  305 |  308 | 201303A0 |
[2022-04-02 19:38:16]  | aBoqfd88wawyPREZ-r_uTytz | XSclEoP-CXQ9ihXZI3ghwBov |  306 |  307 | _PAlbTN  |
[2022-04-02 19:38:16]  | LywPMFEBh4sWSuiMP2Ppi3Ie | EB5bo1ggINfGKizLLE_fni50 |  310 |  315 | 201306   |
[2022-04-02 19:38:16]  | -r8WARpCvCvDCVoqKF6_vOIS | LywPMFEBh4sWSuiMP2Ppi3Ie |  311 |  314 | 201306A0 |
[2022-04-02 19:38:16]  | 8Ayl2tqPxrHbsFgmS6kNaP8W | -r8WARpCvCvDCVoqKF6_vOIS |  312 |  313 | _PAlbTN  |
[2022-04-02 19:38:16]  | qNulpBnt97e6MvsDLaH8caK5 | EB5bo1ggINfGKizLLE_fni50 |  316 |  321 | 201310   |
[2022-04-02 19:38:16]  | axVwVs95gycRZ3vXh1ZiDozB | qNulpBnt97e6MvsDLaH8caK5 |  317 |  320 | 201310A0 |
[2022-04-02 19:38:16]  | oBmSOtwVt7P_oUrYuUr4ZzEg | axVwVs95gycRZ3vXh1ZiDozB |  318 |  319 | _PAlbTN  |
[2022-04-02 19:38:16]  | -PS8dzq1F4Gspfge8JJ_sUlF | EB5bo1ggINfGKizLLE_fni50 |  322 |  327 | 201311   |
[2022-04-02 19:38:16]  | DOFXoSkkuBdBwwWP5JBuBYE0 | -PS8dzq1F4Gspfge8JJ_sUlF |  323 |  326 | 201311A0 |
[2022-04-02 19:38:16]  | uxm_UYCC60knv-2JoN8IaXeW | DOFXoSkkuBdBwwWP5JBuBYE0 |  324 |  325 | _PAlbTN  |
[2022-04-02 19:38:16]  | TQweLHanMsfo6G2BgtZdr0bM | EB5bo1ggINfGKizLLE_fni50 |  328 |  333 | 201312   |
[2022-04-02 19:38:16]  | NHHuee5_rsA0ajj07KAXhWqZ | TQweLHanMsfo6G2BgtZdr0bM |  329 |  332 | 201312A0 |
[2022-04-02 19:38:16]  | 3AQG_b-1aCzJb6AqUNWpyiLD | NHHuee5_rsA0ajj07KAXhWqZ |  330 |  331 | _PAlbTN  |
[2022-04-02 19:38:16]  | dZQ8Lhc_RxfNt7ZyURGGgqRc | EB5bo1ggINfGKizLLE_fni50 |  334 |  339 | 201404   |
[2022-04-02 19:38:16]  | Lr-mxGhiJFefRBYvZXcXxWGU | dZQ8Lhc_RxfNt7ZyURGGgqRc |  335 |  338 | 201404A0 |
[2022-04-02 19:38:16]  | HgE_tpUJaVZk0SaFWQgWZjwQ | Lr-mxGhiJFefRBYvZXcXxWGU |  336 |  337 | _PAlbTN  |
[2022-04-02 19:38:16]  | YXpWCpFQGr9OSjYP2Mb2EbVf | EB5bo1ggINfGKizLLE_fni50 |  340 |  345 | 201405   |
[2022-04-02 19:38:16]  | RHSUg6jBRrz9gMZxSe04ID_r | YXpWCpFQGr9OSjYP2Mb2EbVf |  341 |  344 | 201405A0 |
[2022-04-02 19:38:16]  | qcUPb2IXiAv0xuRacoFbGmn2 | RHSUg6jBRrz9gMZxSe04ID_r |  342 |  343 | _PAlbTN  |
[2022-04-02 19:38:16]  | 3bQkb-9KM2UPyHsBxdCOrO4o | EB5bo1ggINfGKizLLE_fni50 |  346 |  351 | 201406   |
[2022-04-02 19:38:16]  | ONinB7cpurT5nE3XzymcFaU4 | 3bQkb-9KM2UPyHsBxdCOrO4o |  347 |  350 | 201406A0 |
[2022-04-02 19:38:16]  | styPsxtvwT1c1rtvPH7sYPl5 | ONinB7cpurT5nE3XzymcFaU4 |  348 |  349 | _PAlbTN  |
[2022-04-02 19:38:16]  | wIuUWcyKOHZMD71Skn9XV0Dw | EB5bo1ggINfGKizLLE_fni50 |  352 |  357 | 201407   |
[2022-04-02 19:38:16]  | LmHDx6q7-EagUyN-eZvyHKxi | wIuUWcyKOHZMD71Skn9XV0Dw |  353 |  356 | 201407A0 |
[2022-04-02 19:38:16]  | hPtjn3y31VUya3tXRK_Ih_vB | LmHDx6q7-EagUyN-eZvyHKxi |  354 |  355 | _PAlbTN  |
[2022-04-02 19:38:16]  | c377GohpYt9buz3stc4V9U9O | EB5bo1ggINfGKizLLE_fni50 |  358 |  363 | 201409   |
[2022-04-02 19:38:16]  | EPG8yMQfseC1iiw6dt64F3bU | c377GohpYt9buz3stc4V9U9O |  359 |  362 | 201409A0 |
[2022-04-02 19:38:16]  | LiB7rzzpfRQyetE9RslI87ii | EPG8yMQfseC1iiw6dt64F3bU |  360 |  361 | _PAlbTN  |
[2022-04-02 19:38:16]  | khW6Xz6Wxg4c47ccg71irBJc | EB5bo1ggINfGKizLLE_fni50 |  364 |  369 | 201410   |
[2022-04-02 19:38:16]  | I8KVMO73157qSQ2YEmWwjgSB | khW6Xz6Wxg4c47ccg71irBJc |  365 |  368 | 201410A0 |
[2022-04-02 19:38:16]  | Hmx_6g1IbRpsnBvdgdmosTTT | I8KVMO73157qSQ2YEmWwjgSB |  366 |  367 | _PAlbTN  |
[2022-04-02 19:38:16]  | yvDUAPOc78ZtVVwOZQGbKHYL | EB5bo1ggINfGKizLLE_fni50 |  370 |  375 | 201411   |
[2022-04-02 19:38:16]  | NpbKnkM6-NzYxe33pGcGlM-k | yvDUAPOc78ZtVVwOZQGbKHYL |  371 |  374 | 201411A0 |
[2022-04-02 19:38:16]  | eDMvXKY10bOwcwOeWEclyLhC | NpbKnkM6-NzYxe33pGcGlM-k |  372 |  373 | _PAlbTN  |
[2022-04-02 19:38:16]  | ZfcSqeTmsT7lMuZ5_fWim3GV | EB5bo1ggINfGKizLLE_fni50 |  376 |  381 | 201504   |
[2022-04-02 19:38:16]  | 7wlnFKS4m0jpjfD_VoF1COPU | ZfcSqeTmsT7lMuZ5_fWim3GV |  377 |  380 | 201504A0 |
[2022-04-02 19:38:16]  | YHNUkuy84FV-PuHK6UpLZox2 | 7wlnFKS4m0jpjfD_VoF1COPU |  378 |  379 | _PAlbTN  |
[2022-04-02 19:38:16]  | AFb9TIg7x-5FLiqc1GLNYlBS | EB5bo1ggINfGKizLLE_fni50 |  382 |  387 | 201505   |
[2022-04-02 19:38:16]  | L2EgFX9B5L38u7gwYhXRmZRA | AFb9TIg7x-5FLiqc1GLNYlBS |  383 |  386 | 201505A0 |
[2022-04-02 19:38:16]  | XwQECcjIUkwIETCO1eYsGoJ2 | L2EgFX9B5L38u7gwYhXRmZRA |  384 |  385 | _PAlbTN  |
[2022-04-02 19:38:16]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 19:38:16]  42 rows in set (0.000 sec)

I don't know why the four ids are blue, so I put up the pictures below ( My SSH tool is Mobaxterm ):

nagmat84 commented 2 years ago

I don't know why the four ids are blue, so I put up the pictures below

I assume that the color highlighting is done by your console application which gets confused by the starting hyphen. That's nothing to care about.

@nagmat84 Here is the reslut. I think "42 rows" means you are right, there do have 42 child albums.

Well, but I would have expected 43 albums, not 42, because 388-304-1 = 43. I also notice that album O0WXENN69ljYUH7PlsEE5P2J has a _lft value of 304 (good!), but the highest _rgt value is 387 for album AFb9TIg7x-5FLiqc1GLNYlBS (bad!). I would have expected to see one album with _lft = 304 and _rgt = 388 at the top.

Could you please report the results of the following SQL queries?

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a.id = 'EB5bo1ggINfGKizLLE_fni50';

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a.parent_id IN (
  SELECT a2.id
  FROM albums AS a2
  WHERE a2._lft >= 304 AND a2._rgt <= 388
)
ORDER BY a._lft ASC;

Can your confirm that the album reported by the first query is the infringing album which triggers the problem?

geeklihui commented 2 years ago

@nagmat84 Result:

[2022-04-02 19:38:16]  root@localhost [lychee_db]> SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
[2022-04-02 22:22:04]      -> FROM albums AS a
[2022-04-02 22:22:04]      -> JOIN base_albums AS ba
[2022-04-02 22:22:04]      -> ON (ba.id = a.id)
[2022-04-02 22:22:04]      -> WHERE a.id = 'EB5bo1ggINfGKizLLE_fni50';
[2022-04-02 22:22:05]  +--------------------------+-----------+------+------+-------+
[2022-04-02 22:22:05]  | id                       | parent_id | _lft | _rgt | title |
[2022-04-02 22:22:05]  +--------------------------+-----------+------+------+-------+
[2022-04-02 22:22:05]  | EB5bo1ggINfGKizLLE_fni50 | NULL      |  303 |  388 | test  |
[2022-04-02 22:22:05]  +--------------------------+-----------+------+------+-------+
[2022-04-02 22:22:05]  1 row in set (0.000 sec)
[2022-04-02 22:22:05]  
[2022-04-02 22:22:05]  root@localhost [lychee_db]> SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
[2022-04-02 22:22:25]      -> FROM albums AS a
[2022-04-02 22:22:25]      -> JOIN base_albums AS ba
[2022-04-02 22:22:25]      -> ON (ba.id = a.id)
[2022-04-02 22:22:25]      -> WHERE a.parent_id IN (
[2022-04-02 22:22:25]      ->   SELECT a2.id
[2022-04-02 22:22:25]      ->   FROM albums AS a2
[2022-04-02 22:22:25]      ->   WHERE a2._lft >= 304 AND a2._rgt <= 388
[2022-04-02 22:22:25]      -> )
[2022-04-02 22:22:25]      -> ORDER BY a._lft ASC;
[2022-04-02 22:22:26]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 22:22:26]  | id                       | parent_id                | _lft | _rgt | title    |
[2022-04-02 22:22:26]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 22:22:26]  | XSclEoP-CXQ9ihXZI3ghwBov | O0WXENN69ljYUH7PlsEE5P2J |  305 |  308 | 201303A0 |
[2022-04-02 22:22:26]  | aBoqfd88wawyPREZ-r_uTytz | XSclEoP-CXQ9ihXZI3ghwBov |  306 |  307 | _PAlbTN  |
[2022-04-02 22:22:26]  | -r8WARpCvCvDCVoqKF6_vOIS | LywPMFEBh4sWSuiMP2Ppi3Ie |  311 |  314 | 201306A0 |
[2022-04-02 22:22:26]  | 8Ayl2tqPxrHbsFgmS6kNaP8W | -r8WARpCvCvDCVoqKF6_vOIS |  312 |  313 | _PAlbTN  |
[2022-04-02 22:22:26]  | axVwVs95gycRZ3vXh1ZiDozB | qNulpBnt97e6MvsDLaH8caK5 |  317 |  320 | 201310A0 |
[2022-04-02 22:22:26]  | oBmSOtwVt7P_oUrYuUr4ZzEg | axVwVs95gycRZ3vXh1ZiDozB |  318 |  319 | _PAlbTN  |
[2022-04-02 22:22:26]  | DOFXoSkkuBdBwwWP5JBuBYE0 | -PS8dzq1F4Gspfge8JJ_sUlF |  323 |  326 | 201311A0 |
[2022-04-02 22:22:26]  | uxm_UYCC60knv-2JoN8IaXeW | DOFXoSkkuBdBwwWP5JBuBYE0 |  324 |  325 | _PAlbTN  |
[2022-04-02 22:22:26]  | NHHuee5_rsA0ajj07KAXhWqZ | TQweLHanMsfo6G2BgtZdr0bM |  329 |  332 | 201312A0 |
[2022-04-02 22:22:26]  | 3AQG_b-1aCzJb6AqUNWpyiLD | NHHuee5_rsA0ajj07KAXhWqZ |  330 |  331 | _PAlbTN  |
[2022-04-02 22:22:26]  | Lr-mxGhiJFefRBYvZXcXxWGU | dZQ8Lhc_RxfNt7ZyURGGgqRc |  335 |  338 | 201404A0 |
[2022-04-02 22:22:26]  | HgE_tpUJaVZk0SaFWQgWZjwQ | Lr-mxGhiJFefRBYvZXcXxWGU |  336 |  337 | _PAlbTN  |
[2022-04-02 22:22:26]  | RHSUg6jBRrz9gMZxSe04ID_r | YXpWCpFQGr9OSjYP2Mb2EbVf |  341 |  344 | 201405A0 |
[2022-04-02 22:22:26]  | qcUPb2IXiAv0xuRacoFbGmn2 | RHSUg6jBRrz9gMZxSe04ID_r |  342 |  343 | _PAlbTN  |
[2022-04-02 22:22:26]  | ONinB7cpurT5nE3XzymcFaU4 | 3bQkb-9KM2UPyHsBxdCOrO4o |  347 |  350 | 201406A0 |
[2022-04-02 22:22:26]  | styPsxtvwT1c1rtvPH7sYPl5 | ONinB7cpurT5nE3XzymcFaU4 |  348 |  349 | _PAlbTN  |
[2022-04-02 22:22:26]  | LmHDx6q7-EagUyN-eZvyHKxi | wIuUWcyKOHZMD71Skn9XV0Dw |  353 |  356 | 201407A0 |
[2022-04-02 22:22:26]  | hPtjn3y31VUya3tXRK_Ih_vB | LmHDx6q7-EagUyN-eZvyHKxi |  354 |  355 | _PAlbTN  |
[2022-04-02 22:22:26]  | EPG8yMQfseC1iiw6dt64F3bU | c377GohpYt9buz3stc4V9U9O |  359 |  362 | 201409A0 |
[2022-04-02 22:22:26]  | LiB7rzzpfRQyetE9RslI87ii | EPG8yMQfseC1iiw6dt64F3bU |  360 |  361 | _PAlbTN  |
[2022-04-02 22:22:26]  | I8KVMO73157qSQ2YEmWwjgSB | khW6Xz6Wxg4c47ccg71irBJc |  365 |  368 | 201410A0 |
[2022-04-02 22:22:26]  | Hmx_6g1IbRpsnBvdgdmosTTT | I8KVMO73157qSQ2YEmWwjgSB |  366 |  367 | _PAlbTN  |
[2022-04-02 22:22:26]  | NpbKnkM6-NzYxe33pGcGlM-k | yvDUAPOc78ZtVVwOZQGbKHYL |  371 |  374 | 201411A0 |
[2022-04-02 22:22:26]  | eDMvXKY10bOwcwOeWEclyLhC | NpbKnkM6-NzYxe33pGcGlM-k |  372 |  373 | _PAlbTN  |
[2022-04-02 22:22:26]  | 7wlnFKS4m0jpjfD_VoF1COPU | ZfcSqeTmsT7lMuZ5_fWim3GV |  377 |  380 | 201504A0 |
[2022-04-02 22:22:26]  | YHNUkuy84FV-PuHK6UpLZox2 | 7wlnFKS4m0jpjfD_VoF1COPU |  378 |  379 | _PAlbTN  |
[2022-04-02 22:22:26]  | L2EgFX9B5L38u7gwYhXRmZRA | AFb9TIg7x-5FLiqc1GLNYlBS |  383 |  386 | 201505A0 |
[2022-04-02 22:22:26]  | XwQECcjIUkwIETCO1eYsGoJ2 | L2EgFX9B5L38u7gwYhXRmZRA |  384 |  385 | _PAlbTN  |
[2022-04-02 22:22:26]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 22:22:26]  28 rows in set (0.001 sec)

Can your confirm that the album reported by the first query is the infringing album which triggers the problem?

Sorry for my bad English, actually I don't quite understand you. Are you meaning the result of this SQL query?

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a.id = 'EB5bo1ggINfGKizLLE_fni50';

I can confirm the album with title "test" is the album that cannot be deleted. I reproduced it. It's the parent of all child albums.

nagmat84 commented 2 years ago

Sorry for my bad English, actually I don't quite understand you. Are you meaning the result of this SQL query?

No problem. Your post was completely fine. I am running out of ideas what is going wrong. All the DB results look just fine which I did not expect.

As a last resort I would try to run the DELETE query which fails according to logs manually, i.e.

DELETE FROM albums WHERE albums._lft BETWEEN 304 AND 388;

According to the Lychee logs, this query fails, because the FK constraint for parent_id would be broken. However, your SELECT queries did not unveil any album which would not be deleted itself but still refers to the albums which are going to be deleted. I wonder how the result looks like if you directly run the SQL query from CLI.

geeklihui commented 2 years ago

@nagmat84 I got this:

[2022-04-02 22:22:26]  root@localhost [lychee_db]> DELETE FROM albums WHERE albums._lft BETWEEN 304 AND 388;
[2022-04-02 23:17:53]  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`lychee_db`.`albums`, CONSTRAINT `albums_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `albums` (`id`))

nagmat84 commented 2 years ago

At least this error is consistent with the logs. But WTF?! Let's give it another try. It seems that >= AND <= returns another results than BETWEEN.

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a._lft BETWEEN 304 AND 388
ORDER BY a._lft ASC;

SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
FROM albums AS a
JOIN base_albums AS ba
ON (ba.id = a.id)
WHERE a.parent_id IN (
  SELECT a2.id
  FROM albums AS a2
  WHERE a._lft BETWEEN 304 AND 388
)
ORDER BY a._lft ASC;

geeklihui commented 2 years ago

@nagmat84 Hey, it seems the same result:

[2022-04-02 23:17:53]  root@localhost [lychee_db]> SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
[2022-04-02 23:38:40]      -> FROM albums AS a
[2022-04-02 23:38:40]      -> JOIN base_albums AS ba
[2022-04-02 23:38:40]      -> ON (ba.id = a.id)
[2022-04-02 23:38:40]      -> WHERE a._lft BETWEEN 304 AND 388
[2022-04-02 23:38:40]      -> ORDER BY a._lft ASC;
[2022-04-02 23:38:41]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:41]  | id                       | parent_id                | _lft | _rgt | title    |
[2022-04-02 23:38:41]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:41]  | O0WXENN69ljYUH7PlsEE5P2J | EB5bo1ggINfGKizLLE_fni50 |  304 |  309 | 201303   |
[2022-04-02 23:38:41]  | XSclEoP-CXQ9ihXZI3ghwBov | O0WXENN69ljYUH7PlsEE5P2J |  305 |  308 | 201303A0 |
[2022-04-02 23:38:41]  | aBoqfd88wawyPREZ-r_uTytz | XSclEoP-CXQ9ihXZI3ghwBov |  306 |  307 | _PAlbTN  |
[2022-04-02 23:38:41]  | LywPMFEBh4sWSuiMP2Ppi3Ie | EB5bo1ggINfGKizLLE_fni50 |  310 |  315 | 201306   |
[2022-04-02 23:38:41]  | -r8WARpCvCvDCVoqKF6_vOIS | LywPMFEBh4sWSuiMP2Ppi3Ie |  311 |  314 | 201306A0 |
[2022-04-02 23:38:41]  | 8Ayl2tqPxrHbsFgmS6kNaP8W | -r8WARpCvCvDCVoqKF6_vOIS |  312 |  313 | _PAlbTN  |
[2022-04-02 23:38:41]  | qNulpBnt97e6MvsDLaH8caK5 | EB5bo1ggINfGKizLLE_fni50 |  316 |  321 | 201310   |
[2022-04-02 23:38:41]  | axVwVs95gycRZ3vXh1ZiDozB | qNulpBnt97e6MvsDLaH8caK5 |  317 |  320 | 201310A0 |
[2022-04-02 23:38:41]  | oBmSOtwVt7P_oUrYuUr4ZzEg | axVwVs95gycRZ3vXh1ZiDozB |  318 |  319 | _PAlbTN  |
[2022-04-02 23:38:41]  | -PS8dzq1F4Gspfge8JJ_sUlF | EB5bo1ggINfGKizLLE_fni50 |  322 |  327 | 201311   |
[2022-04-02 23:38:41]  | DOFXoSkkuBdBwwWP5JBuBYE0 | -PS8dzq1F4Gspfge8JJ_sUlF |  323 |  326 | 201311A0 |
[2022-04-02 23:38:41]  | uxm_UYCC60knv-2JoN8IaXeW | DOFXoSkkuBdBwwWP5JBuBYE0 |  324 |  325 | _PAlbTN  |
[2022-04-02 23:38:41]  | TQweLHanMsfo6G2BgtZdr0bM | EB5bo1ggINfGKizLLE_fni50 |  328 |  333 | 201312   |
[2022-04-02 23:38:41]  | NHHuee5_rsA0ajj07KAXhWqZ | TQweLHanMsfo6G2BgtZdr0bM |  329 |  332 | 201312A0 |
[2022-04-02 23:38:41]  | 3AQG_b-1aCzJb6AqUNWpyiLD | NHHuee5_rsA0ajj07KAXhWqZ |  330 |  331 | _PAlbTN  |
[2022-04-02 23:38:41]  | dZQ8Lhc_RxfNt7ZyURGGgqRc | EB5bo1ggINfGKizLLE_fni50 |  334 |  339 | 201404   |
[2022-04-02 23:38:41]  | Lr-mxGhiJFefRBYvZXcXxWGU | dZQ8Lhc_RxfNt7ZyURGGgqRc |  335 |  338 | 201404A0 |
[2022-04-02 23:38:41]  | HgE_tpUJaVZk0SaFWQgWZjwQ | Lr-mxGhiJFefRBYvZXcXxWGU |  336 |  337 | _PAlbTN  |
[2022-04-02 23:38:41]  | YXpWCpFQGr9OSjYP2Mb2EbVf | EB5bo1ggINfGKizLLE_fni50 |  340 |  345 | 201405   |
[2022-04-02 23:38:41]  | RHSUg6jBRrz9gMZxSe04ID_r | YXpWCpFQGr9OSjYP2Mb2EbVf |  341 |  344 | 201405A0 |
[2022-04-02 23:38:41]  | qcUPb2IXiAv0xuRacoFbGmn2 | RHSUg6jBRrz9gMZxSe04ID_r |  342 |  343 | _PAlbTN  |
[2022-04-02 23:38:41]  | 3bQkb-9KM2UPyHsBxdCOrO4o | EB5bo1ggINfGKizLLE_fni50 |  346 |  351 | 201406   |
[2022-04-02 23:38:41]  | ONinB7cpurT5nE3XzymcFaU4 | 3bQkb-9KM2UPyHsBxdCOrO4o |  347 |  350 | 201406A0 |
[2022-04-02 23:38:41]  | styPsxtvwT1c1rtvPH7sYPl5 | ONinB7cpurT5nE3XzymcFaU4 |  348 |  349 | _PAlbTN  |
[2022-04-02 23:38:41]  | wIuUWcyKOHZMD71Skn9XV0Dw | EB5bo1ggINfGKizLLE_fni50 |  352 |  357 | 201407   |
[2022-04-02 23:38:41]  | LmHDx6q7-EagUyN-eZvyHKxi | wIuUWcyKOHZMD71Skn9XV0Dw |  353 |  356 | 201407A0 |
[2022-04-02 23:38:41]  | hPtjn3y31VUya3tXRK_Ih_vB | LmHDx6q7-EagUyN-eZvyHKxi |  354 |  355 | _PAlbTN  |
[2022-04-02 23:38:41]  | c377GohpYt9buz3stc4V9U9O | EB5bo1ggINfGKizLLE_fni50 |  358 |  363 | 201409   |
[2022-04-02 23:38:41]  | EPG8yMQfseC1iiw6dt64F3bU | c377GohpYt9buz3stc4V9U9O |  359 |  362 | 201409A0 |
[2022-04-02 23:38:41]  | LiB7rzzpfRQyetE9RslI87ii | EPG8yMQfseC1iiw6dt64F3bU |  360 |  361 | _PAlbTN  |
[2022-04-02 23:38:41]  | khW6Xz6Wxg4c47ccg71irBJc | EB5bo1ggINfGKizLLE_fni50 |  364 |  369 | 201410   |
[2022-04-02 23:38:41]  | I8KVMO73157qSQ2YEmWwjgSB | khW6Xz6Wxg4c47ccg71irBJc |  365 |  368 | 201410A0 |
[2022-04-02 23:38:41]  | Hmx_6g1IbRpsnBvdgdmosTTT | I8KVMO73157qSQ2YEmWwjgSB |  366 |  367 | _PAlbTN  |
[2022-04-02 23:38:41]  | yvDUAPOc78ZtVVwOZQGbKHYL | EB5bo1ggINfGKizLLE_fni50 |  370 |  375 | 201411   |
[2022-04-02 23:38:41]  | NpbKnkM6-NzYxe33pGcGlM-k | yvDUAPOc78ZtVVwOZQGbKHYL |  371 |  374 | 201411A0 |
[2022-04-02 23:38:41]  | eDMvXKY10bOwcwOeWEclyLhC | NpbKnkM6-NzYxe33pGcGlM-k |  372 |  373 | _PAlbTN  |
[2022-04-02 23:38:41]  | ZfcSqeTmsT7lMuZ5_fWim3GV | EB5bo1ggINfGKizLLE_fni50 |  376 |  381 | 201504   |
[2022-04-02 23:38:41]  | 7wlnFKS4m0jpjfD_VoF1COPU | ZfcSqeTmsT7lMuZ5_fWim3GV |  377 |  380 | 201504A0 |
[2022-04-02 23:38:41]  | YHNUkuy84FV-PuHK6UpLZox2 | 7wlnFKS4m0jpjfD_VoF1COPU |  378 |  379 | _PAlbTN  |
[2022-04-02 23:38:41]  | AFb9TIg7x-5FLiqc1GLNYlBS | EB5bo1ggINfGKizLLE_fni50 |  382 |  387 | 201505   |
[2022-04-02 23:38:41]  | L2EgFX9B5L38u7gwYhXRmZRA | AFb9TIg7x-5FLiqc1GLNYlBS |  383 |  386 | 201505A0 |
[2022-04-02 23:38:41]  | XwQECcjIUkwIETCO1eYsGoJ2 | L2EgFX9B5L38u7gwYhXRmZRA |  384 |  385 | _PAlbTN  |
[2022-04-02 23:38:41]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:41]  42 rows in set (0.001 sec)
[2022-04-02 23:38:41]  
[2022-04-02 23:38:41]  root@localhost [lychee_db]> SELECT a.id, a.parent_id, a._lft, a._rgt, ba.title
[2022-04-02 23:38:51]      -> FROM albums AS a
[2022-04-02 23:38:51]      -> JOIN base_albums AS ba
[2022-04-02 23:38:51]      -> ON (ba.id = a.id)
[2022-04-02 23:38:51]      -> WHERE a.parent_id IN (
[2022-04-02 23:38:51]      ->   SELECT a2.id
[2022-04-02 23:38:51]      ->   FROM albums AS a2
[2022-04-02 23:38:51]      ->   WHERE a._lft BETWEEN 304 AND 388
[2022-04-02 23:38:51]      -> )
[2022-04-02 23:38:51]      -> ORDER BY a._lft ASC;
[2022-04-02 23:38:52]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:52]  | id                       | parent_id                | _lft | _rgt | title    |
[2022-04-02 23:38:52]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:52]  | O0WXENN69ljYUH7PlsEE5P2J | EB5bo1ggINfGKizLLE_fni50 |  304 |  309 | 201303   |
[2022-04-02 23:38:52]  | XSclEoP-CXQ9ihXZI3ghwBov | O0WXENN69ljYUH7PlsEE5P2J |  305 |  308 | 201303A0 |
[2022-04-02 23:38:52]  | aBoqfd88wawyPREZ-r_uTytz | XSclEoP-CXQ9ihXZI3ghwBov |  306 |  307 | _PAlbTN  |
[2022-04-02 23:38:52]  | LywPMFEBh4sWSuiMP2Ppi3Ie | EB5bo1ggINfGKizLLE_fni50 |  310 |  315 | 201306   |
[2022-04-02 23:38:52]  | -r8WARpCvCvDCVoqKF6_vOIS | LywPMFEBh4sWSuiMP2Ppi3Ie |  311 |  314 | 201306A0 |
[2022-04-02 23:38:52]  | 8Ayl2tqPxrHbsFgmS6kNaP8W | -r8WARpCvCvDCVoqKF6_vOIS |  312 |  313 | _PAlbTN  |
[2022-04-02 23:38:52]  | qNulpBnt97e6MvsDLaH8caK5 | EB5bo1ggINfGKizLLE_fni50 |  316 |  321 | 201310   |
[2022-04-02 23:38:52]  | axVwVs95gycRZ3vXh1ZiDozB | qNulpBnt97e6MvsDLaH8caK5 |  317 |  320 | 201310A0 |
[2022-04-02 23:38:52]  | oBmSOtwVt7P_oUrYuUr4ZzEg | axVwVs95gycRZ3vXh1ZiDozB |  318 |  319 | _PAlbTN  |
[2022-04-02 23:38:52]  | -PS8dzq1F4Gspfge8JJ_sUlF | EB5bo1ggINfGKizLLE_fni50 |  322 |  327 | 201311   |
[2022-04-02 23:38:52]  | DOFXoSkkuBdBwwWP5JBuBYE0 | -PS8dzq1F4Gspfge8JJ_sUlF |  323 |  326 | 201311A0 |
[2022-04-02 23:38:52]  | uxm_UYCC60knv-2JoN8IaXeW | DOFXoSkkuBdBwwWP5JBuBYE0 |  324 |  325 | _PAlbTN  |
[2022-04-02 23:38:52]  | TQweLHanMsfo6G2BgtZdr0bM | EB5bo1ggINfGKizLLE_fni50 |  328 |  333 | 201312   |
[2022-04-02 23:38:52]  | NHHuee5_rsA0ajj07KAXhWqZ | TQweLHanMsfo6G2BgtZdr0bM |  329 |  332 | 201312A0 |
[2022-04-02 23:38:52]  | 3AQG_b-1aCzJb6AqUNWpyiLD | NHHuee5_rsA0ajj07KAXhWqZ |  330 |  331 | _PAlbTN  |
[2022-04-02 23:38:52]  | dZQ8Lhc_RxfNt7ZyURGGgqRc | EB5bo1ggINfGKizLLE_fni50 |  334 |  339 | 201404   |
[2022-04-02 23:38:52]  | Lr-mxGhiJFefRBYvZXcXxWGU | dZQ8Lhc_RxfNt7ZyURGGgqRc |  335 |  338 | 201404A0 |
[2022-04-02 23:38:52]  | HgE_tpUJaVZk0SaFWQgWZjwQ | Lr-mxGhiJFefRBYvZXcXxWGU |  336 |  337 | _PAlbTN  |
[2022-04-02 23:38:52]  | YXpWCpFQGr9OSjYP2Mb2EbVf | EB5bo1ggINfGKizLLE_fni50 |  340 |  345 | 201405   |
[2022-04-02 23:38:52]  | RHSUg6jBRrz9gMZxSe04ID_r | YXpWCpFQGr9OSjYP2Mb2EbVf |  341 |  344 | 201405A0 |
[2022-04-02 23:38:52]  | qcUPb2IXiAv0xuRacoFbGmn2 | RHSUg6jBRrz9gMZxSe04ID_r |  342 |  343 | _PAlbTN  |
[2022-04-02 23:38:52]  | 3bQkb-9KM2UPyHsBxdCOrO4o | EB5bo1ggINfGKizLLE_fni50 |  346 |  351 | 201406   |
[2022-04-02 23:38:52]  | ONinB7cpurT5nE3XzymcFaU4 | 3bQkb-9KM2UPyHsBxdCOrO4o |  347 |  350 | 201406A0 |
[2022-04-02 23:38:52]  | styPsxtvwT1c1rtvPH7sYPl5 | ONinB7cpurT5nE3XzymcFaU4 |  348 |  349 | _PAlbTN  |
[2022-04-02 23:38:52]  | wIuUWcyKOHZMD71Skn9XV0Dw | EB5bo1ggINfGKizLLE_fni50 |  352 |  357 | 201407   |
[2022-04-02 23:38:52]  | LmHDx6q7-EagUyN-eZvyHKxi | wIuUWcyKOHZMD71Skn9XV0Dw |  353 |  356 | 201407A0 |
[2022-04-02 23:38:52]  | hPtjn3y31VUya3tXRK_Ih_vB | LmHDx6q7-EagUyN-eZvyHKxi |  354 |  355 | _PAlbTN  |
[2022-04-02 23:38:52]  | c377GohpYt9buz3stc4V9U9O | EB5bo1ggINfGKizLLE_fni50 |  358 |  363 | 201409   |
[2022-04-02 23:38:52]  | EPG8yMQfseC1iiw6dt64F3bU | c377GohpYt9buz3stc4V9U9O |  359 |  362 | 201409A0 |
[2022-04-02 23:38:52]  | LiB7rzzpfRQyetE9RslI87ii | EPG8yMQfseC1iiw6dt64F3bU |  360 |  361 | _PAlbTN  |
[2022-04-02 23:38:52]  | khW6Xz6Wxg4c47ccg71irBJc | EB5bo1ggINfGKizLLE_fni50 |  364 |  369 | 201410   |
[2022-04-02 23:38:52]  | I8KVMO73157qSQ2YEmWwjgSB | khW6Xz6Wxg4c47ccg71irBJc |  365 |  368 | 201410A0 |
[2022-04-02 23:38:52]  | Hmx_6g1IbRpsnBvdgdmosTTT | I8KVMO73157qSQ2YEmWwjgSB |  366 |  367 | _PAlbTN  |
[2022-04-02 23:38:52]  | yvDUAPOc78ZtVVwOZQGbKHYL | EB5bo1ggINfGKizLLE_fni50 |  370 |  375 | 201411   |
[2022-04-02 23:38:52]  | NpbKnkM6-NzYxe33pGcGlM-k | yvDUAPOc78ZtVVwOZQGbKHYL |  371 |  374 | 201411A0 |
[2022-04-02 23:38:52]  | eDMvXKY10bOwcwOeWEclyLhC | NpbKnkM6-NzYxe33pGcGlM-k |  372 |  373 | _PAlbTN  |
[2022-04-02 23:38:52]  | ZfcSqeTmsT7lMuZ5_fWim3GV | EB5bo1ggINfGKizLLE_fni50 |  376 |  381 | 201504   |
[2022-04-02 23:38:52]  | 7wlnFKS4m0jpjfD_VoF1COPU | ZfcSqeTmsT7lMuZ5_fWim3GV |  377 |  380 | 201504A0 |
[2022-04-02 23:38:52]  | YHNUkuy84FV-PuHK6UpLZox2 | 7wlnFKS4m0jpjfD_VoF1COPU |  378 |  379 | _PAlbTN  |
[2022-04-02 23:38:52]  | AFb9TIg7x-5FLiqc1GLNYlBS | EB5bo1ggINfGKizLLE_fni50 |  382 |  387 | 201505   |
[2022-04-02 23:38:52]  | L2EgFX9B5L38u7gwYhXRmZRA | AFb9TIg7x-5FLiqc1GLNYlBS |  383 |  386 | 201505A0 |
[2022-04-02 23:38:52]  | XwQECcjIUkwIETCO1eYsGoJ2 | L2EgFX9B5L38u7gwYhXRmZRA |  384 |  385 | _PAlbTN  |
[2022-04-02 23:38:52]  +--------------------------+--------------------------+------+------+----------+
[2022-04-02 23:38:52]  42 rows in set (0.001 sec)

nagmat84 commented 2 years ago

Either I am totally blind or we are indeed looking at the exact same result sets. Why does the DB not allow to drop these records?! Fore me the math does not add up. I cannot make a consistent picture out of it. The only option I see here is that you provide us with a dump of your DB to experiment with. But I have little hope. Moreover, I am on a family trip and I won't have the chance set up a test DB with your records before next weekend.

geeklihui commented 2 years ago

@nagmat84 Wow, have a nice trip! I can provide a dump of DB, please tell me your email address or other way to send it to you. But acutually I am not hurry about this issue, we can focus on it after your trip.

nagmat84 commented 2 years ago

Can you provide me with a direct download link from your server?

geeklihui commented 2 years ago

Can you provide me with a direct download link from your server?

Hi I generated a sharing link with password protected: http://139.224.68.119:20788/s/ZfR5KkoCGcaey6y password: nagmat84_pw It will outdate on 2022-04-11.

nagmat84 commented 2 years ago

I have downloaded the SQL dump. Unfortunately, it might take until next weekend before I get to look at it.

nagmat84 commented 2 years ago

@geeklihui wrote on 2022-03-30

Steps to reproduce the behavior: Issue 1: Click on map. Issue 2: Just visit Lychee. Issue 3: Just open an album. Issue 4: Click "import from server" or just switch albums or click on settings, logs and so on.

Wrt. to issue 1: Map of which album specifically?

Using your DB dump, I cannot reproduce the problems above. Obviously, I don't see any thumbnails, because I only have the DB dump and no image files,but I see proper HTTP GET requests for thumbnails with plausible URLs for thumbnails in the development console of my browser. The number of GET requests and their URLs matches the expectation based on the JSON response for the album. So, no surprises there. In particular, I do not experience any JS exception. This is in line with the fact, that we never have found an error on the DB level.

Please, report the IDs of the albums which specifically failed.

Then I can test those albums explicitly. The ID is displayed as part of the URL in the browser's address bar. It is a 24-character long alpha-numeric string. Your screenshots do not show these IDs, so I did my tests based on randomly browsing your albums. But I am running out of hope to find anything.

@geeklihui wrote on 2022-04-01

A few more question, how can I delete the test album I created according to #1251 (comment)? I tried to delete again just now, and I got these in log:

I haven't yet tested the problem with deleting the test album which you reported later. I have a fairly good assumption what might go wrong there, but still need to check that.

However, using your DB dump I discovered a performance issue which I already assumed to be fixed. Basically, your DB optimizer picks the wrong indices for certain DB queries and thus the affected queries takes 10 times longer than they should. I will open a different issue for that. Nonetheless, this does not affect the correctness of the results and is no explanation for your problems.

geeklihui commented 2 years ago

@nagmat84 Map of album "Cam" with id "yM5DZ3SV6aIORWk1Q8E1KCjR".

nagmat84 commented 2 years ago

Partially good news. The problem with a failing foreign key constraint during deletion should be fixed by https://github.com/LycheeOrg/laravel-nestedset/pull/6. Although, there is still a single test which fails, but I am confident to get that fixed, too.

However, I still cannot reproduce your other problems.