I'm trying to add a column to the database with `alter table` and it fails with `Error code 23: Auth

BBehunin I am getting `Error: D1_ERROR: RangeError: Too many parameter values were provid...

M

Are you using the new backend? Refer to https://developers.cloudflare.com/d1/changelog/#new-default-storage-subsystem for how to check

Changelog · Cloudflare D1 docs

You can now bind a D1 database to your Workers directly in the Cloudflare dashboard . To bind D1 from the Cloudflare dashboard, select your Worker …

Kkika I'm trying to add a column to the database with `alter table` and it fails with ...

M

Matt Silverlock•8/15/23, 12:34 AM

What specific

ALTER TABLE

ALTER TABLE

statement are you trying to run? (Post the query)

Kkika I'm trying to add a column to the database with `alter table` and it fails with ...

M

Matt Silverlock•8/15/23, 12:41 AM

For clarity, all of this works per our tests: https://github.com/cloudflare/workerd/blob/26f0498e303eead759ab4ca9f8767841618eace7/src/workerd/api/sql-test.js#L630-L660

GitHub

workerd/src/workerd/api/sql-test.js at 26f0498e303eead759ab4ca9f876...

The JavaScript / Wasm runtime that powers Cloudflare Workers - cloudflare/workerd

MMatt Silverlock What specific `ALTER TABLE` statement are you trying to run? (Post the query)

K

kikaOP•8/15/23, 12:42 AM

alter table emailsignups add column source TEXT CHECK(source IN ('A','B'));

alter table emailsignups add column source TEXT CHECK(source IN ('A','B'));

K

kikaOP•8/15/23, 12:42 AM

it works with

--local

--local

K

kikaOP•8/15/23, 12:43 AM

assuming the local sqlite engine is the same as "remote"

K

kikaOP•8/15/23, 12:43 AM

very recent, lemme check

K

kikaOP•8/15/23, 12:44 AM

wrangler usually tells me that it's up for update

K

kikaOP•8/15/23, 12:44 AM

3.5.0

K

kikaOP•8/15/23, 12:45 AM

whooops

K

kikaOP•8/15/23, 12:45 AM

so quazi-enums are verboten?

K

kikaOP•8/15/23, 12:46 AM

appreciate it

Ggeelen i happened to be exactly inside that test right now so easy for me to dig

M

Matt Silverlock•8/15/23, 12:50 AM

Probably need to authorize that + allow the associated

PRAGMA ignore_check_constraints

PRAGMA ignore_check_constraints

? https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints
- https://www.sqlite.org/lang_createtable.html#check_constraints

S

Stronk•8/15/23, 7:39 AM

whats the best way to deal with inserting an entry that may have a value or may need to take the default value

S

Stronk•8/15/23, 7:43 AM

sure, so I have this table:

CREATE TABLE lines (
    lineNum INT IDENTITY(1,1) NOT NULL UNIQUE,
    lineCreator INT NOT NULL,
    lineImage TEXT NOT NULL DEFAULT 'https://maps.lineonline.app/line_default.png',
    lineName VARCHAR(100) NOT NULL,
    lineDescription TEXT,
    lineType VARCHAR(100) NOT NULL,
    permanent BOOLEAN NOT NULL,
    lineEndTime DATETIME NOT NULL,
    lineStartTime DATETIME NOT NULL,
    lineInPersonBuffer INT NOT NULL DEFAULT 5,
    -- -1 = auto based on average time per person and adjusted by number of misses
    lineNumberOfRetries INT NOT NULL DEFAULT 3,
    lineTimeoutOfRetryEligibilityMins INT NOT NULL DEFAULT 61,

    PRIMARY KEY (lineNum)
);

CREATE TABLE lines (
    lineNum INT IDENTITY(1,1) NOT NULL UNIQUE,
    lineCreator INT NOT NULL,
    lineImage TEXT NOT NULL DEFAULT 'https://maps.lineonline.app/line_default.png',
    lineName VARCHAR(100) NOT NULL,
    lineDescription TEXT,
    lineType VARCHAR(100) NOT NULL,
    permanent BOOLEAN NOT NULL,
    lineEndTime DATETIME NOT NULL,
    lineStartTime DATETIME NOT NULL,
    lineInPersonBuffer INT NOT NULL DEFAULT 5,
    -- -1 = auto based on average time per person and adjusted by number of misses
    lineNumberOfRetries INT NOT NULL DEFAULT 3,
    lineTimeoutOfRetryEligibilityMins INT NOT NULL DEFAULT 61,

    PRIMARY KEY (lineNum)
);

CREATE TABLE lines (
    lineNum INT IDENTITY(1,1) NOT NULL UNIQUE,
    lineCreator INT NOT NULL,
    lineImage TEXT NOT NULL DEFAULT 'https://maps.lineonline.app/line_default.png',
    lineName VARCHAR(100) NOT NULL,
    lineDescription TEXT,
    lineType VARCHAR(100) NOT NULL,
    permanent BOOLEAN NOT NULL,
    lineEndTime DATETIME NOT NULL,
    lineStartTime DATETIME NOT NULL,
    lineInPersonBuffer INT NOT NULL DEFAULT 5,
    -- -1 = auto based on average time per person and adjusted by number of misses
    lineNumberOfRetries INT NOT NULL DEFAULT 3,
    lineTimeoutOfRetryEligibilityMins INT NOT NULL DEFAULT 61,

    PRIMARY KEY (lineNum)
);

CREATE TABLE lines (
    lineNum INT IDENTITY(1,1) NOT NULL UNIQUE,
    lineCreator INT NOT NULL,
    lineImage TEXT NOT NULL DEFAULT 'https://maps.lineonline.app/line_default.png',
    lineName VARCHAR(100) NOT NULL,
    lineDescription TEXT,
    lineType VARCHAR(100) NOT NULL,
    permanent BOOLEAN NOT NULL,
    lineEndTime DATETIME NOT NULL,
    lineStartTime DATETIME NOT NULL,
    lineInPersonBuffer INT NOT NULL DEFAULT 5,
    -- -1 = auto based on average time per person and adjusted by number of misses
    lineNumberOfRetries INT NOT NULL DEFAULT 3,
    lineTimeoutOfRetryEligibilityMins INT NOT NULL DEFAULT 61,

    PRIMARY KEY (lineNum)
);

S

Stronk•8/15/23, 7:43 AM

and I may or may not want to provide for example lineNumberOfRetries

S

Stronk•8/15/23, 7:44 AM

this is determined by the json a client application sends

S

Stronk•8/15/23, 7:44 AM

for example I can check if I have a field with

insFields.hasOwnProperty('lineImage')

insFields.hasOwnProperty('lineImage')

insFields.hasOwnProperty('lineImage')

S

Stronk•8/15/23, 7:45 AM

this is what I was trying, but it dosent like commas in the bind

const line = await env.DB.prepare("INSERT INTO lines (?1) VALUES (?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)")
                .bind("lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins",
                insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const line = await env.DB.prepare("INSERT INTO lines (?1) VALUES (?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)")
                .bind("lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins",
                insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const line = await env.DB.prepare("INSERT INTO lines (?1) VALUES (?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)")
                .bind("lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins",
                insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const line = await env.DB.prepare("INSERT INTO lines (?1) VALUES (?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)")
                .bind("lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins",
                insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

S

Stronk•8/15/23, 7:46 AM

(if that syntax did work I would need to dynamicaly create the list of vals too)

S

Stronk•8/15/23, 7:49 AM

it was complaining when I sent null

S

Stronk•8/15/23, 7:50 AM

this is a larger context

const defaultVal = null;
            if (!insFields.hasOwnProperty('lineImage')) {
                insFields.lineImage = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineInPersonBuffer')) {
                insFields.lineInPersonBuffer = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineNumberOfRetries')) {
                insFields.lineNumberOfRetries = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineTimeoutOfRetryEligibilityMins')) {
                insFields.lineTimeoutOfRetryEligibilityMins = defaultVal;
            }

            console.log(typeof insFields.permanent.toString());
            const line = await env.DB.prepare("INSERT INTO lines (lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)")
                .bind(insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const defaultVal = null;
            if (!insFields.hasOwnProperty('lineImage')) {
                insFields.lineImage = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineInPersonBuffer')) {
                insFields.lineInPersonBuffer = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineNumberOfRetries')) {
                insFields.lineNumberOfRetries = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineTimeoutOfRetryEligibilityMins')) {
                insFields.lineTimeoutOfRetryEligibilityMins = defaultVal;
            }

            console.log(typeof insFields.permanent.toString());
            const line = await env.DB.prepare("INSERT INTO lines (lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)")
                .bind(insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const defaultVal = null;
            if (!insFields.hasOwnProperty('lineImage')) {
                insFields.lineImage = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineInPersonBuffer')) {
                insFields.lineInPersonBuffer = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineNumberOfRetries')) {
                insFields.lineNumberOfRetries = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineTimeoutOfRetryEligibilityMins')) {
                insFields.lineTimeoutOfRetryEligibilityMins = defaultVal;
            }

            console.log(typeof insFields.permanent.toString());
            const line = await env.DB.prepare("INSERT INTO lines (lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)")
                .bind(insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

const defaultVal = null;
            if (!insFields.hasOwnProperty('lineImage')) {
                insFields.lineImage = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineInPersonBuffer')) {
                insFields.lineInPersonBuffer = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineNumberOfRetries')) {
                insFields.lineNumberOfRetries = defaultVal;
            }
            if (!insFields.hasOwnProperty('lineTimeoutOfRetryEligibilityMins')) {
                insFields.lineTimeoutOfRetryEligibilityMins = defaultVal;
            }

            console.log(typeof insFields.permanent.toString());
            const line = await env.DB.prepare("INSERT INTO lines (lineNum, lineCreator, lineImage, lineName, lineDescription, lineType, permanent, lineEndTime, lineStartTime, lineInPersonBuffer, lineNumberOfRetries, lineTimeoutOfRetryEligibilityMins) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)")
                .bind(insFields.lineNum, insFields.lineCreator, insFields.lineImage, insFields.lineName, insFields.lineDescription, insFields.lineType, insFields.permanent.toString(), insFields.lineEndTime, insFields.lineStartTime, insFields.lineInPersonBuffer, insFields.lineNumberOfRetries, insFields.lineTimeoutOfRetryEligibilityMins).run();

S

Stronk•8/15/23, 7:50 AM

SqliteError: NOT NULL constraint failed: lines.lineInPersonBuffer

SqliteError: NOT NULL constraint failed: lines.lineInPersonBuffer

SqliteError: NOT NULL constraint failed: lines.lineInPersonBuffer

SqliteError: NOT NULL constraint failed: lines.lineInPersonBuffer

S

Stronk•8/15/23, 7:50 AM

thats the error

S

Stronk•8/15/23, 7:50 AM

I thought it would work too

S

Stronk•8/15/23, 8:41 AM

Yeah not to mention I might want to update the defaults during run time

M

mrwind•8/15/23, 8:59 AM

i upgrade from wrangler 3.4.0 to wrangler 3.5.0 and my binding to D1 change from MY_DB to __D1_BETA_MY_DB and that binding only have fetch, connect, put function. It don't have prepare. I use it from pages

TTigersWay Got something similar, happened 3 times in the last 6 hours among ~=1000 SQL ins...

T

TigersWay•8/15/23, 10:09 AM

It happened again today in the middle of 1000s of same kind of inserts, with the last back-end:

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

D1_ERROR: Error: Internal error in D1 storage caused object to be reset.

No code change for more than 24 hours

Ggeelen Update: this doesn't work: ~~I... think if you run `PRAGMA ignore_check_constrai...

K

kikaOP•8/15/23, 2:31 PM

so, until the PR is merged there's no workaround for me to use CHECK in ALTER TABLE, right?

K

kikaOP•8/15/23, 2:31 PM

merged and released

K

kikaOP•8/15/23, 2:33 PM

what I'm wondering: why not use the same defaults for the local sqlite? if it blows when testing locally then there would be less confusion

Kkika so, until the PR is merged there's no workaround for me to use CHECK in ALTER TA...

M

Matt Silverlock•8/15/23, 8:05 PM

Unfortunately not.

Kkika what I'm wondering: why not use the same defaults for the local sqlite? if it bl...

M

Matt Silverlock•8/15/23, 8:05 PM

We should make these work the same. That's on us.

K

kikaOP•8/15/23, 8:07 PM

ok, thanks, no biggie, thanks for jumping on it so promptly. i can live with just a TEXT field without CHECKs

Ggeelen You can create a table with a CHECK constraint, you just can't add a CHECK const...

K

kikaOP•8/15/23, 11:12 PM

yeah, i figured, thanks!

I'm trying to add a column to the database with `alter table` and it fails with `Error code 23: Auth

Similar Threads