With the release of iOS 17.2 and macOS 14.2, there is now the ability to see a count of the number of chat messages stored in iCloud. A bunch of people were comparing counts and what not both on social media and in work chats, so I decided to take a look at my count too. I noticed that for some reason my number was an order of magnitude higher than everyone else’s. While many people had hundreds of thousands of messages, I had 2.9 Million. I am not a popular individual, so I knew something had to be wrong. I decided to investigate by hacking the messages database.
After some internet searching, I found that the messages database on the Mac is stored at ~/Library/Messages/chat.db. It’s a pretty simple SQLite database that can be accessed via a number of different db utilities including the sqlite3 command line tool. The trick is that whatever app is accessing the database needs to be granted full disk access in the security settings. My preferred tool for looking at databases is TablePlus. After a quick group by query into the message table, it became clear that the vast majority of these messages are from SMS.
select service, count(1) from message group by 1;
(Wow, look at those old Jabber messages, I had no idea they were still in there)
Honing in on the SMS messages, I found that 2.8 Million of these messages contained no text content, and had a value of 39 in the error column. Also they all seemed to have a date value of 489239966000000000. It appears that this value is the number of nanoseconds since 2001-01-01, so it correspond to Sunday, July 3, 2016. I have no idea what’s going on with the date. Maybe it is some sort of default value, or my maybe account had some sort of bug at that time and flooded my database with these erred messages.
My next thought is, of course, how do I clean up just these specific messages? I want them removed everywhere, not just my Mac, but iCloud too. These messages are not associated with any particular chat, so I can’t just use the Messages UI, instead I try to see if I can just delete them from the database. I try the following query:
DELETE FROM "message" WHERE "ROWID" = <id goes here>';
When I tried this, I got the error: no such function: after_delete_message_plugin. It appears that this database has a number of triggers defined that do various things when items are deleted from the database. In this case, the message delete trigger is trying to call a function, but apparently this function isn’t defined in the database. I learned that SQLite has this feature called Application-Defined SQL Functions. This feature allows apps to define custom SQL functions that call back into the application code. I assume that Messages must define the after_delete_message_plugin function somewhere within the app code, so I don’t have access to it when talking directly to the db outside of the app context. I searched for a bit to see if there was some way to override this function, but I couldn’t find anything quickly. I decided to try a different approach. What if instead, I associate each of these messages with a chat, then delete the chat in the app!
In order to do this, I found a chat that I will willing to delete: one of those SMS shortcode chats that sent me a one time access code or something else automated. I then crafted a query that would associate all these bad SMS messages with that chat through the chat_message_join table.
INSERT INTO chat_message_join (chat_id, message_id, message_date)
SELECT
1018,
ROWID,
date
FROM
message
LEFT OUTER JOIN chat_message_join ON chat_message_join.message_id = ROWID
LEFT OUTER JOIN chat_recoverable_message_join ON chat_recoverable_message_join.message_id = message.ROWID
WHERE
date = 489239966000000000
AND account_guid IS NULL
AND service = 'SMS'
AND account IS NULL
AND text IS NULL
AND error = 39
AND handle_id = 0
AND attributedBody IS NULL
AND chat_message_join.chat_id IS NULL
AND chat_recoverable_message_join.chat_id IS NULL
AND balloon_bundle_id IS NULL
ORDER BY
ROWID
LIMIT 500000
This query selects all the bad SMS messages (I added some extra conditions just to be extra sure I wasn’t deleting anything that could be important), and inserts them into the chat_message_join table with the chat ID of the chat I want to delete (in this case 1018). By joining to the chat_message_join and chat_recoverable_message_join tables and selecting on NULL values, I also ensure that I don’t insert any new message joins for messages that are already joined to a chat. I also limit the query to 500,000 messages at a time, because I have no idea what Messages will do when trying to delete that extreme number of messages all at once.
After relaunching Messages, the chat in question now shows all these blank messages in their erred state within a chat window.
Now for the moment of truth, I delete the chat.
Message got stuck in this exact state showing the trash can in a red background for many hours while the IMDPersistenceAgent process pegged a CPU core. Whatever the app is doing, it seems like it must be doing it synchronously, for each message, maybe it has something to do with that after_delete_message_plugin function.
Eventually, the process finished and the chat was deleted. It showed in the Deleted Chat view with all of the messages still linked, and I could see all the associations in the chat_recoverable_message_join table.
(I did a 1,000 and 10,000 test batch before doing the full 500,000)
Deleted messages say they take 30 days before the are no longer recoverable on device, and then 10 more days after that before they are deleted from iCloud. To speed this process up, I force deleted the chat out of Recently Deleted. This seemed to remove all the chat_recoverable_message_join associations as well as the original message records from the local database. The operation completed very quickly (relative to the process of deleting the chat in the first place).
After this I forced iCloud syncs on all my devices, and this put my phone into a very hot state. The battery drained very quickly and I even managed to catch this interesting error message:
iCloud still reports the same number of message in the cloud and my other computer has not had these messages deleted from its local database, so I’m not entirely sure yet if this actually did anything useful, but the fact that my phone overheated leads me to believe that it at least did something. I guess I’ll find out in 10 days if the iCloud count goes down or not.
Just for fun, I turned off the iCloud sync on the original Mac and then turned it back on. The sync process re-download all those deleted messages, so I’m thinking that might not be a good sign that this worked the way I wanted. I’ll make an update here after 10 days or if I learn anything new.
Do you have any contacts on the Message team or have any insight here? If so please reach out to me on Mastodon. I have also filed this as FB13485744
Update: 2023-01-09
It has been more than 10 days and the number of messages in iCloud has not gone down, so apparently my process for deleting these messages did not work.