you have to insert null for DEFAULT to work

M

Max (@rozenmd)OP•10/11/23, 12:06 PM

(i think)

MMax (@rozenmd)you have to insert null for DEFAULT to work

D

Dudu•10/11/23, 12:08 PM

isn't it what this does on the UI?

D

Dudu•10/11/23, 12:09 PM

I mean the issues I'm having is with the CF ui, didn't try yet on my worker, let me check

D

Dudu•10/11/23, 12:13 PM

Yep it's just UI, works on console

MMax (@rozenmd)D1's REST API doesn't involve a Worker, which is why it's considerably slower

R

roftt•10/11/23, 12:14 PM

Got it, thanks! And if I want rate limiting functionality for my D1 database, I have to use a Worker, correct?

Rroftt Got it, thanks! And if I want rate limiting functionality for my D1 database, I ...

M

Max (@rozenmd)OP•10/11/23, 12:16 PM

Yeah

DDudu I mean the issues I'm having is with the CF ui, didn't try yet on my worker, let...

M

Max (@rozenmd)OP•10/11/23, 12:16 PM

do you have a quick create table statement i can run to repro the issue?

D

Dudu•10/11/23, 12:19 PM

oh wait

D

Dudu•10/11/23, 12:19 PM

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id integer PRIMARY KEY AUTOINCREMENT,
  status text NOT NULL DEFAULT 'active',
  plan text NOT NULL DEFAULT 'basic',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

M

Max (@rozenmd)OP•10/11/23, 12:22 PM

ah I see the bug, in the console you can

insert licenses (id) values (1);

insert licenses (id) values (1);

while the dash ui is trying to run this:

{"sql":"INSERT INTO [licenses] (\"id\", \"status\", \"plan\", \"created_at\", \"expires_at\") VALUES ($0,$1,$2,$3,$4)","params":["2",null,null,null,null]}

{"sql":"INSERT INTO [licenses] (\"id\", \"status\", \"plan\", \"created_at\", \"expires_at\") VALUES ($0,$1,$2,$3,$4)","params":["2",null,null,null,null]}

M

Max (@rozenmd)OP•10/11/23, 12:23 PM

added it to our backlog, cheers

D

Dudu•10/11/23, 12:24 PM

Yeah that makes sense, glad I could help

I

ilsubyeega•10/11/23, 2:50 PM

will indexing some keys mostly occur that row_read_count being one?
such like

SELECT * from test WHERE id=100

SELECT * from test WHERE id=100

returns

"rows_read": 1

"rows_read": 1

from 150-row table at this moment.

I

ilsubyeega•10/11/23, 2:53 PM

also dunno where to feedback, but just wanting to say maybe adding meta data from query in dash.cloudflare.com would be better (currently doesn't show, needed to open devtools and insepct the response)

K

kian•10/11/23, 3:15 PM

If you prefix your query with

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

, you'll see that your index on

id

id

results in

SEARCH

SEARCH

being used which is only scanning a subset of tables, whereas other keys will show

SCAN

SCAN

which is a full table scan.

K

kian•10/11/23, 3:16 PM

K

kian•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

kian•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

K

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

Try removing the

at the end

U

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

that.. worked. thanks

K

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

If the end of the statement isn't

' '

' '

or

'\n'

'\n'

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

K

kian•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

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

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

K

kian•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

?

U

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

no it was with an sql file

K

kian•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

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

K

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

Ahh

K

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

I think I see the issue

K

kian•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

kian•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)OP•10/11/23, 7:40 PM

do your tables have indexes

Kkian Click to see attachment

M

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

this is a good resource to know why queries are slow

K

kian•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);

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)OP•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.

you have to insert null for DEFAULT to work

Similar Threads

Similar Threads

Similar Threads