Transaction not working in D1 execute --file command

S

SQQ•1/29/23, 6:54 PM

Given the sql file:

BEGIN TRANSACTION;

DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
CREATE INDEX IF NOT EXISTS ...;
CREATE UNIQUE INDEX IF NOT EXISTS ...;

COMMIT;

BEGIN TRANSACTION;

DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
CREATE INDEX IF NOT EXISTS ...;
CREATE UNIQUE INDEX IF NOT EXISTS ...;

COMMIT;

BEGIN TRANSACTION;

DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
CREATE INDEX IF NOT EXISTS ...;
CREATE UNIQUE INDEX IF NOT EXISTS ...;

COMMIT;

BEGIN TRANSACTION;

DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
DROP TABLE IF EXISTS ...;
CREATE TABLE IF NOT EXISTS ...;
CREATE INDEX IF NOT EXISTS ...;
CREATE UNIQUE INDEX IF NOT EXISTS ...;

COMMIT;

If I run the command

npx wrangler d1 execute ACCOUNT_D1_TEST --file ./schema.sql

npx wrangler d1 execute ACCOUNT_D1_TEST --file ./schema.sql

npx wrangler d1 execute ACCOUNT_D1_TEST --file ./schema.sql

npx wrangler d1 execute ACCOUNT_D1_TEST --file ./schema.sql

I get the result:

🌀 Mapping SQL input into an array of statements
🌀 Parsing 8 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 1 command in 0.02170097827911377ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 8 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 1 command in 0.02170097827911377ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 8 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 1 command in 0.02170097827911377ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 8 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 1 command in 0.02170097827911377ms

But if I remove the

BEGIN TRANSACTION

BEGIN TRANSACTION

BEGIN TRANSACTION

BEGIN TRANSACTION and

COMMIT

COMMIT

COMMIT

COMMIT commands I get:

🌀 Mapping SQL input into an array of statements
🌀 Parsing 6 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 6 commands in 31.02824902534485ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 6 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 6 commands in 31.02824902534485ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 6 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 6 commands in 31.02824902534485ms

🌀 Mapping SQL input into an array of statements
🌀 Parsing 6 statements
🌀 Executing on ACCOUNT_D1_TEST (...):
🚣 Executed 6 commands in 31.02824902534485ms

Are transactions not allowed in the

execute

execute

execute

execute command?

S

SQQ•1/29/23, 7:04 PM

It seems to abort on the

BEGIN TRANSACTION

BEGIN TRANSACTION

BEGIN TRANSACTION

BEGIN TRANSACTION command.

S

SQQ•1/29/23, 9:54 PM

I'm not sure what the

executed 1 command

executed 1 command

executed 1 command

executed 1 command results in, but it doesn't create the tables or indexes. The

executed 6 commands

executed 6 commands

executed 6 commands

executed 6 commands i.e no transaction/commit does. It would be nice to see if the first one produced an error.

Transaction not working in D1 execute --file command

Similar Threads

Similar Threads

Similar Threads