Disclaimer: This process worked for me, and it may work for you, but it may screw up your saved SMS messages and your ability to receive future ones. Follow the process below at your own risk, as I am not responsible for anything that goes wrong. Keep in mind: I don’t care if I lose all my SMS messages… I’d just prefer NOT to.

For the last week or so, my iPhone (3G and running v2.1) has had an issue where it states that I have one unread SMS message. The problem is, I don’t have an unread SMS message, or at least, the iPhone isn’t kind enough to display it for me to read and be done with it. As it turns out, I’m not the only one who has experienced this issue, and there isn’t a way to reset the unread SMS message count without buying some non-Apple software (pfft!) or having a jailbroken iPhone so you can go in and clean up the mess yourself.

Thankfully, I have the latter (I really should post about why I jailbroke my iPhone, but I’ll do that later).

As long as you know how to SSH into your iPhone, you’re all set. Oh, and far be it for me to state the obvious, but you’ll also want to ensure that the only unread SMS message(s) on your iPhone are the ones that you cannot read at all (yeah… “duh”). Anyway, here’s how I fixed mine up:

IMPORTANT: I recommend you DO NOT copy-n-paste my examples below. My site uses smart quotes (” “), which are fancy versions of actual quotes, and they don’t translate well when pasting them into Terminal.

First, log into your iPhone via Terminal. Once connected, change the directory to where the SMS database is:

iPhone:~ root# cd /private/var/mobile/Library/SMS/

Next, play it safe, and make a copy of your SMS database:

iPhone:/private/var/mobile/Library/SMS root# cp sms.db sms.db.old

Now, to update the SMS database, you’ll be using “sqlite3″, and specifying the database you wish to update:

iPhone:/private/var/mobile/Library/SMS root# sqlite3 sms.db
SQLite version 3.5.9
Enter “.help” for instructions
sqlite>

We’ll want to find out which message group (one of your contacts) is listed as still having an unread SMS message:

sqlite> select * from msg_group where unread_count = “1″;
1|0|951|1

sqlite returns some useful info. The first number returned before the pipe (”|”) is the “rowid”, which is the message group ID that has the unread SMS message. The second number is the message type, and the third number is the newest message ID within that message group; both of which we’ll leave alone. The last number is the unread message count. We want to set this to zero:

sqlite> update msg_group set unread_count = “0″ where rowid = “1″;

Once done, quit sqlite (note the “.” in “.quit”):

sqlite> .quit

Then, exit out of your SSH connection to your iPhone:

iPhone:/private/var/mobile/Library/SMS root# exit

You’ll want to reboot your iPhone after all this, and once it boots back up, your SMS icon should be clear.

Update (2008-10-07): With my unread SMS message issue above, this was due to the annoying SMS message notification stating that that particular contact has left you a voicemail. After doing the above, I realised that I wasn’t getting any more of those from that contact. Not that I care too much, but I envisioned a time when that table could get rather large, so I looked into a way of deleting the SMS message. It’s a pain in the ass, and who knows if this will screw things up for me down the road, but so far, it looks okay. Here is what I did:

SSH into your iPhone again, go to the directory where “sms.db” is located, and start up “sqlite”. Check to see what messages are flagged as “0″:

sqlite> select * from message where flags = “0″;
716|+14165551212|1223389704|Voicemail   2 New|0|1||1|0|38|0|0

Now, there is an annoyance with sqlite where you have certain “triggers”. These are where actions are performed upon the data based upon other actions; conditions if you will (at least, from my rudimentary understanding of it). To delete an SMS message, we need to temporarily delete the “delete_message” trigger. I recommend making your terminal window as large as possible, and typing:

sqlite> .schema message

This will spit out all the triggers for the message table. Copy this one:

