I finally got some data imported to D1. I ran into a GLOB-related issue. I'm curious if this limit i

I finally got some data imported to D1. I ran into a GLOB-related issue. I'm curious if this limit is something I can expect to be lifted at some point? The SQL query looks like: SELECT key, created, expires FROM user WHERE key GLOB 'user--<some characters>*' ... and if the glob string exceeds 50 characters, I get an error: "LIKE or GLOB pattern too complex." Thanks!

Original message was deleted

Matt Silverlock•5/24/23, 12:36 PM

Not in this fix. We did a patch release to focus on the GC bug. The NULL casting bug should be addressed in our larger weekly.

Ssenojsitruc I finally got some data imported to D1. I ran into a GLOB-related issue. I'm cur...

Matt Silverlock•5/24/23, 12:40 PM

Sorry, missed documenting this. Limit is currently 50. What is the length you’re trying to GLOB with?

senojsitrucOP•5/24/23, 12:41 PM

@elithrar This particular query was maybe doing 75 characters. I definitely end up using more than that with other queries (couple hundred, perhaps).

Rre-thc Even sqlite itself has a limit of about 140TB. It'll never be unlimited!

Epailes•5/24/23, 12:41 PM

As Unsmart says 140TB is effectively unlimited! If anyone was storing that much they can afford to look into a proper sharding strategy.

My concern was more around the limitations of compute/memory for workers causing performance issues in a reasonably sized database (eg 100GB - 1TB)

senojsitrucOP•5/24/23, 12:42 PM

Oh, weird. I just realized my indexes didn't get created. I created the key index and I no longer get that error.

senojsitrucOP•5/24/23, 12:43 PM

Is that limit specific to table scans?

Ssenojsitruc Is that limit specific to table scans?

Matt Silverlock•5/24/23, 12:45 PM

It’s not, but run EXPLAIN QUERY PLAN <your query>EXPLAIN QUERY PLAN <your query>

I suspect the query planner is being smart. Would love to see the output.

senojsitrucOP•5/24/23, 12:46 PM

I did. The query plan changed from a table scan to using the index ("SEARCH user USING INDEX idx_user_key_asc (key>? AND key<?)"), and the 50 character limit went away.

MMatt Silverlock Sorry, missed documenting this. Limit is currently 50. What is the length you’re...

Matt Silverlock•5/24/23, 12:47 PM

Doc PR: https://github.com/cloudflare/cloudflare-docs/pull/9072

GitHub

d1: Update limits.md by elithrar · Pull Request #9072 · cloudflare/...

Table wrap breaks mobile entirely.

