Again i wanted to cut a note from the inbox, when the crash occured. It appears that some errors occur only, when i use CintaNotes heavily. Some tasks i did along my daily work:
- read notes
- update notes
- change tags
- rename tags
- cut and paste notes
I use CintaNotes heavily all day long and have become dependent from the program. The second heavy crash within a couple of days really makes me worry. First time i had luck and got the system working again. Still it took me 1-2 hours to complete the task. This time it was even worse. At last i was able to fix the database. What a relieve.....! Again this took 2 hours and another two to document the issue and write a post here. After fixing the issue i was able to cut and paste that specific note without any problems. Thus i think it is the summary of tasks, that will cause the crash.
After last night's crash, i changed my sqlite-settings to what i think should be the most secure now:
sqlite.pragma.journal_mode = WAL
sqlite.pragma.synchronous = FULL
Let me share my experiences, when trying to fix the issue:
- i could not export to xml, because when i opened cintanotes, i immediately got error 11
- i could not import from db, because i got error 11 on import
- i could not use ".read dump.sql", because i got an error "table 'NotesData_content' already exists"
- i could not use ".read dump.sql" on an empty database, because CintaNotes complains the database was created by an old CintaNotes version.
"Diese Notizbuchdatei "cintanotes.db" wurde von einer älteren Version von CintaNotes erstellt und kann nicht aktualisiert werden. Fehlermeldung: SQLite Error 1: there is already another table or index with this name: NoteCache"
Alex, i think the latter is actually a bug! I checked sqlite's user version, which is 2001.
This is how i solved the issue:
Note: I took the steps mentioned here from an earlier report of Alex and changed it to my specific problem.
- I installed the sqlite command line shell. You can get it here: http://www.sqlite.org/sqlite.html
- for simplicity i copied the exe to the folder containing my database
- i opened my database running "sqlite3 cintanotes.db" from the command line.
- I ran the following command "pragma integrity_check;". You must write the semicolon!
- The output "rowid 7706 missing from index ix_ParaFormat" showed the source of the problem. The index "ix_ParaFormat" had become corrupted.
- I dropped the index with "DROP INDEX ix_ParaFormat;"
- Recreated it with "CREATE INDEX ix_ParaFormat ON ParaFormat (noteid);"
- Note: The task on how to get the specific statement to create the index is something i won't describe here.
Alex, the whole matter gets me to the following questions:
- Does CN use SQLites's Backup API to create a backup or does it simply copy the database file?
- When using sqlite.pragma.journal_mode=WAL, it seems that the WAL-files will not be part of the backup. When using sqlite.pragma.journal_mode=WAL together with sqlite.pragma.synchronous=FULL, i noticed that after a save/commit it takes a couple of seconds until a checkpoint gets executed and the WAL-file disappears. As i take it from sqlite's docs this is by intention. I think this is important, because from my observation i take it, that the WAL-file will not be part of the backup. Thus it appears to be best, to use the two settings together.
- I also tried using Firefox's Addon "SQLite Manager", which gave me the following error, when exporting the database: "Likely SQL syntax error: SELECT * FROM "main","NotesData" {unknown tokenizer:my_unicode]"
- SQLite is embedded/compiled into CintaNotes.exe, right? Do you actually use the latest SQLite-Version 3.7.15.2?
- When i created a sql-dump i observed, that all notes are written without any formatting. Is this correct? This makes sqlite's sql dump meaningless for use with CintaNotes.
- When i did a restore of the file "hourly.cintanotes.db" with a timestamp 23:02 i noticed that the most recent note had a timestamp of 21:57. Something is wrong here, since i am 100% sure, i had saved several notes between 22:00 and 23:00. It appears, that the backup goes wrong/incomplete.
After all, i find it strange, that no other users report such problems concerning sqlite stability. Still i suggest to provide more secure settings when installing CintaNotes. Tweaking sqlite to run faster should be a task for people, who understand what they're doing.
Thomas
Here are some Links to people interested on the issue:
SQLiteException "database disk image is malformed"
http://techblog.dorogin.com/2011/05/sql ... ge-is.html
Recovering a Corrupted Banshee Database
http://thelinuxexperiment.com/guinea-pi ... -database/
Creating and Dropping Indexes in SQLite
http://www.dbtalks.com/uploadfile/ca5be ... in-sqlite/
Write-Ahead logging concept in SQLite
http://www.dbtalks.com/uploadfile/ca5be ... in-sqlite/
-shm and -wal files in SQLite DB
http://stackoverflow.com/questions/1292 ... -sqlite-db