Unreadable Lightning (local.sqlite) Data

[gazanchipa]

Folks, thanks in advance for your assistance.

I started using Lightning (likely v 1.2.1) on 02/05/12 and unfortunately suffered a complete system failure on 02/29/12. I did a recovery on the hard drive and got the last local.sqlite Lightning file, then reinstalled the operating system and Lightning 1.3 and Thunderbird 11.0.1. I reloaded the entire profile folder by dropping it into the appropriate location. Thunderbird email works perfectly, unfortunately I've been unable view my old calendar, i.e. Lightning doesn't recognize the local.sqlite file listed.

Without success I've tried: following the FAQ suggestions about making it visible but don't get how to, I've deleted and reinstalled Thurnderbird and Lightning several times, tried rolling back to v 1.2.1 to then exporting the data, I confirmed local.sqlite has the data I'm looking for by opening as a text file (I can see some data but other pieces are gibberish), and thought of editing the config/prefs.js file but don't know how to make effect the change I need.

Could it be that the current local.sqlite file not set as the default calendar? How can I fix that? I'm missing a month's worth of critical data.

[bathrick]

I have a similar problem. I was running lightning on an XP machine. The machine died, but I was able to recover the local.sqlite from the dead drive. I have been unable to get Lightning to read the file. Not sure if I am putting it in the correct location - is C:\Users\username\AppData\Local\Thunderbird\Profiles\RandomCharacters.default\calendar-data where this is supposed to go?

Do I need to do something else?

Mike

[gazanchipa]

Mike, I was unable to figure out how to get Lightning to work properly again and as a result I will be migrating away from it and Thunderbird.

In the meantime, I was able to figure out a work around to retrieve my data from the "local.sqlite" file; it will take a bit of time but hope this helpful to you.

After manipulating the file for a bit, I realized the data integrity was intact (i.e. the file was not corrupt) and simply needed to be read in a different way. I downloaded the free "SQLite Database Browser" (or you can choose anything else you like here http://www.sqlite.org/cvstrac/wiki?p=ManagementTools), and loaded the file. I quickly realized my data was in the "cal_properties" and "cal_events" tables and that the key to lining things up properly were the matching "item id" and "id" variables (same values!). With that in mind, and one at time by hand, I copied the contents of the "key" and "value" "cal_properties" table variables into different columns in Excel, and then the "title" and "event start" variables from the "cal_events" table. In addition I inserted an order column (1 ,2, 3, etc.) so I could sort the Excel file in different ways but still go back to matching the way the data was listed in "local.sqlite" tables. I then sorted the Excel file by the "event start" data in ascending order and voila my data was in order. What I did to get the date, since I couldn't figure out how to convert the 16 character "event start" integer into a normal date/time format, was to go back into Thunderbird and match emails to listed events and manually added them.

In a nutshell, I extracted all the needed data and recreated the database in Excel; keep in mind I only had a month's worth of data to recover. Good luck.

[ssitter]

Hi, you need to tell Lightning what calendars exists in the local.sqlite file. The corresponding preferences are stored like all preferences in file pref.js in your profile folder and start with "calendar.registry". Restore the lines from the old pref.js file and replace the lines in the new pref.js.

[gazanchipa]

Ssitter: Thanks for your assistance. I do agree as you suggest that

Restor[ing] the lines from the old pref.js file and replac[ing] the lines in the new pref.js

would likely work as a solution if you have the old pref.js file. Unfortunately in my case this is one of the files that was re-written before I was I able to preserve it from the date of computer failure and therefore was not available to me.

[mmecca]

If you open the local.sqlite database as you described before, the cal_id column of the cal_events table contains the id of the old calendar. The prefs.js file will contain preferences starting with calendar.registry followed by the id of the new calendar, you should be able to just replace the id part with the id of the old calendar. I strongly suggest making backups of everything (preferably your entire profile fiolder) before attempting these types of edits.

[walterp96]

Just want to say THANK YOU !!!
This information just saved my life and calendar
Lightning had "lost" my calendar data, and I couldn't get them back ... was struggeling for a whole day - and ready to give up when I saw your post.
Thanks

[ted939]

HELP - I think this thread is the closest thing I've found to helping me with my very similar situation, but I still don't quite get it :-/

First, my system:
Mac OS 10.7.5
ThunderBird (TB) 38.4.0
Lightning 4.0.4.1
Provider for Google Calendar 1.0.4

My situation
For some reason, most of my TB/Lightning Calendar's Events dates/times (but not all, strangely) have changed to later ones (sometimes years later).
It happened sometime after I migrated ALL my Calendar Events from my locally-stored computer to Google Calendar about a month ago.
Luckily, I do have backups of my pref.js files and local.sqlite files, BUT I don't know what to do with them within TB, to see them again.

My efforts
I've tried opening an earlier local.sqlite file via Thunderbird > Open > Calendar File. It shows up as "Untitled Calendar" in my TB sidebar, with a yellow triangle/exclamation point next to it, but none of the Events show up in the Calendar. This .sqlite file DOES have the calendar events in them, as I can see the Events when I look at the file with DB Browser for SQLite and also with TB Add-on SQLite Manager.

This is as far as I've gotten after hours of uneducated floundering.

My SOS
Can anyone help me with viewing/restoring earlier Calendars to my TB?! Pleeeeaassseee.
Also, if someone can tell me how to decipher the event_start numbers, then maybe I can look at various local.sqlite files and see which ones have the correct dates/times in them and at least determine which file I need to work with.

Thanks!!!!
Ted