ok lets me try

MMrXO ok lets me try

I

itsmatteomanf•10/11/23, 9:06 AM

Be careful with SQL injections...

M

MrXOOP•10/11/23, 9:06 AM

M

MrXOOP•10/11/23, 9:06 AM

sure

PPaolo did you migrate the local DB?

A

andyt•10/11/23, 9:33 AM

no, I have created the same table on both local and remote

A

andyt•10/11/23, 9:41 AM

also when I create a migration it is empty

P

Paolo•10/11/23, 9:45 AM

well, you can seed the migration if you want

P

Paolo•10/11/23, 9:45 AM

beside

Aandyt no, I have created the same table on both local and remote

P

Paolo•10/11/23, 9:46 AM

what does

wrangler d1 execute DB --local --command "select * from company"

wrangler d1 execute DB --local --command "select * from company"

return?

A

andyt•10/11/23, 9:46 AM

the row i inserted

Aandyt the row i inserted

P

Paolo•10/11/23, 9:47 AM

can I have a look at the toml file? also, are you using pages or workers?

A

andyt•10/11/23, 10:04 AM

Looking at my db on the web site, it says I have two tables but clearly only have 1

Aandyt Looking at my db on the web site, it says I have two tables but clearly only hav...

P

Paolo•10/11/23, 10:24 AM

that is also a bug I found. it mysteriously vanished on its own after a while

G

GuillaumeLakano•10/11/23, 10:29 AM

Hi! With the future system of dynamic binding ( without explicit name in wrangler.toml ), will you permit us to connect only with the name of the database, or this will be only with the UUID value ?
I need to known if I need to store somewhere (KV, DO, D1...) an association table between the databases names and their UUID.

R

roftt•10/11/23, 11:50 AM

When I call D1's REST API directly like: https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query
Do I automatically trigger a Worker call? So the pricing counts as Worker request + D1 rows read?

Rroftt When I call D1's REST API directly like: https://api.cloudflare.com/client/v4 /a...

M

Max (@rozenmd)•10/11/23, 11:52 AM

D1's REST API doesn't involve a Worker, which is why it's considerably slower

M

Max (@rozenmd)•10/11/23, 11:52 AM

you just pay for the rows read/written

D

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

Can we not set default values for a certain column?

D

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

I have this

status text NOT NULL DEFAULT 'active'

status text NOT NULL DEFAULT 'active'

assuming that the status would default to active but it's not working, I could swear this worked in the past but I'm having issues right now

DDudu I have this `status text NOT NULL DEFAULT 'active'` assuming that the status wou...

M

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

you have to insert null for DEFAULT to work

M

Max (@rozenmd)•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)•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)•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)•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)•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

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'

'\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

wrangler d1 execute ... --command

wrangler d1 execute ... --command?

Similar Threads