and yes, I am using the experimental backend. I was trying to do an `INSERT` operation with fewer th

and yes, I am using the experimental backend. I was trying to do an

INSERT

INSERT

operation with fewer than 20 rows, so I can't imagine it was failing due to any kind of size limit

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

Matt Silverlock•7/14/23, 11:35 AM

Without more details it’s hard to debug. Is the data static? Not all rows are created equal.

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

Matt Silverlock•7/14/23, 11:54 AM

I’d take a look through https://developers.cloudflare.com/d1/platform/limits/ to make sure you aren’t hitting limits. We have some changes queued up that will better expose error messages.

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public alpha. Join the #d1-open-alpha channel in the Cloudflare Developer Discord to keep up to date …

Murder Chicken•7/14/23, 12:31 PM

I seem to be getting a lot of

Error: D1_ERROR: Error: Network connection lost.

Error: D1_ERROR: Error: Network connection lost.

errors with fairly simple (yet critical) queries my worker is running. The DB is using the experimental

d1

d1

backend. Is there a tried and true way to get to the bottom of why this is happening?

MMurder Chicken I seem to be getting a lot of `Error: D1_ERROR: Error: Network connection lost.`...

Matt Silverlock•7/14/23, 2:43 PM

Can you share your database ID here, and an example query? Is this deterministic - e.g. a specific query always causes the error?

Murder Chicken•7/14/23, 2:45 PM

Seems to be, will DM

Murder Chicken•7/14/23, 2:52 PM

@Matt sent.

Original message was deleted

itsmatteomanf•7/14/23, 3:51 PM

Do you have a primary key column?

itsmatteomanf•7/14/23, 3:55 PM

When you configure a database you need a primary key column (or columns) so that the database knows what data should unique. If you don't set that up, how would the DB know what is new and what is data to be replaced?

Original message was deleted

itsmatteomanf•7/14/23, 3:57 PM

Yes!

Original message was deleted

itsmatteomanf•7/14/23, 4:03 PM

I'd personally learn to use an actual SQL query (either via the browser console, or directly from

wrangler d1 execute

wrangler d1 execute

), which allows more granular control than the Ui in the dashboard, and repeatable creations.

itsmatteomanf•7/14/23, 4:03 PM

But this would work.

MMatt Silverlock Without more details it’s hard to debug. Is the data static? Not all rows are cr...

micromashorOP•7/14/23, 6:06 PM

yeah it's 3 text fields (all fields <100chars) and an ID, so I seriously doubt I'm hitting any limits. Will watch out for better error messages though

Lucian•7/14/23, 6:56 PM

,Hi all,

I am having an issue with my D1 database. When attempting to index one of the columns I get the error below:
ERROR 9015: SQL engine error: query error: Error code 11: The database disk image is malformed (database disk image is malformed).

CREATE INDEX idx_teams_team_name ON teams(team_name);

Any idea what can I do to get around this? The table has about 90k rows; I surely need an index to search on this column

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

Cyb3r-Jak3•7/14/23, 7:36 PM

Do you have the database ID? Sounds like something that the D1 team is going to want to look at on their side

CCyb3r-Jak3 Do you have the database ID? Sounds like something that the D1 team is going to ...

Lucian•7/14/23, 7:40 PM

I restored the database to a 1 day old copy and now it works, the index created. I will need to reload about 40k records, but it is what it is

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

Matt Silverlock•7/14/23, 9:46 PM

It sounds like you’re using the original alpha backend - that error is very specific to it.

Matt Silverlock•7/14/23, 9:47 PM

see the note about the experimental backend - https://developers.cloudflare.com/d1/get-started/#2-create-a-database

Get started · Cloudflare D1 docs

This guide will instruct you through:

staticmedia•7/15/23, 7:15 AM

Hi, is the performance between getting data from KV versus from D1 neglible ? (assuming the DB lookup is a find on PK)

staticmedia•7/15/23, 7:20 AM

I'm building on a website that caches all the data from the headless CMS on cloudflare using KV. When the DB has an update a webhook pings the worker and updates the cache. The website makes requests to the same worker that fetches from the cache, and hydrates the HTML before sending back to the user

staticmedia•7/15/23, 7:21 AM

Because KV doesn't provide cache tagging its quite difficult to manage the cache (with nested data and making sure those nested items stay fresh). I want to store the cache in a D1 so I can more easily lookup data that needs refreshing

Tony Leung•7/15/23, 1:41 PM

I'm pretty curious about this: KV is not transaction-ready, is D1 transaction ready? If I have an user from US made a request to my page function using D1 in APAC, will the request take longer? Is there any worker+D1 replica in US which can handle the request for me? If there is and D1 supports transaction, will there any penalty on performance or request time?

vassbence•7/15/23, 2:47 PM

D1 is single writer multi reader (or at least will be)

vassbence•7/15/23, 2:48 PM

but KV and D1 are entirely different products for different usecases

theLaCzar•7/15/23, 11:55 PM

