Database, SSH, Filesystem, Process, and Infrastructure Automation Patterns: Diagnostics, Failure Modes, and Production Practices

1. Diagnostic sequence: preserve → identify layer → correct minimally

1. Preserve the original output.xml, log/report, safe process output, DB query evidence, and target/path configuration.
2. Confirm Robot, Python, DatabaseLibrary/SSHLibrary, DB driver, and tool versions.
3. Confirm the exact suite/test selection, output directory, database path/host/port, variables, and environment.
4. Validate imports/resources and keyword resolution before blaming infrastructure.
5. Inspect Robot variable scope and library connection aliases.
6. Inspect database transaction/rows, process handle/rc, filesystem tree, or SSH connection/trust state.
7. Only then inspect timing, parallel workers, CI workspace, and container network namespaces.
8. Apply the least destructive correction and rerun the smallest controlled slice into a new result directory.

2. Failure: configuration points at the wrong database or host

A hostname such as db.company.example or a path outside the run-owned output directory is not merely another test parameter. It changes the safety classification of the operation. Add a preflight allowlist before any mutating keyword.

${db}=    Normalize Path    ${LAB_ROOT}${/}inventory.sqlite3
${allowed}=    Normalize Path    ${OUTPUTDIR}${/}scratch-rf18${/}inventory.sqlite3
Should Be Equal    ${db}    ${allowed}    msg=Database target is outside the disposable lab

For network targets, compare the resolved host/port to a documented allowlist. Do not “try it and see” with a mutation.

3. Failure: test data becomes SQL syntax

*** Keywords ***
# BROKEN: do not concatenate untrusted data into SQL.
Find Inventory Row Unsafely
    [Arguments]    ${name}
    ${sql}=    Catenate    SEPARATOR=    SELECT id,name,state FROM inventory WHERE name='    ${name}    '
    ${rows}=    Query    ${sql}    alias=${DB_ALIAS}
    RETURN    ${rows}

# REPAIRED: the SQL shape is constant and data is bound separately.
Find Inventory Row Safely
    [Arguments]    ${name}
    ${params}=    Create List    ${name}
    ${rows}=    Query
    ...    SELECT id,name,state FROM inventory WHERE name = ?
    ...    parameters=${params}
    ...    return_dict=${True}
    ...    alias=${DB_ALIAS}
    RETURN    ${rows}

The broken keyword can turn a value into executable SQL. The repaired version binds parameters through the database driver. Preserve the input value in evidence, but redact it if it contains sensitive data in real systems.

4. Failure: destructive SQL is treated as a convenient cleanup tool

DROP DATABASE, TRUNCATE, or broad DELETE FROM table statements are inappropriate cleanup defaults in shared environments. In this course the entire SQLite database file is owned by the run and removed as a file after disconnect. In a shared database, cleanup should target exact synthetic IDs or use a transaction/sandbox specifically designed for tests.

5. Failure: shared transaction state creates order dependence

Symptom: Test B passes only when Test A runs first, or a row is visible in one query but disappears after reconnect. Check alias, connection lifetime, no_transaction, driver autocommit/isolation mode, and whether a query keyword implicitly committed. Rerun the smallest test with a fresh database file. Do not add a retry to a transaction-design bug.

6. Failure: local command text is concatenated and interpreted by a shell

*** Keywords ***
# BROKEN: shell text is assembled from data and delegated to a shell.
Delete File Unsafely
    [Arguments]    ${path_from_test_data}
    Run Process    cmd.exe    /c    del ${path_from_test_data}    shell=${True}

# REPAIRED: use the filesystem library and an exact owned-path guard.
Delete Owned Marker Safely
    ${actual}=    Normalize Path    ${LAB_ROOT}${/}${MARKER_NAME}
    ${allowed}=    Normalize Path    ${LAB_ROOT}${/}${MARKER_NAME}
    Should Be Equal    ${actual}    ${allowed}
    Remove File    ${actual}

The repaired path avoids a shell altogether and uses OperatingSystem for the actual filesystem capability. When a real executable must be called, use Process with program and arguments as separate cells. If a shell is genuinely required, inputs must be trusted/static and the reason documented.

7. Failure: recursive cleanup escapes the scratch root

A path such as scratch-rf18/.., a symlink, an unexpectedly empty variable, or a cwd-dependent relative path can turn recursive deletion into a broad operation. The course uses exact normalized path equality and a run-ownership flag. Production-grade cleanup can add canonical realpath/symlink policy in a small custom library if the standard library checks are insufficient.

