- Cloudflare Developers

K

kianOP•10/11/23, 3:17 PM

If you was more interested in the how rather than the why, https://sqlite.org/queryplanner.html is a good resource.

I

ilsubyeega•10/11/23, 3:18 PM

thanks i will check out when i wake up

Q

qavi•10/11/23, 3:25 PM

Can I import mysqldump file into D1 database, or this can be possible in future?

K

kianOP•10/11/23, 3:26 PM

You'd have to find a tool that can change the MySQL flavour of SQL into SQLite.

Kkian You'd have to find a tool that can change the MySQL flavour of SQL into SQLite.

Q

qavi•10/11/23, 3:27 PM

Ok thank you, there is no up to date tool to do this.

U

Up•10/11/23, 3:51 PM

probably a simple error but does anyone see what's wrong with this statement?

INSERT INTO verification_keys (id, content, account_name) VALUES ('6de221d9-4546-4599-ae7a-956632c9f6c9', 'c9948fff-9da3-4713-a169-9c434dbb1681', 'user1');

INSERT INTO verification_keys (id, content, account_name) VALUES ('6de221d9-4546-4599-ae7a-956632c9f6c9', 'c9948fff-9da3-4713-a169-9c434dbb1681', 'user1');

INSERT INTO verification_keys (id, content, account_name) VALUES ('6de221d9-4546-4599-ae7a-956632c9f6c9', 'c9948fff-9da3-4713-a169-9c434dbb1681', 'user1');

INSERT INTO verification_keys (id, content, account_name) VALUES ('6de221d9-4546-4599-ae7a-956632c9f6c9', 'c9948fff-9da3-4713-a169-9c434dbb1681', 'user1');

U

Up•10/11/23, 3:52 PM

running via wrangler CLI results in

A prepared SQL statement must contain only one statement

A prepared SQL statement must contain only one statement

A prepared SQL statement must contain only one statement

A prepared SQL statement must contain only one statement

K

kianOP•10/11/23, 3:58 PM

Try removing the

at the end

U

Up•10/11/23, 3:58 PM

that.. worked. thanks

K

kianOP•10/11/23, 3:58 PM

If the end of the statement isn't

' '

' '

' '

' ' or

'\n'

'\n'

'\n'

'\n' then it'll reject it as it thinks there's more statements to come.

K

kianOP•10/11/23, 3:59 PM

Could be something that Wrangler can catch and give you a more meaningful error for. cc @rozenmd

U

Up•10/11/23, 3:59 PM

should probably also update the docs then

U

Up•10/11/23, 3:59 PM

https://developers.cloudflare.com/d1/get-started/ has a trailing

K

kianOP•10/11/23, 4:04 PM

Seems like a bug(ish) in Wrangler's statement splitter.

K

kianOP•10/11/23, 4:11 PM

I can't seem to reproduce the error you got though - was it with

wrangler d1 execute ... --command

wrangler d1 execute ... --command

wrangler d1 execute ... --command

wrangler d1 execute ... --command?

U

Up•10/11/23, 4:13 PM

no it was with an sql file

K

kianOP•10/11/23, 4:14 PM

With just that statement, or several?

U

Up•10/11/23, 4:14 PM

wrangler d1 execute my-db --local --file=path/to/file.sql

wrangler d1 execute my-db --local --file=path/to/file.sql

wrangler d1 execute my-db --local --file=path/to/file.sql

wrangler d1 execute my-db --local --file=path/to/file.sql

and the file contents was that single insert statement + a 2nd empty line

K

kianOP•10/11/23, 4:19 PM

Ahh

K

kianOP•10/11/23, 4:19 PM

I think I see the issue

K

kianOP•10/11/23, 4:39 PM

Okay, I see what the bug is. I'll open an issue on the workerd repo.

L

Lane C•10/11/23, 4:53 PM

