jailbreak – [Tutorial] Fix: iPhone Photo Database corrupt and/or Cameral Roll out of order

My photos.sqlite file became corrupt somehow, and ‘Camera Roll’ and other libraries disappeared. Backup was also corrupt.

There is a procedure to rebuild a corrupt database: use iMazing to delete /Media/Photodata/photos.sqlite. Upon reboot, the database rebuilds automatically, but the Camera Roll ends up sorted by the order photos were added during the rebuild instead of their age or date taken, which makes it largely unusable. This is a result of the enumeration of the DCIM/1xxAPPLE folders, which do not store photos in the order taken/added.

I decided to attack the database itself and discover how the sort order is determined. In the zgenericasset table, there is a zaddeddate column, which corresponded perfectly with the displayed order in the Camera Role while zdatecreated corresponded with the date and order taken. Some trial and error led me to a process that worked for me and many since through at least ios13.3.1.

Steps to fix:

  • Install SqliteBrowser

  • Use iMazing to copy /Photodata/photos.sqlite to your machine [/Media/Photodata/ in ios12 and earlier]

  • Open the local copy of photos.sqlite (keep a backup somewhere just in case)

  • Run query(ies) below

  • Write changes to database file (file/write changes)

  • Force quit Photos app on your phone (IMPORTANT)

  • Use iMazing to copy database file back to phone, overwrite the existing file

  • Open Photos app

  • Get some sunglasses

  • Put. Them. On!

Query:
UPDATE zgenericasset
SET zaddeddate = zdatecreated
WHERE zaddeddate>0 AND zdirectory LIKE ‘DCIM%’

My stuff is in ORDER again!! I still can’t believe that worked…

Edit:
If you use iCloud for photos, this query will fix the order for those images too:
UPDATE zgenericasset
SET zaddeddate = zdatecreated
WHERE zaddeddate>0 AND zdirectory LIKE ‘%CPLAssets%’

Update 20170622: The DigiDNA folks who make iMazing gave me a free license for doing this writeup, and they’ve been really great in communications since then. Nice to meet good folks who stand behind their solutions.

Update 20171018: A user reached out to me and confirmed that this worked using a sqllite editor in Terminal (assuming OSX) but that he needed to put a ‘;’ at the end of the query. He got it to work on ios10 and ios11.0.3.

Update 20190103: Someone just gave me gold for this three years later. So glad this is still useful. He said it still works on an iphonex w ios12.

Update 20190920: Just got a note from someone who tested this on ios13: it still works, but the photo database location has changed to /Photodata/.

Update 20200404: Got a pm from someone, says it still works in 13.3.1!

Source

Leave a Reply

Your email address will not be published. Required fields are marked *