Page 1 of 1

database malformed sqlite error 11

Posted: Thu Oct 22, 2015 11:47 am
by Thomas Lohrum
Hi Alex,

this is the first crash corrupting my database for a long time. However, it is a bad one. Directly importing from the corrupted database will fail. Nor can it be opened to export it as XML.

Trying sqlite3 to rebuild (.dump & .read) it caused the following behaviour:

Notebook upgrading

The notebook file "C:..." was created by an older version of CintaNotes.
Do you want to upgrade it?

After upgrading i get the following error:

Notebook

The notebook file "C:..." was created by a older version of CintaNotes and cannot be upgraded.
Error message: SQLite error 1:
there is already another table or index with this name: NoteCache

I think this is a bug.

Any ideas how to fix the database (other than restoring a backup)?

Thomas

Re: database malformed sqlite error 11

Posted: Fri Oct 23, 2015 12:21 pm
by CintaNotes Developer
Hi Thomas,
sorry to hear that the problem is back.
Could you please execute "pragma user_version;" on the database restored with .read and tell me the result?

Re: database malformed sqlite error 11

Posted: Fri Oct 23, 2015 1:10 pm
by Thomas Lohrum
CintaNotes Developer wrote:Could you please execute "pragma user_version;" on the database restored with .read and tell me the result?

It's zero (0).

Thomas

Re: database malformed sqlite error 11

Posted: Fri Oct 23, 2015 2:13 pm
by CintaNotes Developer
Set it to 3010, then the db should load

Re: database malformed sqlite error 11

Posted: Fri Oct 23, 2015 2:27 pm
by Thomas Lohrum
With 3010 i get the error: "Notebook format version 3010 not yet supported".

With 2950 it runs fine.

Will you change something, so this manual handling can be avoided in the future?

Next i did an export of my restored and the re-created database. I wanted to diff, so i can track, whether data got lost or not. However, the export result is unordered, thus diffing is not possible.

Can you add an order by, so this will be possible?

Thanks for your help.

Thomas

Re: database malformed sqlite error 11

Posted: Tue Oct 27, 2015 12:42 pm
by CintaNotes Developer
Thomas, sorry for the delay.
With 3010 i get the error: "Notebook format version 3010 not yet supported".
With 2950 it runs fine.

Yes, the last database schema version is actually less than 3010, so it's good that you tried 2950 as well.

Will you change something, so this manual handling can be avoided in the future?

Yes, the change is trivial. CN will treat user version 0 as equal to the last one.

Next i did an export of my restored and the re-created database. I wanted to diff, so i can track, whether data got lost or not. However, the export result is unordered, thus diffing is not possible.
Can you add an order by, so this will be possible?

Sure, no problem at all. Will add in 3.0.2.

Re: database malformed sqlite error 11

Posted: Thu Jan 07, 2016 12:09 pm
by Jeremy
Hi everyone,

I am experiencing a similar issue. Unfortunately it won't fix by now !

It started yesterday with a "database malformed ... 11" error everytime I tried to save a new note or a modified one (reading was ok, as well as editing a note, as long as I don't save it). I couldn't export anything either (error message).

Today I tried to repair it with sqlite3 (to which I am a beginner).
The first integrity check reported 2 pages never used, "on tree .. page ... cell 0 : a bad ptr map entry key..." and a "wrong # of entries in index ix_Notes_Tags_tagid".
So I used sqlite3 to dump the whole database, and rebuilt it with the command line.

Just like Thomas, I had then to change the user_version to 2950 to overcome the "older version" error message.
But now that I can acess the database... new or modifed notes still won't save ! I get the same original message "database malformed... 11".
Which is strange is that now an integrity_check on the rebuilt database returns "ok" though.
Any idea how to avoid restoring back to last week?

Kind regards,
Jeremy

Re: database malformed sqlite error 11

Posted: Thu Jan 07, 2016 12:21 pm
by Jeremy
To be complete : when rebuilding the db, in order to save time, I added to the dump_all.sql two instructions :
on the second line : PRAGMA journal_mode=OFF;
juste before the last line : PRAGMA journal_mode=MEMORY;

Resolved !

Posted: Thu Jan 07, 2016 2:45 pm
by Jeremy
Eventually, at that point I could successfully export and import all notes via SQL.

Re: database malformed sqlite error 11

Posted: Thu Jan 07, 2016 3:01 pm
by CintaNotes Developer
Hi Jeremy,

Sorry for the delayed reply.
I see that you have already solved the problem by yourself - quite a feat btw, bravo!

If nothing else would help, I'd suggest sending the notebook in to me for fixing.
However I wonder what could have caused this DB corruption. Did you experience any system crashes while
CN was running?

Re: database malformed sqlite error 11

Posted: Thu Jan 07, 2016 3:20 pm
by Jeremy
Hi Alex,

Thanks for replying !

As far as I could see, there was no system crash when the problem occurred.
I use different backup tools, maybe it had some link with different ones trying to access the file at the same time ?
It occurred a short while after launching TeamDrive, while Tresorit was still on.

Anyway... now it works ! :-)

Best regards,
Jeremy

Re: database malformed sqlite error 11

Posted: Wed Jan 20, 2016 10:20 am
by CintaNotes Developer
Jeremy, sorry for the late reply.
I'm glad that it works now, please let me know if something like this happens again.