/ View Ticket
Ticket UUID: 2466653295e655ce00990eae58939c0d33ed0b5c
Title: Various dump issues
Status: Fixed Type: Code_Defect
Severity: Minor Priority: Immediate
Subsystem: Shell Resolution: Fixed
Last Modified: 2011-10-13 13:34:18
Version Found In: 3.6.18
While the dump command is running the database is not locked (eg BEGIN IMMEDIATE). It is possible (but unlikely) for another process to mess with the database while the dump is running (very small windows of time). Worst case another process could start a transaction causing the dump to timeout mid-dump.

There is a zErrMsg variable but it isn't passed to any of the dumping helper functions nor is the error response of those functions checked. This means that if any errors are encountered they are silently ignored.

If SQLITE_CORRUPT is returned then error recovery repeats the effort to dump the table by reverse rowid (ie most recent writes first). Again this appears to be silent.

In the version after 3.6.18 PRAGMA foreign_keys is turned off at the beginning of the dump (even if no foreign keys are present) and never turned back on.

For the record when searches work in Fossil I was concerned that virtual tables are detected by looking for exactly "CREATE VIRTUAL TABLE" but testing shows that if the table is created using lower case, spaces and tabs etc they are all normalized to upper case single space separated before being stored in sqlite_master so there is no problem.

shane added on 2009-10-26 01:35:03:
For .dump, if you want to lock the database during the dump, you should use:

sqlite3> .dump
sqlite3> ROLLBACK;

I'll take a look at the other issues.

PS. Not sure if you were pointing out an issue with the "CREATE VIRTUAL TABLE" comment or not.

rogerb added on 2009-10-26 01:55:04:
The problem is the dump code itself does not lock the database. It expects the database to not change while it is working so if someone else was changing it the .dump code would (silently) keep plodding on. The dump_callback function goes off and issues other queries as an example although it looks like the database would be reader locked while that happens so probably not an issue.

The other comment was because on examining the dump source you can see string comparisons against text like "CREATE VIRTUAL TABLE" and my first thought was well what if it was created as "CrEaTe VirTUAl table". The answer is the SQLite core uses the fixed uppercase strings and so it isn't a problem.