sqlite_database

sqlite_database checks the SQLite database file of another program on the node. SQLite is built into the mmwatchdog program. The check runs a small query through SQLite. The check reports the sizes of the database file and the write-ahead log.

With check_integrity: true, the check also reads every page of the database and fails when SQLite finds damage. The check opens the database file for reading only, as an ordinary reader, like the sqlite3 program or a backup tool. The check never changes the database.

Inputs

  • path (string; required): The absolute path of the SQLite database file.
  • check_integrity (boolean; optional): With true, the check also reads every page of the database and fails when SQLite finds damage. On a large database this takes long.

Measurements

  • database_size_mib (MiB): The size of the database file.
  • write_ahead_log_size_mib (MiB): The size of the write-ahead log beside the database file, or 0 without one.

The module fails a check by itself, and it also reports these measurements. A check sets a limit on a measurement with fail_when_measurement_above or fail_when_measurement_below, and fails when a reading passes its limit. A check with the module always states an alert_severity. The configuration files reference explains limits in checks that measure a number.

When the check fails, and when it cannot check

The check fails when:

  • the file does not exist: /var/lib/myapp/myapp.db does not exist;
  • the path is no regular file: /var/lib/myapp/myapp.db is a directory, not a regular file;
  • the file is no SQLite database: /var/lib/myapp/myapp.db is no SQLite database: the file does not start as a SQLite database does;
  • SQLite finds the database damaged: SQLite cannot run a query on /var/lib/myapp/myapp.db: database disk image is malformed (11), or file is not a database (26);
  • with check_integrity: true, SQLite finds damage: /var/lib/myapp/myapp.db is damaged: the check of its integrity found 2 problems. The check stops after ten problems and then says 10 problems or more. The alert's body lists the problems;
  • a reading passes a limit.

MMWatchdog cannot check, and the run is unknown, when:

  • another program holds the database locked for the whole time limit: MMWatchdog could not run a query on /var/lib/myapp/myapp.db: another program holds the database locked;
  • the query does not end within the check group's time_limit_per_run: MMWatchdog could not finish a query on /var/lib/myapp/myapp.db within the time limit of the run;
  • MMWatchdog cannot open or read the file for another reason, such as a filesystem that answers with an error: MMWatchdog cannot look at /var/lib/myapp/myapp.db: ... or MMWatchdog cannot read /var/lib/myapp/myapp.db: ...;
  • SQLite cannot run the query for a reason that is not damage, such as the two files beside the database that it cannot open or make (see the check is an ordinary reader): MMWatchdog could not run a query on /var/lib/myapp/myapp.db: unable to open database file (14);
  • the database keeps no write-ahead log, and the check states check_integrity: true (see a database without a write-ahead log).

A failed run starts or continues the check's failing time. An unknown run opens a cannot-check incident of its own after the same waiting times, as the design's failing time describes. Both measurements take a limit in the same way as every measurement does (checks that measure a number).

Examples

The database answers, and its write-ahead log stays small

# /etc/mmwatchdog/check_groups/myapp_database.yml
check_groups:
  myapp_database:
    check_every: 1m
    time_limit_per_run: 10s
    alert_after_failing_for: 5m
    recovered_after_healthy_for: 1m
    checks:
      myapp_database_answers_and_its_log_is_small:
        sqlite_database:
          path: /var/lib/myapp/myapp.db
        fail_when_measurement_above:
          write_ahead_log_size_mib: 256
        alert_severity: warn

Every minute, MMWatchdog on the node opens /var/lib/myapp/myapp.db for reading only, runs a small query on it, and measures the database file and its write-ahead log, /var/lib/myapp/myapp.db-wal. When the query runs and the log is at most 256 MiB, the run is healthy, with a message such as write_ahead_log_size_mib is 3.2 (fails above 256); /var/lib/myapp/myapp.db answers a query. A run fails when /var/lib/myapp/myapp.db is missing or damaged, or when /var/lib/myapp/myapp.db-wal is larger than 256 MiB. After the check has been failing for 5 minutes, MMWatchdog sends a warn alert.

A SQLite database that keeps a write-ahead log writes each change to that log first, and moves the changes into the database file from time to time. A program that never copies changes into the database file, or a reader that never ends the read transaction, leaves a log that grows without end. A limit on write_ahead_log_size_mib makes the check fail when the log keeps growing past the limit. A limit on database_size_mib makes the check fail when the database file keeps growing past the limit.

The integrity of the whole database, once a day

check_integrity takes as long as reading the whole file takes. Give such a check its own check group, with a check_every of hours and a time_limit_per_run that fits the size of the database:

# /etc/mmwatchdog/check_groups/myapp_database_integrity.yml
check_groups:
  myapp_database_integrity:
    check_every: 24h
    time_limit_per_run: 10m
    alert_after_failing_for: 0s
    recovered_after_healthy_for: 0s
    checks:
      myapp_database_is_intact:
        sqlite_database:
          path: /var/lib/myapp/myapp.db
          check_integrity: true
        alert_severity: warn

Once a day, MMWatchdog on the node runs the small query, and then SQLite's check of every page, within 10 minutes. The first run that finds damage sends a warn alert at once, because alert_after_failing_for is 0s. A healthy run says /var/lib/myapp/myapp.db answers a query, and its integrity is intact.

How it works

The check is an ordinary reader

MMWatchdog opens the file for reading only, with SQLite's query_only, and asks SELECT count(*) FROM sqlite_schema. With check_integrity: true, MMWatchdog then runs SQLite's PRAGMA integrity_check. Both are ordinary readers of the database, with SQLite's locks, as the sqlite3 program and backup tools are. An ordinary reader sees one finished state of the database for as long as the reader reads, also while SQLite moves changes from the write-ahead log into the database file. MMWatchdog waits for a lock for the rest of the run's time limit.

