SQLite Forum

Recover Database
Login

Recover Database

(1) By Cecil (CecilWesterhof) on 2021-02-08 11:01:44 [link] [source]

I started creating a database with 'DB Browser for SQLite'. During this the program crashed.

There is a .sqlite file (I end all SQLite databases with .sqlite) and a .sqlite-journal file.

Would it be possible to recover the database?

The .sqlite file is not empty, but when I open it with sqlite3 .tables and .schema do not return anything.

(2) By Richard Hipp (drh) on 2021-02-08 12:29:17 in reply to 1 [link] [source]

The database should automatically recover to the last committed transaction when it is reopened. No action is necessary on your part.

However, uncommitted changes are lost. There is nothing that can be done about that, as some or all of the uncommitted changes would have been in memory when the application crashed.

(3) By ddevienne on 2021-02-08 12:53:56 in reply to 2 [link] [source]

A question popped in my mind reading your answer Richard.
What about savepoints? Do they make any difference in terms of revovery?

(4) By Richard Hipp (drh) on 2021-02-08 13:00:45 in reply to 3 [link] [source]

No. A savepoint is a "sub-transaction" - at transaction within a transaction. What matters for recoverability is the outer transaction. Information reaches the disk when the outermost transaction commits. Savepoints don't matter for that.

If you have a SAVEPOINT that means you also have a BEGIN. It is the outer BEGIN that matters. If you do SAVEPOINT that is not within a BEGIN, then that SAVEPOINT becomes an alias for BEGIN. So there is always an outermost BEGIN. The transaction is recoverable if and only if there is a COMMIT that corresponds to the outermost BEGIN.

(5) By ddevienne on 2021-02-08 13:22:57 in reply to 4 [link] [source]

Thank you.

(6) By Cecil (CecilWesterhof) on 2021-02-08 14:35:56 in reply to 2 [link] [source]

I was afraid of that. But luckily it was not to much work. I need to learn to use 'Write Changes' more often.

Just curious: why is there data written to those two files?

(7) By David Raymond (dvdraymond) on 2021-02-08 14:46:13 in reply to 6 [link] [source]

You can see the method for the journal file in the Atomic Commit In SQLite page.

The short version is that the -journal file is a backup of the original file contents in case something goes wrong during the transaction, or you decide to rollback.

(8) By Cecil (CecilWesterhof) on 2021-02-08 14:57:04 in reply to 7 [source]

There was nothing in the original file yet. So it seems that when you do something an empty file is created of 8 KB and copied to the -journal file.

I did a quick look and it seemed they where both mostly empty.

(9) By anonymous on 2021-02-12 06:08:27 in reply to 4 [link] [source]

Let me recap what I've understood what has been said:
a) you can nest save points;
b) save points may have a begin-instruction.

What about a little experiment. Here is my code for the experiment:
  1 SAVEPOINT "A";
  2 CREATE TABLE T1 (X TEXT  NOT NULL DEFAULT "");
  3 
  4 SAVEPOINT "B";
  5 INSERT INTO T1 (X)
  6 VALUES ("A"), ("B");
  7 SELECT "What was inserted?";
  8 SELECT * FROM T1;
  9 BEGIN;
 10         INSERT INTO T1 (X)
 11         SELECT "C";
 12         SELECT "What was inserted?";
 13         SELECT * FROM T1;
 14         ROLLBACK;
 15         SELECT "What was inserted?";
 16         SELECT * FROM T1;
 17 END;
 18 DELETE FROM T1;
 19 SELECT "What was inserted?";
 20 SELECT * FROM T1;
 21 ROLLBACK;
 22 SELECT "What was inserted?";
 23 SELECT * FROM T1;
 24 

save as 't1.sql' and execute sqlite3 t1.db < t1.sql

