Can you share your syntax?

MMatt Silverlock Can you share your syntax?

M

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

➜  npx wrangler@d1 d1 execute test-db --file 0000_initial.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 25 statements
🌀 Executing on test-db (c020574a-5623-407b-be0c-cd192bab9545):

M

Matt SilverlockOP•5/8/23, 6:01 PM

File = https://github.com/cloudflare/wildebeest/blob/main/migrations/0000_initial.sql

M

Matt SilverlockOP•5/8/23, 6:02 PM

This all works, and has for a while, so I'm curious what you're doing differently here, or if your schema is too large or has some newline issue

M

Murder Chicken•5/8/23, 6:02 PM

This is the current bit that's breaking it...

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

CREATE TRIGGER 'updated_records_to_changelog' AFTER UPDATE OF 'status' ON 'availabilities'
WHEN OLD.status <> NEW.status
BEGIN
  INSERT INTO changelog (c_id, for_date, status_was, status_is) 
  VALUES (OLD.c_id, OLD.for_date, OLD.status, NEW.status);
END;

M

Murder Chicken•5/8/23, 6:03 PM

Everything else in the file seems to be fine. Removal of this piece allows it to run.

M

Murder Chicken•5/8/23, 6:04 PM

Wrangler 2.19.0

M

Murder Chicken•5/8/23, 6:06 PM

Of further note, this works locally.

M

Murder Chicken•5/8/23, 6:30 PM

@elithrar ok, I figured it out.

M

Murder Chicken•5/8/23, 6:30 PM

I had to add a single space after the

BEGIN

BEGIN

line.

M

Murder Chicken•5/8/23, 6:31 PM

Spaces anywhere else didn't matter (tried it with and without trailing spaces). As soon as I added a single space after the

BEGIN

BEGIN

the statement worked.

M

Murder Chicken•5/8/23, 6:31 PM

So definitely whitespace-related. Just super-hard to determine from the error message.

MMurder Chicken So definitely whitespace-related. Just super-hard to determine from the error m...

M

Matt SilverlockOP•5/8/23, 7:41 PM

We definitely want to improve these over time.

M

Murder Chicken•5/8/23, 7:45 PM

Oof, yeah, debugging where these queries are failing is an exercise in frustration. Thanks for responding to assist though!

M

Murder Chicken•5/8/23, 7:57 PM

Is there any way to get more details on a

[ERROR]   Error: D1_ERROR

[ERROR]   Error: D1_ERROR

that's happening on production but not locally?

M

Murder Chicken•5/8/23, 8:09 PM

Anyone ever encountered something like this:

  (error) Error: Error: Error 9000: exception caught in D1 core. unreachable RuntimeError: unreachable
    at wasm://wasm/00f5753e:wasm-function[4783]:0x31be54
    at wasm://wasm/00f5753e:wasm-function[4836]:0x322be4
    at wasm://wasm/00f5753e:wasm-function[4877]:0x32c7d6
    at wasm://wasm/00f5753e:wasm-function[237]:0x29575
    at wasm://wasm/00f5753e:wasm-function[4973]:0x34351d
    at Object.s2 [as query] (index.js:2418:157)
    at async Sqlite.query (index.js:3205:23)
    at async sqliteQuery (index.js:9409:29)
    at async index.js:9167:22

  (error) Error: Error: Error 9000: exception caught in D1 core. unreachable RuntimeError: unreachable
    at wasm://wasm/00f5753e:wasm-function[4783]:0x31be54
    at wasm://wasm/00f5753e:wasm-function[4836]:0x322be4
    at wasm://wasm/00f5753e:wasm-function[4877]:0x32c7d6
    at wasm://wasm/00f5753e:wasm-function[237]:0x29575
    at wasm://wasm/00f5753e:wasm-function[4973]:0x34351d
    at Object.s2 [as query] (index.js:2418:157)
    at async Sqlite.query (index.js:3205:23)
    at async sqliteQuery (index.js:9409:29)
    at async index.js:9167:22

  (error) Error: Error: Error 9000: exception caught in D1 core. unreachable RuntimeError: unreachable
    at wasm://wasm/00f5753e:wasm-function[4783]:0x31be54
    at wasm://wasm/00f5753e:wasm-function[4836]:0x322be4
    at wasm://wasm/00f5753e:wasm-function[4877]:0x32c7d6
    at wasm://wasm/00f5753e:wasm-function[237]:0x29575
    at wasm://wasm/00f5753e:wasm-function[4973]:0x34351d
    at Object.s2 [as query] (index.js:2418:157)
    at async Sqlite.query (index.js:3205:23)
    at async sqliteQuery (index.js:9409:29)
    at async index.js:9167:22

  (error) Error: Error: Error 9000: exception caught in D1 core. unreachable RuntimeError: unreachable
    at wasm://wasm/00f5753e:wasm-function[4783]:0x31be54
    at wasm://wasm/00f5753e:wasm-function[4836]:0x322be4
    at wasm://wasm/00f5753e:wasm-function[4877]:0x32c7d6
    at wasm://wasm/00f5753e:wasm-function[237]:0x29575
    at wasm://wasm/00f5753e:wasm-function[4973]:0x34351d
    at Object.s2 [as query] (index.js:2418:157)
    at async Sqlite.query (index.js:3205:23)
    at async sqliteQuery (index.js:9409:29)
    at async index.js:9167:22

M

Murder Chicken•5/8/23, 8:12 PM

I might be bumping up against some kind of cap with prepared batch statements. Is there a limit to how many you statements you can batch in one call?

M

