SQLite Forum

Renaming a database

Renaming a database

(1) By Toby Dickenson (tobydickenson) on 2020-03-17 14:42:44 [link] [source]

The "how to corrupt" page is quite clear that renaming a database file while in use can corrupt the database.

But what if I have a requirement to rename a database, and various disparate clients which might be using it. Is there ever a safe way to rename a database? Or do I need some locking outside of sqlite implemented in every client?


(2) By Stephan Beal (stephan) on 2020-03-17 15:06:50 in reply to 1 [link] [source]

It's never, ever generically safe to rename an in-use sqlite3 file.

Windows won't let you rename an in-use file and in Unix if you rename an in-file file, anyone who has that file handle open is still using the old file handle (which may misbehave if the file is moved to a different filesystem and therefore gets a new inode - i'm not actually sure what happens in that case).

sqlite uses the file's name for creating various temporary files, e.g. the write-ahead log, and renaming a db file will cause a mismatch there.

In order to safely rename an sqlite db, it must not currently be opened by any clients.

(4) By anonymous on 2020-03-18 03:12:03 in reply to 2 [link] [source]

I think in Unix you can't move a file to a different filesystem; you will have to make a copy and then delete the original one. I think in this case, the original file will remain open, although there is no other reference to it, so it will be deleted when it is closed.

(5) By Gunter Hick (gunter_hick) on 2020-03-18 08:24:34 in reply to 4 [link] [source]

Linux mv (rename file) command is guaranteed to be atomic within the same filesystem. When aplied across filesystems, it will internally perform the cp (copy file) and rm (delete old file) actions while losing atomicity.

(6) By Gunter Hick (gunter_hick) on 2020-03-18 08:28:47 in reply to 2 [link] [source]