Here is the result:
What was inserted?
A
B
Error: near line 9: cannot start a transaction within a transaction
What was inserted?
A
B
C
What was inserted?
Error: near line 16: no such table: T1 (→ Rolled the database back to save point A
Error: near line 17: cannot commit - no transaction is active
Error: near line 18: no such table: T1
What was inserted?
Error: near line 20: no such table: T1
Error: near line 21: cannot rollback - no transaction is active
What was inserted?
Error: near line 23: no such table: T1

I think the behavior is different from what I've understood, and I think it isn't compliant with what is stipulated on this page https://sqlite.org/lang_savepoint.html

(10.1) By Keith Medcalf (kmedcalf) on 2021-02-12 06:37:01 edited from 10.0 in reply to 9 [link] [source]

The result is exactly correct.

Line 1 created the outer transaction and named the state immediately after opening the outer transaction "A".

Line 4 created a savepoint "B" representing the location in the transaction after table T1 was created.

Line 9 attempted to open an outer transaction, but a transaction was already in progress, so you were presented with an error message and nothing was done.

Line 14 did a rollback of the outer transaction to BEFORE the creation of table T1 and "got rid of" savepoint locations "A" and "B" in that transaction.

Line 16 attempted to select from the non-existent table T1, you got an error message, and nothing was done.

Line 17 attempted to commit a transaction, but no transaction was in progress, so you got an error message and nothing was done.

Lines 18 and 20 attempted to access non-existent table T1 so you got an error message and nothing was done.

Line 21 attempted to rollback a non-existent transaction, so you got an error message and nothing was done.

Line 23 attempted to access non-existent table T1 so you got an error message and nothing was done.

(11) By anonymous on 2021-02-12 07:15:11 in reply to 10.1 [link] [source]

My problem is the concept of outer and inner transactions. 

What I anticipated: BEGIN signals the start of the transaction. 

To avoid the error I need to move save point "B".
The outer transaction is A 
The inner transaction is B
And the outer transaction doesn't start with the begin instruction.

  1 SAVEPOINT "A";
  2 CREATE TABLE T1 (X TEXT  NOT NULL DEFAULT "");
  3 
  4 INSERT INTO T1 (X)
  5 VALUES ("A"), ("B");
  6 SELECT "What was inserted?";
  7 SELECT * FROM T1;
  8 BEGIN;
  9         SAVEPOINT "B";
 10         INSERT INTO T1 (X)
 11         SELECT "C";
 12         SELECT "What was inserted?";
 13         SELECT * FROM T1;
 14         ROLLBACK TO "B";
 15         SELECT "What was inserted?";
 16         SELECT * FROM T1;
 17 END;
 18 DELETE FROM T1;
 19 SELECT "What was inserted?";
 20 SELECT * FROM T1;
 21 ROLLBACK;
 22 SELECT "What was inserted?";
 23 SELECT * FROM T1;
 24 

And the result is:
What was inserted?
A
B
Error: near line 8: cannot start a transaction within a transaction
What was inserted?
A
B
C
What was inserted?
A
B
What was inserted?
Error: near line 21: cannot rollback - no transaction is active
What was inserted?

Line 8 The error wasn't solved.
Line 17 tells me that BEGIN doesn't start a new transaction because it failed.
So what is the use of a BEGIN in a transaction?

But wait! Is begin short for begin transaction? That would explain a lot.

(12) By Keith Medcalf (kmedcalf) on 2021-02-12 07:17:52 in reply to 9 [link] [source]

Think of the database as a kitchen table with a bunch of plates on it.

When you *BEGIN" a transaction you "roll over a dirty dishes cart" to hold the plates.

When you do SAVEPOINT x you are doing the equivalent of writing x (the savepoint name) on a plate taken from the clean plate cupboard and stacking it on the "dirty dishes cart".

You can do a SAVEPOINT x command without doing the BEGIN first, in which case a dirty dishes cart (BEGIN) will be rolled over for you.

Now each time you make a change to the table (by putting some mashed potatos on a plate, for example) you have to save a "copy" of the plate as it was before the change was made on the "dirty dishes table" stack.

You carry on making copies of plates from the table onto the stack of copies of plates on the "dirty dishes stack", or adding plates on which you have written a savepoint name.

Once in a while you make "rollback to x" where x is a savepoint name that you previously used. When you do this, then each plate from the stack of plates is removed one after each from the "dirty dishes cart" and replaces the plate on the table which was modified. the modified plate from the table is loaded into the dishwasher. When you eventually get to the plate on which "x" is written you stop (you do not remove the plate on which "x" is written.

Once in a while you can also "release x" where x is a savepoint name that you previously used. In this case you look through the pile of plates on the dirty dishes cart looking for the plates you have written on. You place all plates you have written on including the one with "x" written on it in the dishwasher. Note that you have not done anything to the to the data (stack of plate copies) on the dirty dishes cart.

You will note that ROLLBACK x does NOT remove the savepoint name x, and neither rollback x nor release x puts away the cart (end the transaction). You still need to do this.

You may also say "rollback" without specifying a savepoint name, in which case you take each plate one after each from the "dirty dishes cart" and replace the corresponding plate on the table, and put the plate from the table in the dishwasher. If the plate that you take from the top of the pile on the dirty dishes cart has something written on it, it goes directly into the dishwasher. Eventually the dirty dishes cart is empty, so you wheel it back to the corner because the transaction is now finished.

You may also say "commit" or "end" in which case you simply put all the plates on the dirty dishes cart in the dishwasher, and put the cart back in the corner because the transaction is now complete.

If you attempt to "get another cart" while you already have a cart, you get an error message saying that you already have a cart in progress, and nothing will be done.

If you attempt to "close a cart" using "rollback", "commit", or "end" while you do not have a cart in progress you will get an error telling you so, and nothing will be done.

If you attempt to "rollback to x" or "release x" but there is no plate on which "x" is written in the stack of plates on the cart, you will get an error telling you so and nothing will be done.

(13) By Keith Medcalf (kmedcalf) on 2021-02-12 07:49:47 in reply to 11 [link] [source]

The concept of inner and outer transactions is fundamentally flawed, because transactions cannot be nested.

Transactions cannot be nested. There is only one transaction. It is started with a BEGIN TRANSACTION statement (which may implicitly precede a SAVEPOINT x statement if no transaction has yet commenced).

The SAVEPOINT x gives a LABEL to the particular state of the transaction at the point at which the statement occurs.

ROLLBACK TO x rolls back the transaction to the state the transaction was in at the time the label was created, which includes destroying all labels created subsequent to the creation of the label x.

RELEASE x removes the LABEL x and all labels assigned after label x was assigned from the transaction.

You still need to COMMIT or ROLLBACK the entire transaction.

A transaction is like a logbook, which you BEGIN (go fetch an empty logbook) and then xerox each page from the source book before you modify it and put the xerox copy in the logbook.

When you COMMIT or END the transaction, you are throwing the logbook in the shredder (so you can no longer "go back" to where you were before in the source book -- in other words, making the changes you made permanent).

When you ROLLBACK the transaction, you basically remove the pages from the logbook and put them back in the location from where they came in the original book and throwing the modified pages into the shredder (that is, undoing the changes). When the logbook is empty, you throw it in the pager shredder too.

Creating a "SAVEPOINT" basically means that you get a coloured tab page with whatever name you like written on it and insert it at the current (end) position of the logbook.

ROLLBACK TO x basically means that you do the ROLLBACK procedure above, also throwing any coloured tab page that is not the one you are looking for into the paper shredder. When you finally come across the coloured tab page named x you stop, leaving the coloured tab page named x as the last page of the logbook.

RELEASE x basically means that you start ripping coloured tab pages from the logbook from end to beginning until you find the one with x written on it. Once you have thrown the coloured tab page labelled x into the shredder, you stop. You do not change or modify in any way any other page in the logbook.

Note that in the case of SAVEPOINT x, ROLLBACK TO x and RELEASE x, you still have a logbook. You can only "do away with" the logbook by issuing either ROLLBACK or COMMIT or END for the whole thing -- even if the whole process was started implicitly.

(14) By anonymous on 2021-02-12 08:44:03 in reply to 13 [link] [source]

Thank you. This is a clear and concise explanation.

This is the text that confused me:
The SAVEPOINT command starts a new transaction with a name. The transaction names need not be unique. A SAVEPOINT can be started either within or outside of a BEGIN...COMMIT. When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED TRANSACTION.

So the correct way is:
BEGIN TRANSACTION;
 do something
  set a save point for the next step
  and do so more and if it fails rollback to to the save point for a do-over.

when finished execute END; or COMMIT;

(15) By Keith Medcalf (kmedcalf) on 2021-02-12 09:31:28 in reply to 14 [link] [source]

Exactly.

A SAVEPOINT is exactly what its name implies -- it is a named place in the progress of a transaction which you can ROLLBACK TO (ie, undo everything done after that named location) so you can try again (or do a different path). The RELEASE command searches for the given savepoint name marker and removes all savepoint names back to and including the one specified.

There is no requirement for the SAVEPOINT names to be unique within a transaction. The search for a SAVEPOINT name to ROLLBACK TO or RELEASE starts from the current position and works backwards in time.

If you try to ROLLBACK TO or RELEASE a savepoint that does not exist, you get an error message and nothing is done.

sqlite> create table x(x);
sqlite> begin;
sqlite> savepoint a;
sqlite> insert into x values (1);
sqlite> savepoint a;
sqlite> insert into x values (2);
sqlite> savepoint a;
sqlite> insert into x values (3);
sqlite> select * from x;
┌───┐
│ x │
├───┤
│ 1 │
│ 2 │
│ 3 │
└───┘
sqlite> release a;
sqlite> select * from x;
┌───┐
│ x │
├───┤
│ 1 │
│ 2 │
│ 3 │
└───┘
sqlite> rollback to a;
sqlite> select * from x;
┌───┐
│ x │
├───┤
│ 1 │
└───┘
sqlite> rollback to x;
Error: no such savepoint: x
sqlite> select * from x;
┌───┐
│ x │
├───┤
│ 1 │
└───┘
sqlite> end;

(16) By Keith Medcalf (kmedcalf) on 2021-02-14 10:55:59 in reply to 14 [link] [source]

You will note that if there is no transaction in progress and you commence the transaction with a SAVEPOINT name command, then you must end the transaction with RELEASE name (or the equivalent COMMIT or END), even if you have done a ROLLBACK TO name because ROLLBACK TO does not release the transaction.

A transaction which is started with a BEGIN must be terminated with either a COMMIT, END, or ROLLBACK notwithstanding that all savepoint's in the transaction have been RELEASEd.

That is:

SAVEPOINT a;
INSERT ...
RELEASE a;

is equivalent to:

BEGIN;
INSERT ...
COMMIT;

is equivalent to:

SAVEPOINT a;
INSERT ...
COMMIT;