that is the message from the command ```sqlite3 Test.db ".read testdata.sql"```

SStronk that is the message from the command ```sqlite3 Test.db ".read testdata.sql"```

C

chientrm.com•8/16/23, 7:07 AM

u said it, the bin

sqlite3

sqlite3

sqlite3

sqlite3 detected it, not the sqlite engine itself.

S

StronkOP•8/16/23, 7:07 AM

anyway my actual problem is the ghost in my computer that gives me diffrent results from two .sql files I run through d1 even though they are identical (I checked them in a bindary data viewer)

S

StronkOP•8/16/23, 7:08 AM

one runs fine, the other gives me this error

X [ERROR] The supplied SQL string contains more than one statement

X [ERROR] The supplied SQL string contains more than one statement

X [ERROR] The supplied SQL string contains more than one statement

X [ERROR] The supplied SQL string contains more than one statement

S

StronkOP•8/16/23, 7:11 AM

nvm

S

StronkOP•8/16/23, 7:11 AM

so they arent identical

K

kian•8/16/23, 7:11 AM

At a glance line numbers are only if you pass in a file and D1 (iirc) splits up statements and doesn’t submit a file

S

StronkOP•8/16/23, 7:11 AM

but the only diffrence is various comments

Kkian At a glance line numbers are only if you pass in a file and D1 (iirc) splits up ...

S

StronkOP•8/16/23, 7:12 AM

i dont exactly understand what your saying

K

kian•8/16/23, 7:12 AM

Are you using wrangler d1 execute?

S

StronkOP•8/16/23, 7:12 AM

yeah

K

kian•8/16/23, 7:13 AM

https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/execute.tsx#L289

GitHub

workers-sdk/packages/wrangler/src/d1/execute.tsx at main · cloudfla...

Home to Wrangler, the CLI for Cloudflare Workers® - cloudflare/workers-sdk

K

kian•8/16/23, 7:14 AM

It doesn’t see a file (like your .read example would), it sends the statements in its own batches - so there isn’t really any line number. I suspect it could give you a batch, but then it’d be hard to tell which statement in that batch was the issue

K

kian•8/16/23, 7:16 AM

Not saying it can’t be improved, just that this is how it is currently implemented

S

StronkOP•8/16/23, 7:16 AM

ah

S

StronkOP•8/16/23, 7:16 AM

well it could repeat the line that caused the error in full

S

StronkOP•8/16/23, 7:17 AM

that is good enough to find it

S

StronkOP•8/16/23, 7:17 AM

but its just like some closing paren might have an error somewhere near it lmao

S

StronkOP•8/16/23, 7:17 AM

like thanks that will totaly help me find it

S

StronkOP•8/16/23, 7:18 AM

also this code block was causing the errors I was seeing (that works in sqlite3 localy but not on d1 local or remote)

-- CREATE TRIGGER check_lineValuesAreValidIndexes BEFORE
-- INSERT ON lines FOR EACH ROW BEGIN
-- SELECT CASE
--         WHEN (
--             (
--                 SELECT lineManagerId
--                 FROM lineManagers
--                 WHERE lineManagerId = NEW.lineCreator
--             ) IS NULL
--         ) THEN RAISE(
--             ABORT,
--             'lineCreator not found in lineManagers table'
--         )
--     END;
-- END;

-- CREATE TRIGGER check_lineValuesAreValidIndexes BEFORE
-- INSERT ON lines FOR EACH ROW BEGIN
-- SELECT CASE
--         WHEN (
--             (
--                 SELECT lineManagerId
--                 FROM lineManagers
--                 WHERE lineManagerId = NEW.lineCreator
--             ) IS NULL
--         ) THEN RAISE(
--             ABORT,
--             'lineCreator not found in lineManagers table'
--         )
--     END;
-- END;

-- CREATE TRIGGER check_lineValuesAreValidIndexes BEFORE
-- INSERT ON lines FOR EACH ROW BEGIN
-- SELECT CASE
--         WHEN (
--             (
--                 SELECT lineManagerId
--                 FROM lineManagers
--                 WHERE lineManagerId = NEW.lineCreator
--             ) IS NULL
--         ) THEN RAISE(
--             ABORT,
--             'lineCreator not found in lineManagers table'
--         )
--     END;
-- END;

-- CREATE TRIGGER check_lineValuesAreValidIndexes BEFORE
-- INSERT ON lines FOR EACH ROW BEGIN
-- SELECT CASE
--         WHEN (
--             (
--                 SELECT lineManagerId
--                 FROM lineManagers
--                 WHERE lineManagerId = NEW.lineCreator
--             ) IS NULL
--         ) THEN RAISE(
--             ABORT,
--             'lineCreator not found in lineManagers table'
--         )
--     END;
-- END;

S

