database malformed sqlite error 11

Thomas Lohrum
Posts: 1309
Joined: Tue Mar 08, 2011 11:15 am

database malformed sqlite error 11

Postby Thomas Lohrum » Thu Oct 22, 2015 11:47 am

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
User avatar
CintaNotes Developer
Site Admin
Posts: 4661
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: database malformed sqlite error 11

Postby CintaNotes Developer » Fri Oct 23, 2015 12:21 pm

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?
Alex
Thomas Lohrum
Posts: 1309
Joined: Tue Mar 08, 2011 11:15 am

Re: database malformed sqlite error 11

Postby Thomas Lohrum » Fri Oct 23, 2015 1:10 pm

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
User avatar
CintaNotes Developer
Site Admin
Posts: 4661
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: database malformed sqlite error 11

Postby CintaNotes Developer » Fri Oct 23, 2015 2:13 pm

Set it to 3010, then the db should load
Alex
Thomas Lohrum
Posts: 1309
Joined: Tue Mar 08, 2011 11:15 am

Re: database malformed sqlite error 11

Postby Thomas Lohrum » Fri Oct 23, 2015 2:27 pm

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
User avatar
CintaNotes Developer
Site Admin
Posts: 4661
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: database malformed sqlite error 11

Postby CintaNotes Developer » Tue Oct 27, 2015 12:42 pm

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.
Alex
Jeremy
Posts: 4
Joined: Thu Jan 07, 2016 11:56 am
Contact:

Re: database malformed sqlite error 11

Postby Jeremy » Thu Jan 07, 2016 12:09 pm

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
Jeremy
Posts: 4
Joined: Thu Jan 07, 2016 11:56 am
Contact:

Re: database malformed sqlite error 11

Postby Jeremy » Thu Jan 07, 2016 12:21 pm

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;
Jeremy
Posts: 4
Joined: Thu Jan 07, 2016 11:56 am
Contact:

Resolved !

Postby Jeremy » Thu Jan 07, 2016 2:45 pm

Eventually, at that point I could successfully export and import all notes via SQL.
User avatar
CintaNotes Developer
Site Admin
Posts: 4661
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: database malformed sqlite error 11

Postby CintaNotes Developer » Thu Jan 07, 2016 3:01 pm

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?
Alex
Jeremy
Posts: 4
Joined: Thu Jan 07, 2016 11:56 am
Contact:

Re: database malformed sqlite error 11

Postby Jeremy » Thu Jan 07, 2016 3:20 pm

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
User avatar
CintaNotes Developer
Site Admin
Posts: 4661
Joined: Fri Dec 12, 2008 4:45 pm
Contact:

Re: database malformed sqlite error 11

Postby CintaNotes Developer » Wed Jan 20, 2016 10:20 am

Jeremy, sorry for the late reply.
I'm glad that it works now, please let me know if something like this happens again.
Alex

Return to “Bug Reports”