Linux filehandles store device(inode number and original path (e.g. for the lsof command) and will not be affected by the directory entries originally used to translate path/filename into device/inode pointing somewhere else.

The inode and the associated file contents will remain acessible for all currently open filehandles until the last one Closes, at which point the inode and associated contents/disc blocks will be freed.

(7) By Toby Dickenson (tobydickenson) on 2020-03-18 10:13:27 in reply to 2 [link] [source]

It's never, ever generically safe to rename an in-use sqlite3 file.

That somewhat contradicts the suggestion that sqlite works well as an application file format. Never renamed a spreadsheet?

moved to a different filesystem and therefore gets a new inode - i'm not actually sure what happens in that case

A move to a different filesystem is effectively a copy, and that could be achieved without risk of corruption using the backup API. But I'm looking for a solution that avoids rewriting every page.

Windows won't let you rename an in-use file

Well actually that would be a good solution. If Windows blocks any unsafe renames, then I can simply retry. Unfortunately I'm on unix.

In order to safely rename an sqlite db, it must not currently be opened by any clients

So (on unix) is there a way to prevent it being used by any clients? Some exclusive lock that can be taken during the rename?

(8) By Richard Hipp (drh) on 2020-03-18 11:10:31 in reply to 7 [link] [source]

That somewhat contradicts the suggestion that sqlite works well as an application file format. Never renamed a spreadsheet?

Spreadsheets do not incrementally write their files, nor do they preserve their file content if a power-loss occurs while writing, nor do they allow multiple applications to read/write the same file at the same time, nor are they transactional. They instead keep the entire file in memory, and the write the whole thing out to disk (non-atomically) in one go when you do File/Save.

You can achieve the same effect in SQLite by reading the entire database into memory, working with the database while in-memory, then writing the entire thing back out to disk when you are done. The sqlite3_deserialize() interface might be useful for loading an entire database into memory. For writing the database back to disk, you can use the sqlite3_serialize() interface or the backup api or the VACUUM INTO command. If you use SQLite in that way, you can rename the database file whenever you like, just like you do with spreadsheets.

But, if you make use of incremental I/O, transactions, or guarantees of consistency across power failures, then the rule is that you may not rename the database file while it is in use. As long as no other applications have the database open, you can rename it all you want. But renaming an SQLite database out from under another application that is actively using it might cause problems for that other application. And if the application that has the database file renamed out from under it tries to write, that could result in database corruption in the worst case.

(9) By Tim Streater (Clothears) on 2020-03-18 11:42:06 in reply to 7 [link] [source]

Under macOS you can rename files when open for read or write; I do it frequently. But for this to work properly, AIUI it relies on that the filesystem notifies apps that this has happened. I imagine that apps have to register for this service and then have to update their internal info about filenames, but I don't know more about it than that. Even MS gets this nearly right in Office. In Office 2016 for macOS, Word could cope with a file being renamed under it and changed the filename it displays in the window title bar; Excel could not.

(10) By anonymous on 2020-03-18 14:54:54 in reply to 7 [link] [source]

The situation is more complicated.

The behaviour depends on both the operating system and the file system. Various combinations of these

Block attempts to rename a file which is open.

Allow renaming an open file but do not notify the applications which have it open. This means that the application may temporarily close the file (e.g. because it ran out of file handles) then crash when it tries to reopen the file.

Allow renaming, after closing all file handles for the file, meaning that the application which had the file open will crash the next time it tries to use it.

All of these present problems for anything that relies on you renaming files. And they're further complicated because SQLite automatically makes and deletes files with similar names to the database as it works and does not get notified if a filename is changed while its in use.

Once again: anything that has meaning is data. A file's name is not data, it's an obscure detail of how computers maintain some kinds of storage. If you have data it should be in a database, not the name of a file on a storage device.

(3) By anonymous on 2020-03-18 00:32:01 in reply to 1 [link] [source]

You cannot do this.

File names are not data. They shouldn't mean anything that might change. Redesign your system so that the meaning of a file is stored in a file. Either that file or another one.

(11) By skywalk on 2020-03-18 15:19:04 in reply to 3 [link] [source]

Filenames are absolutely data, else the risk of corruption would not be grave.
That said, renaming a shared asset is not good engineering.

(12) By Donald Griggs (dfgriggs) on 2020-03-18 16:35:27 in reply to 1 [source]

Re: "..I have a requirement to rename a database, and various disparate clients which might be using it."

Hi, Toby,

Are you at liberty to elaborate on this requirement? This list includes some really helpful folks who might be able to offer some alternate solutions.

(13) By Toby Dickenson (tobydickenson) on 2020-03-23 10:38:57 in reply to 12 [link] [source]

elaborate on this requirement

I am at the design stage for an application which involves collecting measurements from various measurement instruments. There are several writer processes, recording new timestamped measurements from measurement instruments. There are several reader processes. Queries normally involve a large number of measurements from a single instrument, and rarely involve more than one instrument.

I can see a design which puts everything in one database. This would be the one obvious design if using a server-based database.

But sqlite's one-file-per-database model offers a spectrum of other design options. Maybe it could store all the measurements from each instrument in separate databases. Maybe it stores related instruments in separate databases. These options allow some application requirements to be satisfied in a (potentially) simpler manner, outside the database. For example, archiving a dataset and starting afresh could be achieved by renaming the dataset database file. File names can be data too.

Ive got a good understanding of the relative advantages of all these options.

The documented constraint is:

renaming an open database file results in behavior that is undefined

Thats pretty clear, but I was hoping it was an oversimplification. I completely understand the problems caused by arbitrary renaming, but was hoping sqlite's existing locking mechanisms could be used to allow a safe rename in a controlled manner.

Its not a big deal that this isnt possible. I can add either extra locking in the application layer, or an additional layer of indirection (symlink?) so that my conceptual "rename" gets implemented by changing the link.

(14) By Wout Mertens (wmertens) on 2020-03-23 11:33:31 in reply to 13 [link] [source]

A problem occurs if you use e.g. WAL mode and it actually uses 3 files per database. You can't rename them atomically. You could rename a parent directory.

Anyway, if you want to move "archived" records out of the way and always have a certain filename contain the latest data, it would be better to create the archive db, copy records into it and then delete them from the original once the copy completed. It's more work but no weirdness can ensue from renames.

(15) By Ryan Smith (cuz) on 2020-03-23 12:04:28 in reply to 13 [link] [source]

It's not that it isn't possible though...

It's only a problem if you have multiple connections that wish to write - a very specific case that no normal program really allows, especially not Excel.

Your use-case seems like it would easily allow closing a DB from the single writing thread (especially if you do the file-per-recorder type scenario) at which point it can easily be renamed/moved without loss to file-data or integrity.

What still needs to be addressed then is the reader connections continuity, if only for the purpose of making said readers understand that the data they are looking at now is either for a different filename (*nix) or the file handle is now unbound (Windows), assuming they do not already have machinery to deal with this.

I've done this rather successfully with a simple token file. The moment it appears (with the same name as the open file, plus some marker) then a reader knows to reset it's connection to whatever is in the token. Many more things can be communicated this way, but the need is typically only to flag change to an unconnected system interested in the file. It can also be a setting in the DB file, but the problem is that the DB itself may have moved by the time the reader checks, and at least on some OSes that is a problem.