Is D1 super slow with multiple joins? I have a simple blog schema. I have less than 10k total records. This query executes locally with Sqlite under 200ms but is taking about 7 seconds in production. Is there any way to speed this up or do I need to wait until D1 is more mature for these types of queries?

    SELECT
    posts.id,
    posts.title,
    posts.updatedOn,
    substr(posts.body, 0, 20) as body,
    users.firstName || ' ' || users.lastName as author,
    count(comments.id) as commentCount,
    categories.title as category,
    COUNT() OVER() as total
    FROM posts
    left outer join users
    on posts.userid = users.id
    left outer join comments
    on comments.postId = posts.id
    left outer join categoriesToPosts
    on categoriesToPosts.postId = posts.id
    left outer join categories
    on categoriesToPosts.categoryId = categories.id
    group by posts.id
    order by posts.updatedOn desc
    limit 10
    offset 0

    SELECT
    posts.id,
    posts.title,
    posts.updatedOn,
    substr(posts.body, 0, 20) as body,
    users.firstName || ' ' || users.lastName as author,
    count(comments.id) as commentCount,
    categories.title as category,
    COUNT() OVER() as total
    FROM posts
    left outer join users
    on posts.userid = users.id
    left outer join comments
    on comments.postId = posts.id
    left outer join categoriesToPosts
    on categoriesToPosts.postId = posts.id
    left outer join categories
    on categoriesToPosts.categoryId = categories.id
    group by posts.id
    order by posts.updatedOn desc
    limit 10
    offset 0

    SELECT
    posts.id,
    posts.title,
    posts.updatedOn,
    substr(posts.body, 0, 20) as body,
    users.firstName || ' ' || users.lastName as author,
    count(comments.id) as commentCount,
    categories.title as category,
    COUNT() OVER() as total
    FROM posts
    left outer join users
    on posts.userid = users.id
    left outer join comments
    on comments.postId = posts.id
    left outer join categoriesToPosts
    on categoriesToPosts.postId = posts.id
    left outer join categories
    on categoriesToPosts.categoryId = categories.id
    group by posts.id
    order by posts.updatedOn desc
    limit 10
    offset 0

    SELECT
    posts.id,
    posts.title,
    posts.updatedOn,
    substr(posts.body, 0, 20) as body,
    users.firstName || ' ' || users.lastName as author,
    count(comments.id) as commentCount,
    categories.title as category,
    COUNT() OVER() as total
    FROM posts
    left outer join users
    on posts.userid = users.id
    left outer join comments
    on comments.postId = posts.id
    left outer join categoriesToPosts
    on categoriesToPosts.postId = posts.id
    left outer join categories
    on categoriesToPosts.categoryId = categories.id
    group by posts.id
    order by posts.updatedOn desc
    limit 10
    offset 0

UUp running via wrangler CLI results in `A prepared SQL statement must contain only ...

K

kianOP•10/11/23, 5:00 PM

Opened an issue for this: https://github.com/cloudflare/workerd/issues/1300

GitHub

🐛 Bug Report: SqliteDatabase::prepareSql does not handle CRLF line ...

workerd will reject prepared statements executed in SINGLE mode appear to have more than one statement. workerd/src/workerd/util/sqlite.c++ Lines 392 to 396 in d9ed653 switch (multi) { case SINGLE:...

Kkian Opened an issue for this: https://github.com/cloudflare/workerd/issues/1300

U

Up•10/11/23, 5:09 PM

Ty

LLane C Is D1 super slow with multiple joins? I have a simple blog schema. I have less t...

M

Max (@rozenmd)•10/11/23, 7:40 PM

do your tables have indexes

Kkian Click to see attachment

M

Max (@rozenmd)•10/11/23, 7:41 PM

this is a good resource to know why queries are slow

K

kianOP•10/11/23, 7:42 PM

& https://developers.cloudflare.com/d1/learning/using-indexes/

MMax (@rozenmd)do your tables have indexes

L

Lane C•10/11/23, 7:42 PM

yes there are indexes on all fields pertaining to the joins: https://github.com/lane711/sonicjs/blob/main/src/db/schema.ts

GitHub

sonicjs/src/db/schema.ts at main · lane711/sonicjs

SonicJs Headless CMS - Blazinflg Fast Headless CMS built on Cloudflare Workers. 100% Javascript Based - lane711/sonicjs

C

codebam•10/11/23, 8:34 PM

I executed my schema.sql that was saved locally and contains

CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT);

CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT);

CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT);

CREATE TABLE IF NOT EXISTS Messages (id TEXT, userId TEXT, content TEXT); but when I try to select from Messages it says the table doesn't exist?

Ccodebam I executed my schema.sql that was saved locally and contains `CREATE TABLE IF NO...

M

Max (@rozenmd)•10/11/23, 8:36 PM

wrangler dev defaults to local dev, did you execute the schema with --local?

L

Lane C•10/11/23, 8:37 PM

@Max (@rozenmd) here is my query plan- do you see anything that would explain such a large performance delta between local and production (200ms versus 7000ms)?

MMax (@rozenmd)wrangler dev defaults to local dev, did you execute the schema with --local?

C

codebam•10/11/23, 8:38 PM

oh yeah I used

--local

--local

LLane C Is D1 super slow with multiple joins? I have a simple blog schema. I have less t...

J

jonasb•10/11/23, 9:30 PM

I don't have any perf experience with D1 so take this with a grain of salt. When working on performance with SQLite for my CMS (https://www.dossierhq.dev/) I had similar issues. I believe the root cause is something along the lines of SQLite performing OFFSET/LIMIT at the end, so it ends up joining with those other tables for all rows, not just 10. Unfortunately I can't find any good references right now. But what I ended up doing is a CTE where I only reference the "main" table (

posts

posts

in your case) and do the LIMIT there, and then do the joins in the outer table, if that makes sense.

Dossier

Dossier is an open source toolkit for building headless CMSs