CREATE TRIGGER delete_message AFTER DELETE ON message WHEN NOT read(old.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id; END;

Now, from the above “select” query, the message causing me grief was “716″. This is the “rowid” of the message. With that in mind, we need to drop the trigger preventing us from manually deleting a message, delete the message, and then add back the trigger… as Apple obviously had it there for a reason, and I don’t want to mess around with things more than I have to:

sqlite> drop trigger delete_message;

Trigger is gone. Now delete the message:

sqlite> delete from message where rowid = “716″;

And now add back the trigger:

sqlite> CREATE TRIGGER delete_message AFTER DELETE ON message WHEN NOT read(old.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id; END;

Just because I like to make sure, check to see if the trigger was added back in the list:

sqlite> .schema message

If all is well, verify that the SMS message is indeed gone:

sqlite> select * from message where flags = “0″;

…and there you have it. Future voicemail SMS notifications for my affected contact seem to work, and I have resumed my ability to recieve this annoyance. Isn’t that great?  

Update (2008-10-29): So, this happened to me again, and I decided that deleting data probably isn’t the best way to handle this. Hopefully, those who have followed my directions either still have a backup of their original database, or if you simply log SSH into your iPhone again and open up your “sms.db” via “sqlite3″ again, you should have a history of your previous commands by hitting the up arrow a few times.

Anyway, I opted to throw the old data back into the database. But, to do this, certain “triggers” prevent the “update” command. Gotta love it. A little refresher:

First, log into your iPhone via Terminal. Once connected, change the directory to where the SMS database is:

iPhone:~ root# cd /private/var/mobile/Library/SMS/

Next, play it safe, and make another copy of your SMS database:

iPhone:/private/var/mobile/Library/SMS root# cp sms.db sms.db.backup-20081029

Now, open the SMS database using “sqlite3″ again:

iPhone:/private/var/mobile/Library/SMS root# sqlite3 sms.db
SQLite version 3.5.9
Enter “.help” for instructions
sqlite>

Get a list of the schema for the “message” table:

sqlite> .schema message

You’ll get an output similar to this (I’ll just post the first parts):

sqlite> .schema message
CREATE TRIGGER delete_message AFTER DELETE ON message …
CREATE TRIGGER delete_newest_message AFTER DELETE ON message …
CREATE TRIGGER insert_newest_message AFTER INSERT ON message …
CREATE TRIGGER insert_unread_message AFTER INSERT ON message …
CREATE TRIGGER mark_message_read AFTER UPDATE ON message …
CREATE TRIGGER mark_message_unread AFTER UPDATE ON message …

As you can see, certain triggers prevent the use of certain actions within Sqlite3. For example, the “insert_newest_message” and “insert_unread_message” messages will affect the “insert” command, and the “mark_message_read” and “mark_message_unread” triggers affect the “update” command. With this in mind, to perform these actions, we need to temporarily delete those triggers. Here is a quick rundown of what I did. Before doing ANY of this, make sure you have output the “schema” for the “message” table and use that as your reference for copying back the triggers.

First, I checked to see what row had a “flags” of “0″:

sqlite> select * from message where flags = “0″;
961|+14165551212|1225234358|Voicemail   1 New|0|1||1|0|38|0|0

Great. It’s the same number. Big surprise there. Now, I want to add back the original txt msg that I probably shouldn’t have deleted. To do so, I have to temporarily delete the triggers that will prevent me from using the “insert” command:

sqlite> drop trigger insert_newest_message;
sqlite> drop trigger insert_unread_message;

Now that that’s done, let’s insert the old data:

sqlite> insert into message values (’716′,’+14165551212′,’1223389704′,’Voicemail   2 New’,'0′,’1′,”,’1′,’0′,’38′,’0′,’0′);

…and now, add back the triggers, as they are kinda needed:

sqlite> CREATE TRIGGER insert_newest_message AFTER INSERT ON message WHEN new.ROWID >= IFNULL((SELECT MAX(ROWID) FROM message WHERE message.group_id = new.group_id), 0) BEGIN UPDATE msg_group SET newest_message = new.ROWID WHERE ROWID = new.group_id; END;

sqlite> CREATE TRIGGER insert_unread_message AFTER INSERT ON message WHEN NOT read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END;

I like checking that all was successful:

sqlite> select * from message where flags = “0″;
716|+14165551212|1223389704|Voicemail   2 New|0|1||1|0|38|0|0
961|+14165551212|1225234358|Voicemail   1 New|0|1||1|0|38|0|0

Now, let’s temporarily drop the triggers that are preventing us from using the “update” command:

sqlite> drop trigger mark_message_read;
sqlite> drop trigger mark_message_unread;

Now, with that done, I also wanted to see all the “Voicemail” messages I had as a comparison. These are the messages that screw up for me:

sqlite> select * from message where text like “Voicemail%”;
716|+14165551212|1223389704|Voicemail   2 New|0|1||1|0|38|0|0
961|+14165551212|1225234358|Voicemail   1 New|0|1||1|0|38|0|0
1052|+14035551212|1224391073|Voicemail   1 New|2|1||30|0|38|0|0

So, I have one “Voicemail” message that isn’t messed up. I’ll use that as my template. The “flags” value was set to “2″ (right after the text of the message). So, I’ll update the others to “2″:

sqlite> update message set flags = “2″ where flags = “0″;

…and check if it worked:

sqlite> select * from message where text like “Voicemail%”;
716|+14165551212|1223389704|Voicemail   2 New|2|1||1|0|38|0|0
961|+14165551212|1225234358|Voicemail   1 New|2|1||1|0|38|0|0
1052|+14035551212|1224391073|Voicemail   1 New|2|1||30|0|38|0|0

Looks good, so let’s add back the triggers:

sqlite> CREATE TRIGGER mark_message_read AFTER UPDATE ON message WHEN NOT read(old.flags) AND read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) - 1 WHERE ROWID = new.group_id; END;

sqlite> CREATE TRIGGER mark_message_unread AFTER UPDATE ON message WHEN read(old.flags) AND NOT read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END;

Lastly, we should update the “unread_count” value in the “msg_group” table, as that is what displays the number on the SMS icon in the iPhone interface. I like seeing the existing values before changing anything:

sqlite> select * from msg_group where unread_count = “1″;
1|0|1175|1

So, just like last time, set the “unread_count” to “0″, and we know the “rowid” is “1″ (the first number in the output):

sqlite> update msg_group set unread_count = “0″ where rowid = “1″;

We’re done. Time to quit:

sqlite> .quit

You may need to reboot your iPhone before you see any of the changes.