iOS 11 Camera Roll Out of Order FIX/tutorial

Question

Level 3

809 points

iOS 11 Camera Roll Out of Order FIX/tutorial

I keep reposting this here, but figured it would be worth posting on its own. After upgrading to iOS 11, I noticed that the photos in my camera roll were no longer ordered by date, but seemingly randomly organized. New photos were correctly placed in the roll, but any existing ones were random. After discovering a reddit thread with a fix that didn't work, I tweaked the method using Terminal on the Mac. My method should be possible using Windows as well, via the command prompt:

0)

Back up your phone and place it in Airplane mode (DigiDNA's suggestion)

1)

Make sure Photos is running on your iPhone

Download iMazing or similar

Connect iPhone to Mac via USB

In the sidebar select your phone

Click File System > Media > PhotoData

Select Photos.sqlite and Copy to Mac at bottom

Find the file you've just copied

First, make a copy of it in a separate location in case of emergency

2)

Open Terminal

At the prompt type

$ sqlite3

You should see your SQLite version listed

Type .open at the sqlite> prompt, then drag the Photos.sqlite file from Finder to the sqlite> prompt, then hit Enter:

sqlite> .open /Users/yourname/Desktop/Photos.sqlite

To test that you've opened the file properly, type .tables and hit enter. You should see a list of files starting with Z, which is what you want.

At the prompt again, paste in:

UPDATE zgenericasset SET zaddeddate = zdatecreated WHERE zaddeddate>0 AND zdirectory LIKE 'DCIM%';

Hit enter. There should be a slight, 1 second delay before you see the sqlite> prompt again. This means the query is working, which is good. You should also see the Modified date for the sqlite file change in Finder

Finally, type .save Photos.sqlite and hit Enter (not actually sure if this is necessary or does anything, but I did it when I was successful)

Quit Terminal, clicking Close on the warning window

3)

Go back to iMazing into the PhotoData folder

I made a 'backup' of the existing Photos.sqlite by renaming it to oldPhotos.sqlite

Copy over the modified Photos.sqlite from your Mac

Quit iMazing

Force quit the Photos app

Reopen Photos

Hopefully, your photos will be in order!

Let me know if this works for you!

iPhone SE, iOS 11.0.3

Posted on Oct 25, 2017 12:37 PM

Reply

Answer 1

Top-ranking reply

annapl

Level 1

4 points

Mar 8, 2018 9:29 AM in response to minacrime

Thank you worked for me i would like to guide windows users little better follow

step 1 copy Photos.sqlite to c: drive

step 2

download sqlite3 from https://www.sqlite.org/2018/sqlite-tools-win32-x86-3220000.zip move to c:

rename extracted directory to sqlite now run command prompt as administrator type cd\ hit enter type cd sqlite sqlite> .open c:\Photos.sqlite To test that you've opened the file properly, type .tables and hit enter. You should see a list of files starting with Z, which is what you want. At the prompt again, paste in: UPDATE zgenericasset SET zaddeddate = zdatecreated WHERE zaddeddate>0 AND zdirectory LIKE 'DCIM%'; Hit enter. There should be a slight, 1 second delay before you see the sqlite> prompt again. This means the query is working, which is good. You should also see the Modified date for the sqlite file change in Finder Finally, type .save Photos.sqlite and hit Enter (not actually sure if this is necessary or does anything, but I did it when I was successful) Quit Terminal, clicking Close on the warning window then follow step 3 Thanks to @minacrime

Reply

Answer 2

Nov 24, 2017 6:39 AM in response to minacrime

If you have iCloud Photo enabled and you want to ensure the re-ordering by date created of ALL photos in "All Photos", remove the zdirectory clause:

UPDATE zgenericasset SET zaddeddate = zdatecreated WHERE zaddeddate>0;

Reply

Answer 3

minacrime Author

Level 3

809 points

Jan 2, 2018 8:23 AM in response to bpondo89

According to another response here:

If you have iCloud Photo enabled and you want to ensure the re-ordering by date created of ALL photos in "All Photos", remove the zdirectory clause:

