iCloud Messages in iCloud shows millions of messages in the cloud?

[Makosuke]

I use Messages sync between my phone and two Macs, which mostly just does its thing. I've been having some moderately glitchy behavior recently, though, so started looking into fixing it.

In the process, I noticed something weird, though; the iCloud Messages sync section (both macOS and iOS) shows an absolutely absurd number of messages--over 4 million:

I don't text all that much, and certainly not upwards of 1300 messages a day, every day, for the 10 years since I first bought an iPhone. The Mac and iPhone versions both show the same number, and it does change slightly when new messages come in, so it's not a local display glitch.

Additionally suspicious: On my phone's storage, under Messages, it shows about 1GB of photos, 800MB of videos, 90MB of GIFs, and... 3.5 GB of "other". Tapping "Other" doesn't show anything, and it could be that someone Messaged me an immense file at some point, but there's nothing in the Top Conversations that points to that which makes me wonder if I haven't somehow ended up with 3.4GB of duplicated text messages.

I could of course just delete everything and start fresh, but I'd really rather not wipe my message history if I don't have to, so I'm wondering if anyone else has seen something like this or has any suggestions.

Current plan is the same as for any similar bug, turn of syncing, wait a bit, then turn it back on, for each device. Thus far that's taking an incredibly long amount of time and the number has only gone up slightly, not down.

 

[Porschelust]

Am having the same exact issue! What's the fix on your end? I have a call coming from Mac Support tomorrow...I think it's in the hard drive message folder of the Mac, where it stores all the past messages...I think iCloud is trying to upload or sync everything from there, but I could be wrong! I waited 30 days after turning off iMessage iCloud sync, but that didn't do a thing.

 

[Makosuke]