Murder Chicken•5/8/23, 8:13 PM

Definitely possible. It's a lot of inserts.

M

Murder Chicken•5/8/23, 8:46 PM

That was it. Batching the batch seemed to get past it.

MMurder Chicken Definitely possible. It's a lot of inserts.

M

Matt SilverlockOP•5/9/23, 12:11 AM

Curiously: how many? How long (in bytes) is each row/statement, and how big were the batches versions?

M

Murder Chicken•5/9/23, 12:23 AM

50k updates. Once I split it into 10k batch batches, it went through.

M

Murder Chicken•5/9/23, 12:24 AM

Small updates.

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

INSERT INTO calendar (c_id, for_date, status) VALUES (?1, ?2, ?3) ON CONFLICT DO UPDATE SET status = excluded.status;'

M

Murder Chicken•5/9/23, 12:24 AM

With bind values of integer, date and ~10 character string, so nothing too substantial.

M

Murder Chicken•5/9/23, 12:26 AM

So, 50k of basically this:

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) VALUES ('122393', '2023-04-03', 'AVAILABLE') ON CONFLICT DO UPDATE SET status = excluded.status;

Ssourabh.sashank Yeah, but for the other table with 44K rows with the similar size of data size w...

M

Matt SilverlockOP•5/9/23, 11:08 AM

It’s not just the number of rows. How big is each row? What indexes are on the table? The issue is likely materializing the results from disk -> running OOM.

Ssourabh.sashank Yeah, but for the other table with 44K rows with the similar size of data size w...

M

Matt SilverlockOP•5/9/23, 11:08 AM

As before: d1-database

MMurder Chicken So, 50k of basically this: `INSERT INTO calendar (c_id, for_date, status) VALUES...

M

Matt SilverlockOP•5/9/23, 11:56 AM

Useful, thanks. Compiling some examples that will work on the new storage engine

MMatt Silverlock Useful, thanks. Compiling some examples that will work on the new storage engine...

M

Murder Chicken•5/9/23, 12:01 PM

Awesome! Looking forward to seeing how this feature progresses. One question. Is the issue the query / bind payload? If so, for my example, is it possible to use more compact syntax through the native binding functionality? Like this:

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

INSERT INTO calendar (c_id, for_date, status) 
VALUES 
  (122393 '2023-04-03', 'AVAILABLE'), 
  (122393 '2023-04-04', 'AVAILABLE'), 
  (122393, '2023-04-05', 'AVAILABLE'), 
  ...
ON CONFLICT DO UPDATE SET status = excluded.status;

M

Murder Chicken•5/9/23, 12:05 PM

After reading through the docs, it didn't seem possible to bind an array of objects/data to statement values like this but I may have missed it. This would definitely reduce the payload of larger inserts/updates.

MMurder Chicken Awesome! Looking forward to seeing how this feature progresses. One question. ...

M

Matt SilverlockOP•5/9/23, 12:08 PM

The Wasm runtime we use now is pretty resource limited - a big single batch of inserts is just a lot of bytes.

MMurder Chicken After reading through the docs, it didn't seem possible to bind an array of obje...

M

Matt SilverlockOP•5/9/23, 12:09 PM

This is a clever way to bind arrays: d1-database

M

Murder Chicken•5/9/23, 12:09 PM

Gotcha. Makes sense. Thanks for your help and responsiveness. Get the warm and fuzzies about D1 and workers so far.

MMatt Silverlock This is a clever way to bind arrays: https://discord.com/channels/59531799019139...

M

Murder Chicken•5/9/23, 12:10 PM

Thanks! I'll dig into that example!

Ssourabh.sashank Here are the error logs with e.cause that we are getting while performing differ...

M

Matt SilverlockOP•5/9/23, 12:36 PM

To re-state: these are expected in the alpha and the prototype that it is. Hold tight

Ssourabh.sashank Here are the error logs with e.cause that we are getting while performing differ...

M

Matt SilverlockOP•5/9/23, 12:37 PM

This is also a public group and so any private data (if any) that sheet = not the place to post it.

D

divby0•5/9/23, 12:52 PM

https://github.com/cloudflare/workers-sdk/issues/3160
Has there been any status on this? I'd not ask this here because there already is an issue open, but someone from the cloudflare team replied and said that it seemed to be the wrong repo for submitting issues there so I don't know where to track this issue or if it's on your radar at all

GitHub

🐛 BUG: when trying to left join 2 tables that have same name column...

Which Cloudflare product(s) does this pertain to? D1 What version of Wrangler are you using? 2.19.0 What operating system are you using? macOS Describe the Bug As per issue #555 we can see, that d1...

Original message was deleted

D

divby0•5/9/23, 1:00 PM

I am using drizzle orm and theoretically the two values should reside in two different keys so they can have the same name. But I don't know the abstraction between drizzle and D1, does d1 always return the joined data as a flat json with only one level of key-value pairs? (in contrast to nested {user: {id: something}} format?

D

divby0•5/9/23, 1:24 PM

Thanks

PPrudhvi We have to insert a dynamic object into the table We don't want to use SQL inj...

M

Matt SilverlockOP•5/10/23, 4:08 PM

https://developers.cloudflare.com/d1/platform/client-api/#parameter-binding

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 …

Ggotfredsen regarding working with D1 build in json functions building a Order managment sho...

M

Matt SilverlockOP•5/10/23, 7:55 PM

That’s really up to you. You can mix and match - core relational data in native SQL types and more fluid data as TEXT columns you use json_* functions over.

Similar Threads

Similar Threads

Similar Threads