search

phiresky / sql.js-httpvfs

Hosting read-only SQLite databases on static file hosters like Github Pages
Apache License 2.0
3.47k stars 105 forks source link

Firefox issue with virtual tables (gitlab pages sqlite.db file hosting) #54

Closed 4www closed 1 year ago

4www commented 1 year ago

Edit: seems I've had different issues, when creating the virtual tables (on my side), and consuming the sqlite db file from gitlab pages; putting an update in next messages.

Hello! Thanks for the really cool lib!

I'm trying to use it, with a database served from a gitlab page/artifact, but it seems the library complains from the database being malformed, while when downloading it and opening it with sqlite or a db-browser it seems to work.

Stacktrace:

Uncaught (in promise) Error: SQLite: database disk image is malformed
    handleError https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    step https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    exec https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    r https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    l https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    serialize https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    g https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    r https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    promise callback*r https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    l https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    630 https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    __webpack_require__ https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    <anonymous> https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    <anonymous> https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    <anonymous> https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1
    <anonymous> https://joblist.gitlab.io/components/assets/sqlite.worker-e48eea05.js:1

Console warning:

Warning: The server did not respond with Accept-Ranges=bytes. It either does not support byte serving or does not advertise it (`Accept-Ranges: bytes` header missing), or your database is hosted on CORS and the server doesn't mark the accept-ranges header as exposed. This may lead to incorrect results. (seen response headers: cache-control: max-age=600
content-length: 5263360
content-type: application/octet-stream
date: Wed, 16 Aug 2023 09:13:26 GMT
etag: "1febf8e0dcc95f159ed8d35648ce1493f78e109342e7186a17e3254c687ad317"
expires: Wed, 16 Aug 2023 09:23:26 UTC
last-modified: Wed, 16 Aug 2023 03:03:27 GMT
permissions-policy: interest-cohort=()
vary: Origin
x-firefox-spdy: h2)

I'm not sure at all it is a problem from your lib, but putting it here as maybe a cross-link reference for the related usage issues

I wonder if maybe that is because of how i've got the project setup with the build tools. As it seems to be working in chromium but not firefox (testing on linux).

4www commented 1 year ago

The browser console error in Firefox is:

constructing url database https://joblist.gitlab.io/workers/joblist.db sqlite.worker-e48eea05.js:1:11166
filename https:__joblist.gitlab.io_workers_joblist.db sqlite.worker-e48eea05.js:1:11519
constructing url database https://joblist.gitlab.io/workers/joblist.db filename https:__joblist.gitlab.io_workers_joblist.db sqlite.worker-e48eea05.js:1:11545
[xhr of size 4 KiB @ 0 KiB] sqlite.worker-e48eea05.js:1:7166
constructed vfs sqlite.worker-e48eea05.js:1:13752
[xhr of size 4 KiB @ 20 KiB] sqlite.worker-e48eea05.js:1:7166
XHRGET
https://joblist.gitlab.io/workers/joblist.db
[HTTP/2 200 OK 2440ms]

Search error. Error: SQLite: vtable constructor failed: companies_fts
    handleError https://components.joblist.today/assets/sqlite.worker-e48eea05.js:1
    exec https://components.joblist.today/assets/sqlite.worker-e48eea05.js:1
    r https://components.joblist.today/assets/sqlite.worker-e48eea05.js:1
index-758f0db7.js:56:1092

Tried from this URL and locally.

It does not seem to be working in Firefox but it works correctly in chromium (linux).

Still unsure if the error is on my side or in the lib (sorry if that's the case).

Cross issue ref

4www commented 7 months ago

I think the issue came from fts3 (working) vs fts5 (not working) tables See my changes here https://gitlab.com/joblist/workers/-/commit/e291a9e0ee6243ef69f04c5c6cb449f377058759

The sqlite3 database generated, should have fts3 tables to be consumes by phiresky/sql.js-httpvfs in the frontend and have the fts table work.