SQLite3 Backup Fails When Running as Daemon: Causes and Fixes

Issue Overview: SQLite3 Backup Process Fails in Daemon Mode with Empty Backup Files

The core issue revolves around an SQLite3 backup process that functions correctly when executed in the foreground but fails when run as a daemon using a systemd service. Specifically, the backup files created in daemon mode are consistently 4096 bytes in size and contain no data, despite the original databases being actively written to by a Python SQLite3 client. This discrepancy suggests that the backup process is not correctly capturing or transferring data from the source databases to the backup destination when running in the background.

The problem manifests in the following way: When the backup process runs in the foreground, the backup files grow in size and contain the expected data. However, when the same process is executed as a daemon via a systemd service, the backup files remain at a fixed size of 4096 bytes and are empty upon inspection. This behavior indicates that the backup process is either failing to access the source databases correctly or is encountering an issue during the data transfer phase when running in daemon mode.

The systemd service configuration provided is minimal, with no explicit specification of the working directory or user/group context under which the service should run. This lack of configuration could be contributing to the issue, as the relative paths used for the source databases may resolve differently when the process is run as a daemon compared to when it is run in the foreground.

Possible Causes: Relative Paths, Working Directory, and Daemon Context

One of the primary suspects in this issue is the use of relative paths for the source databases. In the provided code, the source database paths are specified as relative paths (e.g., .SYSDB, .CONNDB). When the program is run in the foreground, the current working directory (CWD) is typically the directory from which the program was launched. However, when the program is run as a daemon via systemd, the CWD may not be the same, leading to the resolution of these relative paths to unexpected locations.

If the CWD for the daemon process is different from the expected directory, the program may attempt to open and back up databases that do not exist or are empty. This would explain why the backup files created in daemon mode are consistently 4096 bytes in size and contain no data. The backup process would be operating on empty or non-existent databases, resulting in empty backup files.

Another potential cause is the lack of explicit user/group context in the systemd service configuration. When running as a daemon, the process may not have the necessary permissions to access the source databases or the backup directory. This could result in the backup process failing silently, leading to the creation of empty backup files.

Additionally, the systemd service configuration does not specify a working directory for the daemon process. Without an explicit working directory, the daemon may inherit the root directory (/) as its CWD, which would cause the relative paths for the source databases to resolve incorrectly. This misalignment between the expected and actual CWD could be the root cause of the issue.

Troubleshooting Steps, Solutions & Fixes: Ensuring Correct Path Resolution and Permissions

To resolve the issue, the first step is to ensure that the source database paths are correctly resolved when the program is run as a daemon. This can be achieved by using absolute paths for the source databases instead of relative paths. By specifying the full path to each source database, the program will be able to locate and access the databases correctly, regardless of the CWD.

For example, instead of using .SYSDB as the source database path, the program should use /path/to/database/.SYSDB. This ensures that the path to the source database is unambiguous and will resolve correctly in any context, including when the program is run as a daemon.

The next step is to explicitly specify the working directory in the systemd service configuration. This can be done by adding the WorkingDirectory directive to the [Service] section of the systemd service file. The WorkingDirectory should be set to the directory containing the source databases, ensuring that any relative paths used within the program resolve correctly.

For example, the systemd service configuration could be updated as follows:

[Unit]
Description=Log backup service
[Service]
Type=simple
ExecStart=/usr/sbin/logbackupd
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
WorkingDirectory=/path/to/database
User=yourusername
Group=yourgroupname

In this configuration, the WorkingDirectory is set to /path/to/database, which is the directory containing the source databases. The User and Group directives specify the user and group under which the daemon should run, ensuring that the process has the necessary permissions to access the source databases and the backup directory.

Additionally, it is important to verify that the backup directory (/root/backup/) is accessible by the user under which the daemon is running. If the backup directory is not accessible, the backup process may fail silently, resulting in empty backup files. To ensure accessibility, the backup directory should have the appropriate permissions set, allowing the daemon user to read and write to the directory.

If the issue persists after making these changes, it may be necessary to add logging to the backup process to capture more detailed information about the failure. This can be done by modifying the BackUp function to log additional details, such as the resolved paths of the source and destination databases, the return codes from SQLite3 API calls, and any error messages returned by SQLite3.

For example, the BackUp function could be updated as follows:

