My best guess, but purely speculative, is that the WAL file for the SQLite db used by PHPhotoLibrary (or some other part of PhotoKit) wasn't properly checkpointed—maybe it failed? Or an iCloud backup saved it prior to a checkpoint, and it hung around?—perhaps even from years ago before PhotoKit was introduced.
Whenever it happened, it left some unreachable but failed commits behind, leading to an inconsistency that was never overwritten by later checkpoints--maybe because they had some logic to just create a new WAL?
Then, some change in iOS 17.5 included changes to either SQLite itself or the PHPhotoLibrary framework schema used for photos, necessitating some migration to run which caught the earlier commits and, well, committed them. Whatever happened to the WAL was preserved via iCloud Backup. As a result, image files with seemingly orphaned references were never deleted from iCloud Backups because they were never marked for deletion.
This is my guess because (a) WAL was introduced in 2010 (the latest that photos reappeared) but nobody (that I've found) reported photos newer than 2016 reappearing, which is when a SQLite change to WAL handling fixed an issue where large writes in a single transaction could result in multiple duplicate pages being saved to the WAL, due to the checkpoint starving out when it couldn't get an exclusive read of the WAL itself.
All speculation, of course. But I have personally noticed the WAL for photos getting way too big in earlier iOS versions, so I suspect something around checkpointing is the root cause.
>This update provides important bug fixes and addresses a rare issue where photos that experienced database corruption could reappear in the Photos library even if they were deleted.
Could someone explain how this is a "database corruption" issue.
Whenever it happened, it left some unreachable but failed commits behind, leading to an inconsistency that was never overwritten by later checkpoints--maybe because they had some logic to just create a new WAL?
Then, some change in iOS 17.5 included changes to either SQLite itself or the PHPhotoLibrary framework schema used for photos, necessitating some migration to run which caught the earlier commits and, well, committed them. Whatever happened to the WAL was preserved via iCloud Backup. As a result, image files with seemingly orphaned references were never deleted from iCloud Backups because they were never marked for deletion.
This is my guess because (a) WAL was introduced in 2010 (the latest that photos reappeared) but nobody (that I've found) reported photos newer than 2016 reappearing, which is when a SQLite change to WAL handling fixed an issue where large writes in a single transaction could result in multiple duplicate pages being saved to the WAL, due to the checkpoint starving out when it couldn't get an exclusive read of the WAL itself.
All speculation, of course. But I have personally noticed the WAL for photos getting way too big in earlier iOS versions, so I suspect something around checkpointing is the root cause.