Hi I am seeing strange behaviour in D1. Inserting two rows of data and the API reporting back 34 row

Jjepp Hi I am seeing strange behaviour in D1. Inserting two rows of data and the API r...

M

Max (@rozenmd)•6/1/24, 12:27 PM

how many indexes do you have?

M

maj•6/2/24, 5:06 AM

Today I figured out that I had tens of millions of "rows read" per day - using a table of only 30k records. Turns out most of them were due to using "json_each()" on a JSON array. Would those "virtual rows" count against that quota, too? I'm a bit surprised, as I'm still on the free plan, with a limit of 5M rows read per day. Yet I haven't been billed anything yet.

A

Ashley•6/2/24, 8:10 AM

Just wanted to give a shoutout to this request: https://github.com/cloudflare/workers-sdk/issues/3581, I think at the moment the dance with different wrangler.toml files is okay but it's certainly not optimal and being able to either specify database_id or the environment would be a really nice QoL change to make CI/CD easier

GitHub

Issues · cloudflare/workers-sdk

Home to Wrangler, the CLI for Cloudflare Workers® - Issues · cloudflare/workers-sdk

A

Ashley•6/2/24, 8:48 AM

Another little thing I noticed: if you create an API Token using the

Edit Cloudflare Workers

Edit Cloudflare Workers

Edit Cloudflare Workers

Edit Cloudflare Workers template, it doesn't include D1. This caught me out a little bit running D1 commands in CI, as it does include KV and R2, so seems a little inconsistent it doesn't include D1, but perhaps there is a reason for that

T

Tempo•6/2/24, 7:37 PM

Hi, I know branching is in the work but is there an other way now to have a new preview database for each preview branch ?

Mmaj Today I figured out that I had tens of millions of "rows read" per day - using a...

M

Matt Silverlock•6/2/24, 9:37 PM

Yes, since those virtual tables reside in memory and require read operations.

If you are on the free plan, you can’t be billed anything. If you are on the $5 paid plan, you will only be billed once you exceed 25 billion reads per month.

J

jepcd•6/3/24, 5:22 AM

I seem to be running into some limits with a large query

  [cause]: Error: parser stack overflow
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.raw (cloudflare-internal:d1-api:184:32) {
    [cause]: undefined
  }

  [cause]: Error: parser stack overflow
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.raw (cloudflare-internal:d1-api:184:32) {
    [cause]: undefined
  }

  [cause]: Error: parser stack overflow
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.raw (cloudflare-internal:d1-api:184:32) {
    [cause]: undefined
  }

  [cause]: Error: parser stack overflow
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.raw (cloudflare-internal:d1-api:184:32) {
    [cause]: undefined
  }

is this something that can be increased somehow?

