Glad some of my research was useful for you.

Sadly I still haven't found a reliable way to fix those issues... So if someone has got any ideas feel free to post those - and sorry I was so silent lately... time is pretty rare at the moment..

I've created a simple sh script that automatically try to repair a damaged sms index I've tested on my n900 and it works but I take no responsability cause It could need more testing
Everytime you notice damaged Conversations, you have only to start FixConversations in your Menu and wait for the program to autoclose by itself.
After you should see the Conversations fixed. I say should because sometimes it is impossible to recover the damaged db and the only solution is to save your SMS with SaveSMS (extra-devel) in a text file and delete the damaged conversation.

Instructions:
1) Remove ".txt" from the end of both files
2) Put "fixconversations.desktop" in:
/usr/share/applications/hildon/
3) Put "fixconversations.sh" in:
/home/user/MyDocs/
Refresh your menu by opening CatorizePlus and choosing "Save" from the dropdown menu

Obviously you can see by yourself the script by opening in a text editor, but I take no responsability in case of damage ecc ecc

I've been doing a lot of work with the conversations database as I've been working on a event importer/exporter for the last few months on and off.

1st, the code you linked to is an old version. I put up the current version here: https://gitorious.org/rtcom-eventlog...om-eventlogger

2nd, I encountered a problem when making changes to the database when the event count > ~2500. Basically, the triggers for keeping the group cache up to date seems to be horribly inefficient and would block for several seconds for each event. This isn't a problem when you send a SMS or make a phone call several minutes apart at best, but when bulk loading content or in a quick SMS conversation it might break. Anyway, here's the culprits:
In my utility I disable the triggers and achieve the same effect with some code afterwards:
It runs just the once, updates everything, and is quick. If I ever get around to rewriting the conversations UI, I'll try dumping the triggers and just have that code execute when the conversations opens.

...though admittedly, you can't use that from the command line as the flags require a custom function '_OR()' in order to be able to Or the flag values together (not sure why it even does that TBH...but I have to do it just in case...)

...anyway...try disabling the triggers and see if it resolves your problem, and if so...try to come up with a way to update the cache another way :/

BIG Thank you to friesoft for pointing me in the right direction & God for answering prayer! Was getting terribly frustrated by this problem.

I was able to fix the problem of messages not showing correctly, a red exclamation mark on one of my conversation threads, messages disappearing and re-appearing, messages not showing as sent, etc.

Note: Everyone should make a back up of the el-v1.db file and a backup using the built in Backup app before trying any of this if you care to retain your messages. In case something goes wrong just restore from that!

Following friesoft's directions I started messing with the database using Sqliteman. This is what I did (comments followed by sql statements):

1: Put the device in offline mode
2: Make a back up using the Backup app
3: Copy the /home/user/.rtcomm-eventlogger folder somewhere safe (MyDocs or somewhere on your computer)
4: Copy the /home/user/.rtcomm-eventlogger/el-v1.db to your computer
5: Use Sqliteman or another sqlite program that allows running queries on the database and use the following as a general guide

• See if there are any messages showing as not read:
  
  Code:

  select * from events where is_read = 0

• If there are, change them all to read at once:
  
  Code:

  update "events"
  set "is_read" = 1
  where "is_read" = 0

• Find all messages with no remote_uid (no phone number, there should always be one):
  
  Code:

  select * from events where remote_uid = ""

• Run this command multiple times, replacing the id to match each one of the messages found above, and the remote_uid with the correct phone number of the contact that sent that message:
  
  Code:

  update "events"
  set "remote_uid" = "+12345678901"
  where "id" = 2233

• Find all the pending messages (flags set to 1, meaning SMS is in a pending state):
  
  Code:

  select * from events where flags = "1"

• If there are some, unflag all (flags set to 0):
  
  Code:

  update "events"
  set "flags" = "0"
  where "flags" = "1"

• Clear flags stored in groupcache for that conversation (makes things appear right in the conversations overview):
  
  Code:

  update "groupcache"
  set flags = "0"
  where flags != "0"

Copy the database back to the phone in the correct location, be sure the owner/permissions match the other files in the directory, and restart the phone. When it prompts to go online while turning on let it.

Other observations:

