Removal of the trigger from the schema file and it works as expected but I was under the impression

MMurder Chicken Removal of the trigger from the schema file and it works as expected but I was u...

M

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

Can you share your syntax?

MMatt Silverlock Can you share your syntax?

M

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

➜  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 Silverlock•5/8/23, 6:01 PM

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

M

Matt Silverlock•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 ChickenOP•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 ChickenOP•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 ChickenOP•5/8/23, 6:04 PM

Wrangler 2.19.0

M

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

Of further note, this works locally.

M

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

@elithrar ok, I figured it out.

M

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

I had to add a single space after the

BEGIN

BEGIN

BEGIN

BEGIN line.

M

Murder ChickenOP•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

BEGIN

BEGIN the statement worked.

M

Murder ChickenOP•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 Silverlock•5/8/23, 7:41 PM

We definitely want to improve these over time.

M

Murder ChickenOP•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 ChickenOP•5/8/23, 7:57 PM

Is there any way to get more details on a

[ERROR]   Error: D1_ERROR

[ERROR] Error: D1_ERROR

[ERROR]   Error: D1_ERROR

[ERROR] Error: D1_ERROR that's happening on production but not locally?

M

Murder ChickenOP•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 ChickenOP•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 ChickenOP•5/8/23, 8:13 PM

Definitely possible. It's a lot of inserts.

M

Murder ChickenOP•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 Silverlock•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 ChickenOP•5/9/23, 12:23 AM

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

M

Murder ChickenOP•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;'

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 ChickenOP•5/9/23, 12:24 AM

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

M

Murder ChickenOP•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;

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 Silverlock•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 Silverlock•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 Silverlock•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 ChickenOP•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 ChickenOP•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 Silverlock•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 Silverlock•5/9/23, 12:09 PM

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

M

Murder ChickenOP•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 ChickenOP•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 Silverlock•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 Silverlock•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 Silverlock•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 Silverlock•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.

Removal of the trigger from the schema file and it works as expected but I was under the impression

Similar Threads