One of the files in question has around ~68k rows. And I could probably just run it as a single/few

H

And in case I do need to run each separately, would it be more efficient to run it as a few large statements, or each row as a single statement in a batch?

H

Hello, I’m Allie!OP•8/1/23, 12:57 AM

To be clear, I'm not expecting to be able to do it within a single read/write, I'm just wondering if there is anything I could do that is faster/more efficient than ~70k+ inserts every day...

H

Hello, I’m Allie!OP•8/1/23, 1:00 AM

If it matters, my dataset is a GTFS dump from BART. In this case, the number of rows is comparatively small, but it can scale to a fair bit large with larger transit systems, so I'm just going for best practices here

H

Hello, I’m Allie!OP•8/1/23, 1:03 AM

And it shouldn't

be outside the workable range of D1, as the CSV-form dataset is currently 6.5 MB

HHello, I’m Allie!One of the files in question has around ~68k rows. And I could probably just run...

M

Matt Silverlock•8/1/23, 1:06 AM

Or https://www.sqlite.org/lang_UPSERT.html

?

MMatt Silverlock Or https://www.sqlite.org/lang_UPSERT.html - ?

H

Hello, I’m Allie!OP•8/1/23, 3:08 AM

Would there also be a handy way to drop rows that aren’t in the CSV dataset?

C

chientrm.com•8/1/23, 3:21 AM

how can I check

rowsAffected

rowsAffected

rowsAffected

rowsAffected?

HHello, I’m Allie!Would there also be a handy way to drop rows that **aren’t** in the CSV dataset?

I

itsmatteomanf•8/1/23, 7:22 AM

At that point just create a new table...?

M

Matt Silverlock•8/1/23, 9:05 AM

So as of a few hours ago, every D1 database on the new backend now has a limit of 500MB.

That’s 500MB per database, and you each have 10 databases. This is just a stop on the way to even larger databases, which we’ll enable for those of you on the paid Workers plan in the near-ish future. Happy querying!

Changelog entry: https://developers.cloudflare.com/d1/changelog/#per-database-limit-now-500-mb

MMatt Silverlock So as of a few hours ago, **every D1 database on the new backend now has a limit...

E

Erisa•8/1/23, 9:37 AM

@D1

MMatt Silverlock So as of a few hours ago, **every D1 database on the new backend now has a limit...

I

itsmatteomanf•8/1/23, 1:16 PM

Ohhh, I was just considering moving a 227MB database, but couldn't. Can now

I

itsmatteomanf•8/1/23, 1:17 PM

Though, to be fair, it now works very well with Grafana, I'd need to reconfigure everything.

I

itsmatteomanf•8/1/23, 1:17 PM

But I could get fancy and use e-mail workers to add the logs to the DB from a single worker

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

M

Matt Silverlock•8/1/23, 3:32 PM

Are you using the new backend? Executed as-is for me.

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

M

Matt Silverlock•8/1/23, 3:33 PM

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

ZZebra Hello! I have a stupid question but how do I view my table entries in D1 ? I c...

D

divby0•8/1/23, 8:27 PM

Seeing the same for all tables on my project, worked fine before. I guess this is a temporary thing

Ddivby0 Seeing the same for all tables on my project, worked fine before. I guess this i...

M

Matt Silverlock•8/1/23, 8:32 PM

Specifically after 9? Or just in general.

MMatt Silverlock Specifically after 9? Or just in general.

D

divby0•8/1/23, 8:34 PM

Definitely independant of the number cause it didn't change

D

divby0•8/1/23, 8:35 PM

Some tables have 1 row, some have 4, some 11, none of them are showing up, but the console tab is still working fine so in case this is temporary it's totally okay for me at least

Ddivby0 Some tables have 1 row, some have 4, some 11, none of them are showing up, but t...

M

Matt Silverlock•8/1/23, 8:37 PM

I suspect a bug (we didn’t push any code today, but the dashboard team may have). Let me take a look shortly.

MMatt Silverlock I suspect a bug (we didn’t push any code today, but the dashboard team may have)...

D

divby0•8/1/23, 8:37 PM

no rush - I appreciate it

M

Matt Silverlock•8/1/23, 8:41 PM

I can't immediately repro: one of my DBs seems fine?

M

Matt Silverlock•8/1/23, 8:41 PM

M

Matt Silverlock•8/1/23, 8:41 PM

ZZebra And sorry for spamming with images here, just thought it might help with bug fix...

M

Matt Silverlock•8/1/23, 8:56 PM

No, that's helpful. But also weird — there's no paid tier at all (yet). Zero changes there.

ZZebra And sorry for spamming with images here, just thought it might help with bug fix...

M

Matt Silverlock•8/1/23, 8:56 PM

cc @geelen when he's in shortly

A

AFSANEH ABBAS•8/1/23, 9:18 PM

Why Web
https://api.sandbox-transgerwise-tech
Can not work with email

U

Unsmart•8/1/23, 9:21 PM

Not sure what you mean

I

itsmatteomanf•8/1/23, 9:30 PM

I saw 61 instances of this error in the span of a few seconds, seems fine now...

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.

I

itsmatteomanf•8/1/23, 9:30 PM

New backend, Database ID:

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2.

T

TigersWay•8/1/23, 9:55 PM

Got something similar, happened 3 times in the last 6 hours among ~=1000 SQL inserts:

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

I

itsmatteomanf•8/1/23, 9:57 PM

My volume is higher than that, and those were all subsequent writes.

ZZebra oh and response says I'm not authorized, hm, gonna try log out and back in

D

divby0•8/2/23, 6:11 AM

can confirm I have the exact same error code and message (and the same result of not being able to view rows inside dashboard)

Iitsmatteomanf I saw 61 instances of this error in the span of a few seconds, seems fine now......

I

itsmatteomanf•8/2/23, 8:34 AM

Got a few more hits in another Worker ~1h and ~2h later than that time.
So:

Aug 1, 2023 23:26:15 CEST — Aug 1, 2023 23:26:19 CEST
Aug 2, 2023 00:15:42 CEST
Aug 2, 2023 01:04:36 CEST

N

nick•8/2/23, 12:32 PM

Did the d1 database fail? My website was directly impacted badly!

N

nick•8/2/23, 12:34 PM

My worker is bound to my database, but all of them can't work now, it was fine yesterday

Nnick Did the d1 database fail? My website was directly impacted badly!

C

Cyb3r-Jak3•8/2/23, 1:00 PM

Are you seeing any errors in your logs?

Nnick Did the d1 database fail? My website was directly impacted badly!

M

Matt Silverlock•8/2/23, 1:03 PM

We’re not seeing any widespread failures. Can you share:

What errors you’re seeing?
Confirm you didn’t run out of storage?
your account & database IDs?

One of the files in question has around ~68k rows. And I could probably just run it as a single/few

Similar Threads