yup, got that `Error: too many terms in compound SELECT` (coming from a `select {condition} union al

yup, got that

Error: too many terms in compound SELECT

Error: too many terms in compound SELECT

Error: too many terms in compound SELECT

Error: too many terms in compound SELECT (coming from a

select {condition} union all select {another condition}

select {condition} union all select {another condition}

select {condition} union all select {another condition}

select {condition} union all select {another condition} ).. I suppose I'm wondering what SQLite differences would account for this seeing that the query works in the regular backend but errors out in the experimental?

Bbetim yup, got that `Error: too many terms in compound SELECT` (coming from a `select ...

Max (@rozenmd)•6/21/23, 10:03 AM

The experimental backend doesn't run in a WASM container, so there are stricter security limits in place https://developers.cloudflare.com/d1/platform/limits/

MMax (@rozenmd)The experimental backend doesn't run in a WASM container, so there are stricter ...

betimOP•6/21/23, 10:04 AM

yup, all good on this front.. everything is within the limits on the query and data side

betimOP•6/21/23, 10:05 AM

is there a version difference in the underlying sqlite runtime between regular and experimental backend? wondering if the query parsing/execution happens differently in the (I'm suspecting) newer version of sqlite that the experimental backend uses..

kian•6/21/23, 10:07 AM

The experimental backend has SQLITE_LIMIT_COMPOUND_SELECTSQLITE_LIMIT_COMPOUND_SELECT set to 33

betimOP•6/21/23, 10:08 AM

does the backend support modifying that at either creation-time or runtime?

Max (@rozenmd)•6/21/23, 10:08 AM

that'll be why

betimOP•6/21/23, 10:08 AM

I think our query needs 5

Max (@rozenmd)•6/21/23, 10:09 AM

We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962c2898dcb4a4bd0c4e1b2a60949f74/src/workerd/util/sqlite.c%2B%2B#L645, I think a github issue in workerd would help

MMax (@rozenmd)We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962...

betimOP•6/21/23, 10:11 AM

k, thanks all.. this prevents us from moving to the experimental backend, which we've been really looking forward to for perf. will log a ticket to make this adjustable

Bbetim k, thanks all.. this prevents us from moving to the experimental backend, which ...

Matt Silverlock•6/21/23, 10:13 AM

Not adjustable: we’re likely to just increase it. A minimal sample query that fails now, but would work at (e.g.) 5 would be great.

Original message was deleted

Matt Silverlock•6/21/23, 10:14 AM

I don’t remove .cause.cause in that

Original message was deleted

Matt Silverlock•6/21/23, 10:15 AM

The PR doesn’t remove it, and it’s not merged, so…

MMax (@rozenmd)We can set it in workerd: https://github.com/cloudflare/workerd/blob/cfe19fdd962...

betimOP•6/21/23, 10:26 AM

FYI, ticket logged https://github.com/cloudflare/workerd/issues/795

GitHub

🐛 Experimental backend too restrictive on compount SELECT statement...

The experimental D1 backend configuration seems to have set limits around the compound SELECT statements that align with the SQLite high-security guidelines, but conflict with fairly simple use cas...

vassbence•6/21/23, 11:03 AM

Is there a limit on the number of inserts in a batch?

Bbetim FYI, ticket logged https://github.com/cloudflare/workerd/issues/795

Matt Silverlock•6/21/23, 11:32 AM

Commented.

Vvassbence Is there a limit on the number of inserts in a batch?

Matt Silverlock•6/21/23, 11:33 AM

Not specific to INSERT, outside of any normal limits. “Thousands” works fine.

vassbence•6/21/23, 11:34 AM

ok so definitely higher than queues max batch size, thanks

Pprogramjm How do you go about testing/running locally?

itsmatteomanf•6/21/23, 2:36 PM

It's kind of a pain... as it's a pain regardless testing with D1 on Pages, you need to pass it through the cli.

itsmatteomanf•6/21/23, 2:36 PM

I moved most of what I can to separate APIs, tbh.

vassbence•6/21/23, 3:02 PM

I'd like to get some help with the batch API.

I have a queue consumer which gets a list of messages to process and write them to db. If a write fails in .batch().batch() an error is thrown and everything is rolled back. This is not what I'd like to do, rather I'd like to ack/retry each item based on the success attribute

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

results.forEach(({ success }, index) => {
  if (success) {
    batch.messages[index].ack();
    return;
  }

  batch.messages[index].retry();
});

Is this possible or I'll need to use .run().run()s inside try catches?

vassbence•6/21/23, 3:04 PM

guess i could a condition in the sql to not do the insert if the foreign key fails but that would then output success: true and i'd get false positives

vassbence•6/21/23, 3:11 PM

or maybe checking for the foreign key before running the batch is good enough

Max (@rozenmd)•6/21/23, 3:20 PM

naively, I'd probably chuck the writes into a Promise.allSettled and retry the failed ones myself

vassbence•6/21/23, 3:24 PM

i think i figured out, with a conditional insert intoinsert into i can look at the results.changesresults.changes attribute to tell if a row was inserted or not then .ack().ack() or .retry().retry() based on that

vassbence•6/21/23, 3:24 PM

not like retrying will realy help if a foreign key was not present to begin with

vassbence•6/21/23, 3:30 PM

is meta.changesmeta.changes the same in production and in development? it's present in dev but the docs do not mention it, only meta.durationmeta.duration

Vvassbence is `meta.changes` the same in production and in development? it's present in dev...

Matt Silverlock•6/21/23, 4:00 PM

Yes, it should be the same. Same code.

Pprogramjm Do you mean like Astro endpoints? And are you able to test them locally with Pos...

itsmatteomanf•6/21/23, 4:15 PM

Or Pages functions, but yeah, if you use the adapter correctly.

itsmatteomanf•6/21/23, 4:16 PM

Do note that you need to use SSR for D1 to work, it won't work to generate statically, unless you have an API already somewhere.

Bbetim FYI, ticket logged https://github.com/cloudflare/workerd/issues/795

Matt Silverlock•6/21/23, 7:26 PM

https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go out in the next week or two. We'll update the changelog + limits docs once it does.

GitHub

sql: increase SQLITE_LIMIT_COMPOUND_SELECT to 5 by elithrar · Pull ...

Fixes #795
This PR increases the SQLITE_MAX_COMPOUND_SELECT limit (https://www.sqlite.org/limits.html#max_compound_select) from the dark arts recommended 3 to a slightly more permissive 5 based on ...

MMatt Silverlock https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go ...

betimOP•6/21/23, 7:49 PM

great! will keep an eye out for it. thanks for the quick turnaround!

MMatt Silverlock https://github.com/cloudflare/workerd/pull/796#event-9597303949 -> this will go ...

Dahoom•6/21/23, 7:50 PM

While we wait for time travel could we please get a wrangler option or something to just download the db as an sqlite file?

DDahoom While we wait for time travel could we please get a wrangler option or something...

Matt Silverlock•6/21/23, 8:01 PM

That's not how D1 works: it's not a "single file on disk". Time Travel will land very shortly, but we don't expect to support "export a blob" for a while.

Dahoom•6/21/23, 8:04 PM

apologies for my ignorance, good luck with the implementation

vassbence•6/21/23, 8:07 PM

wait then what does db.dump()db.dump() do if not dumping the db content into an sqlite file?

Vvassbence wait then what does `db.dump()` do if not dumping the db content into an sqlite ...

Matt Silverlock•6/21/23, 10:41 PM

That’s not available on the experimental backend, which will be the default in the near future.

Matt Silverlock•6/21/23, 10:43 PM

The “original” alpha was, as alphas tend to be, a prototype: SQLite in Wasm. A lot has changed since then - some of that I wrote about in our recent blog: d1-database

Original message was deleted

Matt Silverlock•6/21/23, 11:00 PM

Prod is workerd@a2fbc3dfe25workerd@a2fbc3dfe25 which maps to https://github.com/cloudflare/workerd/commits/main?before=a2fbc3dfe254ce6e94ceb183f7b1d6476b6c2b29+35&branch=main&qualified_name=refs%2Fheads%2Fmain -> "ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse""ALTER TABLE alterme RENAME COLUMN metadata TO somethingelse" passes.

TL;DR: Yes.

Original message was deleted

Matt Silverlock•6/21/23, 11:05 PM

Let me look into that. We may have a bug. @geelen

Original message was deleted

Matt Silverlock•6/21/23, 11:13 PM

There’s likely an edge case with how our storage layer & authorizer interact with temp tables during a DROP COLUMNDROP COLUMN op.

amorfati•6/22/23, 5:37 PM

how do I go about having my D1 id in wrangler.tomlwrangler.toml? The repo is public so I do I make the id public or is it supposed to stay secret? I assume the latter so currently I'm manually adding it to wrangler.tomlwrangler.toml every time before I deploy

amorfati•6/22/23, 5:40 PM

ah got it. Awesome, good to know

amorfati•6/22/23, 5:41 PM

Thank you!

Walshy•6/22/23, 5:43 PM

Everything in wrangler.toml is safe to have public

itsmatteomanf•6/23/23, 9:58 AM

Is there a way to get all table names from D1 via a statement in the JS API?

itsmatteomanf•6/23/23, 9:59 AM

I know of /tables/tables in the console, but does that work in the JS API?

Original message was deleted

itsmatteomanf•6/23/23, 10:10 AM

SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'd1_%' AND name != '_cf_KV'

yup, got that `Error: too many terms in compound SELECT` (coming from a `select {condition} union al

Similar Threads

Similar Threads

Similar Threads