example of a long running maintenance operation is moving all data from one table to another after m

example of a long running maintenance operation is moving all data from one table to another after modifying / adding some foreign key constraints. this consistently fails in the web console for 2 million rows, i had to reduce it to 100k rows max

Ddatwelk example of a long running maintenance operation is moving all data from one tabl...

lambrospetrou•12/16/24, 2:13 PM

Yeah schema migrations for whole tables would block your database, no way around that at the moment. Splitting your migrations in manageable sizes is the usual recommendation if and when it's possible.

rob•12/16/24, 3:04 PM

We have been running into some basic issues with migrations on D1. Has anyone else experienced issues with D1 failing on queries for tables larger than 30MB or found workarounds for modifying columns given the lack of ALTER TABLE support?

Rrob We have been running into some basic issues with migrations on D1. Has anyone el...

datwelkOP•12/16/24, 6:10 PM

i managed to get it working by splitting my migration files into chunks of 300k lines each. if any of those fail with D1_RESET_DO that means a constraint is failing. try isolating the failing query by executing it on the web console

Llambrospetrou > how are we supposed to do long running maintenance operations in d1? What do ...

datwelkOP•12/16/24, 7:45 PM

something simple as pruning all records older dan x date also consistently fails (eg: Error: D1_ERROR: internal error). it is supposed to delete around 200k rows in a 3 million row db. query: DELETE FROM Foo WHERE bar < x. table has index on bar. any advice how to improve this?

Ddatwelk something simple as pruning all records older dan x date also consistently fails...

James•12/16/24, 7:49 PM

get the ids first and batch out the deletes

JJames get the ids first and batch out the deletes

datwelkOP•12/16/24, 8:06 PM

d1 has limit of 100 variables, so when binding the ids for the delete i should have batch size 100? if i run this in a worker it will fail after max 1000 api requests no?

James•12/16/24, 8:09 PM

youd have to chain them via queue or worker request likely

James•12/16/24, 8:09 PM

thats just one way, you could limit your delete another way, just add additional bounds to the delete where clause and step through it

JJames youd have to chain them via queue or worker request likely

datwelkOP•12/16/24, 8:11 PM

cheers, will step through it with some pagiantion client / server interplay

James•12/16/24, 8:11 PM

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

DELETE FROM table_name
WHERE id IN (
    SELECT id
    FROM table_name
    WHERE condition
    LIMIT 10
);

James•12/16/24, 8:12 PM

id bet you could bypass the 100 id limit with a subquery, could just be an api limitation, not sqlite

Ddatwelk d1 has limit of 100 variables, so when binding the ids for the delete i should h...

lambrospetrou•12/16/24, 8:16 PM

The limit is for how many variables you can

bind()

bind()

, not how many rows you can operate on.

Ddev it is, yes. data needs to live in Canada for compliance reasons. I know CF doesn...

dave•12/16/24, 8:20 PM

You may like using Workers + Hyperdrive

Llambrospetrou The limit is for how many variables you can `bind()`, not how many rows you can ...

datwelkOP•12/16/24, 11:26 PM

but copying 2 million rows from one table to another i havent gotten to work yet, this presumably then hits some query timeout limits?

RexNormous•12/17/24, 2:01 AM

Are there any tips to decrease the rate of

D1_ERROR: Network connection lost

D1_ERROR: Network connection lost

errors?

I am see a concerning increase in these that is causing issues in my application.

RRexNormous Are there any tips to decrease the rate of `D1_ERROR: Network connection lost` e...

Dean•12/17/24, 2:05 AM

not that i'm aware of. i get about 1-2 per day that i monitor on my worker. Only real solution is re-attempt the query

andifined.dev•12/17/24, 10:20 AM

I‘m looking for an architecture example of how to scale D1 per user as listed within the First Couple of lines in the documentation. Can someone help me?

Aandifined.dev I‘m looking for an architecture example of how to scale D1 per user as listed wi...

lambrospetrou•12/17/24, 10:36 AM

There isn't much advertised architecture really. The idea is that you create more than 1 database depending on your needs.
If your data can be sharded in a manageable way, you can create N databases, and shard your data onto those databases.
The extreme case is when you have a new database for each individual user/account, but right now you need a bit of extra work to create and attach those databases dynamically through the REST API.

My suggestion is the first one, a couple of databases (5-20) and sharding your data across them depending on your load/access needs. But as always, it depends what you want to do, and what you data looks like, and how your access patterns look like.

Zn4rK•12/17/24, 10:36 AM

Has sessions for D1 shipped? Would the session api allow for transactions since it's sequential consistent for that session?

ZZn4rK Has sessions for D1 shipped? Would the session api allow for transactions since ...

lambrospetrou•12/17/24, 10:38 AM

By sessions, do you refer to the read replication feature? Which blog post do you have in mind?