• Still had the red exclamation in the Conversations overview until reset the flags in groupcache,
• Might also want to just set read_events equal to total_events to be sure none show as unread in the overview? I did not have any discrepancies so cannot comment on that.
• Do blank values in the headers table matter? I have some but wasn't sure so just left them alone.

Wow, this is pretty nifty [and fu***n advanced] thread! I wished I could make a better sense of it and fix my N900 that get SMS messages completely messed up (sent sms lost, incoming sms in disarray and outgoing ones never marked as sent or delivered). N900+me=love/hate-relationship

Anyways, I want to learn and fix my /rtcom-evetlogger/ database, but I am not sure which of the suggestions posted here is the solution and all seem different.

On a specific note: I got Sqliteman, but how do I extract the home/user/.rtcomm-eventlogger/el-v1.db to my computer? Please step by step, including how to put it back.

I am a noob, but I want to learn and try fixing this. (Hey, I got sudo gainroot thing on my N900

Thank you in advance!

Well you could try the other fixes listed like copying the database, but I had similar issues to what you are having and they were not resolved until I actually edited the info in it.

Ok, to help clarify the specifics of from my previous post
Important note: I incorrectly typed the directory, it is ".rtcom-eventlogger" (I put ".rtcomm" previously)

1. Put the n900 in offline mode (Press Power button once, chose offline from menu)

2. Make a back up using the Backup App (A full back up is a good idea, I only backed up the conversations (Communication and Calendar option)

3. [Modified this step to only back up the database instead of the whole folder] Make a copy of the /home/user/.rtcom-eventlogger/el-v1.db file in case of problems, then copy it where you can access from your computer
3a. Open X-Terminal on the n900 (ctrl + shift + x or through the menus) and type the following commands in it pressing enter after each (3b through 3e)
3b. Get root access by typing: 3c. Make a back up of the el-v1.db database file: 3d. Copy the el-v1 database file to the "MyDocs" folder so it will be available when you mount it: 3e. Type exit twice to close the terminal (first one exits root mode, second one closes terminal): 4. Now connect the n900 to your computer using "Mass Storage Mode". You should be able to open Sqliteman, and then open the el-v1.db file directly on the mounted n900.

5. Go through the sql part of my previous post, executing sql commands as necessary. When finished close the program.

6. Safely remove the n900 from the machine (exactly how varies by operating system, should be a tray icon in Windows)

7. Copy the el-v1.db file back to the correct location
7a. Open X-Terminal on the n900 (ctrl + shift + x or through the menus)
7b. Get root access by typing: 7c. Copy the el-v1 database file from where we put it in "MyDocs" back to correct location: 7d. Change the owner and group on the database to what they should be: 7e. Change the permissions to what they should be: 7f. For what it is worth, you can compare the permissions of everything in the directory by typing: 7g. Type exit twice to close the terminal (first one exits root mode, second one closes terminal): Restart the phone, let it go online, and try to see if it resolves the issue. If the Conversations app won't open or something you can always restore from the Backup app.

Thank you for great instructions on how to proceed, especially with clear steps for x terminal.

I was able to find loads of messages with "select * from events where is_read = 0" and some with "flag=1" but I don't seem to be able to figure out how to perform a correction step in Sqliteman. (Lol, never used or heard of it before, and just got it figured intuitively.)

Got it figured! Just copied and pasted the code commands you provided into tho top right area ow the window in Sqliteman (Windows version).

All operations completed. Results: everything seems to be like before :-P
I can not yet attest to the "sent messages getting lost" or other issues that occur during usage, but the sms history is now repaired (!) for one of the important sms threads I inspected. Thanks!

p.s. Do you know how to force Nokia Ovi Suite to reload msgs from the device so that the phone database changes would get reflected on my PC? (Without deleting the Ovi database. I have old msgs that are no longer on the phone [I cleared all sms threads on my N900 sometime ago] and also other older Nokia both stored in Ovi.)

Not really sure about your Ovi Suite question. My guess would be if you sync it will correctly detect the changes. Might be a good idea to make a copy of the Ovi Suite data folder or database, however it stores its data first just in case. Also another back up on the n900 using the Backup app before trying it. Then if something goes wrong, just restore both & try to find a different solution.