The weird thing is that the error was returned, but the delete succeded.

KJolibois•2/29/24, 4:14 PM

I'm working on d1 to build something out with a friend, I added him to my account (e.g kj@emailprovider.com) and gave him (e.g kfriend@emailprovider.net) workers permission for all domains. At first he could not see the databases when logged in, whenever he clicked on d1 it kept sending him to the overview page. After I sent him a link to the page I was using (eg https://dash.cloudflare.com/{D1-ID-HERE}/workers/d1) he was able to see the database, but the account name on the top left changed from his email to mine. We were able to collaborate just fine. There was probably a step we missed where he needed to change over from his default account to mine ,but I thought I should ping just in case it was unexpected behavior. Thanks for all the great work on d1. I'm loving it so far .

Iitsmatteomanf The weird thing is that the error was returned, but the delete succeded.

Tony Leung•2/29/24, 4:26 PM

but not all of it right? I guess that you are batching a lot of DELETE commands, not delete by ID. According to the document, I think cf implements batch using a loop, trying to find a gap between other queries to run commands. If the batch does not success after a period of time, it will flag the batch as overloaded and returns the failed command index (as shown on the batch array). Since queries are run sequentially and commit on each success one then that's why your batch failed but some of them are executed.

Tony Leung•2/29/24, 4:27 PM

As a mysql user, on some large delete execution plan, i tend to select all the record ID which I want to delete first then delete by ID later

TTony Leung but not all of it right? I guess that you are batching a lot of DELETE commands,...

itsmatteomanfOP•2/29/24, 4:28 PM

So, afaik, batches are transactions behind the scenes, so either they all run or none do.
In any case, here, I was doing a

DELETE FROM table WHERE datetime < datetime('yyyy-mm-dd')

DELETE FROM table WHERE datetime < datetime('yyyy-mm-dd')

, single query.

Iitsmatteomanf So, afaik, batches are transactions behind the scenes, so either they all run or...

Tony Leung•2/29/24, 4:29 PM

do you index the

datetime

datetime

row?

TTony Leung do you index the `datetime` row?

itsmatteomanfOP•2/29/24, 4:29 PM

Yes, of course

Iitsmatteomanf Yes, of course 🙂

Tony Leung•2/29/24, 4:30 PM

oof so your table must have a million rows XD

TTony Leung oof so your table must have a million rows XD

itsmatteomanfOP•2/29/24, 4:30 PM

The table was actually 10M, before the deletes, which I did in 14d groups, to avoid too much overloading, but wasn't enough sometimes apaprently :lul:

TTony Leung oof so your table must have a million rows XD

Tony Leung•2/29/24, 4:32 PM

it reminds me of one time I tried to execute 10 or 20 query like that on a mysql table which has 300m rows. The production was down shortly after, the db was not responding . I was using aurora serverless v1 so there is no way to reboot the DB. It hangs for 8 or 9 hrs and costs me about 200 - 300$

Iitsmatteomanf The table was actually 10M, before the deletes, which I did in 14d groups, to av...

Tony Leung•2/29/24, 4:33 PM

oh...

Tony Leung•2/29/24, 4:33 PM

I don't think a cluster of oracle DB could handle that...

TTony Leung oh...

itsmatteomanfOP•2/29/24, 4:36 PM

To be fair, I was told to go ham on it and it does sustain 1000 write queries per minute, along with 100 read queries per minute. Optimized queries, most are single or a few rows each, but still.

itsmatteomanfOP•2/29/24, 4:37 PM

No sharding, multiple Workers at the same time, it's pretty good for what it is.

Tony Leung•2/29/24, 4:37 PM

I think you should select the ID first, then mark the record as "is_deleted" then actual delete them on low-traffic hour

Iitsmatteomanf To be fair, I was told to go ham on it and it does sustain 1000 write queries pe...

Tony Leung•2/29/24, 4:37 PM

that's an impressive number with a price next to 0$