Llambrospetrou By sessions, do you refer to the read replication feature? Which blog post do yo...

Zn4rK•12/17/24, 10:42 AM

Yes, the read replication feature.

This blog post:
https://blog.cloudflare.com/building-d1-a-global-database/

But it actually doesn't mention a timeline there, I don't know where I got end of the year from. I've been reading up on DO SQLite and D1 quite a lot today, so it might just be wishful thinking from my side

ZZn4rK Yes, the read replication feature. This blog post: https://blog.cloudflare.com/...

lambrospetrou•12/17/24, 10:44 AM

Yes, this sessions API and read replication is coming soon-ish. And transactions/writes will continue working fine too.

Zn4rK•12/17/24, 10:54 AM

Transactions in D1 today requires the use of .batch(), right?

I was hoping that the transaction api from DO SQLite would be available within sessions as well (https://github.com/outerbase/starbasedb/issues/12).

This is not something that has been mentioned anywhere (other than this blog post from 2022: https://blog.cloudflare.com/whats-new-with-d1/).

ZZn4rK Transactions in D1 today requires the use of .batch(), right? I was hoping that...

lambrospetrou•12/17/24, 10:57 AM

The DO transactions API works really well because you access a local SQLite database, so you get very good performance even with tens/hundreds of queries. Unless you mess it up with async IO yourself.

D1 is a remote database, away from your Worker. This transaction API doesn't work well with that since you would be making multiple remote calls each time. Not saying there isn't an API that can work OK-ish, but that one isn't it.

Zn4rK•12/17/24, 10:59 AM

Thank you! Then I think I'm beginning to understand the limitations and differences between DO SQLite and D1 better

Zn4rK•12/17/24, 10:59 AM

Very exciting stuff!

ZZn4rK Thank you! Then I think I'm beginning to understand the limitations and differen...

lambrospetrou•12/17/24, 11:00 AM

Local vs remote is the key difference. Everything else derives from that.

You choose D1 for the usability layer (UI, insights for slow queries and metrics, wrangler commands, import/export), and you choose DO SQLite if you really want the local access + dynamic bindings benefits and are OK building the usability layer yourself (or using a library that provides it for you).

Llambrospetrou Local vs remote is the key difference. Everything else derives from that. You c...

Zn4rK•12/17/24, 11:12 AM

Nice! Thank you!

Here's where I found the timeline btw: https://blog.cloudflare.com/sqlite-in-durable-objects/#what-about-d1

By the end of 2024, D1 will support automatic read replication for scalability and low-latency access around the world. If this managed model appeals to you, use D1.

Aandifined.dev I‘m looking for an architecture example of how to scale D1 per user as listed wi...

James•12/17/24, 4:26 PM

I use DO per user, but in my case it works nicely since user accounts are separate tenants. Each user registration runs the schema startup script / migrations to create the db on the fly. I ensure the DO ID is deterministic based on some user specific key. Every subsequent login / request, the key is unpacked / looked up and then I can re-access the DO dynamically.

Palash Bansal•12/17/24, 6:32 PM

Hello, I am trying to run a query in D1 with multiple

UNION ALL SELECT

UNION ALL SELECT

terms. According to SQLite documentation, the number of terms in the query is limited by the value

SQLITE_MAX_COMPOUND_SELECT

SQLITE_MAX_COMPOUND_SELECT

which defaults to 500. But in D1 this is set to 5 and any query with more than 5 terms (like for inserting multiple values at once) fail with

too many terms in compound SELECT

too many terms in compound SELECT

.
Is there any reason for setting this to so low(like any other limitations) and is there way to increase this now or in the future with D1 or SQLite in DO?

Original message was deleted

Palash Bansal•12/17/24, 6:46 PM

I see thanks. Also saw its a recursive algorithm so makes sense now

ajgeiss0702•12/18/24, 3:15 AM

ive been seeing a bunch of

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

errors being thrown since friday. Any idea if this is on d1 or my side?

ajgeiss0702•12/18/24, 3:18 AM

a similar issue appears to be happening with durable objects itself. Isn't D1 based on DO? so might actually be a DO issue?

Llambrospetrou Local files are not deployed in production. > But on production the 5 records a...

karenr•12/18/24, 9:00 AM

Sorry for the confusion.

The production has 21 records, see the attached. And the local in my machine has 5 records. If I am running the local using the wrangler dev + cloudflare since I am using opennext it behaves as expected, in the UI it only shows 5 records. but when i deploy with the wrangler deploy + cloudflare, say I fetched 21 records from an API. It still displays the 5 records from the local.

I used the --remote flag when migrating but still displaying the 5 records from the local.

I am using swr to reflect the real time changes from the database and storage. Not sure if this affects it.

Kkarenr Sorry for the confusion. The production has 21 records, see the attached. And t...

lambrospetrou•12/18/24, 9:06 AM

If the database in the cloudflare dashboard shows you the right rows, then it means your D1 database is correct.
So, the issue is in the code build process.

I don't use opennext or swr so cannot comment for those, but make sure when you build your application before deploying that it does not use the local database to build up the content of your site in some way and you fetch the data at runtime using the correct database in production. If you try to fetch at build time, then it's very likely using your local database. Again, just a guess though, so check your build process.

Llambrospetrou If the database in the cloudflare dashboard shows you the right rows, then it me...

karenr•12/18/24, 9:09 AM

I think it is. Cos when I delete the local generated sql from .wrangler. it displays the 21 records in the UI.

Kkarenr I think it is. Cos when I delete the local generated sql from .wrangler. it disp...

karenr•12/18/24, 10:27 AM

this is what solve the issue

import { defineConfig } from "drizzle-kit";
import type { Config } from "drizzle-kit";
import fs from "node:fs";
import path from "node:path";

function getLocalD1DB() {
try {
const basePath = path.resolve(".wrangler");
const dbFile = fs
.readdirSync(basePath, { encoding: "utf-8", recursive: true })
.find((f) => f.endsWith(".sqlite"));

if (!dbFile) {
throw new Error(

.sqlite file not found in ${basePath}

.sqlite file not found in ${basePath}

);
}

const url = path.resolve(basePath, dbFile);
return url;
} catch (err) {
console.log(

Error ${err}

Error ${err}

);
}
}

export default defineConfig({
dialect: "sqlite",
schema: "./schemas/drizzle.ts",
out: "./drizzle",
...(process.env.CLOUDFLARE_D1_ACCOUNT_ID
? {
driver: "d1-http",
dbCredentials: {
accountId: process.env.CLOUDFLARE_D1_ACCOUNT_ID,
databaseId: process.env.DATABASE,
token: process.env.CLOUDFLARE_D1_API_TOKEN,
},
}
: {
dbCredentials: {
url: getLocalD1DB(),
},
}),
}) satisfies Config;

Aajgeiss0702 ive been seeing a bunch of `D1_ERROR: Network connection lost.` errors being thr...

ajgeiss0702•12/18/24, 11:31 PM

the error has changed to

D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

Aajgeiss0702 the error has changed to `D1_ERROR: D1 DB storage operation exceeded timeout whi...

Chaika•12/18/24, 11:42 PM

Anything interesting with metrics? Does it happen even on simple queries, consistently, etc?
The Network Connection Lost error just does happen every so often..unfortunately, but it depends on frequency and such.

ajgeiss0702•12/18/24, 11:44 PM

its not consistent. there doesn't appear to be anything out of the ordinary on the db's metrics

ajgeiss0702•12/18/24, 11:44 PM

well, that error specifically seems to only happen on one query, but when it happens on that one query seems random

ajgeiss0702•12/18/24, 11:45 PM

select * from table where timestamp > ? order by timestamp desc

select * from table where timestamp > ? order by timestamp desc

Chaika•12/18/24, 11:46 PM

index on timestamp? (or is table small enough to not need it?)
npx wrangler d1 insights <db> can be help as well to look through expensive queries

ajgeiss0702•12/18/24, 11:47 PM

that table should get cleared at the end of every week, so I would be suprised if it had more than a few thousand rows at the absolute most

ajgeiss0702•12/18/24, 11:48 PM

yeah that table currently has 0 rows

theo•12/19/24, 5:23 AM

I'm working on some tooling which is handling very sensitive data on behalf of my customers, along with user-defined workers (Workers for Platforms). But I'd like to ensure that I never have access to that data. Obviously I can encrypt the data with a key that I dont have, but I need workers to be able to access the data, and I dont want the user to have to manage that key either. Any experience / advice for doing something like this?

Aajgeiss0702 the error has changed to `D1_ERROR: D1 DB storage operation exceeded timeout whi...

Max (@rozenmd)•12/19/24, 9:49 AM

whats your database_id?

Ttheo I'm working on some tooling which is handling very sensitive data on behalf of m...

lambrospetrou•12/19/24, 10:02 AM

If you want encryption then someone has to have the key, otherwise you won't be able to decrypt the data. You could in theory add a secret in your worker and "lose it" so that it's only in the worker dashboard, but then if you ever need to put that into a different worker, or decrypt the data outside of that worker you cannot do anything, so do NOT do this.
You could infer keys from something unique to each user that they anyway provide into your application, as an alternative.

MMax (@rozenmd)whats your database_id?

ajgeiss0702•12/19/24, 4:26 PM

03bb813c-a64f-4a34-9483-dc285ab59ba2

example of a long running maintenance operation is moving all data from one table to another after m

Similar Threads

Similar Threads

Similar Threads