int BackUp(const char *database_src, const char *database_dst) {
  int rc;
  sqlite3 *p_src;
  sqlite3 *p_dst;
  sqlite3_backup *p_backup;

  syslog(LOG_INFO, "Attempting to open source database: %s", database_src);
  rc = sqlite3_open(database_src, &p_src);
  if (rc != SQLITE_OK) {
    syslog(LOG_ERR, "Can't open source database=%s, rc=%d, errmsg=%s", database_src, rc, sqlite3_errmsg(p_src));
    return rc;
  }

  syslog(LOG_INFO, "Attempting to open destination database: %s", database_dst);
  rc = sqlite3_open(database_dst, &p_dst);
  if (rc != SQLITE_OK) {
    syslog(LOG_ERR, "Can't open destination database=%s, rc=%d, errmsg=%s", database_dst, rc, sqlite3_errmsg(p_dst));
    sqlite3_close(p_src);
    return rc;
  }

  sqlite3_busy_handler(p_src, SQLBusyHandler, (void *)p_src);
  sqlite3_busy_handler(p_dst, SQLBusyHandler, (void *)p_dst);

  syslog(LOG_INFO, "Initializing backup from %s to %s", database_src, database_dst);
  p_backup = sqlite3_backup_init(p_dst, "main", p_src, "main");
  if (p_backup == NULL) {
    syslog(LOG_ERR, "Can't initialize backup, rc=%d, errmsg=%s", rc, sqlite3_errmsg(p_src));
    sqlite3_close(p_src);
    sqlite3_close(p_dst);
    return rc;
  }

  syslog(LOG_INFO, "Performing backup step");
  sqlite3_backup_step(p_backup, -1);
  sqlite3_backup_finish(p_backup);

  rc = sqlite3_errcode(p_dst);
  if (rc != SQLITE_OK) {
    syslog(LOG_ERR, "Can't finish backup, rc=%d, errmsg=%s", rc, sqlite3_errmsg(p_src));
    sqlite3_close(p_src);
    sqlite3_close(p_dst);
    return rc;
  }

  syslog(LOG_INFO, "Closing source database: %s", database_src);
  rc = sqlite3_close(p_src);
  if (rc != SQLITE_OK) {
    syslog(LOG_ERR, "Can't close source database, rc=%d, errmsg=%s", rc, sqlite3_errmsg(p_src));
    return rc;
  }

  syslog(LOG_INFO, "Closing destination database: %s", database_dst);
  rc = sqlite3_close(p_dst);
  if (rc != SQLITE_OK) {
    syslog(LOG_ERR, "Can't close destination database, rc=%d, errmsg=%s", rc, sqlite3_errmsg(p_dst));
    return rc;
  }

  syslog(LOG_INFO, "Backup success for database: %s", database_src);
  return 0;
}

With this additional logging, the system logs will contain detailed information about the backup process, making it easier to diagnose any issues that arise. The logs can be reviewed using the journalctl command, which provides access to the systemd service logs.

Finally, it is important to ensure that the SQLite3 library and the backup process are not affected by any systemd-specific behaviors or restrictions. Systemd services can impose various limits and constraints on processes, such as resource limits, environment variables, and security contexts. These constraints can sometimes interfere with the normal operation of a process, leading to unexpected behavior.

To rule out any systemd-specific issues, the service configuration can be modified to relax any potential constraints. For example, the LimitNOFILE directive can be used to increase the maximum number of open file descriptors, and the Environment directive can be used to set any necessary environment variables.

For example, the systemd service configuration could be updated as follows:

[Unit]
Description=Log backup service
[Service]
Type=simple
ExecStart=/usr/sbin/logbackupd
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
WorkingDirectory=/path/to/database
User=yourusername
Group=yourgroupname
LimitNOFILE=65536
Environment=LD_LIBRARY_PATH=/path/to/sqlite3/lib

In this configuration, the LimitNOFILE directive increases the maximum number of open file descriptors to 65536, and the Environment directive sets the LD_LIBRARY_PATH environment variable to the directory containing the SQLite3 library. These changes ensure that the backup process has sufficient resources and access to the necessary libraries to function correctly.

By following these troubleshooting steps and implementing the suggested solutions, the SQLite3 backup process should function correctly when run as a daemon via a systemd service. The key is to ensure that the source database paths are correctly resolved, the working directory is explicitly specified, and the necessary permissions and resources are available to the daemon process. With these changes in place, the backup process should reliably capture and transfer data from the source databases to the backup destination, regardless of whether it is run in the foreground or as a daemon.