Troubleshooting iCloud Photo Sync on Windows – WARNING OBSOLETE

UPDATE: I got 80% of the way through this blog-as-I-debug post before realizing that while iTunes happily pestered me into updates every time I opened it, iCloud was not upgraded along with it. I was on version 7, after upgrading to 11, things changed a lot. I’m publishing this unfinished post since some of it was interesting, and maybe someone who insists on staying on Version 7 of iCloud will have some use for it. There was some interesting SQLite browsing that I found zero references to in my searching. Possibly SQLite still backs iCloud, I haven’t found it though, also, I haven’t tried.

UPDATE 2: After writing the above, I decided to do a dir /s client.db and found a likely new location for the database in current versions of iCloud for Windows – C:\Users\[username]\AppData\Local\Packages\AppleInc.iCloud_nzyj5cx40ttqa\LocalCache\Local\Apple Inc\iCloudPhotoLibrary
I’m not sure if the random character section changes per install or per version or what.

——————

My iPhone upgrade seemed to go magically. That weird cloud pattern and sync was soooo smooth, that obviously it couldn’t be perfect. I noticed eventually that the photo count was wrong.

To be fair, my photo setup was kind of messed up, I’ve been moving files off manually and the photos have migrated over two phones previously. It was a good thing I made a manual backup of the “DCIM” folder.

I only noticed a few days afterwards, so I didn’t want to do a full restore from iTunes – this might not have been the best choice in the end.

After much fiddling, I’ve decided to try and use iCloud photos to get the photos back onto the phone. I hope that I will be able to use it to manage my photos over the long term better anyway, manually grabbing files off the DCIM folder isn’t ideal anymore. The Windows integration was and still is pathetic (which is why this blog post is being written); but, at least the web interface is much better now. I think.

Moving all the JPG only to C:\Users\[username]\Pictures\iCloud Photos\Uploads (the default folder for uploads from my Windows 10 PC).

I don’t have that many photos that I want to keep on my phone… only about 1800 but of course, it didn’t work.

And for some reason, I can’t find any good debugging information.

Check iCloud version, it’s in fine print towards top right when you have iCloud program window open. I started with 7.21, newest is 11 – and it did NOT auto-update. (i may have disabled an auto-update feature in the past)

iCloud Photos is stuck with 300 images left to upload and synchronize. And no indication of what is stuck or why. And multiple web searches turned up only partial help.

First, the work database used by the iCloud Photos sync is in this folder: C:\Users\[username]\AppData\Local\Apple Inc\iCloudPhotoLibrary\client.db

With a little wizardry figured out it was a SQLite database.
(I just opened the file in a text editor and looked for hints in the text – it’s was pretty obvious looking at the head of the file.)

I’m lazy, so I found a GUI: https://sqlitebrowser.org/

I cracked open the client.db file, and I’m more than happy to see that the Apple writers made the clearest design that I could have hoped for.

I found the list of files to be uploaded in the table “files_to_upload” (WOW).

As far as I can tell so far, there is a record for every file that has ever been in the Photos\Uploads folder, and also any files that were manually added via the Tray Icon.

Sort the table by the upload_state column. I only have 2 values in my table:

  • 0 seems to be not uploaded – it matches my stuck file count exactly
  • 2 seems to be upload done

Other columns:

  • path – The file in question is in the “path”
  • cpl_asset_record_id – none of the files that did not upload have this blank; however, a few of the ones that are uploaded also have a blank here – I suspect this is something akin to “cloud” ID
  • mmcs_file_hash – I suspect it’s a hash of the file.
  • path_hash – this is set as the primary key, I would assume (maybe) uploads are done in path_hash ASC order?
  • ROWID – apparently SQLite has ROWID always that can be queried – maybe this is used for order of uploads

I thought maybe this would find some duplicates (because I know there are some) – but no… SELECT COUNT(*) Kount, mmcs_file_hash FROM files_to_upload GROUP BY mmcs_file_hash HAVING Kount > 1

I know that when it is stuck on a file, it does go on to the next one – my test batch of 20 or so files had a couple stuck, but when I uploaded the rest – most of the later ones uploaded.

Is it the file? I made a copy of one to “IMG_5893 – Copy.JPG”

PNG files (screenshots) are NOT handled (WTF). MOV movie files no too. I don’t have a plan, I might convert the PNG files to JPG and just be done with those. MOV I might move to Google Drive or One Drive, or leave the off the phone altogether.

Event Log? I don’t see any in common logs, nor do I see an Apple file.

iCloud executables seem to be here: C:\Program Files (x86)\Common Files\Apple\Internet Services – no signs of log files

Some logs here: C:\Users\lchan\AppData\Roaming\Apple Computer\Logs-Old (found this after upgrading to version 11, I suspect folder was named “Logs” before)

Argh. After all of the above, I found out I was running a really old version (7) of iCloud and iCloud Photos.

C:\Program Files\WindowsApps\AppleInc.iCloud_11.4.12.0_x86__nzyj5cx40ttqa\iCloud

TODO: I got tired of this task and gave up, but the next steps would be to take the list of files in the database that did not upload, write some sort of batch or PowerShell or Python script to copy those out to a new folder, and then try the upload again, I guess. Please comment below if you take it that far.

Tags: , , , ,

Leave a Reply