In a database with a write-ahead log, a reader does not make a writer wait. Four things follow from an ordinary reader, the same as from every other reader of the database:

  • While the check reads the database, the write-ahead log cannot be emptied, so the log grows for the duration of the read. The small query takes a few milliseconds. With check_integrity: true, the read lasts for the whole integrity check.
  • While no other program has the database open, SQLite makes the two files that every reader needs: the log, with the ending -wal, and a file with the ending -shm. The -wal and -shm files stay after the check, as after the sqlite3 program. MMWatchdog runs as root, and the -wal and -shm files get the database file's owner and permissions. The database's own program can therefore continue using the -wal and -shm files.
  • When SQLite can neither open nor make the database's -wal and -shm files, the run is unknown, with SQLite's reason. For example, the filesystem may be mounted read-only.
  • A program that waits for no lock at all can, rarely, be told once that the database is busy. SQLite asks every program that shares a database to wait for a lock with a busy timeout (busy_timeout), and most libraries do so by default: Python's sqlite3 module waits 5 seconds. A program that waits for a lock does not notice the check.

A database without a write-ahead log

MMWatchdog reads the database file's first bytes, which say that the file is a SQLite database and whether the database keeps a write-ahead log, and the database file's size.

On a database without a write-ahead log, MMWatchdog runs no query. SQLite changes a database without a write-ahead log in place, and a reader makes the writing program wait, or fail, at the program's next change. The message says so: /var/lib/myapp/myapp.db is a SQLite database; it keeps no write-ahead log, so MMWatchdog ran no query on it. With check_integrity: true such a run is unknown, with /var/lib/myapp/myapp.db keeps no write-ahead log, so MMWatchdog runs no query on it and cannot check its integrity: a reader of such a database makes the program that writes it wait.

The measurements

database_size_mib is the size of the database file, and write_ahead_log_size_mib the size of the file beside it whose name ends with -wal, both in MiB. Without such a file the log's size is 0.

Errors and what they mean

  • check_groups.myapp_database.checks.myapp_database_answers_and_its_log_is_small.sqlite_database.path: is required: the absolute path of the SQLite database file. The path is missing, relative, or not in its simplest form, such as one with .. or //. Write the whole path, such as /var/lib/myapp/myapp.db.
  • SQLite cannot run a query on /var/lib/myapp/myapp.db: .... SQLite found the database damaged. SQLite's reason follows: database disk image is malformed (11) or file is not a database (26). Run sqlite3 /var/lib/myapp/myapp.db 'PRAGMA integrity_check' on the node, and restore the database from a backup.
  • MMWatchdog could not run a query on /var/lib/myapp/myapp.db: .... The run is unknown. SQLite's reason follows. unable to open database file (14) means that SQLite can neither open nor make the files myapp.db-wal and myapp.db-shm beside the database: see that they are files, and that the directory is writable.
  • MMWatchdog could not run a query on /var/lib/myapp/myapp.db: another program holds the database locked. The run is unknown. A program kept a lock for the whole time limit.
  • MMWatchdog could not finish the check of its integrity on /var/lib/myapp/myapp.db within the time limit of the run. Give the check a longer time_limit_per_run, in a check group of its own.

Tested on

  • Go tests: TestSQLiteDatabaseCheck in server_checks_test.go runs the check against real databases: the query, the check of the integrity, the database file unchanged; the two files that the check makes beside a database that no program has open, with the permissions of the database file and, as root in the native suite, its owner; a database whose file is overwritten in its first page while the newest version of that page is in the log, as in the middle of a move of changes into the file, which is healthy; the same damage with an empty log, which fails; a file beside the database that SQLite cannot open, which is unknown; a program that writes without a pause, waits for a lock for at most one second, and moves every change into the database file at once, never getting an error over 300 runs of the check and 40 checks of the integrity; the size of the write-ahead log with a limit; a database without a write-ahead log; a file that is no database; and a damaged database.
  • On every distribution: the services suite (service_scenarios.py) checks a database that a program of another account writes 20 rows a second, with Python's default wait for a lock, while the check reads it with check_integrity: true: the program never gets an error, the files of the database keep their owner, and both sizes are measured. It also checks a file that is no database and a missing file. The latest run passed on every target distribution (validation record).

Why it works this way

  • The check never changes the database. The check opens the database file for reading only, with query_only.
  • The check is an ordinary reader, never a reader without locks. SQLite can read a file without any lock (immutable=1), but only a file that cannot change. SQLite's documentation says that such a reader of a file that changes can get wrong answers and SQLITE_CORRUPT errors. Beside a busy program, a reader with immutable=1 calls a healthy database malformed, even after several attempts. A reader using immutable=1 can read the database file while SQLite copies changes from the write-ahead log into the database file. An ordinary reader is what every tool that reads the live database of another program uses, from the sqlite3 program to backup tools, and SQLite's locks give it one finished state of the database.
  • Of SQLite query errors, only a verdict of damage fails the check. SQLite's verdicts of damage are SQLITE_CORRUPT (database disk image is malformed) or SQLITE_NOTADB (file is not a database). Other SQLite query errors mean only that MMWatchdog could not read the database, so the run is unknown. Examples include a lock held for the whole time limit or a query stopped by the time limit. Another example is a file beside the database that SQLite cannot open.
  • No query on a database without a write-ahead log. SQLite changes the database file in place. A reader therefore makes the writing program wait for a lock or fail to write.

This page is docs/reference/modules/sqlite_database.md in the MMWatchdog repository.