Ssenojsitruc I did. The query plan changed from a table scan to using the index ("SEARCH user...

Matt Silverlock•5/24/23, 12:50 PM

Intended - see “The LIKE Optimization” here: https://www.sqlite.org/optoverview.html (on mobile so can’t deep link)

senojsitrucOP•5/24/23, 12:53 PM

That's awesome. So far that LIKE/GLOB thing was the only limitation I had run into and adding the missing indexes fixed that. Now I just have to hope that CF Workers doesn't freak out at a bunch of concurrent connections from a single IP.

Thanks for your help here (and on Twitter).

Jason Hostetter•5/24/23, 4:22 PM

Suspected bug in the dashboard table viewer:
I have a virtual generated column like type TEXT AS ('patient')type TEXT AS ('patient')

in the dashboard, this column doesn't get a typetype column header, so the column headers all get shifted one to the left and the headers don't line up with the correct data column.

Jason Hostetter•5/24/23, 4:24 PM

Screen_Shot_2023-05-24_at_12.22.51_PM.png

Matt Silverlock•5/24/23, 4:35 PM

New documentation we've just published:

Local development w/ D1 (using the new
```
wrangler 3.0
```
```
wrangler 3.0
```
) - https://developers.cloudflare.com/d1/learning/local-development/
Debugging D1 (just getting started here, but surfacing how to tail logs, capture errors, etc) - https://developers.cloudflare.com/d1/learning/debug-d1/
D1 is now in the Bindings documentation - https://developers.cloudflare.com/workers/wrangler/configuration/#d1-databases

JJason Hostetter Click to see attachment

Matt Silverlock•5/24/23, 8:07 PM

Thanks: we'll take a look.

Ggreg_Hello, I have a D1 DB that I'm trying to use for a Pages project. The bindings a...

Matt Silverlock•5/24/23, 8:36 PM

Related: d1-database

MMatt Silverlock Related: https://discord.com/channels/595317990191398933/992060581832032316/1085...

Matt Silverlock•5/24/23, 8:37 PM

(They had the inverse problem but see their notes about the app config)

AAnna | DevMiner Having D1 put it's error cause into `Error.cause` is super unfortunate because i...

James•5/24/23, 10:10 PM

I completely agree that the DX here isn't great. Nothing else really follows this pattern and it's not super intuitive yet.

Error.cause

Error.cause

is an awesome thing slowly surfacing within JS, but it feels a little out of place right now.
Filing an issue at https://github.com/cloudflare/workers-sdk might be the best place to further discuss this?

JJames I completely agree that the DX here isn't great. Nothing else really follows thi...

Matt Silverlock•5/24/23, 10:12 PM

We're already planning to address this, but have a couple of other higher priority items to address first. Hold tight.

James•5/24/23, 10:12 PM

Awesome to hear, ty for the update

Original message was deleted

Max (@rozenmd)•5/25/23, 2:55 PM

the fix for this should be rolled out tomorrow evening, thanks for reporting it

Sameer Ali•5/25/23, 6:54 PM

any rough ETA on storage limit increase?

SSameer Ali any rough ETA on storage limit increase?

DaniFoldi•5/25/23, 7:00 PM

I don't think there was a timeline shared, but I wouldn't be surprised if it was coming very soontm

44x4notfound Would those performance improvements be part of that?

Matt Silverlock•5/25/23, 10:01 PM

If you use the new backend (per the blog post) you can already get the performance improvements: https://blog.cloudflare.com/d1-turning-it-up-to-11/ (caveat: no backups on the new backend yet).

Existing non-new-backend databases will not get a storage increase. The original, prototype backend just doesn’t scale for it. Wasn’t the intent.

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

44x4notfound Can we migrate?

Matt Silverlock•5/25/23, 10:48 PM

Yes: per the blog, you can export (https://developers.cloudflare.com/d1/learning/backups/#downloading-a-backup-locally) and then import (https://developers.cloudflare.com/d1/learning/importing-data/#import-an-existing-database) - we’ll be publishing a single guide once we get to beta and make the new backend the default.

We don’t have near term plans for an automatic migration (such is the trade-off for an alpha).

Backups · Cloudflare D1 docs

D1 has built-in support for creating and restoring backups of your databases, including support for scheduled automatic backups and manual backup …

Importing data · Cloudflare D1 docs

D1 allows you to import existing SQLite tables and their data directly, enabling you to migrate existing data into D1 quickly and easily. This can be …

Cyb3r-Jak3•5/25/23, 11:59 PM

Anyone have tips for debugging

not authorized [code: 7500]

not authorized [code: 7500]

when trying to execute an sql file? I can manually run the commands on the same database and table using

--command

--command

but

--file

--file

always fails.

Ggeelen can you post your command/file somewhere? You can DM me if you don't want to sha...

Cyb3r-Jak3•5/26/23, 1:23 AM

If you break it down into smaller files it works

charge.sql4.83KB

Cyb3r-Jak3•5/26/23, 1:36 AM

I'm using wrangler 2 but using the experimental backend

Cyb3r-Jak3•5/26/23, 1:38 AM

Oh that worked

Cyb3r-Jak3•5/26/23, 1:43 AM

I was following a migration guide

Zzhendalf Is it possible to use Prisma with D1?

Max (@rozenmd)•5/26/23, 7:20 AM

not yet: https://github.com/prisma/prisma/issues/13310

senojsitrucOP•5/26/23, 2:32 PM

Does D1 specifically not support "LIMIT" on "DELETE" queries?

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

Produces the error:

near "LIMIT": syntax error

near "LIMIT": syntax error

Dropping the "LIMIT" part of the query allows it to execute successfully.

kian•5/26/23, 2:34 PM

ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite which isn’t currently enabled on D1 - https://github.com/cloudflare/workerd/pull/608

GitHub

d1: enable UPDATE_DELETE and STAT4 at build by elithrar · Pull Requ...

Enables two SQLite options:

The STAT4 query planner, which is enabled in the alpha: https://sqlite.org/draft/compile.html#enable_stat4

the "downside" is that the query planner becomes ...

Kkian ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite whic...

Matt Silverlock•5/26/23, 2:36 PM

To add: it’s complicated. It’s not part of the standard amalgamation. It’s not likely we’ll support this soon.

senojsitrucOP•5/26/23, 2:37 PM

I used it for sanity reasons, but it definitely isn't strictly necessary for me. Thanks for the info.

Ggeelen ah yeah the `.dump` statement includes transactions... we should automatically r...

Max (@rozenmd)•5/26/23, 2:57 PM

the next wrangler release will trim the first

BEGIN TRANSACTION/COMMIT

BEGIN TRANSACTION/COMMIT

it sees in an sqlite dump

I finally got some data imported to D1. I ran into a GLOB-related issue. I'm curious if this limit i

Similar Threads

Similar Threads

Similar Threads