weird. I have an index

W

WcaleNieWolnyOP•1/27/24, 4:37 PM

even if, that does not explain the writes

W

WcaleNieWolnyOP•1/27/24, 4:40 PM

still does not explain the inserts. Sort of explain reads to the database

SSamantas5855 Thank you, feel free to ask if you need further information to reproduce

M

Max (@rozenmd)•1/27/24, 4:41 PM

can you try again? I just pushed a fix

MMax (@rozenmd)can you try again? I just pushed a fix

S

Samantas5855•1/27/24, 4:47 PM

Sure thing, give me 5 minutes

W

WcaleNieWolnyOP•1/27/24, 4:48 PM

I still cannot see the reason for huge inserts

S

Samantas5855•1/27/24, 4:56 PM

@Max (@rozenmd) You are a godsend

S

Samantas5855•1/27/24, 4:56 PM

it worked

S

Samantas5855•1/27/24, 4:56 PM

What was the issue?

SSamantas5855 What was the issue?

M

Max (@rozenmd)•1/27/24, 5:04 PM

work-in-progress feature i'm working on that'll let folks debug their queries easier (to make them faster and cost less rows read/written) - turns out an internal service was refusing queries bigger than 5KB, and I didn't expect that

MMax (@rozenmd)work-in-progress feature i'm working on that'll let folks debug their queries ea...

S

Samantas5855•1/27/24, 5:18 PM

That seems useful

WWcaleNieWolny weird. I have an index

W

WcaleNieWolnyOP•1/27/24, 6:45 PM

@Matt any idea from those explain statements / queries what is causing the write?

C

chientrm.com•1/27/24, 11:54 PM

Uhm, where can I find more analytics about this? suddenly 754k rows read

Cchientrm.com Uhm, where can I find more analytics about this? suddenly 754k rows read

M

Matt Silverlock•1/28/24, 12:21 AM

Read the docs on rows: https://developers.cloudflare.com/d1/observability/metrics-analytics/

754k in just under 30 days is well within the 25 billion included in the paid plan. You’d need to do 33,156x that volume to exceed what’s included.

Metrics and analytics · Cloudflare D1 docs

D1 exposes database analytics that allow you to inspect query volume, response size, and query latency across all and/or each database in your …

P

Pepper•1/28/24, 6:18 AM

Is there any good way to edit data manually through a gui? I don't want to have to click through 20 pages to get to my most recently added row using the dashboard. I tried this https://github.com/JacobLinCool/d1-manager but it didn't work (maybe I set it up incorrectly but I just get a white screen).

GitHub

GitHub - JacobLinCool/d1-manager: D1 Manager is a web UI and API fo...

D1 Manager is a web UI and API for Cloudflare D1, a serverless SQL database. It provides a web interface for managing databases, tables, and records, as well as an AI assistant to help you write qu...

WWcaleNieWolny @Matt any idea from those explain statements / queries what is causing the write...

K

K•1/28/24, 8:16 AM

There is a thread about this in the community forum. https://community.cloudflare.com/t/d1-rows-written-is-counted-in-select-query/564031 . This is what I guess is happening in your case. Tagging @Matt . It would be better to mention this behavior somewhere in docs.

K

kian•1/28/24, 8:22 AM

Yeah, it's usually just virtual/temporary tables.

K

kaopanana•1/28/24, 11:03 AM

can i test d1 with postman

Kkaopanana can i test d1 with postman

C

Cyb3r-Jak3•1/28/24, 1:44 PM

Yes, you can use the REST API

R

Register•1/28/24, 6:55 PM

I am interested to start a new project using Pages but I want to use D1 in this Pages app.
I've gone ahead and setup my basic Pages repo where I have a

/site

/site

/site

/site directory where I keep my frontend and is what is built when I run

npm run build

npm run build

npm run build

npm run build to the

/dist

/dist

/dist

/dist directory.
And I have my

/functions

/functions

/functions

/functions directory which is of course where I am storing my backend API.
I am wondering if I should use a

/sql

/sql

/sql

/sql directory to house the SQL and migrations?
Or should this be kept in a different repo?

Main question - how should I be structuring my Pages repo and use/include D1 migrations?

E

EDAC•1/28/24, 9:17 PM

I've been seeing a lot of exceptions from my worker, all caused by the generic error message "D1_ERROR: Error: Error 9000: something went wrong". It seems to come and go, but when it happens more than half of my traffic ends with exceptions.

This doesn't seem to be input-related, as I can run the worker with identical inputs later on and it works. (This is a very simple app, all it really does is take GET values and save them).

Any suggestions on what I can do to troubleshoot this?

I

ItsWendell•1/28/24, 11:49 PM

Why is it that when I do a count

count(distinct "id")

count(distinct "id")

count(distinct "id")

count(distinct "id") I get rows_written in meta?

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