8. Failure: a started child outlives the test

Run Process waits for the child, but Start Process creates persistent process state. Keep the returned handle or an explicit alias and terminate/wait for that exact child. Killing by executable name can affect unrelated processes on the developer workstation or CI runner.

${handle}=    Start Process    ${PYTHON}    -c    import time; time.sleep(30)    alias=owned-worker
# ... verify the intended condition ...
Terminate Process    owned-worker
${result}=    Wait For Process    owned-worker
Log    rc=${result.rc}

Use this only in a disposable lab. In real integration tests, prefer a fixture process with a clean shutdown protocol and bounded startup/readiness checks.

9. Failure: “it connects” is mistaken for SSH trust

Current stable SSHLibrary 3.8.0's Python client configures Paramiko AutoAddPolicy. That means an unknown host key can be accepted. This is precisely why the mandatory lab does not present a successful SSHLibrary login as security proof. Host-key verification failure must be solved by provisioning/verifying the correct host key and using a client/library policy that rejects unknown or changed keys—not by disabling checks.

Credentials are another boundary. Stable SSHLibrary predates Robot Framework's modern Secret type; do not assume passwords/private keys are automatically protected from all logs or downstream libraries. Use fake credentials only in teaching artifacts.

10. Failure: fixed ports collide under parallel or CI runs

A local SSH container, fixture daemon, or service may fail to bind because another run owns the port. Diagnose the listener and worker identity first. Prefer dynamic/worker-namespaced ports with explicit handoff to Robot. Sleeping longer does not free a port.

11. Failure: localhost points to the wrong network namespace

Inside a container, 127.0.0.1:22 reaches the container itself. A sibling SSH container must be addressed by its service/DNS name on the shared network; a host service requires a platform-specific host mapping. Record the actual network topology in CI evidence. Never substitute a public host to bypass local networking confusion.

12. Performance and artifact cost

Separate causes: Robot parsing/import/setup, keyword execution, DB/SSH/network latency, child process execution, logging/output serialization, Pabot scheduling, and container startup. Large DB query logs, verbose SSH transcripts, or huge process stdout can dominate artifact size even when the operation itself is fast. Reduce logging narrowly and preserve enough evidence to diagnose the first failure.

13. Intentionally broken path guard: interpret before repair

*** Test Cases ***
Broken Cleanup Configuration
    ${LAB_ROOT}=    Normalize Path    ${OUTPUTDIR}${/}scratch-rf18${/}..
    ${allowed}=    Normalize Path    ${OUTPUTDIR}${/}scratch-rf18
    Should Be Equal    ${LAB_ROOT}    ${allowed}    msg=Cleanup guard rejected escaped path
    # Remove Directory is intentionally NOT reached.

Expected evidence is a Robot assertion failure showing two different normalized paths. The correct repair is to restore the intended root configuration, not to weaken the assertion or wrap it in TRY/EXCEPT.

14. Troubleshooting shortcuts to reject

• No blanket retry around SQL/SSH/process failures.
• No giant sleeps or timeouts to hide readiness or ownership bugs.
• No broad EXCEPT that converts an unknown infrastructure failure to PASS.
• No arbitrary PYTHONPATH changes to make an import appear.
• No global mutable variables as cross-test lock/state.
• No TLS/SSH trust disablement.
• No production experiments.
• No result-file deletion after a failed run.

Knowledge check

15. Summary and checkpoint bridge

Most infrastructure failures become easier to diagnose when you first identify which external store owns the symptom. The checkpoint now asks you to predict those state transitions, inject controlled failures, preserve evidence, and prove cleanup cannot escape the lab.

References and version anchors

• Robot Framework 7.4.2 User Guide, Process 7.4.2, and OperatingSystem 7.4.2 — core execution, variables/lifecycle, structured child processes, filesystem operations, and current standard-library semantics.
• DatabaseLibrary 2.4.1 keyword documentation, PyPI release, and maintainer repository — connection, query, transaction wrapper, parameter, retry, alias, and SQLite guidance.
• SSHLibrary keyword documentation, SSHLibrary 3.8.0 on PyPI, and maintainer repository — connection/session behavior and current release status. The mandatory lab does not rely on a real SSH server.
• Python sqlite3 documentation — SQLite connection/transaction behavior used by the local database fixture.