TTony Leung that's an impressive number with a price next to 0$

itsmatteomanfOP•2/29/24, 4:38 PM

Definitely!

TTony Leung I think you should select the ID first, then mark the record as "is_deleted" the...

itsmatteomanfOP•2/29/24, 4:38 PM

It's mostly manual deletes now, I should just look at the time and do it not at the start of the task that spikes up in query volume :lul:

Tony Leung•2/29/24, 4:38 PM

well at least cf will protect me from ddos attack which would cost me 104k of hard earned money

Iitsmatteomanf It's mostly manual deletes now, I should just look at the time and do it not at ...

Tony Leung•2/29/24, 4:41 PM

I usually schedule deletion on 1 to 3am

Original message was deleted

Tony Leung•2/29/24, 4:57 PM

my customer's timezone, since my app are only used in some countries with adjacent timezones so at that time most of them are sleeping

Tony Leung•2/29/24, 4:58 PM

(or at least that's what it shown on the traffic chart XD)

TTony Leung well at least cf will protect me from ddos attack which would cost me 104k of ha...

Tony Leung•2/29/24, 5:20 PM

btw it actually happened with a netlify user https://redd.it/1b14bty

From the webdev community on Reddit

Explore this post and more from the webdev community

Unsmart•2/29/24, 7:00 PM

afaik its just standard sqlite, but you should be able to create foreign keys if thats what you mean from that

SLW•2/29/24, 7:07 PM

hey guys, How do I integrate prisma with D1?:NotLikeThis:

kian•2/29/24, 7:10 PM

that's basically the only thing taken from Turso

kian•2/29/24, 7:10 PM

kian•2/29/24, 7:10 PM

just the rows read/written stuff

kian•2/29/24, 7:11 PM

it's built from sqlite source still

Bbun ok but why does this not work ``` await this.db.batch([ this.db.insert(bl...

KJolibois•2/29/24, 8:54 PM

where is .insert from? I don't see it in the docs. what is blobs? is there a way to map classes to tables without using prepare and run or are you using an orm on top of d1?

KKJolibois where is .insert from? I don't see it in the docs. what is blobs? is there a wa...

bun•2/29/24, 9:00 PM

It’s fixed. Was my issue

SSLW hey guys, How do I integrate prisma with D1?:NotLikeThis:

Max (@rozenmd)•2/29/24, 9:26 PM

you can't use prisma with d1 yet

Hhannes So my understanding from some workerd commits is that D1 is built on top of http...

Max (@rozenmd)•2/29/24, 9:28 PM

rows_read/rows_written was the one feature we took from libsql, the rest is security-mode sqlite

Bbun It’s fixed. Was my issue

KJolibois•2/29/24, 9:30 PM

I'm still curious as to how you called .insert, can you link me to where they go over that? I don't see that method here https://developers.cloudflare.com/d1/build-databases/query-databases/

Cloudflare Docs

Query databases · Cloudflare D1 docs

This guide documents D1’s client API and how to query D1 from Cloudflare Workers, how D1 maps types from JavaScript/TypeScript and SQL, and common …

KKJolibois I'm still curious as to how you called .insert, can you link me to where they go...

bun•2/29/24, 9:32 PM

Its drizzle orm

KJolibois•2/29/24, 9:33 PM

thank you @nora and @bun

Ggeelen sadly no. Out of interest, what's your use-case?

Tony Leung•3/1/24, 4:37 AM

i guess that he wants to terminate the execution if it does not finish online. In mysql they have query hint like:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ --in milliseconds * FROM table

SELECT /*+ MAX_EXECUTION_TIME(1000) */ --in milliseconds * FROM table

Tony Leung•3/1/24, 4:47 AM

since workers only lives for 30 seconds per fetch invocation, i guess he want it failed rather than worker eviction to raise a reasonable error to the user, or to do something else

TTony Leung since workers only lives for 30 seconds per fetch invocation, i guess he want it...