Jjepcd I seem to be running into some limits with a large query ``` [cause]: Error: p...

K

kian•6/3/24, 7:55 AM

I’d recommend opening an issue on https://github.com/cloudflare/workerd/issues/new/choose with some real-world queries that run into the limit. I believe it can be raised in SQLite, but results in a slower parser so it would need good justification.

D

dreamingearthman•6/3/24, 3:09 PM

For those who want a fully managed D1 database with a well-built table editor & console, check out https://dzero.dev. You can sign up for early access and I'll approve it

Dzero

Dzero - The fastest globally distributed SQLite database

Dzero is a fully managed serverless SQLite database for production scale. Instantly distribute your database on EDGE nodes around the world at no extract cost. Query your database from any environment or framework. Easily manage your database with the table editor.

S

SupItsTom•6/3/24, 6:33 PM

when does d1 alpha actually stop processing new queries?
on the d1 dashboard it says august 1st but on alpha-migration it says july 1st

G

Gerbuuun•6/3/24, 6:33 PM

Does

PRAGMA defer_foreign_keys=ON

PRAGMA defer_foreign_keys=ON

PRAGMA defer_foreign_keys=ON

PRAGMA defer_foreign_keys=ON not disable

on update

on update

on update

on update and

on delete

on delete

on delete

on delete actions? Kinda weird when SQLite often requires recreating tables (and thus triggering those actions)

Original message was deleted

M

Max (@rozenmd)•6/4/24, 8:20 AM

sqlite still stores the "type", even if you choose to ignore it

M

Max (@rozenmd)•6/4/24, 8:20 AM

we could probably add this

Original message was deleted

M

Max (@rozenmd)•6/4/24, 8:22 AM

in the meanwhile you could query

select * from pragma_table_info('TABLE_NAME_HERE');

select * from pragma_table_info('TABLE_NAME_HERE');

select * from pragma_table_info('TABLE_NAME_HERE');

select * from pragma_table_info('TABLE_NAME_HERE');

Original message was deleted

M

Max (@rozenmd)•6/4/24, 9:31 AM

I mean, we'd be running the additional SQL on our side if we added it

GGerbuuun Does `PRAGMA defer_foreign_keys=ON` not disable `on update` and `on delete` acti...

G

Gerbuuun•6/4/24, 11:54 AM

Do you guys recommend not using the

on delete

on delete

on delete

on delete and

on update

on update

on update

on update features because of this? Or should I dump my current db, create a new one, and upload the data?

G

Gyurmatag•6/4/24, 1:31 PM

Hi guys! Is it possible to make a copy of an existing D1 database from the Cloudflare dashboard or with just one command?

MMax (@rozenmd)I mean, we'd be running the additional SQL on our side if we added it

I

Isaac McFadyen•6/4/24, 1:34 PM

Possibly an optional thing then?

I

Isaac McFadyen•6/4/24, 1:35 PM

This would also improve d1-jdbc-driver's introspection because currently (or well, in the v2 PR) I run pragma_table_info and then cache the results to avoid querying that again, but if a table changes it's tricky to know when to invalidate.

I

IEatBeans•6/4/24, 6:21 PM

Sentry has reported a few “D1: Internal Errors” randomly. I’m it sure which queries this is affecting but is this a known issue?

N

notjoemartinez•6/4/24, 10:13 PM

Is there any documentation on this error?

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running the docs just define it as

Generic error.

Generic error.

Generic error.

Generic error.. I'm getting it while using the

db.batch()

db.batch()

db.batch()

db.batch() command and my code has been working fine until today. Is there a limit to how many prepared statements

db.batch

db.batch

db.batch

db.batch can run?

const transcriptPrepared = db.prepare(`
INSERT INTO transcripts
    (
        feed_id, archive_date, archive_date_time, 
        object_key, segment_start, segment_end,
        segment
    )

    VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
    `);

const transcriptQueriesToRun = allSegments.map(segmentObj => transcriptPrepared.bind(
        segmentObj.feed_id,
        segmentObj.archive_date,
        segmentObj.archive_date_time,
        segmentObj.object_key,
        segmentObj.segment_start,
        segmentObj.segment_end,
        segmentObj.segment
    ));

console.log("transcriptQueriesToRun.length: ", transcriptQueriesToRun.length);
console.log("transcriptQueriesToRun[0] ", JSON.stringify(transcriptQueriesToRun[0]));
if (transcriptQueriesToRun.length > 0) {
    try {
        await db.batch(transcriptQueriesToRun);

    } catch (e: any) {
        console.error({
            message: e.message
        });

        if (e instanceof Error) {
            return new Response(JSON.stringify({
                "error": e.message,
                "traceback": e.stack
            }), {
                status: 500,
                headers: getCorsHeaders()
            });
        }

    }
}

const transcriptPrepared = db.prepare(`
INSERT INTO transcripts
    (
        feed_id, archive_date, archive_date_time, 
        object_key, segment_start, segment_end,
        segment
    )

    VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
    `);

const transcriptQueriesToRun = allSegments.map(segmentObj => transcriptPrepared.bind(
        segmentObj.feed_id,
        segmentObj.archive_date,
        segmentObj.archive_date_time,
        segmentObj.object_key,
        segmentObj.segment_start,
        segmentObj.segment_end,
        segmentObj.segment
    ));

console.log("transcriptQueriesToRun.length: ", transcriptQueriesToRun.length);
console.log("transcriptQueriesToRun[0] ", JSON.stringify(transcriptQueriesToRun[0]));
if (transcriptQueriesToRun.length > 0) {
    try {
        await db.batch(transcriptQueriesToRun);

    } catch (e: any) {
        console.error({
            message: e.message
        });

        if (e instanceof Error) {
            return new Response(JSON.stringify({
                "error": e.message,
                "traceback": e.stack
            }), {
                status: 500,
                headers: getCorsHeaders()
            });
        }

    }
}

const transcriptPrepared = db.prepare(`
INSERT INTO transcripts
    (
        feed_id, archive_date, archive_date_time, 
        object_key, segment_start, segment_end,
        segment
    )

    VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
    `);

const transcriptQueriesToRun = allSegments.map(segmentObj => transcriptPrepared.bind(
        segmentObj.feed_id,
        segmentObj.archive_date,
        segmentObj.archive_date_time,
        segmentObj.object_key,
        segmentObj.segment_start,
        segmentObj.segment_end,
        segmentObj.segment
    ));

console.log("transcriptQueriesToRun.length: ", transcriptQueriesToRun.length);
console.log("transcriptQueriesToRun[0] ", JSON.stringify(transcriptQueriesToRun[0]));
if (transcriptQueriesToRun.length > 0) {
    try {
        await db.batch(transcriptQueriesToRun);

    } catch (e: any) {
        console.error({
            message: e.message
        });

        if (e instanceof Error) {
            return new Response(JSON.stringify({
                "error": e.message,
                "traceback": e.stack
            }), {
                status: 500,
                headers: getCorsHeaders()
            });
        }

    }
}

const transcriptPrepared = db.prepare(`
INSERT INTO transcripts
    (
        feed_id, archive_date, archive_date_time, 
        object_key, segment_start, segment_end,
        segment
    )

    VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
    `);

const transcriptQueriesToRun = allSegments.map(segmentObj => transcriptPrepared.bind(
        segmentObj.feed_id,
        segmentObj.archive_date,
        segmentObj.archive_date_time,
        segmentObj.object_key,
        segmentObj.segment_start,
        segmentObj.segment_end,
        segmentObj.segment
    ));

console.log("transcriptQueriesToRun.length: ", transcriptQueriesToRun.length);
console.log("transcriptQueriesToRun[0] ", JSON.stringify(transcriptQueriesToRun[0]));
if (transcriptQueriesToRun.length > 0) {
    try {
        await db.batch(transcriptQueriesToRun);

    } catch (e: any) {
        console.error({
            message: e.message
        });

        if (e instanceof Error) {
            return new Response(JSON.stringify({
                "error": e.message,
                "traceback": e.stack
            }), {
                status: 500,
                headers: getCorsHeaders()
            });
        }

    }
}

N

notjoemartinez•6/4/24, 10:14 PM

This is the full traceback:

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running.\n    at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)\n    at async D1Database.batch (cloudflare-internal:d1-api:36:23)\n    at async updateTranscripts (index.js:15762:9)\n    at async Object.fetch (index.js:16415:16)

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running.\n    at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)\n    at async D1Database.batch (cloudflare-internal:d1-api:36:23)\n    at async updateTranscripts (index.js:15762:9)\n    at async Object.fetch (index.js:16415:16)

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running.\n    at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)\n    at async D1Database.batch (cloudflare-internal:d1-api:36:23)\n    at async updateTranscripts (index.js:15762:9)\n    at async Object.fetch (index.js:16415:16)

Error: D1_ERROR: The destination execution context for this RPC was canceled while the call was still running.\n    at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)\n    at async D1Database.batch (cloudflare-internal:d1-api:36:23)\n    at async updateTranscripts (index.js:15762:9)\n    at async Object.fetch (index.js:16415:16)

N

notjoemartinez•6/4/24, 11:50 PM

nvm, my insert query was either going over the 30 second query limit. I fixed it by deleting some triggers that were slowing down the inserts. https://developers.cloudflare.com/d1/platform/limits/

Cloudflare Docs

Limits · Cloudflare D1 docs

If you would like to explore other storage solutions for your application, Cloudflare also offers Workers KV, Durable Objects, and R2.

MMax (@rozenmd)working on that - we just shipped a query param to let you skip pages (and go ba...

M

Max (@rozenmd)•6/5/24, 7:47 AM

going to make the dashboard table UI look more like DataGrip by the end of the summer

GGerbuuun Does `PRAGMA defer_foreign_keys=ON` not disable `on update` and `on delete` acti...

G

Gerbuuun•6/5/24, 3:37 PM

Has really nobody ran into this problem?

F

Foywards Bound•6/5/24, 4:32 PM

I have a TypeScript worker bound to a D1 database and I'm trying to insert a row like so:

let r : D1Response = await DATABASE.prepare(query).bind(id,name,type,created_at).run();
console.log("Response success: " + r.success);
console.log("Response error: " + r.error);

let r : D1Response = await DATABASE.prepare(query).bind(id,name,type,created_at).run();
console.log("Response success: " + r.success);
console.log("Response error: " + r.error);

let r : D1Response = await DATABASE.prepare(query).bind(id,name,type,created_at).run();
console.log("Response success: " + r.success);
console.log("Response error: " + r.error);

let r : D1Response = await DATABASE.prepare(query).bind(id,name,type,created_at).run();
console.log("Response success: " + r.success);
console.log("Response error: " + r.error);

but it never gets to the logging part, I'm guessing the promise is not returning

F

Foywards Bound•6/5/24, 4:44 PM

I added some more logging and awaiting and now I'm seeing it's a syntax error, oops!

F

Foywards Bound•6/5/24, 4:44 PM

const query = "INSERT INTO services(id,name,type,created_at)"
    + " VALUES (?,?,?,?)"
    + " ON CONFLICT (id) UPDATE" 
    + " SET name = excluded.name, type = excluded.type"
    + " WHERE name != excluded.name OR type != excluded.type";

const query = "INSERT INTO services(id,name,type,created_at)"
    + " VALUES (?,?,?,?)"
    + " ON CONFLICT (id) UPDATE" 
    + " SET name = excluded.name, type = excluded.type"
    + " WHERE name != excluded.name OR type != excluded.type";

const query = "INSERT INTO services(id,name,type,created_at)"
    + " VALUES (?,?,?,?)"
    + " ON CONFLICT (id) UPDATE" 
    + " SET name = excluded.name, type = excluded.type"
    + " WHERE name != excluded.name OR type != excluded.type";

const query = "INSERT INTO services(id,name,type,created_at)"
    + " VALUES (?,?,?,?)"
    + " ON CONFLICT (id) UPDATE" 
    + " SET name = excluded.name, type = excluded.type"
    + " WHERE name != excluded.name OR type != excluded.type";

F

Foywards Bound•6/5/24, 4:49 PM

What am I missing? This should be valid SQLite, no? https://www.sqlite.org/lang_upsert.html

F

Foywards Bound•6/5/24, 4:53 PM

I tried in the D1 Console and

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT (id) UPDATE SET name = excluded.name, type = excluded.type WHERE name != excluded.name OR type != excluded.type

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT (id) UPDATE SET name = excluded.name, type = excluded.type WHERE name != excluded.name OR type != excluded.type

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT (id) UPDATE SET name = excluded.name, type = excluded.type WHERE name != excluded.name OR type != excluded.type

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT (id) UPDATE SET name = excluded.name, type = excluded.type WHERE name != excluded.name OR type != excluded.type

does not work

F

Foywards Bound•6/5/24, 4:53 PM

However, this works:

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT DO NOTHING

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT DO NOTHING

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT DO NOTHING

INSERT INTO services (id,name,type,created_at) VALUES ("1", "2", "3", "4") ON CONFLICT DO NOTHING

F

Foywards Bound•6/5/24, 4:54 PM

So... does D1 not fully support SQLite? The UPSERT feature seems to be unimplemented?

F

Foywards Bound•6/5/24, 4:54 PM

Or am I missing something obvious?

F

Foywards Bound•6/5/24, 6:09 PM

I am missing something obvious. DO!

A

alok•6/5/24, 8:09 PM

I wrote the following small helper class to make it easier to batch D1 queries: https://github.com/zxsdotch/d1-batch. Let me know if you find it useful or if you would like me to add more features.

D

dpkp•6/5/24, 8:52 PM

Any recommendations for using d1 data with an external analytics system like metabase?

H

Hannes•6/6/24, 8:58 AM

Hi, I'm monitoring my services with Sentry and I'm getting a few D1 errors every day, although there's not a lot of traffic on the site. I'm worried it will become worse once there are more visitors?
The errors I'm seeing are:
D1_ERROR: Network connection lost.
D1_ERROR: internal error

HHannes Hi, I'm monitoring my services with Sentry and I'm getting a few D1 errors every...

M

Max (@rozenmd)•6/6/24, 10:06 AM

Are they particularly intensive queries? mostly reads, or writes?

MMax (@rozenmd)Are they particularly intensive queries? mostly reads, or writes?

H

Hannes•6/6/24, 10:14 AM

Interesting. I'm only seeing it on reads, though that might be due to the much bigger number of reads than writes. It is difficult to say which queries, because I'm using Prisma ORM and it does all the SQL under the hood. I believe that it does happen for quite simple queries, too. Like (pseudo sql)

SELECT field1, field2, field3, field4, field5 FROM table WHERE field9 = "k" OR field9 IS NULL

SELECT field1, field2, field3, field4, field5 FROM table WHERE field9 = "k" OR field9 IS NULL

SELECT field1, field2, field3, field4, field5 FROM table WHERE field9 = "k" OR field9 IS NULL

SELECT field1, field2, field3, field4, field5 FROM table WHERE field9 = "k" OR field9 IS NULL.

M

Max (@rozenmd)•6/6/24, 10:17 AM

Network connection lost.

Network connection lost.

Network connection lost.

Network connection lost. in particular is the error a DO client throws when it disconnects from the DO - as long as it's a SELECT, it's safe to retry

MMax (@rozenmd)`Network connection lost.` in particular is the error a DO client throws when it...

H

Hannes•6/6/24, 12:38 PM

Ok, thanks. Unsure how to implement that, though, as requests are handled entirely by prisma. I'll see if I open an issue over there.
Is that expected, though, or is it an error that you're monitoring and expecting to improve?

HHannes Ok, thanks. Unsure how to implement that, though, as requests are handled entire...

M

Max (@rozenmd)•6/6/24, 6:09 PM

I'd probably wrap your prisma calls in a try/catch (if its throwing an error) and try again - we're monitoring all errors and will find fixes for them over time

MMax (@rozenmd)I'd probably wrap your prisma calls in a try/catch (if its throwing an error) an...

H

Hannes•6/6/24, 6:13 PM

Thanks for the info. My application more than 1000 prisma calls, so that'd probably have to be a more generic approach

But great to know that you're monitoring this.

F

Foywards Bound•6/6/24, 11:44 PM

Is there a best practice around primary keys / auto incrementing ids in D1?

B

berniesumption•6/7/24, 8:58 AM

To flesh out Alexander's answer a bit, since we're using SQLite, every row will have an auto-incrementing ID by default. If you define your own

INTEGER PRIMARY KEY

INTEGER PRIMARY KEY

INTEGER PRIMARY KEY

INTEGER PRIMARY KEY column, that's the auto-incrementing ID. If you don't, you'll get a default one called

ROWID

ROWID

that you can use for queries. You can opt out by defining the table as

WITHOUT ROWID

WITHOUT ROWID

WITHOUT ROWID

WITHOUT ROWID but that comes with some additional constraints - https://sqlite.org/withoutrowid.html - and you should only do that if you know what you're doing.

So SQLite, and D1, support auto-incrementing IDs just fine.

Separately, there are all kinds of reasons why UUIDs are preferable to auto-incrementing IDs for most apps regardless of the database engine. So it is good advice to use UUIDs by default unless you have a good reason to use auto-incrementing integers and understand the potential pitfalls with them (e.g. enabling id enumeration attacks and leaking information about the size of tables).

J

jepcd•6/7/24, 9:15 AM

Hi, is there a limit on the number of statements in a batch call?

G

Gerbuuun•6/7/24, 9:31 AM

I use auto incrementing IDs for internal use (server and joins) and have a nanoid which is used by users

Hi I am seeing strange behaviour in D1. Inserting two rows of data and the API reporting back 34 row

Similar Threads

Similar Threads

Similar Threads