I've reported and added a little more information, specifically that commenting out the wildcard rou

I've reported and added a little more information, specifically that commenting out the wildcard route definitions makes it work as expected locally, but those are required for production.

https://github.com/cloudflare/workers-sdk/issues/3635

GitHub

🐛 BUG: reported url differs between production and local · Issue #3...

Which Cloudflare product(s) does this pertain to? Wrangler core What version(s) of the tool(s) are you using? 3.3.0 What version of Node are you using? No response What operating system are you usi...

Ccdslash I've reported and added a little more information, specifically that commenting ...

Max (@rozenmd)•7/19/23, 11:54 AM

cheers - btw #wrangler might be a better place to ask folks if they've run into this issue

MMax (@rozenmd)cheers - btw #wrangler might be a better place to ask folks if they've run into ...

cdslashOP•7/19/23, 12:08 PM

Great, thanks for the tip

MMax (@rozenmd)cheers - btw #wrangler might be a better place to ask folks if they've run into ...

cdslashOP•7/19/23, 12:42 PM

Only just realised I posted this in the D1 channel - sorry! Must've hit the wrong tab when aiming for wrangler (immediately above it)

Original message was deleted

Max (@rozenmd)•7/19/23, 1:58 PM

nah the idea is to keep them around so you can setup the project from scratch

Max (@rozenmd)•7/19/23, 1:59 PM

d1 keeps track of what you're already applied, but if you made a new db you could apply all migrations from the start and have a working copy

Max (@rozenmd)•7/19/23, 1:59 PM

yep

TTraffy I my migrating my CMS to edge platform. I exported data from mongo and it turns ...

Cyb3r-Jak3•7/19/23, 2:54 PM

They announced in a blog post that paid plan will be getting 1GB max. Only ETA is soontm

Nnexx same question, D1 doesn't work with this ``` insert into oauth (UserID, IsCool) ...

Matt Silverlock•7/19/23, 3:49 PM

ON DUPLICATE KEYON DUPLICATE KEY isn’t supported by D1’s query engine (SQLite) - use UPSERTUPSERT or INSERT OR IGNOREINSERT OR IGNORE instead: https://stackoverflow.com/a/4330694/556573

Stack Overflow

UPSERT *not* INSERT or REPLACE

http://en.wikipedia.org/wiki/Upsert
Insert Update stored proc on SQL Server
Is there some clever way to do this in SQLite that I have not thought of?
Basically I want to update three out of four co...

Ashley•7/19/23, 6:22 PM

Quick question: when applying migrations to production databases, what are the most common ones that cause (or is there a list) requests to be held while the migration applies? I know the backup is one, but for example, are ALTER TABLEALTER TABLE likely to also cause requests to be held while it applies?

Ashley•7/19/23, 6:22 PM

And presumably things like creating new tables and inserting data won't cause such problems

Ashley•7/19/23, 6:23 PM

And similarly to alter table, adding indexes would possibly be one?

TTraffy I want to migrate my data to d1. I exported my mongo collection with 37000 docum...

Cyb3r-Jak3•7/19/23, 6:48 PM

D1 maxes out at 100 MB

Chaika•7/19/23, 6:53 PM

I've hit that before, it's just wayy too much for wrangler to handle/split properly, and even if you get past it, my experience was trying to insert any mass amount of data (like 20-40 MB in my case, was large rows) to the new experimental backend would just fail with a d1 error. Hopefully will improve/make it easier to import lots of data eventually, but it's still alpha

kian•7/19/23, 9:43 PM

table_listtable_list, table_infotable_info and table_xinfotable_xinfo work

kian•7/19/23, 9:44 PM

No other pragmas are currently allowed, but if you have a valid use-case for any others then open an issue on https://github.com/cloudflare/workerd/issues/new/choose for it to be considered.

kian•7/19/23, 9:47 PM

Sure - I'm not saying there's any reason the ones you listed shouldn't be allowed, I don't represent the Workers team. They just haven't currently allowed them, so that's why you get that error.

Original message was deleted

Lucian•7/20/23, 1:12 AM

I think it is the dashboard. I had a similar issue with a CURRENT_TIMESTAMP DEFAULT value. When in code worked fine (the proper date was inserted) but when adding records in dashboard I got the text 'CURRENT_TIMESTAMP '

micromashor•7/20/23, 6:31 AM

Bug report: the dashboard table viewer munches tables which have generated columns
to reproduce:

CREATE TABLE example (
    fruit TEXT,
    fruit_but_lowercase TEXT GENERATED ALWAYS AS (lower(fruit)) VIRTUAL,
    vegetable TEXT
);

INSERT INTO example (fruit, vegetable) VALUES
    ('Apples', 'Carrots'),
    ('Bananas', 'Broccoli');

CREATE TABLE example (
    fruit TEXT,
    fruit_but_lowercase TEXT GENERATED ALWAYS AS (lower(fruit)) VIRTUAL,
    vegetable TEXT
);

INSERT INTO example (fruit, vegetable) VALUES
    ('Apples', 'Carrots'),
    ('Bananas', 'Broccoli');

