Are you able to provide a sample schema that reproduces the issue? last time we tried, we couldn't r

MMax (@rozenmd)Are you able to provide a sample schema that reproduces the issue? last time we ...

L

luna•7/8/24, 11:50 AM

yes and i worked out the issue. i’ll upload a code example in a bit.

MMax (@rozenmd)Are you able to provide a sample schema that reproduces the issue? last time we ...

L

luna•7/8/24, 1:33 PM

CREATE TABLE
    user (
        id TEXT NOT NULL PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );

CREATE TABLE
    session (
        id TEXT NOT NULL PRIMARY KEY,
        expires_at INTEGER NOT NULL,
        user_id TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES user (id)
    );

CREATE TABLE
    user (
        id TEXT NOT NULL PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );

CREATE TABLE
    session (
        id TEXT NOT NULL PRIMARY KEY,
        expires_at INTEGER NOT NULL,
        user_id TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES user (id)
    );

CREATE TABLE
    user (
        id TEXT NOT NULL PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );

CREATE TABLE
    session (
        id TEXT NOT NULL PRIMARY KEY,
        expires_at INTEGER NOT NULL,
        user_id TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES user (id)
    );

CREATE TABLE
    user (
        id TEXT NOT NULL PRIMARY KEY,
        username TEXT NOT NULL,
        email TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
    );

CREATE TABLE
    session (
        id TEXT NOT NULL PRIMARY KEY,
        expires_at INTEGER NOT NULL,
        user_id TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES user (id)
    );

i used the above schema and created users + sessions in my app. i then tried deleting both tables which wouldnt work. i had to delete the sessions table first and then the users.
would be nice if the error at least said which table caused the issue.