Nope, my attempted solution was to let one of my Macs sync completely (which took several days), then turn off iCloud messages on everything and wait 31 days for the "undo" to expire. (Note: As far as I can tell, you can only see the undo countdown on an iPhone; it's there on a Mac but is invisible). Which it just did a day or two ago, so I tried turning sync back on tonight.

Nope! Now I have 4,508,501 messages synchronizing, with the same 4,491,618 in the cloud. Unless that ends up getting fixed when the sync completes (which I assume will take days), right back where I started.

If it had worked on the first device I tried, I was planning to wipe Messages on the other two and re-sync from the cloud only, to purge any database weirdness locally.

Which I suppose does act as a little bit of a diagnostic: I have not added 17,000 messages in the past month (or anything within probably two orders of magnitude of that), and that number showed up immediately when I turned on sync, even when I immediately turned it off then tried the next device. As far as I can tell, that only leaves these possibilities:

1. The messages database in the cloud didn't actually purge after 31 days, so I'm just back to syncing with the same corrupt database, but I have a total local history of about 17K messages that it's now trying to add. (This seems unlikely, since I got the same result on three different devices.)

2. There's something fundamentally screwed up with my account, so the phantom 4.5 million messages would be there even if I had zero locally, and no matter how long I wait.

3. There's some database corruption that got synchronized to all three of my devices previously, so they all think they have 4.5 million messages because of some corrupt "magic message" in there. (This isn't likely the case, otherwise it wouldn't have increased by 17K messages.)

4. That number isn't real at all, it's just a calculation bug (but that doesn't explain why it's taking days to sync, then... unless MMS files are somehow getting counted as thousands of individual messages--I guess I could test by deleting images from non-iMessage conversations, or just deleting all media).

If you make any progress with support, please let me know--I'd love to have more info. And if they want to contact another user with the same problem, give me a case number or contact or something, and I'll ping about it.

The only thing else I can think to try is to sync with a device, delete EVERYTHING on that device and let it sync (so there's zero in the cloud and it's synchronized) to see if I can get the count to go down to zero, then try enabling sync on a second device to see if it restores only a few thousand messages or if it jumps up to 4.5 million again. That should at least narrow it down further, but I fear losing 10 years of message history I'd rather not if something goes wrong.

 

[Porschelust]

Scroll down to discussion by secret_agent47. I believe he is on to something there. I deleted messages as instructed a month ago. After I waited 31 days to toggle iCloud sync back on, the expected behavior that I was hoping for did in fact occur on my iPad. The 2 million plus messages disappeared and only one long conversation that I had saved, about 238 messages, was all that remained. YAY! HOWEVER, as soon as I turned on iCloud sync on my newer MAC 1 (Sonoma), it began syncing thousands of messages and will continue to do so, I suppose, all night long. What was at first a celebration for me turned to exasperation when suddenly thousand and thousands of messages began syncing. Even after I tried to “disable and delete” again, nothing was downloading to my devices. So where are these messages coming from and where are they going? Very strange. My suspicion is this has something to do with the hard drive folders which are storing years of iMessages. I saw one folder called chat.db with 1gb or more of data. Before I start deleting cache and other related message folders, I want Apple support to walk me through it so that I’m not accidentally trashing system folders. Read below for a better explanation. I’ll get back to you as I learn more. I have no idea where these 2 million plus messages are or where they are coming from…syncing from the cloud or are they buried in my Mac hard drive and are trying to sync up to the cloud? There is only one conversation in all 3 of my devices consisting of a couple hundred messages. That’s it! It’s quite a mystery.

https://www.reddit.com/r/ios/comments/14id4oa

 

[Makosuke]

That thread didn't really help much, but I did dig a little deeper, and at least can say definitively what's going on, although not necessarily a solution:

On a Mac, I opened up the chat.db database that Messages uses with Database Browser for SQLite to see what's actually going on in there.

It's obviously not easily readable to someone who doesn't know their way around the database structure (which I don't), but it was easy enough to see that the "message" table is where message text is stored, and does indeed contain 4.5 million records. A small fraction contain message text as expected, but the vast majority have more or less no data in them other than the error field, which is "39", and are presumably the problem. (I'll note that there are non-garbage-looking entries also with the error field of 39 instead of 0, and a few others with other error codes.)

This explains why purging everything didn't work--the database full of phantom, empty messages has gotten synced to all my devices, so it doesn't matter what happens on the cloud side, it's just going to re-sync the garbage from whichever device tries.

This doesn't help figure out what created the garbage in the database, although it does superficially appear that it's not still being generated; there appears to have been millions of bad records inserted at some point roughly around the end of last year, but none mixed in with the real messages since.

It may be possible to use a query to manually purge the garbage from the database, but having no idea what the overall structure is that could also easily screw up my entire message history so isn't a great option.

 

[Porschelust]

I think I follow most of what you said, but most of your reply is too deep in the weeds for me. I think I’ve reduced this to something more simple. I’m not a developer, but like you, I did manage to find an application, TextEdit, of all things that enabled me to open and view some of the texts in the history of my message folder. Chat.db had 1 GB more of content in there. Just as I surmised, there were countless texts going back years still buried in there from when I used Time Machine backup to restore a former Mac to my newer Mac. That was an “ah ha“ moment for the Mac support rep I was talking to for almost an hour. He was as perplexed as I was with the millions of messages in there but that I could not see nor download. He believed that this might also be a server issue, and he was going to elevate the issue to the engineering team, since he also noted many other inputs from other users with similar syncing issues to mine. It’s getting attention and he told me to stay current with all software updates.

At the same time though, he tried one thing to get the ball rolling in case the issue is also related to something on my end, and I believe he cracked the code. We went in and simply tossed the entire Mac OS message folder in the trash. Finder > Go To Folder > Username > Messages…I believe that’s the flow to locate message folder, or close to it, but am not sitting in front of my Mac right now. After trashing the entire message folder, we signed out of ICloud on all devices, did a restart, and signed back in, making sure all devices were synced up again. Sure enough, my millions of messages suddenly went away…there were only 159,000 messages or so left, but again, he was confused as to why I could not see them or why they wouldn’t download. I only have one message conversation thread that I want to keep on all three devices. In that thread there are 200 plus messages, many with photos, videos. So, where are the other 159,000 messages that I can’t see?

He was still confused but says they are there somewhere, but he is not allowed to see them, or there is a counting error of some kind on the server end. His last advice was just let it sit, and be patient…it may resolve itself with continued syncing. Sure enough, today those 159,000 messages are now down to 98,000 and falling. I believe when I reset the memory from keep messages forever to 30 days, it is now eliminating those leftover messages as each day counts down. I remember going into my message app, and trashing messages along the way these last few years. Then, just to clean up my local storage more, I would enter the trash, and delete them there as well, instead of waiting the 30 days for them to automatically go away. I remember the warning - if you delete from the trash, you cannot recover them.…this action can’t be undone, or something to that effect. Perhaps that is why they would not download from the server. I deleted them permanently never to be recovered, but the server has the count still in place for those lost messages.

In any event, all is well now, and my message issues are slowly resolving themselves with time and repeated syncs. I would chat with Apple Support and walk through with them online the deletion of your message folder, and go from there. One important point…every time, you make any changes to your iMessage parameters, the Keep Forever option comes back as a default. It will even pop up to let you know this. If you don’t want all this crap to build up in your folder over time, and you don’t need or require an extensive history of your messages, then simply choose 30 days or 1 year instead of forever. Hope this helps. Case number for me that you can reference is: 102232122670

 

[Makosuke]

Thanks for the information on what support had you do; it sounds like the rep you dealt with was much more helpful than either of the two I had a month ago--neither looked anywhere near that deeply into the issue, so I gave up on trying to get "official" help.

I only have one message conversation thread that I want to keep on all three devices. In that thread there are 200 plus messages, many with photos, videos. So, where are the other 159,000 messages that I can’t see?

This is a fundamental difference between your situation and mine, and why I've been trying very different strategies to get it resolved, even now knowing roughly what the problem is: I really do want to keep my entire message history (I've already manually deleted conversations I don't want to keep), so "just delete it all" is not a step I want to try.

I'm going to keep prodding at alternate options before going the nuclear route because of that.

I'll note that I suspect, though am not sure, that since the phantom messages appear to all have been created at roughly the same time, and their timestamp I believe shows an old garbage date (it's hard to translate the timestamp format), were I to set it to keep 30 days of messages I suspect they'd get cleaned out along with everything else old. Not going to test, though.

