SQLPage process stops responding after executing a COPY command that errors - thread 'actix-rt|system:0|arbiter:2' panicked at ...

[grodger]

Introduction

When the COPY command references a non existent table whilst attempting to process a CSV file posted by the form component, SQLPage produces an error and stops responding to further requests and needs to be restarted.

To Reproduce

The following code invokes the error:

-- temporary table 'csv_tmp'

create temporary table if not exists csv_tmp(csv text);
delete from csv_tmp;

-- the copy command below will fail as the temporary table 'csv_tmp_1' does not exist (it should be 'csv_tmp')
-- this triggers a SQL/SQLPage error; it appears that when the COPY command triggers an error of any sort SQLPage process stops responding
 
copy csv_tmp_1 (csv) from 'recon_csv_file_input' DELIMITER '*' CSV;

Actual behavior

A standard SQLPage error is produced and displayed.

When clicking on or selecting another link within SQLPage the process is no longer reachable.

The act of requesting another page (after the COPY error) causes the process to panic and stop responding.

2025-01-24 05:03:28 thread 'actix-rt|system:0|arbiter:2' panicked at /usr/local/cargo/registry/src/index.crates.io-6f17d22bba15001f/sqlx-core-oldapi-0.6.38/src/postgres/message/data_row.rs:23:20: 2025-01-24 05:03:28 index out of bounds: the len is 1 but the index is 1

The SQLPage instance then needs to be restarted.

Screenshots

Attached.

Expected behavior

SQLPage should continue to respond as it does with any other error.

Version information

• OS: MacOS Sequoia 15.2
• Database: PostgreSQL 16.4
• SQLPage Version: v0.32.1
• Both SQLPage and PostgreSQL running as docker containers

Additional context

SQLPagePanic.log

