Cloudflare Developers•2y ago

My DB fully imploded from a single DELETE query (it was 15d of logs, maybe, not sure what you would

My DB fully imploded from a single DELETE query (it was 15d of logs, maybe, not sure what you would classify that) .

itsmatteomanfOP•2/17/24, 6:10 PM

Can't reach it anymore... id is 546787dd-35d5-4822-9b50-36fa0a4310c2546787dd-35d5-4822-9b50-36fa0a4310c2.

itsmatteomanfOP•2/17/24, 6:10 PM

Mind you, the column used on the WHEREWHERE part has an index.

itsmatteomanfOP•2/17/24, 6:11 PM

And it was at max size, but I already deleted a bit of data to test the query worked, so wasn't technically anymore.

Iitsmatteomanf My DB fully imploded from a single DELETE query (it was 15d of logs, maybe, not ...

kenton•2/17/24, 8:05 PM

doh, the scheduling algorithm made a bad call and colocated your database with some other big chonkers and they ran out of disk space. We kicked the machine which should redistribute all the databases and are working on a more lasting fix to this issue. In any case your DB should be coming back now.

Kkenton doh, the scheduling algorithm made a bad call and colocated your database with s...

itsmatteomanfOP•2/17/24, 8:05 PM

Oh, thanks Kenton!

itsmatteomanfOP•2/17/24, 8:06 PM

It’s interesting it was broken after it was reduced in size ahah

kenton•2/17/24, 8:07 PM

heh, well the problem is "reducing" the size briefly increases the size due to writing all the changes to the WAL file

Kkenton heh, well the problem is "reducing" the size briefly increases the size due to w...

itsmatteomanfOP•2/17/24, 8:08 PM

Oh, yeah, true…

Kkenton doh, the scheduling algorithm made a bad call and colocated your database with s...

itsmatteomanfOP•2/17/24, 8:34 PM

Can confirm it's back! And the DELETE actually was successful before the error :lul:

Original message was deleted

kenton•2/17/24, 9:27 PM

arguably a bug, but not something we can easily fix. The sqlite DB contains some hidden metadata tables that we don't want to give applications direct access to, which means we use an authorizer callback to block access. If you try to enumerate all tables and query them all you will likely run afoul of this.

itsmatteomanfOP•2/17/24, 9:32 PM

Is there any specific way, or plans for a way, to see storage used by each table and, not sure if possible, each index?

Iitsmatteomanf Is there any specific way, or plans for a way, to see storage used by each table...

Max (@rozenmd)•2/17/24, 9:52 PM

SELECT SUM("pgsize") FROM "dbstat" WHERE name='TABLENAME';

Max (@rozenmd)•2/17/24, 9:52 PM

apparently

Max (@rozenmd)•2/17/24, 9:54 PM

SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name;

for all tables

MMax (@rozenmd)SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name; for all tables

itsmatteomanfOP•2/17/24, 9:54 PM

Was literally typing that out :lul:

itsmatteomanfOP•2/17/24, 9:54 PM

Thanks!

itsmatteomanfOP•2/17/24, 9:54 PM

Let me try it

Max (@rozenmd)•2/17/24, 9:54 PM

includes indexes too!

itsmatteomanfOP•2/17/24, 9:54 PM

Uhm... no such table: dbstatno such table: dbstat

kian•2/17/24, 9:55 PM

proobably isn't enabled in D1

itsmatteomanfOP•2/17/24, 9:55 PM

I'm not using any storage! :party_yay:

kian•2/17/24, 9:55 PM

it's a compile-time flag (SQLITE_ENABLE_DBSTAT_VTABSQLITE_ENABLE_DBSTAT_VTAB)

Kkian proobably isn't enabled in D1

itsmatteomanfOP•2/17/24, 9:55 PM

Yeah, I figured...

kian•2/17/24, 9:56 PM

https://github.com/cloudflare/workerd/blob/4d32cdc6252fc4d93147638243f476d810393611/build/BUILD.sqlite3#L10-L18 :^(

Kkian <https://github.com/cloudflare/workerd/blob/4d32cdc6252fc4d93147638243f476d81039...

itsmatteomanfOP•2/17/24, 9:57 PM

That's sad

Max (@rozenmd)•2/17/24, 10:14 PM

copy paste it?

MMax (@rozenmd)copy paste it?

itsmatteomanfOP•2/17/24, 10:14 PM

I did

Max (@rozenmd)•2/17/24, 10:14 PM

oh boo i was testing on an alpha db

Max (@rozenmd)•2/17/24, 10:14 PM

that was a useful feature

MMax (@rozenmd)oh boo i was testing on an alpha db

itsmatteomanfOP•2/17/24, 10:14 PM

Oh :NotLikeThis:

kian•2/17/24, 10:14 PM

tut tut

MMax (@rozenmd)that was a useful feature

itsmatteomanfOP•2/17/24, 10:15 PM

It really was!

MMax (@rozenmd)that was a useful feature

Isaac McFadyen•2/17/24, 11:16 PM

You should bring up adding it for discussion in workerdworkerd

Isaac McFadyen•2/17/24, 11:17 PM

So that at least it's considered in terms of any security issues vs the extra usability

Dylan Jones•2/18/24, 1:40 AM

Trying to add a D1 database to a worker through the API:

https://developers.cloudflare.com/api/operations/worker-script-patch-settings

getting error:

"code": 10202,
"message": "workers.api.error.invalid_script_config"

The docs say for the binding type

Allowed value:
kv_namespace

Is D1 bindings not allowed to be added through the API?

Trying a POC where I dynamically create a D1 for each user, and bind it a worker so I can query the database.

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

kian•2/18/24, 2:26 AM

What’s your full request body?

Dylan Jones•2/18/24, 2:32 AM

Using insomia to test

settings: {"bindings":[ {"database_id": "ID", "name": "DB1","type": "d1"}]}

Dylan Jones•2/18/24, 2:32 AM

but I think I got it working... just randomly tried id instead of database_id

Dylan Jones•2/18/24, 2:42 AM

ah, that seems to work!! that should be in the docs some where. When you get the worker settings it has "database_id", but to add you have to just use "id"

Dylan Jones•2/18/24, 2:43 AM

the docs: https://developers.cloudflare.com/api/operations/worker-script-get-settings say it returns as namespace_id

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

kian•2/18/24, 2:44 AM

The API docs render it very poorly, you need to click on the array (oneOf) [objects]array (oneOf) [objects] next to the bindings until you find the D1 one.

kian•2/18/24, 2:44 AM

kian•2/18/24, 2:44 AM

I was under the impression that database_iddatabase_id vs

id

id

was fixed though, since the API does return database_iddatabase_id in a few places, so I'll ask the team on Monday.

Dylan Jones•2/18/24, 2:49 AM

oh wow, I had no idea you could cycle through them like that. That would have made it more clear, I completely guessed

Bblurrrb Do we have read replicas enabled for d1?

Isaac McFadyen•2/18/24, 3:56 AM

D1 does not currently have read replicas, no. Read replica support is planned but is not here yet and no timeline has been given.

Dylan Jones•2/18/24, 4:25 AM

Does that mean each D1 database is only in a single region?

Isaac McFadyen•2/18/24, 4:28 AM

That's correct, yes.

Isaac McFadyen•2/18/24, 4:29 AM

Your DB will either be created in the nearest region to you when you create it, or you can optionally provide a "region hint" to request a specific region (and the DB placer will try to place it as close as possible to your requested region).

Dylan Jones•2/18/24, 4:31 AM

okay thanks! thats good to know

My DB fully imploded from a single DELETE query (it was 15d of logs, maybe, not sure what you would

Similar Threads

Similar Threads

Similar Threads