I'll also add that, now that I get what's going on with the database, all the things you were seeing make sense: The backend sync apparently syncs the entire message database, which includes millions of garbage effectively-blank messages that aren't attached to any conversation, which is why even a tech looking at the backend couldn't see anything. They're in the database, but no tool that shows "real" messages will show anything, because they're effectively "not really there".

That also pretty much explains why your fix slowly worked: A bunch of the garbage messages had been synced to the cloud, but once you deleted the ENTIRE database on your end, it started re-syncing, and since all messages, including the phantom ones, no longer existed locally, it is gradually deleting them from the cloud as well as the database syncs.

It's possible that if there is actual database corruption, and the cloud sync doesn't check for it, you could have problems down the line if you don't wait the full 30 days for it to purge before re-syncing a "fresh" OS with no bad messages on it, but you'll probably be okay once it finishes deleting everything in the cloud.

I am curious how this happens in the first place, though, and will hold out hope that if enough people are having it Apple will roll a fix into Messages that'll clean the database for the user.

In the mean time I'm going to see if I can't do it manually, although apparently either my sqlite3 install has something weird in it or the database is corrupt in some way that it can't do a bulk delete from it--when trying to run a delete query I get the error "Error: no such function: after_delete_message_plugin", which Google impressively finds NO results for. How is that even possible with an open-source program?

 

[Porschelust]

Thx for your reply. You pretty much have a good grasp of the situation as I see it, although, like you, I’m not all that sure what caused these issues in the first place. I think it may have come down to the confluence of several factors that happened over time.

1. Time Machine restore when I upgraded my Mac, which brought in all that history.
2. My unconventional habit of keeping my local storage at a minimum by first deleting the messages that I didn’t want to keep and then subsequently deleting the trash folder too (Warning - cannot be undone!). So, messages are not really visible there anymore on the server, and therefore can’t be downloaded if you use the “disable and delete“ function, but remain in the count with repeated syncs?
3. Corruption in original database that has now been ostensibly removed by my nuclear option of trashing the message folder and starting over.
4. Not fully appreciating that the default message history parameter was set at keeping messages forever, so thats why they pile up over time within the database. If you combine the forever option with any uncorrected database corruption, along with buggy behavior with each progressive OS upgrade, then you may have the recipe for overlapping issues complicating things even more over time. Maybe as secret_agent47 surmised, there was a syncing error that caused duplicate copies of crap being recycled over and over. Also, the more stuff archived, the longer these syncs take if database gets overwhelmed with content.


As of this morning, all of those last messages of the remaining 159,000 or so have now been purged from the cloud. There are only 193 remaining in the cloud which matches what I see locally on each device. It only took 3 days or so, since I talked to the tech, for this syncing process to finish up. I can also see quite clearly that, because I have message history set for 30 days, each message at the top is gradually being deleted as they each reach the elapsed 30 days. Now that I’m back to square one, I may now choose to keep messages for 1 year so that I don’t let recent history, which is relatively important for me to retain in the short term, to get away from me. That may be a good compromise to keep things from building up excessively in the database, but if Apple wants to make the use of iCloud backups attractive and user friendly, and less time consuming, then syncing really should be self-cleansing and able to handle the archiving of message history no matter what duration is selected. That’s where my tech intimated that they are well aware of these syncing anomalies and are drilling down with laser focus on these issues, as I write, so stay tuned and keep current with imminent software updates that should hopefully kick this problem to the curb. If they don’t fix this once and for all, then I may simply turn off message syncing and delete locally from each device.

Best, Ray