{
  "collectionItems": {
      "success": true,
      "meta": {
        "served_by": "v3-prod",
        "duration": 0.6292,
        "changes": 0,
        "last_row_id": 0,
        "changed_db": false,
        "size_after": 3862528,
        "rows_read": 1014,
        "rows_written": 342
      },
      "results": [
        {
          "count(distinct \"id\")": 342
        }
      ]
    }
}

I

ItsWendell•1/29/24, 1:29 AM

@colexemi ahhh that makes sense, good to know thanks I'll check for that!

A

Adi•1/29/24, 5:39 AM

Hey all, wanted some help in understanding the metrics I'm seeing on my D1 database.
There is always an inconsistency in the write queries vs the rows written.
E.g over the last 24 hours There has been 1 write query but 6.47k rows written? Trying to work out how that happens.
I initially thought maybe it's index rebalancing, but that doesn't make sense because the underlying data doesn't change (only 1 write query).

A

Adi•1/29/24, 6:12 AM

Oh, database-id for the above is

849f0f66-2128-44b7-ab25-8776dacfb571

849f0f66-2128-44b7-ab25-8776dacfb571

849f0f66-2128-44b7-ab25-8776dacfb571

849f0f66-2128-44b7-ab25-8776dacfb571 (in case it's needed)

K

kian•1/29/24, 6:45 AM

Probably read queries that include temporary tables, which incur rows written

F

Fran•1/29/24, 11:43 AM

Hi, im traying d1 with nuxt and it say me DB is not defined. This is de code and wrangler.toml

F

Fran•1/29/24, 11:44 AM

Captura_de_pantalla_2024-01-29_a_las_12.43.30.png

F

Fran•1/29/24, 11:44 AM

Captura_de_pantalla_2024-01-29_a_las_12.44.49.png

K

kian•1/29/24, 12:11 PM

Take a look at https://developers.cloudflare.com/pages/framework-guides/deploy-a-nuxt-site/#use-bindings-in-your-nuxt-application

A

Ashish Rawat•1/29/24, 12:27 PM

Is it possible to do full text search in D1 (SQLite)

K

kian•1/29/24, 12:28 PM

The fts5 extension is enabled

K

kian•1/29/24, 12:28 PM

https://www.sqlite.org/fts5.html

C

carlos-alberto•1/29/24, 1:37 PM

Over the weekend we've started seeing

SQL execute error: database or disk is full

SQL execute error: database or disk is full

SQL execute error: database or disk is full

SQL execute error: database or disk is full errors on our D1 db, we are using 104.86 MB and are on a paid plan. (DB ID is

2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a

2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a

2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a

2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a if it helps)

Ccarlos-alberto Over the weekend we've started seeing `SQL execute error: database or disk is fu...

M

Max (@rozenmd)•1/29/24, 2:20 PM

can you try running

npx wrangler d1 info <dbname>

npx wrangler d1 info <dbname>

npx wrangler d1 info <dbname>

npx wrangler d1 info <dbname> on that DB?

M

Max (@rozenmd)•1/29/24, 2:21 PM

i get the feeling it's an alpha db, which has a limit of 100MB

MMax (@rozenmd)can you try running `npx wrangler d1 info <dbname>` on that DB?

C

carlos-alberto•1/29/24, 2:22 PM

➜  ~ npx wrangler d1 info odometer                                                                                                                                                                                 ✘ 1
┌────────────┬──────────────────────────────────────┐
│            │ 2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a │
├────────────┼──────────────────────────────────────┤
│ name       │ odometer                             │
├────────────┼──────────────────────────────────────┤
│ created_at │ 2023-06-08T10:24:40.711Z             │
├────────────┼──────────────────────────────────────┤
│ version    │ alpha                                │
├────────────┼──────────────────────────────────────┤
│ num_tables │ 2                                    │
├────────────┼──────────────────────────────────────┤
│ file_size  │ 105 MB

➜  ~ npx wrangler d1 info odometer                                                                                                                                                                                 ✘ 1
┌────────────┬──────────────────────────────────────┐
│            │ 2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a │
├────────────┼──────────────────────────────────────┤
│ name       │ odometer                             │
├────────────┼──────────────────────────────────────┤
│ created_at │ 2023-06-08T10:24:40.711Z             │
├────────────┼──────────────────────────────────────┤
│ version    │ alpha                                │
├────────────┼──────────────────────────────────────┤
│ num_tables │ 2                                    │
├────────────┼──────────────────────────────────────┤
│ file_size  │ 105 MB

➜  ~ npx wrangler d1 info odometer                                                                                                                                                                                 ✘ 1
┌────────────┬──────────────────────────────────────┐
│            │ 2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a │
├────────────┼──────────────────────────────────────┤
│ name       │ odometer                             │
├────────────┼──────────────────────────────────────┤
│ created_at │ 2023-06-08T10:24:40.711Z             │
├────────────┼──────────────────────────────────────┤
│ version    │ alpha                                │
├────────────┼──────────────────────────────────────┤
│ num_tables │ 2                                    │
├────────────┼──────────────────────────────────────┤
│ file_size  │ 105 MB

➜  ~ npx wrangler d1 info odometer                                                                                                                                                                                 ✘ 1
┌────────────┬──────────────────────────────────────┐
│            │ 2211f2a0-4f44-4b32-b1a6-01b4d3f09f0a │
├────────────┼──────────────────────────────────────┤
│ name       │ odometer                             │
├────────────┼──────────────────────────────────────┤
│ created_at │ 2023-06-08T10:24:40.711Z             │
├────────────┼──────────────────────────────────────┤
│ version    │ alpha                                │
├────────────┼──────────────────────────────────────┤
│ num_tables │ 2                                    │
├────────────┼──────────────────────────────────────┤
│ file_size  │ 105 MB

K

K•1/29/24, 6:53 PM

How many points of presence will read replications have?

Original message was deleted

E

EDAC•1/29/24, 8:19 PM

Hopefully this fixes my problems too

F

Frank Walsh•1/30/24, 2:26 AM

I saw a few posts about this error but didn't see any resolutions. Has anyone had luck fixing and/or working around this error?

[ERROR] Internal error in D1 storage caused object to be reset. [code: 7500]

[ERROR] Internal error in D1 storage caused object to be reset. [code: 7500]

[ERROR] Internal error in D1 storage caused object to be reset. [code: 7500]

[ERROR] Internal error in D1 storage caused object to be reset. [code: 7500]

P

Patrick M•1/30/24, 3:22 AM

I was having trouble recreating a table in a migration since

pragma foreign_keys=0

pragma foreign_keys=0

pragma foreign_keys=0

pragma foreign_keys=0 does not work in D1’s automatic transactions. I found some advice in this discord to use

pragma defer_foreign_keys=1

pragma defer_foreign_keys=1

pragma defer_foreign_keys=1

pragma defer_foreign_keys=1 which still didn’t fix my issue, just got a mysterious error message. Finally I stumbled on this hint: https://sqlite.org/forum/forumpost/825039816c3f0097 If you also reset

pragma defer_foreign_keys=0

pragma defer_foreign_keys=0

pragma defer_foreign_keys=0

pragma defer_foreign_keys=0 at the end of the script it will completely skip the foreign key checking. Feels a bit risky since even

pragma foreign_key_check

pragma foreign_key_check

pragma foreign_key_check

pragma foreign_key_check is disabled in D1 and I don’t have a good way to confirm my data is still valid. But I’m glad I could at least apply my migration.

A

Amr Yasser•1/30/24, 5:25 AM

Hello, I know I can use a local D1 using miniflare and next-on-pages, is it possible to use next-on-pages but with a deployed d1 instance without deploying the app? like supabase where I can connect to the deployed db.

J

Jakob Norlin•1/30/24, 8:39 AM

Is there a way to have the sqlite file generated when running wrangler d1 with the

--local

--local

--local

--local flag have a more deterministic name? Right now my db file is located at

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

.wrangler/state/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite, but I'd prefer something more memorable. I'm also not sure if someone else who runs the setup locally would get the same file name.

I've tried with

--persist-local=db.sqlite

--persist-local=db.sqlite

--persist-local=db.sqlite

--persist-local=db.sqlite, but that seems to change the top level folder only, so then the DB file is at

db.sqlite/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

db.sqlite/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

db.sqlite/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

db.sqlite/v3/d1/miniflare-D1DatabaseObject/e7352547963de7050bd7d94658afc4fe78b61811b7815da12d90be8e863abf4d.sqlite

H

h•1/30/24, 9:17 AM

Hi. if there are 500 items when executing SELECT COUNT(*) FROM foo on D1, 500 read counts to be charged? Currently I use D1 on production and read count is over 800 billions, gonna bankrupt

Original message was deleted

H

h•1/30/24, 9:20 AM

Ok, thanks

Hh Hi. if there are 500 items when executing SELECT COUNT(*) FROM foo on D1, 500 re...

M

Matt Silverlock•1/30/24, 9:23 AM

You should consider using indexes to reduce the rows you read (and make your queries faster!) - https://developers.cloudflare.com/d1/how-to/using-indexes/

review the pricing: https://developers.cloudflare.com/d1/platform/pricing/

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) …

Pricing · Cloudflare D1 docs

D1 is currently free to use during the public beta. Billing is expected to be enabled in February 2024 for developers using D1 on the Workers paid …

M

Musk•1/30/24, 11:08 AM

Hi, I would like to ask if one of the 3 million row data table queries in D1 will consume 3 million rows to avoid or 1 row to avoid? Will there be different consumption after increasing the index

Similar Threads