Comments (18)
This is SO weird:
select rowid, from test_insert_replace
Why would it EVER generate that SQL query? Especially only intermittently?
from datasette.
One of those failures was SQLite: 3.25.3
- not sure if all failures are against that version.
from datasette.
It would be fantastic if we could figure out a way to reliably replicate this failure in dev, so we can pdb
it.
from datasette.
Uh oh... hit a different flaky write test, presumably for the same underlying reason?
Also SQLite 3.25.3
platform linux -- Python 3.10.14, pytest-8.2.2, pluggy-1.5.0
SQLite: 3.25.3
https://github.com/simonw/datasette/actions/runs/9503858661/job/26195236184
async def test_upsert(ds_write, initial, input, expected_rows, should_return):
token = write_token(ds_write)
# Insert initial data
initial["table"] = "upsert_test"
create_response = await ds_write.client.post(
"/data/-/create",
json=initial,
headers=_headers(token),
)
assert create_response.status_code == 201
if should_return:
input["return"] = True
response = await ds_write.client.post(
"/data/upsert_test/-/upsert",
json=input,
headers=_headers(token),
)
> assert response.status_code == 200
E assert 400 == 200
E + where 400 = <Response [400 Bad Request]>.status_code
tests/test_api_write.py:540: AssertionError
=========================== short test summary info ============================
FAILED tests/test_api_write.py::test_upsert[False-initial3-input3-expected_rows3] - assert 400 == 200
+ where 400 = <Response [400 Bad Request]>.status_code
======= 1 failed, 1425 passed, 9 skipped, 1 xfailed in 121.93s (0:02:01) =======
from datasette.
That change did indeed help further understand. the problem: https://github.com/simonw/datasette/actions/runs/9503993969/job/26195656683
FAILED tests/test_api_write.py::test_upsert[True-initial1-input1-expected_rows1] - AssertionError: {"ok": false, "errors": ["Row 0 is missing primary key column(s): \"rowid\""]}
assert 400 == 200
Row 0 is missing primary key column(s): "rowid"
So it looks like the test_upsert
failure is the exact same underlying problem as the test_create_table_ignore_replace
failure.
from datasette.
I'm very suspicious that there might be some intermittent SQLite bug present in 3.25.3 involving rowid
columns.
from datasette.
The SQLite changelog, tags stripped, is about 77,000 tokens:
curl 'https://sqlite.org/changes.html' | strip-tags | ttok
So I ran it through Claude Haiku:
curl 'https://sqlite.org/changes.html' | strip-tags | llm -m haiku \
--system 'identify any fixed bugs that could impact how rowid column selects work, and quote those lines along with the SQLite version number'
Based on the release history, I was able to identify the following fixed bugs that could potentially impact how rowid column selects work:
In version 3.24.0 (2018-06-04), there was a fix for a bug in CREATE TABLE AS where "columns of type TEXT never end up holding an INT value." This could potentially impact how rowid column selects work if the rowid column is of type TEXT.
In version 3.23.0 (2018-02-03), there was a fix for a bug involving "arbitrary expressions, including function calls and subqueries, in the filename argument to ATTACH." This could potentially impact how rowid column selects work if the rowid column is referenced in such an expression.
In version 3.8.2 (2013-12-06), there was a change to the defined behavior for the CAST expression when casting floating point values greater than +9223372036854775807 into integers. This could potentially impact how rowid column selects work if the rowid column is of type INTEGER and the values being cast are outside the valid range.
Those are all earlier than 3.25 so it's not them.
from datasette.
Follow-up:
llm -c 'Anything later than 3.25 ? The bug I am seeing is present in 3.25.3 - the error I got was Row 0 is missing primary key column(s): "rowid"'
But no, neither of these look likely. I may have to read the release notes myself!
Okay, got it. Let's look for any relevant bug fixes after version 3.25.3 then.
In the release history, I found the following potentially relevant bug fixes after 3.25.3:
- In version 3.35.0 (2021-03-12), there was an enhancement to the UPSERT feature, which could have impacted how rowid column selects work. The release notes mention:
"Generalize UPSERT: Allow multiple ON CONFLICT clauses that are evaluated in order, The final ON CONFLICT clause may omit the conflict target and yet still use DO UPDATE."
This change to the UPSERT behavior could have inadvertently introduced a bug related to rowid column selects.
- In version 3.34.0 (2020-12-01), there was a bug fix for an issue with the "ALTER TABLE RENAME COLUMN" command, which could have impacted how rowid column selects work if the rowid column was renamed.
The release notes state:
"Fix the ALTER TABLE RENAME COLUMN command so that it works for tables that have redundant UNIQUE constraints."
So these two versions, 3.35.0 and 3.34.0, would be good candidates to investigate further for potential bugs related to the issue you're seeing with the "Row 0 is missing primary key column(s): "rowid"" error.
from datasette.
Couldn't find anything searching https://sqlite.org/changes.html for rowid
manually either.
from datasette.
3.25.3 amalgamation: https://www.sqlite.org/2018/sqlite-amalgamation-3250300.zip
(I had to use Internet Archive to find it, couldn't quite guess the URL: https://web.archive.org/web/20181120115249/https://www.sqlite.org/download.html)
from datasette.
I figured out how to run that version of SQLite on macOS here:
from datasette.
Means I can run the tests like this:
DYLD_LIBRARY_PATH=/tmp/sqlite-amalgamation-3250300 pytest -n auto
from datasette.
Both failing tests are in test_api_write.py
- but I've not been able to get them to fail on macOS:
DYLD_LIBRARY_PATH=/tmp/sqlite-amalgamation-3250300 \
pytest tests/test_api_write.py -n auto
Weirdly without -n auto
I get a flurry of AttributeError: '_UnixSelectorEventLoop' object has no attribute '_ssock'
errors which I think are unrelated.
Maybe I need to try getting the tests to fail under Ubuntu in a Docker container instead.
from datasette.
I'm going to leave this for the moment.
from datasette.
I figured out how to run the test suite in a Ubuntu Docker container with that old version of SQLite here - but the tests pass for me:
from datasette.
Using that trickery I still can't replicate the failing tests using Ubuntu and Python 3.12.3 and SQLite: 3.25.3 - which is the same combo as the failed run in https://github.com/simonw/datasette/actions/runs/9503993969/job/26195656683
platform linux -- Python 3.12.3, pytest-8.2.2, pluggy-1.5.0
SQLite: 3.25.3
rootdir: /datasette
configfile: pytest.ini
plugins: timeout-2.3.1, asyncio-0.23.7, anyio-4.4.0, xdist-3.6.1
asyncio: mode=Mode.STRICT
collected 114 items
tests/test_api_write.py ............................................................................................... [ 83%]
.........XX........ [100%]
=============================================== 112 passed, 2 xpassed in 2.17s ================================================
See #2349 (comment) for how I ran that.
from datasette.
Might be easier to replicate this using GitHub codespaces. Those might be closer to the GitHub Actions environment
from datasette.
Another option would be a way to have that specific test dumped out a SQLite database file if it fails which then gets uploaded to the run as a as artifact.
from datasette.
Related Issues (20)
- `Stable` documentation refers to `latest` demo instance
- get_metadata() plugin hook should support async await_me_maybe pattern HOT 2
- Datasette 1.0 rc tracking issue
- Usablity issue with need for root user
- Consider releasing a 0.65 with some forwards compatibility for 1.0 HOT 2
- Bug (in docs?): the "_internal" table on latest.datasette.io doesn't load HOT 1
- Consider adding a new plugin hook: "pre_query" or similar HOT 3
- Proposal - store metadata inside `internal.db` tables HOT 2
- Broken link in documention: fivethirtyeight.datasettes.com
- Fix font size on filter inputs
- base_url getting appended twice in redirects when applying filters?
- Accessibility: add a `lang` attribute to `html` HOT 1
- What minimal SQLite version should Datasette support? HOT 9
- Remove upserts in `set_XXX_metadata()` methods
- PyOdide test failure HOT 7
- Canned queries with named parameters fail with error against SQLite 3.46.0 HOT 11
- derive_named_parameters() method that works with latest SQLite HOT 5
- Flaky test_max_csv_mb test HOT 3
- multiple plugins extending the same base template?
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from datasette.