UPDATE zgenericasset SET zaddeddate = zdatecreated WHERE zaddeddate>0;

I have not attempted this since I don't use iPL but as long as you're making a backup of Photos.sqlite you should be fine. Also, note that the database will also rebuild itself even if it's deleted, so there's not too much risk of damage.

From my understanding, those other two files get updated alongside the actual db, but I have never personally had to copy all 3. Others have only reported success when doing all of them.

Reply

Answer 4

Egd88

Level 1

4 points

Feb 2, 2018 10:35 AM in response to minacrime

Just tried all steps on windows 7 and...It worked like a charm! 🙂🙂🙂

- First I tried to delete the 3 sqlite files, and IOS indeed rebuilt files and populated photo app in the same incorrect folder 😉

- Then I downloaded sqlite for windows and IMazing for windows (which is excellent, i used to make my ios stuff with Itools)

Prompt commands reacts exactly the same as mentionned in your tutorial. The 3 files changed their dates after the UPDATE command, but also changed their dates after the .save command (guess it matters).

I also noticed that the -wal file took almost the same size that the simple Photos.sqlite, but then reduced randomly when i transfered it back on the phone (maybe a TMP file?).

Thank you so much for this Fix!! I'll get rid off hard reboot now...😝

Reply

Answer 5

minacrime Author

Level 3

809 points

Nov 24, 2017 6:39 AM in response to djthomasryan

Thank you!

Reply

Answer 6

minacrime Author

Level 3

809 points

Nov 24, 2017 7:42 AM in response to MightyCamel

Your photos are unlikely to have been deleted; if you remove the sqlite file and immediately open camera roll, it will appear empty while it rebuilds thumbnails.

Yes, try to copy those other files as well. I did not but others have and then been successful.

Reply

Answer 7

Apr 1, 2018 7:38 AM in response to minacrime

Ok just tried my brother’s MacBook last night and that worked! Thanks!

Reply

Answer 8

minacrime Author

Level 3

809 points

Apr 23, 2018 7:35 AM in response to minacrime

I learned about scripting over the weekend and have created a macOS script available on GitHub: https://github.com/messagehome/camerarollfix

Reply

Answer 9

Dec 14, 2017 2:50 PM in response to minacrime

Thank you so much, you’re a life savior🙌-1F3FC;. Btw, it only worked for me after copying the 3 files, Photos.sqlite/sqlite-wal/sqlite-shm.

Reply

Answer 10

Tyderian

Level 1

4 points

Feb 2, 2018 6:40 AM in response to minacrime

OMG! A million thanks to you. This worked perfect. To anyone reading this, I added all THREE files that are created after the "UPDATE..." command - meaning the .sqlite // .sqlite-shm // .sqlite-wal

Thanks again!!

Reply

Answer 11

Dec 1, 2017 5:39 PM in response to minacrime

Went back to the old scrambled out of order format after taking a pic. Apple needs to get this fixed and in order for that to happen, we all need to flood their support inboxes.

Reply

Answer 12

minacrime Author

Level 3

809 points

Dec 29, 2017 8:46 AM in response to matthew.dmg

Did you run the .tables command and if so, did it show anything? Can you upload a Terminal window?

Reply

Answer 13

annapl

Level 1

4 points

Mar 16, 2018 10:07 AM in response to deniseapplehelp

TRY DIFFERENT PC

run command prompt in administrator mode

Reply

Answer 14

Nov 24, 2017 7:28 AM in response to minacrime

hello,

Tried the above and all my photos got deleted.

When saving the new photos.sqilte file two other files are created photos.sqilte-wal and -shm do I need to copy those over as well?

please let me know. thanks.

Reply

Answer 15

marsh73

Level 1

7 points

Dec 18, 2017 2:48 PM in response to minacrime

So I just saw this after I took my phone and macbook to my local repair/service shop and we went through all those steps twice and it didn't work. Could not having it in Airplane Mode be the culprit?
Help! I'm at the end of my tether here..

Reply