SQLite Table Not Found via PHP Despite Correct Absolute Path

Issue Overview: PHP Script Fails to Locate Existing SQLite Table in Specified Database

The core issue revolves around a PHP script failing to locate an existing SQLite table when connecting to a database file at an absolute path (/tmp/robo.db). The user confirms that the table exists and is accessible via the SQLite command-line interface (CLI), but the PHP script throws a "no such table" error. Key observations include:

• The PHP script uses SQLITE3_OPEN_READWRITE to avoid creating a new database file.
• Changing the filename results in an "unable to open database file" error, confirming the script is targeting the correct path.
• Subsequent investigation reveals the database file is stored in a systemd-private subdirectory under /tmp, not the user-specified /tmp/robo.db.

This discrepancy arises from system-level process isolation mechanisms (e.g., systemd’s PrivateTmp feature) that redirect file operations to sandboxed directories. The PHP script, running under a web server (e.g., Apache), inherits these isolation policies, causing it to interact with a different physical file than the CLI. This creates a paradox where the same logical path (/tmp/robo.db) points to different physical files depending on the execution context.

Possible Causes: Path Redirection, Permissions, and Environment Isolation

1. Systemd PrivateTmp Sandboxing

Modern Linux distributions use systemd to manage services, including web servers like Apache. A common configuration is PrivateTmp=true, which creates isolated /tmp directories for services. For example:

/tmp/systemd-private-<hash>-apache2.service-<random>/tmp/robo.db

This isolation ensures service-specific temporary files don’t interfere with global /tmp but causes the PHP script to read/write to a hidden subdirectory. The CLI, running outside this sandbox, accesses the "real" /tmp/robo.db, leading to the observed mismatch.

2. Filesystem Permissions and Ownership

The PHP script executes under the web server’s user (e.g., www-data), while the CLI uses the logged-in user (e.g., pi on Raspberry Pi). If the database file or its parent directory lacks read/write permissions for the web server user, the script might fail to access it. However, the user confirmed that the script successfully opens the file (changing the filename causes an error), ruling out basic permission issues. The problem lies in which file is being accessed, not whether access is permitted.

3. Table Name Mismatches or Schema Corruption

While the user initially reported discrepancies between users and users1, they clarified both tables exist. However, case sensitivity or hidden characters in table names could still cause issues. SQLite treats unquoted table names as case-insensitive, but this depends on the underlying filesystem (e.g., EXT4 is case-sensitive). If the PHP script references users1 and the CLI uses Users1, this would fail on case-sensitive filesystems. Schema corruption is less likely since the CLI confirms the table exists.

4. Race Conditions or Cached Connections

PHP scripts using persistent database connections might reuse handles pointing to outdated schemas. However, the user’s code explicitly opens and closes connections, minimizing this risk. The problem is reproducible across multiple runs, suggesting a systemic issue rather than transient state.

Troubleshooting Steps, Solutions & Fixes

Step 1: Confirm the Physical File Location

Use system introspection tools to identify the actual file accessed by the PHP script.

a. Trace File Access with strace
Run the PHP script via strace to monitor system calls:

strace -e trace=file php /path/to/script.php 2>&1 | grep 'robo.db'

This outputs the exact path used by the script. If the path matches /tmp/robo.db but the file isn’t modified, systemd’s PrivateTmp is likely redirecting access.

b. Inspect Process File Descriptors
Identify the web server’s process ID (PID) and list its open files:

ps aux | grep apache2  # or httpd, depending on the server
ls -l /proc/<PID>/fd | grep robo.db

This reveals whether the server has an open handle to the expected file.

c. Create a Test Table via PHP
Modify the script to create a test table and check its existence via CLI:

$db->exec("CREATE TABLE IF NOT EXISTS phptest (id INTEGER)");

If the CLI doesn’t see phptest, the PHP script is writing to a different file.

Step 2: Disable or Work Around Systemd PrivateTmp

To eliminate sandboxing as the cause, reconfigure the web server to disable PrivateTmp.

a. Edit the systemd Service File
For Apache on Ubuntu/Raspbian:

sudo systemctl edit --full apache2

Locate the PrivateTmp=true line and change it to PrivateTmp=false. Restart the service:

sudo systemctl daemon-reload
sudo systemctl restart apache2

Repeat the test. If the script now accesses /tmp/robo.db directly, the issue was caused by PrivateTmp.

b. Use a Non-/tmp Directory
Avoid systemd’s /tmp redirection entirely by storing the database in a custom directory:

$databaseFile = '/var/lib/myapp/robo.db';

Ensure the directory exists and grants write permissions to the web server user:

sudo mkdir -p /var/lib/myapp
sudo chown www-data:www-data /var/lib/myapp

Step 3: Validate Table Names and Schema

Ensure the PHP script references the exact table name and schema.

a. Query sqlite_master via PHP
Modify the script to dump the database schema:

$result = $db->query("SELECT name, sql FROM sqlite_master WHERE type='table'");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
  echo "Table: " . $row['name'] . "\nSQL: " . $row['sql'] . "\n";
}

If no tables appear, the script is accessing an empty or different database file.

b. Case Sensitivity Checks
Explicitly quote table names in queries to enforce case sensitivity:

$tableName = '"users1"';  # Double quotes for SQLite identifiers
$query = "SELECT * FROM $tableName";

Compare with the schema returned by sqlite_master.

Step 4: Audit File Permissions and SELinux/AppArmor

Even if basic permissions are correct, mandatory access control (MAC) systems like SELinux or AppArmor might block access.

a. Check Audit Logs
Inspect /var/log/audit/audit.log (SELinux) or /var/log/syslog (AppArmor) for denials related to robo.db or the PHP process.

b. Temporarily Disable MAC
Test with SELinux in permissive mode:

sudo setenforce 0

Or unload the AppArmor profile for the web server:

sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.apache2

If the issue resolves, create a custom policy allowing database access.

Step 5: Use Absolute Paths with Resolved Symlinks

Ensure the specified path isn’t a symlink redirected by the system. Resolve symlinks in PHP:

$databaseFile = '/tmp/robo.db';
$realPath = realpath($databaseFile);
if ($realPath === false) {
  die("Database file does not exist.");
}
$db = new SQLite3($realPath, SQLITE3_OPEN_READWRITE);

This confirms the script uses the canonical path.

Final Solution: Move the Database Outside /tmp

To avoid systemd’s PrivateTmp and ensure consistent access:

1. Create a dedicated directory:

   sudo mkdir -p /srv/myapp/db
   sudo chown www-data:www-data /srv/myapp/db
   

2. Update the PHP script:

   $databaseFile = '/srv/myapp/db/robo.db';
   

3. Migrate existing data:

   sudo cp /tmp/robo.db /srv/myapp/db/
   sudo chown www-data:www-data /srv/myapp/db/robo.db
   

This guarantees the PHP script and CLI tools access the same physical file, eliminating environment-specific redirections.