search

sqlpage / SQLPage

Fast SQL-only data application builder. Automatically build a UI on top of SQL queries.
https://sql-page.com
MIT License
1.66k stars 99 forks source link

The `sqlpage_files` table does not exist #718

Open BlueHtml opened 3 days ago

BlueHtml commented 3 days ago

Hello, I used the table component, and the SQL is as follows:

select 
    'table' as component;
select 
    1 as a,
    2 as b;
select 
    3 as a,
    4 as b;

When connecting to the local MSSQL, it works fine. However, when connecting to the MSSQL on the server, it reports an error as follows:

Sorry, but we were not able to process your request. 

Below are detailed debugging information which may contain sensitive data. Set environment to "prod" in the configuration file to hide this information. 

Failed to create a render context from the header context.

Caused by:
    0: The shell component should always exist
    1: Unable to get the component 'shell'
    2: Couldn't load "sqlpage/templates/shell.handlebars" into cache
    3: Unable to read "sqlpage/templates/shell.handlebars" from the database
    4: error returned from database: 对象名 'sqlpage_files' 无效。
    5: 对象名 'sqlpage_files' 无效。

Then I manually created the sqlpage_files table (on the server's MSSQL), and it worked fine. However, the local MSSQL did not require creating this table. What could be the reason for this?

Version information

Additional context

The console log is as follows:

[2024-11-27T05:50:09.970Z DEBUG sqlpage::webserver::http] Processing SQL request: "ms-table.sql"
[2024-11-27T05:50:09.971Z TRACE sqlpage::file_cache] Attempting to get from cache "ms-table.sql"
[2024-11-27T05:50:09.971Z TRACE sqlpage::file_cache] Loading and parsing "ms-table.sql"
[2024-11-27T05:50:09.972Z DEBUG sqlpage::filesystem] Reading file "ms-table.sql" from "D:\\code\\codeup\\sqlpage\\m2\\ms-table.sql"
[2024-11-27T05:50:09.972Z TRACE actix_http::h1::dispatcher] end timers:
[2024-11-27T05:50:09.973Z TRACE actix_http::h1::dispatcher]   head timer is inactive
[2024-11-27T05:50:09.973Z TRACE actix_http::h1::dispatcher]   keep-alive timer is inactive
[2024-11-27T05:50:09.974Z TRACE actix_http::h1::dispatcher]   shutdown timer is inactive
[2024-11-27T05:50:09.974Z TRACE actix_http::h1::dispatcher] end flags: Flags(STARTED)
[2024-11-27T05:50:09.979Z TRACE actix_http::h1::dispatcher] start flags: Flags(STARTED)
[2024-11-27T05:50:09.979Z TRACE actix_http::h1::dispatcher] start timers:
[2024-11-27T05:50:09.980Z TRACE actix_http::h1::dispatcher]   head timer is inactive
[2024-11-27T05:50:09.981Z TRACE actix_http::h1::dispatcher]   keep-alive timer is inactive
[2024-11-27T05:50:09.981Z TRACE actix_http::h1::dispatcher]   shutdown timer is inactive
[2024-11-27T05:50:09.982Z DEBUG sqlpage::webserver::database::sql] Parsing SQL file "ms-table.sql"
[2024-11-27T05:50:09.982Z TRACE sqlpage::webserver::database::sql] Parsing SQL: select
        'table' as component;
    select
        1 as a,
        2 as b;
    select
        3 as a,
        4 as b;
[2024-11-27T05:50:09.983Z DEBUG sqlparser::parser] parsing expr
[2024-11-27T05:50:09.984Z DEBUG sqlparser::parser] prefix: Value(SingleQuotedString("table"))
[2024-11-27T05:50:09.984Z DEBUG sqlparser::dialect] get_next_precedence_full() TokenWithLocation { token: Word(Word { value: "as", quote_style: None, keyword: AS }), location: Location { line: 2, column: 13 } }
[2024-11-27T05:50:09.985Z DEBUG sqlparser::parser] next precedence: 0
[2024-11-27T05:50:09.985Z DEBUG sqlpage::webserver::database::sql] Parsed statement: SELECT 'table' AS component
[2024-11-27T05:50:09.986Z DEBUG sqlpage::webserver::database::sql] Optimised a static simple select to avoid a trivial database query: SELECT 'table' AS component optimized to [("component", Static(String("table")))]
[2024-11-27T05:50:09.986Z DEBUG sqlparser::parser] parsing expr
[2024-11-27T05:50:09.987Z DEBUG sqlparser::parser] prefix: Value(Number("1", false))
[2024-11-27T05:50:09.988Z DEBUG sqlparser::dialect] get_next_precedence_full() TokenWithLocation { token: Word(Word { value: "as", quote_style: None, keyword: AS }), location: Location { line: 4, column: 7 } }
[2024-11-27T05:50:09.988Z DEBUG sqlparser::parser] next precedence: 0
[2024-11-27T05:50:09.989Z DEBUG sqlparser::parser] parsing expr
[2024-11-27T05:50:09.989Z DEBUG sqlparser::parser] prefix: Value(Number("2", false))
[2024-11-27T05:50:09.990Z DEBUG sqlparser::dialect] get_next_precedence_full() TokenWithLocation { token: Word(Word { value: "as", quote_style: None, keyword: AS }), location: Location { line: 5, column: 7 } }
[2024-11-27T05:50:09.994Z DEBUG sqlparser::parser] next precedence: 0
[2024-11-27T05:50:09.995Z DEBUG sqlpage::webserver::database::sql] Parsed statement: SELECT 1 AS a, 2 AS b
[2024-11-27T05:50:09.996Z DEBUG sqlpage::webserver::database::sql] Optimised a static simple select to avoid a trivial database query: SELECT 1 AS a, 2 AS b optimized to [("a", Static(Number(1))), ("b", Static(Number(2)))]
[2024-11-27T05:50:09.996Z DEBUG sqlparser::parser] parsing expr
[2024-11-27T05:50:09.997Z DEBUG sqlparser::parser] prefix: Value(Number("3", false))
[2024-11-27T05:50:09.997Z DEBUG sqlparser::dialect] get_next_precedence_full() TokenWithLocation { token: Word(Word { value: "as", quote_style: None, keyword: AS }), location: Location { line: 7, column: 7 } }
[2024-11-27T05:50:09.998Z DEBUG sqlparser::parser] next precedence: 0
[2024-11-27T05:50:09.998Z DEBUG sqlparser::parser] parsing expr
[2024-11-27T05:50:09.999Z DEBUG sqlparser::parser] prefix: Value(Number("4", false))
[2024-11-27T05:50:09.999Z DEBUG sqlparser::dialect] get_next_precedence_full() TokenWithLocation { token: Word(Word { value: "as", quote_style: None, keyword: AS }), location: Location { line: 8, column: 7 } }
[2024-11-27T05:50:10.000Z DEBUG sqlparser::parser] next precedence: 0
[2024-11-27T05:50:10.000Z DEBUG sqlpage::webserver::database::sql] Parsed statement: SELECT 3 AS a, 4 AS b
[2024-11-27T05:50:10.001Z DEBUG sqlpage::webserver::database::sql] Optimised a static simple select to avoid a trivial database query: SELECT 3 AS a, 4 AS b optimized to [("a", Static(Number(3))), ("b", Static(Number(4)))]
[2024-11-27T05:50:10.002Z TRACE sqlpage::file_cache] Writing to cache "ms-table.sql"
[2024-11-27T05:50:10.002Z TRACE sqlpage::file_cache] Done writing to cache "ms-table.sql"
[2024-11-27T05:50:10.003Z TRACE sqlpage::file_cache] "ms-table.sql" loaded in cache
[2024-11-27T05:50:10.003Z DEBUG sqlpage::webserver::http_request_info] Not parsing POST data from request without known content type
[2024-11-27T05:50:10.005Z TRACE actix_http::h1::dispatcher] end timers:
[2024-11-27T05:50:10.009Z TRACE actix_http::h1::dispatcher]   head timer is inactive
[2024-11-27T05:50:10.010Z TRACE actix_http::h1::dispatcher]   keep-alive timer is inactive
[2024-11-27T05:50:10.011Z TRACE actix_http::h1::dispatcher]   shutdown timer is inactive
[2024-11-27T05:50:10.011Z TRACE actix_http::h1::dispatcher] end flags: Flags(STARTED)
[2024-11-27T05:50:10.012Z DEBUG sqlpage::render] Handling header row: {"component":"table"}
[2024-11-27T05:50:10.012Z DEBUG sqlpage::render] Creating the shell component for the page
[2024-11-27T05:50:10.013Z TRACE sqlpage::render] No shell component found in the first row. Adding the default shell: {"component":"shell"}
[2024-11-27T05:50:10.014Z TRACE sqlpage::file_cache] Attempting to get from cache "sqlpage/templates/shell.handlebars"
[2024-11-27T05:50:10.014Z TRACE sqlpage::file_cache] Loading and parsing "sqlpage/templates/shell.handlebars"
[2024-11-27T05:50:10.015Z TRACE sqlpage::filesystem] Normalizing template path "sqlpage/templates/shell.handlebars" to "\\\\?\\D:\\code\\codeup\\sqlpage\\m2\\sqlpage\\templates\\shell.handlebars"
[2024-11-27T05:50:10.015Z DEBUG sqlpage::filesystem] Reading file "sqlpage/templates/shell.handlebars" from "\\\\?\\D:\\code\\codeup\\sqlpage\\m2\\sqlpage\\templates\\shell.handlebars"
[2024-11-27T05:50:10.016Z DEBUG sqlpage::filesystem] Reading file sqlpage/templates/shell.handlebars from the database
[2024-11-27T05:50:10.039Z TRACE sqlx::query] /* SQLx ping */; rows affected: 0, rows returned: 0, elapsed: 22.213ms
[2024-11-27T05:50:10.060Z TRACE sqlx::query] SELECT contents from sqlpage_files …; rows affected: 0, rows returned: 0, elapsed: 21.091ms

    SELECT
      contents
    from
      sqlpage_files
    WHERE
      path = @p1

[2024-11-27T05:50:10.061Z TRACE sqlpage::file_cache] Evicting "sqlpage/templates/shell.handlebars" from the cache because the following error occurred: Couldn't load "sqlpage/templates/shell.handlebars" into cache
[2024-11-27T05:50:10.062Z TRACE sqlpage::file_cache] Removing from cache "sqlpage/templates/shell.handlebars"
[2024-11-27T05:50:10.063Z TRACE sqlpage::file_cache] Done removing from cache "sqlpage/templates/shell.handlebars"
[2024-11-27T05:50:10.063Z ERROR sqlpage::webserver::http] An error occurred before starting to send the response body: Failed to create a render context from the header context.: The shell component should always exist: Unable to get the component 'shell': Couldn't load "sqlpage/templates/shell.handlebars" into cache: Unable to read "sqlpage/templates/shell.handlebars" from the database: error returned from database: 对象名 'sqlpage_files' 无效。: 对象名 'sqlpage_files' 无效 。
[2024-11-27T05:50:10.064Z TRACE actix_http::h1::dispatcher] start flags: Flags(STARTED)
[2024-11-27T05:50:10.065Z TRACE actix_http::h1::dispatcher] start timers:
[2024-11-27T05:50:10.065Z TRACE actix_http::h1::dispatcher]   head timer is inactive
[2024-11-27T05:50:10.066Z TRACE actix_http::h1::dispatcher]   keep-alive timer is inactive
[2024-11-27T05:50:10.066Z TRACE actix_http::h1::dispatcher]   shutdown timer is inactive
[2024-11-27T05:50:10.067Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET /ms-table.sql HTTP/1.1" 500 632 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.6261.95 Safari/537.36" 0.096701
[2024-11-27T05:50:10.067Z TRACE actix_http::h1::dispatcher] end timers:
lovasoa commented 3 days ago

Hello !

Are you using the same version of sqlpage locally and on the remote server ? Are you sure you did not have a sqlpage_files when you started sqlpage that you then deleted ?

The logic in sqlpage is the following:

  1. On startup, prepare (but don't run) the following sql statement SELECT 1 from sqlpage_files WHERE last_modified >= @p1 AND path = @p2

  2. If this fails, display the following debug log, and mark the on-database filesystem as unavailable:

    [2024-11-27T11:54:19.218Z DEBUG sqlpage::filesystem] Using local filesystem only, could not initialize on-database filesystem. You can host sql files directly in your database by creating the following table: 
CREATE TABLE sqlpage_files(path NVARCHAR(255) NOT NULL PRIMARY KEY, contents VARBINARY(MAX), last_modified DATETIME2(3) NOT NULL DEFAULT CURRENT_TIMESTAMP); 
The error while trying to use the database file system is: In "autogenerated sqlpage query": The following error occurred while executing an SQL statement:
error returned from database: Invalid object name 'sqlpage_files'.

The SQL statement sent by SQLPage was:
SELECT 1 from sqlpage_files WHERE last_modified >= @p1 AND path = @p2
  3. Later, when a request comes in, if the on-database filesystem is available, use it as a fallback when a sql file or custom component is not found locally. Otherwise, use only the local filesystem.

The error you see seems very strange. If you manage to reproduce it consistently with the latest sqlpage version, I would love to get reproduction instructions. If you have the full verbose logs (including of the time when sqlpage is starting up), that would be useful too!

BlueHtml commented 2 days ago

The sqlpage.exe program is the same; I only changed the database_url to connect to the local database and the server database.

When connecting to the server database, the sqlpage::filesystem log is always missing.

image

lovasoa commented 2 days ago

It looks like everything is working smoothly on both sides.

The remote MSSQL instance has a sqlpage_files table, so it sets up with the on-database filesystem enabled.

The local MSSQL instance doesn’t have a sqlpage_files table, so it continues without the on-database filesystem.

The logs don’t show any issues, and queries should be processing normally on both sides. Everything seems to be running just as it should. 😊

BlueHtml commented 2 days ago

@lovasoa The remote MSSQL does not have the sqlpage_files table. Whenever a request comes in, it reports an error: The sqlpage_files table does not exist.

I have sent you an email titled SQLPage#718-Logs related to the non-existence of the sqlpage_files table, which contains detailed logs. Please take a look.

lovasoa commented 2 days ago

Hi ! I received your email with the logs, and it indeed seems that there is a problem with sqlpage not detecting the absence of the sqlpage_files table on startup. I'll investigate, and may need you again for testing, since I cannot reproduce this issue locally.