quick question, i designed microservices with 1ms : 1db design, however, we are limited to 10DB currently per account, knowing i have several sites under my account, so i guess i have to fit them in, my question is does D1 experience the same issues with normal relations db like table locking, running out of resource etc?

theLaCzar•7/15/23, 11:57 PM

because if D1 is efficient enough that table locking is not much of an issue, i guess ill go with a monolitic DB design and lots of workers

TtheLaCzar because if D1 is efficient enough that table locking is not much of an issue, i ...

Matt Silverlock•7/16/23, 12:25 AM

You can have multiple concurrent readers. Reads don’t block.

Unsmart•7/16/23, 9:27 PM

Its rounded up. So a unit of 0.00000000000001 would still be 1 full unit

samratarmas•7/17/23, 6:12 AM

Hi team, can we use SQLite triggers in d1?

samratarmas•7/17/23, 6:13 AM

My usecase is as follows:

samratarmas•7/17/23, 6:14 AM


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

samratarmas•7/17/23, 6:14 AM

Basically using triggers to keep the fts5 virtual table indexes in sync with a content table, is this possible?

Ssamratarmas Hi team, can we use SQLite triggers in d1?

muslax•7/17/23, 7:23 AM

I currently use triggers in D1. So yes.

chientrm.com•7/17/23, 11:26 AM

is there anything wrong with the latest

"@cloudflare/workers-types": "^4.20230710.1"

"@cloudflare/workers-types": "^4.20230710.1"

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

chientrm.com•7/17/23, 11:26 AM

why is the

first

first

return

Record<string, T>

Record<string, T>

but not

chientrm.com•7/17/23, 11:27 AM

Here is version

4.20230518.0

4.20230518.0

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

Original message was deleted

Cyb3r-Jak3•7/17/23, 2:29 PM

d1-database

Cchientrm.com why is the `first` return `Record<string, T>` but not `T`? 🤔

Matt Silverlock•7/17/23, 5:02 PM

This is a bug: d1-database cc @geelen

《

《 Sarge 》•7/17/23, 5:42 PM

Hey has anyone come across a bug when attempting to execute a

DROP TABLE IF EXISTS [table001]

DROP TABLE IF EXISTS [table001]

statement, and wrangler returns an error

no such table: main.[table001]?

no such table: main.[table001]?

I can execute the same script with no issue via DBeaver, but when attempting to do it via wrangler 3.20 and wrangler 2.20 I get this no such table error?

《

《 Sarge 》•7/17/23, 5:57 PM

I have been using brackets, and it didn't seem to cause an issue for me.

《

《 Sarge 》•7/17/23, 5:57 PM

The brakets here are just an example, I don't have that in the code.

《

《 Sarge 》•7/17/23, 5:59 PM

here is a better example, I'm just being careful not to share IP that is not my own.

Original message was deleted

《

《 Sarge 》•7/17/23, 6:12 PM

Okay, I'll attempt to change all my square brackets to be '`'
The thing is, it doesn't even seem to get past the initial DROP IF EXISTS statement shown above, and that has not brackets around it.
Probably should just ignore that original example I gave, and look at the screenshots.

Original message was deleted

《

《 Sarge 》•7/17/23, 6:17 PM

Yeah, tried that, and again it all ran fine in DBeaver, but failed with the same error in wrangler@3.20

《《 Sarge 》Hey has anyone come across a bug when attempting to execute a`DROP TABLE IF EXIS...

Matt Silverlock•7/17/23, 6:50 PM

Don't refer to it as

main.table_name

main.table_name

. Just

table_name

table_name

Matt Silverlock•7/17/23, 6:51 PM

e.g. (this is all junk data, not real emails)

Original message was deleted

《

《 Sarge 》•7/17/23, 6:51 PM

The

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

runs fine in DBeaver, but not wrangler@3.20

MMatt Silverlock e.g. (this is all junk data, not real emails)

《

《 Sarge 》•7/17/23, 6:54 PM

No I'm not querying the 'main.' in SQL at all. I have a build data script that drops everything and rebuilds the database from scratch.
at he beginning of it, I have the drops, then I have the entire DB created again.
The script is rather large @ikea.se, and I'd probably not be able to post it all here anyway

-- Create a table. And an external content fts5 table to index it. CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c); CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a'); -- Triggers to keep the FTS index up to date. CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END; CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); END; CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END;

declare abstract class D1PreparedStatement { bind(...values: any[]): D1PreparedStatement; first<T = unknown>(colName: string): Promise<T | null>; first<T = unknown>(): Promise<Record<string, T> | null>; run<T = unknown>(): Promise<D1Result<T>>; all<T = unknown>(): Promise<D1Result<T[]>>; raw<T = unknown>(): Promise<T[]>; }

declare abstract class D1PreparedStatement { bind(...values: any[]): D1PreparedStatement; first<T = unknown>(colName?: string): Promise<T>; run<T = unknown>(): Promise<D1Result<T>>; all<T = unknown>(): Promise<D1Result<T>>; raw<T = unknown>(): Promise<T[]>; }

and yes, I am using the experimental backend. I was trying to do an `INSERT` operation with fewer th

Similar Threads