.
.
.
2025-01-24 05:01:55 [2025-01-24T03:01:55.444Z TRACE sqlpage::webserver::response_writer] Flushing data to client: </div>    <hr class="" />
2025-01-24 05:01:55     
2025-01-24 05:01:55 [2025-01-24T03:01:55.444Z DEBUG sqlpage::webserver::database::execute_queries] Preparing statement: CREATE TEMPORARY TABLE IF NOT EXISTS csv_tmp (csv TEXT);
2025-01-24 05:01:55 [2025-01-24T03:01:55.444Z TRACE sqlpage::webserver::database::execute_queries] Executing query "CREATE TEMPORARY TABLE IF NOT EXISTS csv_tmp (csv TEXT);"
2025-01-24 05:01:55 [2025-01-24T03:01:55.478Z DEBUG sqlpage::webserver::database::execute_queries] Finished query with result: AnyQueryResult { rows_affected: 0, last_insert_id: None }
2025-01-24 05:01:55 [2025-01-24T03:01:55.478Z TRACE sqlpage::webserver::http] Received item from database: FinishedQuery
2025-01-24 05:01:55 [2025-01-24T03:01:55.478Z DEBUG sqlpage::render] -> Query 3 finished
2025-01-24 05:01:55 [2025-01-24T03:01:55.478Z DEBUG sqlpage::webserver::database::execute_queries] Preparing statement: DELETE FROM csv_tmp;
2025-01-24 05:01:55 [2025-01-24T03:01:55.478Z TRACE sqlpage::webserver::database::execute_queries] Executing query "DELETE FROM csv_tmp;"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z DEBUG sqlpage::webserver::database::execute_queries] Finished query with result: AnyQueryResult { rows_affected: 0, last_insert_id: None }
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::webserver::http] Received item from database: FinishedQuery
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z DEBUG sqlpage::render] -> Query 4 finished
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z DEBUG sqlpage::webserver::database::execute_queries] Executing CSV import: CsvImport { query: "COPY csv_tmp1 (csv) FROM STDIN DELIMITER '*' CSV ", table_name: "csv_tmp1", columns: ["csv"], delimiter: Some('*'), quote: None, header: None, null_str: None, escape: None, uploaded_file: "recon_csv_file_input" }
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z ERROR sqlpage::webserver::database::execute_queries] running CSV import from recon_csv_file_input to csv_tmp1
2025-01-24 05:01:55     
2025-01-24 05:01:55     Caused by:
2025-01-24 05:01:55         0: running COPY IN
2025-01-24 05:01:55         1: error returned from database: relation "csv_tmp1" does not exist
2025-01-24 05:01:55         2: relation "csv_tmp1" does not exist
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::webserver::http] Received item from database: Error(running CSV import from recon_csv_file_input to csv_tmp1
2025-01-24 05:01:55     
2025-01-24 05:01:55     Caused by:
2025-01-24 05:01:55         0: running COPY IN
2025-01-24 05:01:55         1: error returned from database: relation "csv_tmp1" does not exist
2025-01-24 05:01:55         2: relation "csv_tmp1" does not exist)
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z ERROR sqlpage::render] SQL error: running CSV import from recon_csv_file_input to csv_tmp1
2025-01-24 05:01:55     
2025-01-24 05:01:55     Caused by:
2025-01-24 05:01:55         0: running COPY IN
2025-01-24 05:01:55         1: error returned from database: relation "csv_tmp1" does not exist
2025-01-24 05:01:55         2: relation "csv_tmp1" does not exist
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Closing a template ('divider before each block')
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Rendering the after_list template with the following local variables: LocalVars { first: None, last: None, index: None, key: None, extra: {"component_index": Number(4), "csp_nonce": Number(5771830405246303347), "row_index": Number(0)} }
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Closing a template ('divider before each block')
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] Attempting to get from cache "sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] Loading and parsing "sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::filesystem] Normalizing template path "sqlpage/templates/error.handlebars" to "/etc/sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z DEBUG sqlpage::filesystem] Reading file "sqlpage/templates/error.handlebars" from "/etc/sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] File "sqlpage/templates/error.handlebars" not found, loading it from static files instead.
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] Writing to cache "sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] Done writing to cache "sqlpage/templates/error.handlebars"
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::file_cache] "sqlpage/templates/error.handlebars" loaded in cache
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Starting rendering of a template ('error before each block') with the following top-level parameters: {"query_number":5,"description":"running CSV import from recon_csv_file_input to csv_tmp1","backtrace":["running COPY IN","error returned from database: relation \"csv_tmp1\" does not exist","relation \"csv_tmp1\" does not exist"],"note":"You can hide error messages like this one from your users by setting the 'environment' configuration option to 'production'."}
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Closing a template ('error before each block')
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Rendering the after_list template with the following local variables: LocalVars { first: None, last: None, index: None, key: None, extra: {"component_index": Number(5), "csp_nonce": Number(5771830405246303347), "row_index": Number(0)} }
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::webserver::response_writer] Flushing data to client: <div class="alert alert-danger text-muted mt-2" role="alert">
2025-01-24 05:01:55         <p>
2025-01-24 05:01:55             <span class="status status-red">
2025-01-24 05:01:55                 <span class="status-dot status-dot-animated"></span> Error
2025-01-24 05:01:55             </span>
2025-01-24 05:01:55             We are sorry, but an error occurred while generating this page.
2025-01-24 05:01:55             You should contact the site's administrator.
2025-01-24 05:01:55         </p>
2025-01-24 05:01:55         <p>
2025-01-24 05:01:55                 Error in query number <strong>5</strong>:
2025-01-24 05:01:55         </p>
2025-01-24 05:01:55         <pre><code>running CSV import from recon_csv_file_input to csv_tmp1</code></pre>
2025-01-24 05:01:55             <details open class="fs-5">
2025-01-24 05:01:55                 <summary>Backtrace</summary><pre class="fs-5 mt-1 p-1 my-1"><code>running COPY IN</code></pre><pre class="fs-5 mt-1 p-1 my-1"><code>error returned from database: relation &quot;csv_tmp1&quot; does not exist</code></pre><pre class="fs-5 mt-1 p-1 my-1"><code>relation &quot;csv_tmp1&quot; does not exist</code></pre></details>
2025-01-24 05:01:55             <p class="fs-5 mt-1 p-1 my-1">You can hide error messages like this one from your users by setting the &#x27;environment&#x27; configuration option to &#x27;production&#x27;.</p>
2025-01-24 05:01:55     </div>
2025-01-24 05:01:55     
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Closing a template ('divider before each block')
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Closing a template ('shell before each block')
2025-01-24 05:01:55 [2025-01-24T03:01:55.479Z TRACE sqlpage::render] Rendering the after_list template with the following local variables: LocalVars { first: None, last: None, index: None, key: None, extra: {"component_index": Number(0), "csp_nonce": Number(5771830405246303347), "row_index": Number(1)} }
2025-01-24 05:01:55 [2025-01-24T03:01:55.480Z TRACE sqlpage::webserver::response_writer] Flushing data to client: </main>
2025-01-24 05:01:55     
2025-01-24 05:01:55                 <footer class="w-100 text-center fs-6 my-2 text-secondary" id="sqlpage_footer">
2025-01-24 05:01:55                         <p>Built with <a href="https://sql-page.com">SQLPage</a> -
2025-01-24 05:01:55     Terms and Conditions <a href="/footerlinks/tcs.sql">T&amp;Cs</a> -
2025-01-24 05:01:55     About Us <a href="/footerlinks/about.sql">About</a></p>
2025-01-24 05:01:55                 </footer>
2025-01-24 05:01:55             </div>
2025-01-24 05:01:55         </div>
2025-01-24 05:01:55     </body>
2025-01-24 05:01:55     </html>
2025-01-24 05:01:55 [2025-01-24T03:01:55.480Z DEBUG sqlpage::webserver::http] Successfully finished rendering the page
.
.
. Click on new SQLPage
.
.
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z TRACE sqlpage::webserver::database::sql_to_json] Decoded value: String("y9ubvDrbL2hU1idOOd9Qz7Hg5Q1UBE0D")
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z DEBUG sqlpage::webserver::database::execute_queries] Setting variable username to "y9ubvDrbL2hU1idOOd9Qz7Hg5Q1UBE0D"
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z DEBUG sqlpage::webserver::database::execute_queries] Preparing statement: SELECT 'redirect' AS component, 'signin.sql?error=true&from=shell' AS link WHERE CAST($1 AS TEXT) IS NULL AND CAST($2 AS TEXT) IS NULL;
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z TRACE sqlpage::webserver::database::execute_queries]      evaluating parameter 1: $username
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z DEBUG sqlpage::webserver::database::execute_queries]      parameter 1: y9ubvDrbL2hU1idOOd9Qz7Hg5Q1UBE0D
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z TRACE sqlpage::webserver::database::execute_queries]      evaluating parameter 2: $allow_no_auth
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z DEBUG sqlpage::webserver::database::execute_queries]      parameter 2: NULL
2025-01-24 05:03:28 [2025-01-24T03:03:28.179Z TRACE sqlpage::webserver::database::execute_queries] Executing query "SELECT 'redirect' AS component, 'signin.sql?error=true&from=shell' AS link WHERE CAST($1 AS TEXT) IS NULL AND CAST($2 AS TEXT) IS NULL;"
2025-01-24 05:03:28 thread 'actix-rt|system:0|arbiter:2' panicked at /usr/local/cargo/registry/src/index.crates.io-6f17d22bba15001f/sqlx-core-oldapi-0.6.38/src/postgres/message/data_row.rs:23:20:
2025-01-24 05:03:28 index out of bounds: the len is 1 but the index is 1
2025-01-24 05:03:28 note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

[lovasoa]

Thank you for the detailed report! I'll try to reproduce it.

[lovasoa]

In the meantime, you should be able to work around the problem by closing the connection after the error from on_reset.sql, to prevent a subsequent select to occur on the corrupted connection.

## on_reset.sql
You can also use this script to close database connections that are in an undesirable state, such as being in a transaction that was left open. To close a connection, write a select statement that returns a single row with a single boolean column named is_healthy, and set it to false.

https://github.com/sqlpage/SQLPage/blob/main/configuration.md

[grodger]

In the meantime, you should be able to work around the problem by closing the connection after the error from on_reset.sql, to prevent a subsequent select to occur on the corrupted connection.

## on_reset.sql
You can also use this script to close database connections that are in an undesirable state, such as being in a transaction that was left open. To close a connection, write a select statement that returns a single row with a single boolean column named is_healthy, and set it to false.

https://github.com/sqlpage/SQLPage/blob/main/configuration.md

Thank-you; I came across the fault due to an error I made in (mis) naming the table, in the normal course of events the error does not manifest.