micromashor•7/20/23, 6:32 AM

if you run SELECT * FROM exampleSELECT * FROM example with wrangler or the dashboard console, it works as expected, but the table viewer shows this:

micromashor•7/20/23, 6:39 AM

99% sure this is happening because the dashboard appears to use the table_infotable_info pragma to discover the structure of the table, when, for historical reasons, that pragma doesn't show generated columns - table_xinfotable_xinfo must be used instead. (see https://www.sqlite.org/gencol.html 2.3 #9)

LLucian I think it is the dashboard. I had a similar issue with a CURRENT_TIMESTAMP DEFA...

Max (@rozenmd)•7/20/23, 10:08 AM

yeah, dashboard bug - default values render weirdly

Mmicromashor 99% sure this is happening because the dashboard appears to use the `table_info`...

Max (@rozenmd)•7/20/23, 10:10 AM

cheers for the bug report + the fix MeowHeartCloudflare

micromashor•7/20/23, 4:42 PM

I'm having trouble getting foreign key constraints to work. I know with SQLite you need to run PRAGMA foreign_keys=onPRAGMA foreign_keys=on on every connection, but this seems to always be a no-op. Anyone else run into this before?

Mmicromashor I'm having trouble getting foreign key constraints to work. I know with SQLite y...

Lucian•7/20/23, 5:31 PM

They worked for me. What makes you think they didn't work for you and how were they created? I think in sqlite you can only create them within CREATE TABLE and not with ALTER TABLE ADD CONSTRAINT .

LLucian They worked for me. What makes you think they didn't work for you and how were t...

micromashor•7/20/23, 5:38 PM

Yes, all of them were created within the CREATE TABLE statements. But the DB happily accepts garbage data which should be caught by foreign key constraints. Like I said, I couldn't even get the pragma turned on to begin with.

Mmicromashor Yes, all of them were created within the CREATE TABLE statements. But the DB hap...

Lucian•7/20/23, 5:58 PM

Hmm, to be precise the foreign key constraints screamed at me when trying to drop and recreate a table (referenced by ) but I never really tried to add data violating the constraints. So my anecdotal example might not be relevant to you

LLucian Hmm, to be precise the foreign key constraints screamed at me when trying to dro...

micromashor•7/20/23, 6:14 PM

Were you using the alpha backend? One difference I just found is that for some reason, the alpha backend defaults to foreign_keys=1foreign_keys=1, while the beta backend defaults to foreign_keys=0foreign_keys=0.

micromashor•7/20/23, 6:26 PM

actually that means that foreign keys work exactly as intended on the alpha backend, which means that the only thing left to figure out is how to set foreign_keys=1foreign_keys=1 on the beta backend. It's tricky because that's per-SQLite connection, but the D1 API doesn't have a concept of a connection

Mmicromashor Were you using the alpha backend? One difference I just found is that for some r...

Lucian•7/20/23, 6:40 PM

Yes, I was on the alpha backend

Mmicromashor actually that means that foreign keys work exactly as intended on the alpha back...

Lucian•7/20/23, 6:42 PM

I wonder if you can use a db.batch in your worker and have your pragma foreign key as the first statement

LLucian I wonder if you can use a db.batch in your worker and have your pragma foreign k...

micromashor•7/20/23, 7:21 PM

I was going to try a workaround like that, but it's still not ideal because I can't use prepared statements that way

micromashor•7/20/23, 7:22 PM

CF must have somehow set foreign_key default on for the alpha backend, so it'd be really nice if they could expose an option to allow that same behavior for the beta backend

micromashor•7/20/23, 7:23 PM

If I have some extra time this afternoon I might file an issue on the github repo

kian•7/20/23, 7:43 PM

foreign key default is set on the experimental backend meowthinking

kian•7/20/23, 7:44 PM

or it might be soon

kian•7/20/23, 7:44 PM

it was committed to workerd 2 weeks ago so it'll be in the next release if it hasn't already released

Kkian foreign key default is set on the experimental backend <:meowthinking:3506809418...

Matt Silverlock•7/20/23, 8:15 PM

Right, for the experimental backend:

all queries run inside a transaction
You can’t enable foreign keys via the PRAGMA inside that context
We are making foreign key enforcement the default
You can relax it with PRAGMA defer_foreign_keys = onPRAGMA defer_foreign_keys = on
Docs coming very very soon

MMatt Silverlock Right, for the experimental backend: 1. all queries run inside a transaction 2....

micromashor•7/20/23, 8:17 PM

Thank you! I'm glad this is on the radar and coming soon.

Mmicromashor Thank you! I'm glad this is on the radar and coming soon.

Matt Silverlock•7/20/23, 8:39 PM

WIP docs: https://silverlock-d1-foreign-keys.cloudflare-docs-7ou.pages.dev/d1/learning/foreign-keys/

Need to write up FK actions, add basic schema examples (how to define fkeys between tables), and how to handle errors/resolve FK issues - before I publish it proper.

I've reported and added a little more information, specifically that commenting out the wildcard rou

Similar Threads

Similar Threads

Similar Threads