mORMot Open Source

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

sqlite - recover a database corrupted (backup/restore)

I have a corrupt SQLite database.

I'm trying to use the "sqlite3.exe" tool to recover it.
I can create the backup of the base but when I try to restore, generates the error below.

C:\myapplication>sqlite3.exe mydatabase.db3 ".dump" > mydatabase.sql
C:\myapplication>sqlite3.exe mynewdatabase.db3 < mydatabase.sql
Error: near line 3: no such collation sequence: SYSTEMNOCASE
Error: near line 4: no such table: TableABC
Error: near line 6: no such collation sequence: SYSTEMNOCASE
Error: near line 7: no such collation sequence: ISO8601
Error: near line 8: no such collation sequence: ISO8601
Error: near line 9: no such collation sequence: ISO8601
Error: near line 11: no such collation sequence: ISO8601
Error: near line 12: no such collation sequence: ISO8601
Error: near line 13: no such collation sequence: ISO8601
Error: near line 14: no such collation sequence: ISO8601
Error: near line 15: no such collation sequence: ISO8601
Error: near line 16: no such collation sequence: SYSTEMNOCASE
Error: near line 17: no such collation sequence: ISO8601
...

The database above is ok. I'm trying to find a way to perform the backup and restore to later try to recover the corrupted database.

I was reading about "Column collations" but I did not find how to set it used to sqlite3.exe tool.

Please, someone can help me?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: sqlite - recover a database corrupted (backup/restore)

The faster and safer is to use backup functions of SynSQlite3.pas.

See TSQLDataBase.BackupBackground() and BackupBackgroundWaitUntilFinished methods.

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

Re: sqlite - recover a database corrupted (backup/restore)

Ok AB, thanks.

Now I will use the BackupBackground.

But if I have a database corrupted and unfortunately I have not ran the backup. I could do the following replacement before restoring the base?
Replace 'COLLATE SYSTEMNOCASE' by ''
Replace 'COLLATE NOCASE' by ''
and replace 'COLLATE ISO8601' by ''

If I do this replacement I can restore the database...

C:\myapplication>sqlite3.exe mynewdatabase.db3 < mydatabase.sql

... but I still do not know the consequence of it.

Could you tell me what problems I can face or that detail is set automatically ?

Last edited by dorival (2015-08-07 17:06:03)

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Re: sqlite - recover a database corrupted (backup/restore)

How did your database corrupt?

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: sqlite - recover a database corrupted (backup/restore)

just get rid of the COLLATE statements in the CREATE TABLE.

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

Re: sqlite - recover a database corrupted (backup/restore)

Ok, thanks AB

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

Re: sqlite - recover a database corrupted (backup/restore)

btw, does anyone know how to simulate problems in a database sqlite?

What I do is fix problems after they happen.
I want to try to anticipate what might happen to already implement in my application a "AutoRecover".

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,272

Website

Re: sqlite - recover a database corrupted (backup/restore)

AFAIK SQLite3 has very few "problems".
It is very difficult to corrupt a database, in normal use case.

See https://www.sqlite.org/testing.html

Perhaps, not using "exclusive" and "synch off" modes would increase potential issues.
At the expense of performance.

What did you do to crash the DB file?

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

Re: sqlite - recover a database corrupted (backup/restore)

Ok Ab,

I was using just:
Synchronous := smFull;

now, I will include:
Lockingmode := lmExclusive;

I had some problems and I could not look at all yet. In fact, I could analyze only two situations - the first the problem was a defect in harddisk - obviously , in this case the only solution is to restore the backup.

in the dump file I found this

...
INSERT INTO "MYTABLE" BLA BLA...
/**** ERROR: (10) disk I/O error *****/
INSERT INTO "MYTABLE" BLA BLA...
....

The second case I'm with the corrupted database and I can restore through the following commands:

sqlite3.exe mydatabase.db3 ".dump" > mydatabase.sql

...edit mydatabase.sql and replace 'COLLATE SYSTEMNOCASE', 'COLLATE NOCASE' and 'COLLATE ISO8601' for nothing and ...

sqlite3.exe mynewdatabase.db3 < mydatabase.sql

in the mydatabase.sql I found this:

...
INSERT INTO "MYTABLE" BLA BLA...
/**** ERROR: (11) database disk image is malformed *****/
INSERT INTO "MYTABLE" BLA BLA...
....

the source of the corruption of this database still can not identify - perhaps for not having used Lockingmode : = lmExclusive ;.

Last edited by dorival (2015-08-10 11:20:24)

dorival

Member

From: Brasil

Registered: 2013-04-17

Posts: 35

Re: sqlite - recover a database corrupted (backup/restore)

Hi,

Just you know, all of cases (corrupted database) was physical problem in the HDD.
Even with this problem, after run CHKDSK /F/R, I got access the database file and recover the information.

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Re: sqlite - recover a database corrupted (backup/restore)

I have experienced a "database disk image is malformed" error because the user forced the computer to shutdown when the program is operating the sqlite db.

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.