[Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Hi!

As I repeatedly had problems lately with SMS not being saved and therefore lost or pending forever in "sending" status I just started looking into fixing this.

I have just summarized possible solutions and items to think about on which I would greatly appreciate any comments or pointers into the right direction.

Problem:
SMS Conversations end up pending/not sent but are actually sent - Ui shows them as pending
and after closing they are removed/ui goes wild

References (some of them):

• http://talk.maemo.org/showthread.php?t=69475
• http://talk.maemo.org/showthread.php?p=953995
• http://www.forums.internettablettalk...ad.php?t=68729
• http://forums.internettablettalk.com...ad.php?t=58984

One of the "fixes" (which didn't work for me): http://talk.maemo.org/showpost.php?p...2&postcount=40
Database location: /home/user/.rtcom-eventlogger/el-v1.db
View the database using sqlitebrowser (fast and all you need imho) (can also edit display the statements in the application log)

What goes wrong?
Events.remote_uid: empty --> remoteid of a contact must be added
Events.flags: 1 --> 0 (change from pending to sent)

SQL Statement:
UPDATE Events SET flags='0' WHERE rowid=ROWID;
UPDATE Events SET remote_uid='remoteid' WHERE rowid=ROWID;

Ideas to keep integrity while editing:

Solution 1: set offline mode (to ensure not receiving sms while doing this)
Solution 2: transaction? - should definately be used

Possibility to integrate:

Solution 1: automatic check interval (daemon?)
Personal note: I would like to trigger it as soon as I see a problematic message/let it happen automatically for me)

Solution 2: on sms send --> (.js) --> check database - may be really slow (needs to be tested)

--> is modifying the sqlite database from js possible?
--> can the system be notified? is there already such a solution? (afaik there is a solution to send sms from commandline)
--> could just check the last entry and fix it if it failed - how to determine if sms sending really failed or who is the contact to update the entry

Possible solution for the "who is the contact the sms was sent to": set the field to an identifier nobody would ever use on a real name (e.g.: "_smsSendCorruption") where all a utility could read out all those fields containing that text and present the user the option to choose the right one (on device))

Source for the responsible library (longer term fix):
http://maemo.gitorious.org/maemo-rtcom

Imho there should be added a simple transaction and that's about it... but well I have no idea up to now what is going wrong... the C Code looks pretty... ugly...

Collaboration:
If you think I should create a wiki page to collaborate on this I would appreciate to do so - or even better: let it do someone else as I'm pretty short on time...

[Edit]: yes if we come up with a good fix I'll for sure be glad to get this integrated into the CSSU :)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

After further investigation I have the bad feeling that sometimes (in my case all the time atm) new messages from myself are not even inserted into the database.

At least opening sqlite3 el-v1.db and selecting the last entries doesn't show my last messages ;(

Statement:
select * from Events order by rowid desc limit 3;

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Is this reported in Bugzilla?

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

To admit I didn't yet look for it on bugzilla... I guessed that because there are so many people having that problem someone has already looked... but well then they could have already filed a bug... so no I didn't look yet.... but I should :P

[Update]: after looking around I didn't find anything related beside of this:
https://bugs.maemo.org/show_bug.cgi?id=10614

but it doesn't completely match the problems I'm having and others were/are having...

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Any updates? Ive had this problem months ago and tried the above fixes which worked for the time being. Within the last month or so it has occurring much more frequently

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

This problem appears to me when my sent/received sms's cross 8000. It kept on continuing from the past 4-5 months and last week i flashed the EMMC and now everything is back to normal.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

This has just happened to me for the second time. it seems to tend to happen when the N900 gets frozen and has to be power cycled (typically during processing by Panorama or BlessN900), presumably due to corruption of the database, and once it has started, it's hard to get rid of. in fact, i think i ended up flashing last time and restoring an old backup. I'd prefer not to have to do that again because i don't have a recent backup preserving some important SMSes. What have people found to work?

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Not sure if this is useful, but I've been able to reproduce the same issue fairly regularly in the following way:

Create and send an SMS to a contact in the normal way, however, before that SMS is actually indicated as being sent (little square arrow thing vanishes) delete the conversation thread entirely.

I'm not really convinced this is the actual trigger of the problem itself rather than just an indicator of the actual cause - which is I think: 'n900 becomes sluggish after a few days'

It happens for me with much greater frequency when my uptime is over the 1 day mark, conversations is often bogged down, takes a while to open incoming messages, the application is slow and the CPU is often pegged at max.

Whatever conversations is doing under the hood, I figure some of it is time sensitive, when the N900 is busy I suspect it doesn't gather all the data points it needs to populate the sqlite db, thus corrupting it.

I guess it would be pretty straight forward to code up a daemon in C to listen for db events and take a look each time a message passes over the bus, though given my suspicion on the cause, it'd also be a good idea to have that daemon wake up every few minutes while the screen is active and do the same check.

I don't have the problem terribly often, so the manual corrections to the sqlite db aren't terribly bothersome.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

This is also happening to me especially when skype messages and other IM messaging are downloaded from the respective providers. Only way is to reboot but the problem appears again after a few connects and disconnects. Even the desktop becomes sluggish -AFTER RUNNING the speedup scripts. its fine for a while until conversations is opened up a couple of times.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Thanks again for the triage report, friesoft. The other "solutions" floating around didn't work, but i copied the database

/home/user/.rtcomm-eventlogger/el-v1.db

to MyDocs and then connected up to my comp in mass storage mode. I opened the db file with sqlitebrowser as suggested and, sure enough, found that the SMS right when things went bad was missing the remote_uid field, with the flag still 1 (unsent). I changed the two fields, saved, disconnected, and copied the db back to its location in home. After a reboot, all is well.

