`Error: statement too long` what is the statement size limit?

Error: statement too long

Error: statement too long

Error: statement too long

Error: statement too long

what is the statement size limit?

Aamorfati `Error: statement too long` what is the statement size limit?

Matt Silverlock•6/21/23, 1:54 AM

https://developers.cloudflare.com/d1/platform/limits/

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public alpha. Join the #d1-open-alpha channel in the Cloudflare Developer Discord to keep up to date …

Aamorfati `Error: statement too long` what is the statement size limit?

amorfatiOP•6/21/23, 1:54 AM

from docs "100,000 bytes (100KB)" is that accounted for experimental backend?

Aamorfati from docs "100,000 bytes (100KB)" is that accounted for experimental backend?

Matt Silverlock•6/21/23, 1:54 AM

That is the experimental backend, yes.

Pprogramjm Are there any examples of using D1 in an Astro project?

itsmatteomanf•6/21/23, 7:36 AM

I’m using it, what do you want to know?

XxPhi any ETA on when d1 will be in beta or production ready? just say 10y if it's fee...

itsmatteomanf•6/21/23, 7:37 AM

It’s pretty good already and it’s very stable, I’m sure the plan would be beta this year, but I have no specific knowledge. We need to wait for Matt.

betim•6/21/23, 9:49 AM

any way to create a DB with the regular backend alongside another with the experimental backend, locally? some of our queries don't work with the experimental backend (after importing data from our existing DBs that use the regular backend) and wanted to debug locally..

betim•6/21/23, 9:50 AM

this is using wrangler 3.1.0

Original message was deleted

betim•6/21/23, 9:57 AM

I was afraid that'd be the answer.. any guidance on figuring out why the experimental backend doesn't like our queries? any high level differences in SQLite support between regular and experimental backend that I should be aware of?

Original message was deleted

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

causecause should not be deprecated, it's still there

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

Original message was deleted

betim•6/21/23, 10:01 AM

yup, got that Error: too many terms in compound SELECTError: too many terms in compound SELECT (coming from a 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 ...

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

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

betim•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

betim•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

betim•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...

betim•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...

betim•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 ...

betim•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?

`Error: statement too long` what is the statement size limit?

Similar Threads

Similar Threads

Similar Threads