StronkOP•8/16/23, 7:18 AM

i dont understand why that would be a problem as its all comented out

S

StronkOP•8/16/23, 7:18 AM

lmao

S

StronkOP•8/16/23, 7:19 AM

I think this is acutaly a problem with d1

SStronk I think this is acutaly a problem with d1

M

Matt Silverlock•8/16/23, 2:41 PM

Please open an issue with a clear reproduction — e.g. a file that we can run, including the commands you run - so we can take a look: https://github.com/cloudflare/workers-sdk/issues/new?assignees=&labels=bug&projects=&template=bug-template.yaml&title=%F0%9F%90%9B+BUG%3A

CCyb3r-Jak3 If you share your account ID and database ID someone can probably take a look wh...

M

Matt Silverlock•8/16/23, 2:42 PM

We're taking a look into some of these cases to understand why some DBs got stuck overnight. If folks have a DB that is NOT responding to queries, reply with your account ID and database ID (these are OK to be shared publicly) to this message.

B

benjamin•8/16/23, 3:48 PM

It appears that triggers are supported (applied several to my tables) but wanted to confirm and see if there is any documentation or potential future considerations (lack of support). Thanks!

Bbenjamin It appears that triggers are supported (applied several to my tables) but wanted...

M

Matt Silverlock•8/16/23, 4:48 PM

No intent to remove support. We just haven’t documented them yet (note: this is for the new backend)

S

StronkOP•8/16/23, 5:48 PM

Are there docs for D1 further than just how the bindings work?

SStronk Are there docs for D1 further than just how the bindings work?

M

Matt Silverlock•8/16/23, 6:12 PM

What are you specifically after?

SStronk Are there docs for D1 further than just how the bindings work?

M

Matt Silverlock•8/16/23, 6:13 PM

Get started: https://developers.cloudflare.com/d1/get-started/
The client API: https://developers.cloudflare.com/d1/platform/client-api/
Indexes, JSON, local dev, debugging, time travel, etc, etc:https://developers.cloudflare.com/d1/learning/

R

Rabid•8/17/23, 3:15 AM

I am super excited about D1, do you envision it basically being a drop in replacement for something like RDS? I know there are drawbacks right now to max DB size and what not, but do you see this evolving to be as powerful as something like RDS or Aurora eventually?

E

Elliot Hesp•8/17/23, 3:47 AM

Just came across this blog post. Are procedures a thing or not? Don't see any reference to them in the docs

E

Elliot Hesp•8/17/23, 3:52 AM

Dang

having constant issues with prismas data proxy, would love to use d1 but without transaction support it seems like a no go

E

Elliot Hesp•8/17/23, 3:54 AM

Yeah that's our usecase unfortunately

E

Elliot Hesp•8/17/23, 4:27 AM

For context, I came across d1 support in drizzle orm, which has a transaction api. However after a search I discovered it'll throw an error if attempting to do that

M

mukhtharcm•8/17/23, 10:22 AM

any one experiencing this?

when I try to run any deletion or table creation command, I'm getting a foreign key constraints failed error

M

mukhtharcm•8/17/23, 10:22 AM

the same command works on local sqlite db

M

mukhtharcm•8/17/23, 10:23 AM

I tried on cli by providing a schema.sql and also from the cf dashbaord

E

erasmodanarni•8/17/23, 10:50 AM

hello! is LIKE supported? I have:

E

erasmodanarni•8/17/23, 10:51 AM

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

E

erasmodanarni•8/17/23, 11:07 AM

Sorry for having asked too quickly: I just found this in the docs... https://developers.cloudflare.com/d1/platform/client-api/#searching-with-like

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

Mmukhtharcm any one experiencing this? when I try to run any deletion or table creation com...

M

Matt Silverlock•8/17/23, 11:59 AM

Can you provide more details, such as the query / DDL you are trying to execute? If you are defining FK relationships that aren’t valid you will get that error.

MMatt Silverlock Can you provide more details, such as the query / DDL you are trying to execute?...

M

mukhtharcm•8/17/23, 12:36 PM

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

This is the query I was trying to run.

M

mukhtharcm•8/17/23, 12:37 PM

I even tried just running only this much

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

M

mukhtharcm•8/17/23, 12:38 PM

still getting the same error
@Matt

Mmukhtharcm I even tried just running only this much ```sql DROP TABLE IF EXISTS user; DROP...

M

Matt Silverlock•8/17/23, 12:38 PM

If those tables exist you can’t drop them if there is a foreign key dependency between them, depending on how you defined the constraint

Mmukhtharcm still getting the same error @Matt

M

Matt Silverlock•8/17/23, 12:39 PM

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

that is the message from the command ```sqlite3 Test.db ".read testdata.sql"```

Similar Threads

Similar Threads

Similar Threads