Build solutions where you're in full control of the content. Bring your own auth, database and backend, to build a headless CMS and integrate it with your app.

J

jonasb•10/11/23, 9:50 PM

Like this: https://gist.github.com/jonasb/42c4acab8226b0afd0590124be756878

Gist

Using LIMIT on CTE

Using LIMIT on CTE. GitHub Gist: instantly share code, notes, and snippets.

Jjonasb Like this: https://gist.github.com/jonasb/42c4acab8226b0afd0590124be756878

L

Lane C•10/11/23, 9:53 PM

Thanks will try that. Although I can imagine there could be something off with my query or indexes, it doesn't explain why the query executes in less than 200ms locally and 7 seconds in production against the same-sized dataset...

HHello, I’m Allie!Though you can also add multiple value fields to a single insert

S

Samantas5855•10/11/23, 10:08 PM

How can we do this by the way?

S

Samantas5855•10/11/23, 10:10 PM

For example if we want to perform 10 identical inserts how can this be done without repeating the query with one https request?

I

ilsubyeega•10/12/23, 3:03 AM

do reading from subset of table not counts as rows_read?

For example, if the users table has an index on a timestamp column
created_at
created_at
, the query
SELECT * FROM users WHERE created_at > ?1
SELECT * FROM users WHERE created_at > ?1
SELECT * FROM users WHERE created_at > ?1
SELECT * FROM users WHERE created_at > ?1 would only need to read a subset of the table.

For https://developers.cloudflare.com/d1/platform/pricing/, I still cannot understand how rows read works at this moment. Rows reading measurement only shows example of full table scan, but not others such like partial read (filterable, join etc).

Also, how will db.dump() from client api being calculated as billing, is it just full scan of rows_read?

I

ilsubyeega•10/12/23, 3:05 AM

^ also https://developers.cloudflare.com/workers/wrangler/commands/#backup-download

D

divby0•10/12/23, 5:26 AM

I read that d1 is strongly consistent apart form read replicas. Are there read replicas yet, do I maybe (without knowing) have turned them on? Or can I rely that after a write is done, all reads will include that new data?

D

Dahoom•10/12/23, 6:03 AM

you could probably Jerryrig an inefficient replication system with the cache api

DDahoom you could probably Jerryrig an inefficient replication system with the cache api

D

divby0•10/12/23, 6:07 AM

Yeah but I just have an edge case where it would be comfortable to rely on the strong consistency without checking stuff. But I guess I'll just do the check in case I want to have read replication later on and forget about that part

B

Bangonkali•10/12/23, 7:56 AM

Hi guys just a quick question. has anyone ever succeeded in running a local instance

wrangler pages dev

wrangler pages dev

bound to a local instance of

D1

D1

started up with something like

pnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sql

pnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sql

pnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sql

pnpx wrangler -c wrangler.toml d1 execute DB --local --file=migrations/001.sql ? Just want to make sure this is a supported use case for

D1

D1

in the Beta stage @ the moment. Note: I already have a working

D1

D1

setup with pages on Cloudflare. I'm interesting in running locally. So my question essentially is just if whether this is already supported or not.

BBangonkali Hi guys just a quick question. has anyone ever succeeded in running a local inst...

F

forslunds•10/12/23, 8:28 AM

i have no problems running D1 locally? just have to remember as you are trying that its a seperate db from remote so you need to migrate/seed the local also.

V

Varna•10/12/23, 9:22 AM

I dumped a 300mb dump.sql file and fed it to

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql

pnpx wrangler d1 execute db_name --local --file=./src/dump/dump.sql but it stayed the same for past 1h:

Mapping SQL input into an array of statements

Fforslunds i have no problems running D1 locally? just have to remember as you are trying t...

B

Bangonkali•10/12/23, 9:35 AM

Hi @forslunds , were you able to connect your local cloudflare pages dev instance to your local D1? I'm having issues with binding since my Env.DB is null after migrations. D1 stand-alone is working well in my case.

VVarna I dumped a 300mb dump.sql file and fed it to `pnpx wrangler d1 execute db_name -...

I

itsmatteomanf•10/12/23, 10:06 AM

That tells me you have very long single queries... which are probably gonna fail.

I

itsmatteomanf•10/12/23, 10:07 AM

Can you try and reduce the size of each query or do it in parts?

Iitsmatteomanf Can you try and reduce the size of each query or do it in parts?

V

Varna•10/12/23, 10:24 AM

not sure. I'm trying to figure out if it is possible to migrate MySQL -> D1. I could import only schema, and then setup a background Worker to populate the data during night or smth. But I couldn't manage to run a Worker that could connect to a MySQL db, so I would probably need to put the dump.sql on a R2 first.

V

Varna•10/12/23, 10:29 AM

well, people will need a way to migrate into D1 after release anyways, so I guess this is the chance to find or make an example for such case

Similar Threads