Enhancement: Purging Database Without Losing Configuration Settings Or Export/Import Configuration Settings

[jlpoolen]

I'm testing various instances of moonfire on different servers using TCP and UDP. Unfortunately, on one instance, I neglected to "export TZ=US/Pacific" prior to running and the result is that rows based UTC (7 hours ahead of my zone as of Oct 1, 2021) were presumably saved in the database. When I then reran moonfire with the time zone appropriately specified, the results in the database from the earlier run appear to be affecting the web UI which is showing nothing recently recorded.

What I'd like to do is be able to purge all the data entries relating to recordings, but not the configuration. Could there a SQL script that could be promulgated that accomplishes this without corrupting the internal relations? I could propose one, but the consequences of row deletions is outside my ken of how moonfire interacts with the database.

Or, it would be great to have an export/import of configuration settings, i.e. to an XML or JSON file, since it takes some time and keyboard entry of values to specify the configurations. Presumably, I could just delete the database, import the configuration settings and start anew. Also, a peeve: when configuring the disk space, I cannot specify the limit, I have to re-enter the configuration setting in order to provide a limit value -- a step I sometimes forget. It would be helpful to be able to specify the maximum disk space in the initial configuration setting ("new").

[scottlamb]

Unfortunately, on one instance, I neglected to "export TZ=US/Pacific" prior to running and the result is that rows based UTC (7 hours ahead of my zone as of Oct 1, 2021) were presumably saved in the database.

The stored values are all UTC-based. The server uses the timezone only to set the date boundaries for its in-memory indexes and to tell the UI what timezone to display. So I'm not sure exactly what went wrong, but if you restart the server with the correct timezone and refresh the webpage, the previous timezone shouldn't matter at all.

One annoying weakness in the current model is that the server communicates the timezone to the UI by name, rather than giving it the full rules associated with the timezone. If say the server has a different idea of what US/Pacific means than web browser, results will be...weird. Like the server saying there's video in a particular day but nothing actually showing up in the list. Maybe that's what's happening here. US/Pacific is a deprecated time zone name; you might try America/Los_Angeles and see if that works better.

But yeah, an API to purge stuff is a totally reasonable request, as is config import/export.

[valpackett]

"purge all the data entries relating to recordings" is also required for the "I just deleted the video directory on disk" case. Currently there doesn't seem to be a good way to handle that :/ just pressing on the config entry in "sample file directories" when the directory has been nuked just quits the config UI, and even changing a stream's directory is not possible, it says "Unable to edit camera: can't change sample_file_dir_id 1->None for non-empty stream 1".

[scottlamb]

Yeah, we don't have much support yet for cleaning up after mistakes. I think there are basically three options now:

1. Nuke the database too and start over.

2. Run moonfire-nvr check which can clean up after some/all missing sample files as long as you still have the directory's meta file. If you don't, this won't work. PR welcome to add a moonfire-nvr check --replace-meta-file or the like. (The fomrat is described in server/db/proto/schema.proto, and the existing code for dealing with it is in server/db/dir/mod.rs.) moonfire-nvr check was written to handle missing/suspect files after fsck cleans up filesystem corruption. Missing files after sysadmin mistakes aren't that different.)

3. Manually mess with the SQLite database. If the directory was the only one, you can do something like this (untested):

$ moonfire-nvr sql
pragma foreign_keys = on;
begin transaction;
delete from recording_playback;
delete from recording_integrity;
delete from recording;
delete from garbage;
update stream set sample_file_dir_id = null;
delete from sample_file_dir;
commit;

It'd be nice to also have some commands for things like:

• I moved this sample file directory from /old/path to /new/path on my own; match that so moonfire-nvr run works again.
• I lost /old/path completely; remove all of its data (via commands similar to the ones above, but keeping any other directories that still exist) so moonfire-nvr works again.
• I restored /old/path from backup; remove database rows newer than its meta, then update its meta so moonfire-nvr run works again.
• ...probably other things...

[valpackett]

Yeah (3) is pretty much what I did, except I used the system sqlite3 binary, eventually managed to do update stream set sample_file_dir_id = null; from the config UI, and did not touch garbage (heh)

[scottlamb]

moonfire-nvr sql uses the system sqlite3 binary, too. It just locks the directory and picks the right arguments, then does this:

moonfire-nvr/server/src/cmds/sql.rs

Lines 54 to 58 in 0406e09

	Err(Command::new("sqlite3")
	.arg(&db)
	.args(&args.arg)
	.exec()
	.into())

I should add to those arguments: -cmd "pragma foreign_keys = on;". Without that pragma, SQLite3 doesn't check foreign key constraints, so you might have garbage rows that reference the missing dir. You can check for that:

select
  count(*)
from
  garbage
where
  not exists (
    select
      'x'
    from
      sample_file_dir
    where
      sample_file_dir.id = garbage.sample_file_dir_id);