Hooray!

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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..

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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 ;)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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 :/

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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!

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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.)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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.)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

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.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

@computerinfo21
I think you'll find it more problematic when messages have a null group_uid than a missing remote_uid.. it's group_uid that lets the phone assign a message to a conversation...

e.g. if I text these numbers:
+447711223344
07711223344
00447711223344 (for most countries where 00 is the international escape code)

It goes to the same person but the remote_uid field is different in each case. As is standard with phones, they take the last 7 digits and assume it's unique to a contact, thus removing the problem that prefixes cause the same number to appear differently

Try it by calling your friend, but swap some number *not* in the last 7 digits, for another.. Phone will say youre calling ur friend, but the call wont connect

For SMS in el.db the group_uid is the last 7 digits of the phone number.. Its also used in groupcache and from what I've seen so far groupcache is the table that influences the appearance of the message list (error icons, unread msgs etc)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

you should also take care to truly only implement fixes of the nature described here on rows where service_id = 3 (sms) because you may end up screwing up your calls and chat lists if you apply blanket changes to services 1 (calls) and 2 (chat)

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Good thoughts on the subject cjard, seem like good things to keep in mind when editing the database. But that's why a couple good back ups beforehand are done in case of a mistake.
Keep that in mind when editing the database folks, have to be careful & be sure the changes you are making actually make sense.
However, with that said, I am not sure if those things have gone wrong for others, but for me only the things I mentioned were problems. Going back to the update of the groupcache on those fields I believe.
I looked through the database pretty good before making any changes, and the first SQL statement of each step is to help narrow down if that particular problem exists.

The group_uid was always assigned correctly in my case, and the only places I was having problems were the SMS messages, those having problems with other areas will definitely need to implement other fixes. Thanks for warning them.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

I hope you all would come up with a sustainable solution! Repairing database worked great for me, but it lasted until the next time N900 got overwhelmed and screwed up to loose 11 sms in a row from a thread (both sent and received). :(

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

i still have the problem, unsent and error messages are gone, but still everytime i send a message and when i receive a message, it still did not update on my conversations. i still could not see the new sent and received messages. i don't want to loose my messages. please help me

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

The problem sometimes happened on my device when a miscall and an sms alert me at the same time. After that when i am going a send an sms, its just in a pending status. But the receiver already receive my sms. And after i rebooted my device, the conversation normalizes again.
But in this case, an sms and a miscall alert me at the same time. So i just tap the miscall notification to close. And prioritize to open the sms. After i deleted the sms, i saw the number in the miscall appeared in my conversation with an "arrow icon" which should not be in the conversation. I deleted that number in the conversation and when i open again the converstion, the number that miscall me keeps on coming back in the conversation. So i deleted the number in the phone log as well. Then the number didn't show on the conversation anymore. But when i am going to send an sms, it doesn't show any pending, nor any sent items. Even the sms should be on top of the conversation list. I rebooted my device a lot of times, but no success. The problem still exist.
I followed the instructions on the first page, and its true, the messages with the pending status fixed! But the problem of not showing the sent items or even the message should be on the top pf the list still exist. My conversation is not updating anymore. Even when i receive an sms, i could not open the latest sms that i received, i can only open a message thru notificatgion. But afetr the notification disappeared, there's no luck to open the message anymore.
I also tried to back up and restore the messages but the problem still exist.
Anyone could help me please. I don't want to delete my messages.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

anyone could help?

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Well maybe this will help.
I just kept repairing anything that messed up for a while, but got tired of it today. looked up some other options & I decided to do a 2 part back up solution.

First, I looked for & found a program to keep the messaging & call history on the phone: glogarchive.
It is slow for accessing the whole history of long conversations, but it has a search feature that is reasonably fast even on my almost 16000 events. And usually what I need is to find a specific message, not so much read an entire conversation, which is still possible, just slow.
Just archive messages & you have a good copy that is searchable.

Second, always wanting multiple backups, I went offline & renamed (moved) the database with the date to keep a copy from today just in case.
From the terminal:
Then I restarted the phone, all messages & history were gone from phone but safe in glogarchive to refer back to later. You could also probably just clear the big conversations & help some, but I wanted everything working like new, & so far it is.

Hope this helps some, or gives some ideas. I know I could archive messages in the Nokia Suite (which I do have a backup there too, can't ever be too safe ;) ), but when I want a message from the past, I want it on the phone & usually am not near my computer.

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Well, this issue has now spread to call log. The N900 doesn't record some incoming calls in the log so I can't see the call I had.

I am growing tired of fixing the database, and this is anything (perhaps the funky USB port) is going to force me to buy another phone. Too bad there aren't many other phones with video skype, great calendar, decent email interface, and hardware keyboard.

Any suggestions what to buy in place of N900 or what to do with loosing sms?

Thanks, and Happy New Year!

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

Worked for me. I had a contact that got stuck always showing my sent messages as pending, and the status updates properly now. I used sqlite3 on the phone to edit the db in-place instead of jacking around with copying files back and forth and screwing with permissions (I still made a backup beforehand, of course).

Is it possible to programmatically recover the remote_uid? I noticed that the messages that were missing remote_uid still showed up in the correct conversations.

Any update on the possibility of getting this into the CSSU?

Re: [Conversations] WIP: Fix Conversations (SMS) send problem with database
 

The root of the problem needs to be fixed first, without some workaround messing with the database after the fact.

Has anyone tried to find the bug in rtcom-eventlogger? Any findings?