Lluna ```sql CREATE TABLE user ( id TEXT NOT NULL PRIMARY KEY, use...

S

SteffTek•7/8/24, 1:39 PM

sounds like regular sqlite

S

SteffTek•7/8/24, 1:40 PM

if you are using raw sql and not an ORM (like a mad man) you can start a transaction and set the foreign key pragma to OFF, then delete your stuff, and then turn the pragma back on

SSteffTek if you are using raw sql and not an ORM (like a mad man) you can start a transac...

L

luna•7/8/24, 1:49 PM

you cant do that with the dashboard and again "would be nice if the error at least said which table caused the issue."

S

SteffTek•7/8/24, 1:49 PM

Technically you can

L

luna•7/8/24, 1:49 PM

okay but im saying this is an issue. that response doesnt really help me.

S

SteffTek•7/8/24, 1:50 PM

Yeah like I said it's an sqlite problem not cloudflare

L

luna•7/8/24, 1:50 PM

okay..

SSteffTek Yeah like I said it's an sqlite problem not cloudflare

L

luna•7/8/24, 1:51 PM

a bit of advice. this isnt helpful at all.

S

SteffTek•7/8/24, 1:55 PM

Yeah I'm not support staff so tbh I don't really care if you find my advice helpful. I just tried to tell you cloudflare cannot really do much here

L

luna•7/8/24, 1:55 PM

incorrect. they can fix the error

next time just dont comment if you have nothing to add.

S

SteffTek•7/8/24, 2:01 PM

:lul:

FFinn 🔜 39C3 no i have to give stefftek a point here, you are referencing a user in a session...

L

luna•7/8/24, 2:09 PM

no it is not. this is a bug in the ui. the ui should be surfacing the full error which includes the table name.

im not posting here asking for help. i was asked by someone that works at cloudflare to reply. i really wish people would stop trying to but into things.

FFinn 🔜 39C3 (as far as i understand the problem at hand)

L

luna•7/8/24, 2:10 PM

no one asked you to. d1-database

S

SteffTek•7/8/24, 2:11 PM

It isn't even an error , it's by design you can't delete a referenced table entry

S

SteffTek•7/8/24, 2:11 PM

Lol

L

luna•7/8/24, 2:13 PM

... i didnt ask for help.

L

luna•7/8/24, 2:13 PM

please read before commenting it really helps.

K

kian•7/8/24, 2:14 PM

This isn't a place to discuss the merits of a given piece of feedback, please stop. The team are always interested in how to improve error messaging, like we've done before for syntax errors.

L

luna•7/8/24, 2:15 PM

exactly my point...

K

kian•7/8/24, 2:15 PM

Some SQLite errors are only enriched with information available in a shell (like when you run

sqlite3

sqlite3

sqlite3

sqlite3 locally) and the actual 'library' itself used by embedders doesn't show that.

L

luna•7/8/24, 2:15 PM

as a user this makes things harder. cloudflare tends to fix those types of issues. hence me posting about it.

L

luna•7/8/24, 2:16 PM

seems either it should detect this and delete them in the right order or at least tell me which table in the selection i picked that caused it.

K

kian•7/8/24, 2:16 PM

Yup, and we've done it before: https://github.com/cloudflare/workerd/pull/1392

K

kian•7/8/24, 2:17 PM

I have no idea if this particular error/scenario can be improved (technically), but the team can log it as a feature request/improvement nonetheless.

L

luna•7/8/24, 2:18 PM

plus at least if someone searches for that same error theyll hit this chat and hopefully realise the issue.

MMax (@rozenmd)?

O

Old Man Maker•7/8/24, 3:51 PM

Might be a Terminal or PowerShell issue? Had to rename the table to get info

Lluna ```sql CREATE TABLE user ( id TEXT NOT NULL PRIMARY KEY, use...

M

Mayuresh Choudhary•7/9/24, 3:38 AM

It's because you've added foreign key constraints.

In SQL it's a basic thing, you need to delete all the dependent tables (tables which has foreign keys) and then you need to delete the independent table.

In your case session is the dependent table and user is the independent table.
So deleting user table and then session table would result into an error.

Ik sometimes error messages don't make sense but yeah this is the reason you got the error

MMayuresh Choudhary It's because you've added foreign key constraints. In SQL it's a basic thing, y...

L

luna•7/9/24, 3:54 AM

please read before replying. d1-database

Lluna please read before replying. https://discord.com/channels/595317990191398933/992...

M

Mayuresh Choudhary•7/9/24, 4:00 AM

I'm not giving any feedback or discussing the merits of it, i am just highlighting the reason behind it to educate, so that others facing similar issue would get what to do.

L

luna•7/9/24, 4:02 AM

i already know the issue. as stated right from the beginning. please stop trying to add to an already finished conversation. a mod has already said this.

L

luna•7/9/24, 4:02 AM

you’re now the third person todo this.

C

Codename_A•7/9/24, 5:58 AM

Excecuting remotely is giving me the error

X [ERROR] not authorized

X [ERROR] not authorized

X [ERROR] not authorized

X [ERROR] not authorized

C

Codename_A•7/9/24, 5:59 AM

The log

wrangler-2024-07-09_05-59-26_126.log4.96KB

C

Codename_A•7/9/24, 6:02 AM

Could it be a malformed sql file somehow?

C

Codename_A•7/9/24, 6:03 AM

here is the start of the sqlite file:

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;

#=====================================================================
#
#  Quran Translation
#  Name: Pickthall
#  Translator: Mohammed Marmaduke William Pickthall
#  Language: English
#  ID: en.pickthall
#  Last Update: September 4, 2010
#  Source: Tanzil.net
#
#=====================================================================
# CREATE DATABASE IF NOT EXISTS `quran`
# USE `quran`;
DROP TABLE IF EXISTS `en_pickthall`;

CREATE TABLE `en_pickthall` (
`index` INTEGER NOT NULL ,
`sura` INTEGER NOT NULL default '0',
`aya` INTEGER NOT NULL default '0',
`text` text NOT NULL,
PRIMARY KEY  (`index`)
);
INSERT INTO `en_pickthall` (`index`, `sura`, `aya`, `text`) VALUES
(1, 1, 1, 'In the name of Allah, the Beneficent, the Merciful.'),
(2, 1, 2, 'Praise be to Allah, Lord of the Worlds,'),
(3, 1, 3, 'The Beneficent, the Merciful.'),
(4, 1, 4, 'Master of the Day of Judgment,'),
(5, 1, 5, 'Thee (alone) we worship; Thee (alone) we ask for help.'),
(6, 1, 6, 'Show us the straight path,'),

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;

#=====================================================================
#
#  Quran Translation
#  Name: Pickthall
#  Translator: Mohammed Marmaduke William Pickthall
#  Language: English
#  ID: en.pickthall
#  Last Update: September 4, 2010
#  Source: Tanzil.net
#
#=====================================================================
# CREATE DATABASE IF NOT EXISTS `quran`
# USE `quran`;
DROP TABLE IF EXISTS `en_pickthall`;

CREATE TABLE `en_pickthall` (
`index` INTEGER NOT NULL ,
`sura` INTEGER NOT NULL default '0',
`aya` INTEGER NOT NULL default '0',
`text` text NOT NULL,
PRIMARY KEY  (`index`)
);
INSERT INTO `en_pickthall` (`index`, `sura`, `aya`, `text`) VALUES
(1, 1, 1, 'In the name of Allah, the Beneficent, the Merciful.'),
(2, 1, 2, 'Praise be to Allah, Lord of the Worlds,'),
(3, 1, 3, 'The Beneficent, the Merciful.'),
(4, 1, 4, 'Master of the Day of Judgment,'),
(5, 1, 5, 'Thee (alone) we worship; Thee (alone) we ask for help.'),
(6, 1, 6, 'Show us the straight path,'),

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;

#=====================================================================
#
#  Quran Translation
#  Name: Pickthall
#  Translator: Mohammed Marmaduke William Pickthall
#  Language: English
#  ID: en.pickthall
#  Last Update: September 4, 2010
#  Source: Tanzil.net
#
#=====================================================================
# CREATE DATABASE IF NOT EXISTS `quran`
# USE `quran`;
DROP TABLE IF EXISTS `en_pickthall`;

CREATE TABLE `en_pickthall` (
`index` INTEGER NOT NULL ,
`sura` INTEGER NOT NULL default '0',
`aya` INTEGER NOT NULL default '0',
`text` text NOT NULL,
PRIMARY KEY  (`index`)
);
INSERT INTO `en_pickthall` (`index`, `sura`, `aya`, `text`) VALUES
(1, 1, 1, 'In the name of Allah, the Beneficent, the Merciful.'),
(2, 1, 2, 'Praise be to Allah, Lord of the Worlds,'),
(3, 1, 3, 'The Beneficent, the Merciful.'),
(4, 1, 4, 'Master of the Day of Judgment,'),
(5, 1, 5, 'Thee (alone) we worship; Thee (alone) we ask for help.'),
(6, 1, 6, 'Show us the straight path,'),

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;

#=====================================================================
#
#  Quran Translation
#  Name: Pickthall
#  Translator: Mohammed Marmaduke William Pickthall
#  Language: English
#  ID: en.pickthall
#  Last Update: September 4, 2010
#  Source: Tanzil.net
#
#=====================================================================
# CREATE DATABASE IF NOT EXISTS `quran`
# USE `quran`;
DROP TABLE IF EXISTS `en_pickthall`;

CREATE TABLE `en_pickthall` (
`index` INTEGER NOT NULL ,
`sura` INTEGER NOT NULL default '0',
`aya` INTEGER NOT NULL default '0',
`text` text NOT NULL,
PRIMARY KEY  (`index`)
);
INSERT INTO `en_pickthall` (`index`, `sura`, `aya`, `text`) VALUES
(1, 1, 1, 'In the name of Allah, the Beneficent, the Merciful.'),
(2, 1, 2, 'Praise be to Allah, Lord of the Worlds,'),
(3, 1, 3, 'The Beneficent, the Merciful.'),
(4, 1, 4, 'Master of the Day of Judgment,'),
(5, 1, 5, 'Thee (alone) we worship; Thee (alone) we ask for help.'),
(6, 1, 6, 'Show us the straight path,'),

D

Disciple of Caoximeihao•7/9/24, 8:05 AM

can i use d1 and not create worker is it possible?

DDisciple of Caoximeihao can i use d1 and not create worker is it possible?

H

harshil1712•7/9/24, 9:47 AM

You want to use D1 outside of a worker/pages project? Did I understand it correctly?

CCodename_A here is the start of the sqlite file: ``` PRAGMA journal_mode = MEMORY; PRAGMA s...

M

Max (@rozenmd)OP•7/9/24, 11:45 AM

remove the PRAGMAs and comment block, should work

DDisciple of Caoximeihao can i use d1 and not create worker is it possible?

M

Max (@rozenmd)OP•7/9/24, 11:45 AM

Possible yes, but not performant

AAskar MrMirql Hi Guys! what do you think? is d1 database is capable of working with highload o...

M

Max (@rozenmd)OP•7/9/24, 11:51 AM

At the moment each D1 db depends on a single DO thread, meaning you can run into performance issues at around 500-800 requests per second, depending on the query (faster queries = more performance)

AAskar MrMirql thanks! what about concurrent operations, race conditions, transactions? i think...

M

Max (@rozenmd)OP•7/9/24, 11:56 AM

D1's storage implementation uses WAL mode

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

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

you may also want to read https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#batch-statements

S

Sheha•7/9/24, 1:48 PM

Just asking. Whats the size limit of a single D1 database ? is it 5 Gigs or 500 MB ?

Are you able to provide a sample schema that reproduces the issue? last time we tried, we couldn't r

Similar Threads