search

cloudflare / workers-sdk

⛅️ Home to Wrangler, the CLI for Cloudflare Workers®
https://developers.cloudflare.com/workers/
Apache License 2.0
2.7k stars 709 forks source link

🐛 BUG: "wrangler d1 execute dbname --file foo.sql" fails with useless diagnostics on large SQL scripts #4407

Open johnjozwiakmodulus opened 1 year ago

johnjozwiakmodulus commented 1 year ago

Which Cloudflare product(s) does this pertain to?

D1

What version(s) of the tool(s) are you using?

3.15.0

What version of Node are you using?

v21.1.0

What operating system are you using?

Mac

Describe the Bug

I have a valid sqlite3 file, used locally in macos, and from which I generated a SQLite SQL script per cloudflare instructions, with the sqlite3 commandline.

the SQL script is about 21 million lines long. wrangler errors out with pretty useless callstack dump, rather than detecting a problem with the script's length (presumably). if i excerpt the long SQL script, say the first 1000 lines, wrangler d1 execute handles it fine.

Please provide a link to a minimal reproduction

No response

Please provide any relevant error logs

% wrangler d1 execute meepmeep --file /Volumes/ExtremeSSD/EraseMe/ingesteds/ip2location/searchtree.sql FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal 1: 0x102517858 node::Abort() [/opt/homebrew/Cellar/node/21.1.0/bin/node] 2: 0x102518ab8 node::OOMErrorHandler(char const, v8::OOMDetails const&) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 3: 0x10268dbd8 v8::Utils::ReportApiFailure(char const, char const) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 4: 0x102528128 node::fs::ReadFileUtf8(v8::FunctionCallbackInfo const&) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 5: 0x1022a2a38 Builtins_CallApiCallbackGeneric [/opt/homebrew/Cellar/node/21.1.0/bin/node] 6: 0x10a6a72dc 7: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 8: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 9: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 10: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 11: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 12: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 13: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 14: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 15: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 16: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 17: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 18: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 19: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 20: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 21: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 22: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 23: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 24: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 25: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 26: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 27: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 28: 0x1022a0b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 29: 0x10229e8ac Builtins_JSEntryTrampoline [/opt/homebrew/Cellar/node/21.1.0/bin/node] 30: 0x10229e594 Builtins_JSEntry [/opt/homebrew/Cellar/node/21.1.0/bin/node] 31: 0x1027ad9d4 v8::internal::(anonymous namespace)::Invoke(v8::internal::Isolate, v8::internal::(anonymous namespace)::InvokeParams const&) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 32: 0x1027ad3cc v8::internal::Execution::Call(v8::internal::Isolate, v8::internal::Handle, v8::internal::Handle, int, v8::internal::Handle) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 33: 0x1026a187c v8::Function::Call(v8::Local, v8::Local, int, v8::Local) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 34: 0x1024f4294 node::builtins::BuiltinLoader::CompileAndCall(v8::Local, char const, node::Realm) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 35: 0x10258cc48 node::Realm::ExecuteBootstrapper(char const) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 36: 0x1024d5bcc node::StartExecution(node::Environment, char const) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 37: 0x1024d5ae0 node::StartExecution(node::Environment, std::__1::function<v8::MaybeLocal (node::StartExecutionCallbackInfo const&)>) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 38: 0x10243dfcc node::LoadEnvironment(node::Environment, std::__1::function<v8::MaybeLocal (node::StartExecutionCallbackInfo const&)>) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 39: 0x10255fccc node::NodeMainInstance::Run(node::ExitCode, node::Environment) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 40: 0x10255fa68 node::NodeMainInstance::Run() [/opt/homebrew/Cellar/node/21.1.0/bin/node] 41: 0x1024d9a9c node::Start(int, char**) [/opt/homebrew/Cellar/node/21.1.0/bin/node] 42: 0x18ffbd058 start [/usr/lib/dyld]

leachuk commented 6 months ago

This is also occurring for me with wrangler v3.50.0, also MacOS

The file is just over 1GB in size. A smaller version of the file works.

The source file is a converted version of name.basics.tsv.gz (it was imported to sqlite3 locally, then a 'dump' performed to produce the .sql, which was then massaged to work with D2 as documented at https://developers.cloudflare.com/d1/build-with-d1/import-data/#convert-sqlite-database-files)

A workaround for importing large data sets would be much appreciated (assuming such a thing exists)

FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal ----- Native stack trace -----

1: 0x100ddcec0 node::OnFatalError(char const, char const) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 2: 0x100f5c660 v8::Utils::ReportApiFailure(char const, char const) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 3: 0x100debf00 node::fs::ReadFileUtf8(v8::FunctionCallbackInfo const&) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 4: 0x100b5aa38 Builtins_CallApiCallbackGeneric [/opt/homebrew/Cellar/node/21.7.1/bin/node] 5: 0x108f235fc 6: 0x100b58b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.7.1/bin/node] 7: 0x100b58b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.7.1/bin/node] 8: 0x100b58b84 Builtins_InterpreterEntryTrampoline [/opt/homebrew/Cellar/node/21.7.1/bin/node] 9: 0x100b8fb10 Builtins_AsyncFunctionAwaitResolveClosure [/opt/homebrew/Cellar/node/21.7.1/bin/node] 10: 0x100c492d8 Builtins_PromiseFulfillReactionJob [/opt/homebrew/Cellar/node/21.7.1/bin/node] 11: 0x100b7e654 Builtins_RunMicrotasks [/opt/homebrew/Cellar/node/21.7.1/bin/node] 12: 0x100b56794 Builtins_JSRunMicrotasksEntry [/opt/homebrew/Cellar/node/21.7.1/bin/node] 13: 0x10107c1b4 v8::internal::(anonymous namespace)::Invoke(v8::internal::Isolate, v8::internal::(anonymous namespace)::InvokeParams const&) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 14: 0x10107c978 v8::internal::(anonymous namespace)::InvokeWithTryCatch(v8::internal::Isolate, v8::internal::(anonymous namespace)::InvokeParams const&) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 15: 0x10109e028 v8::internal::MicrotaskQueue::RunMicrotasks(v8::internal::Isolate) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 16: 0x10109de60 v8::internal::MicrotaskQueue::PerformCheckpointInternal(v8::Isolate) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 17: 0x100cf0b90 node::InternalCallbackScope::Close() [/opt/homebrew/Cellar/node/21.7.1/bin/node] 18: 0x100cf10e8 node::InternalMakeCallback(node::Environment, v8::Local, v8::Local, v8::Local, int, v8::Local, node::async_context) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 19: 0x100d086b8 node::AsyncWrap::MakeCallback(v8::Local, int, v8::Local) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 20: 0x100de334c node::fs::FSReqCallback::Resolve(v8::Local) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 21: 0x100de4c10 node::fs::AfterNoArgs(uv_fs_s) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 22: 0x100dcba00 node::MakeLibuvRequestCallback<uv_fs_s, void ()(uv_fs_s)>::Wrapper(uv_fs_s) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 23: 0x104422dfc uvwork_done [/opt/homebrew/Cellar/libuv/1.48.0/lib/libuv.1.dylib] 24: 0x1044264a8 uv__async_io [/opt/homebrew/Cellar/libuv/1.48.0/lib/libuv.1.dylib] 25: 0x104436164 uvio_poll [/opt/homebrew/Cellar/libuv/1.48.0/lib/libuv.1.dylib] 26: 0x10442693c uv_run [/opt/homebrew/Cellar/libuv/1.48.0/lib/libuv.1.dylib] 27: 0x100cf196c node::SpinEventLoopInternal(node::Environment) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 28: 0x100e247fc node::NodeMainInstance::Run(node::ExitCode, node::Environment) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 29: 0x100e24550 node::NodeMainInstance::Run() [/opt/homebrew/Cellar/node/21.7.1/bin/node] 30: 0x100d93e40 node::Start(int, char**) [/opt/homebrew/Cellar/node/21.7.1/bin/node] 31: 0x1878ea0e0 start [/usr/lib/dyld]

----- JavaScript stack trace -----

1: readFileSync (node:fs:455:20) 2: readFileSync (/Users/stewart.leach/projects/samples/nextjs-supabase/d1-tutorial/node_modules/wrangler/wrangler-dist/cli.js:119964:16) 3: executeSql (/Users/stewart.leach/projects/samples/nextjs-supabase/d1-tutorial/node_modules/wrangler/wrangler-dist/cli.js:142722:22) 4: Handler (/Users/stewart.leach/projects/samples/nextjs-supabase/d1-tutorial/node_modules/wrangler/wrangler-dist/cli.js:142660:28)

CharlesWiltgen commented 6 months ago

I'm having this issue too (file a bit smaller than 4GB), and would be happy to test and report back if any Cloudflare folks have a beta fix or a workaround to recommend. Does anyone happen to know what the limit is?

leachuk commented 6 months ago

I've been doing some more testing around this and have found additional issues. When the file size is reduced to a point where it can be processed, but is still around 1million lines (so about 100MB in size), I start getting V8 out of memory issues which leads to a program crash. At first glance, and judging by the linear increase of my laptops memory usage (beyond 2GB for the node process) there appears to be a memory leak issue (similar to https://github.com/cloudflare/workers-sdk/issues/2223).

My current workaround involves cutting the singular large .sql file into multiple smaller .sql files using awk, like awk "NR>=$start && NR<=$end" dbfull.sql > dbfull-$i.sql. Then I can load the smaller individual files without issue. I find about 100,000 lines of sql to be fine. I still get an error thrown (workerd/jsg/setup.c++:508: error: CODE_MOVED for unknown code block?) but I've read this can be ignored, and will be removed from the output in a future version.

I'd recommend doing this all in a script, otherwise you'll be dealing with around 100 files for a ~1GB sql file. Mine currently looks like 

#!/bin/zsh

# Get the total number of lines in dbfull.sql
total_lines=$(wc -l < dbfull.sql)

# Calculate the number of iterations required
iterations=$((($total_lines + 99999) / 100000))

for i in {1..$iterations}; do
  start=$((($i - 1) * 100000 + 1))
  end=$((($i * 100000)))
  if [[ $end -gt $total_lines ]]; then
    end=$total_lines
  fi
  awk "NR>=$start && NR<=$end" dbfull.sql > dbfull-$i.sql
  echo "start line: $start, end line: $end. Output file: dbfull-$i.sql"
done

The slowest part was awk, which took about 20-30 seconds per file. I found frawk (https://github.com/ezrosent/frawk/tree/master) and went down the rabbit hole of installing this (it needed cloning and building directly with rust, which had it's own challenges). It's worth it though, as replacing awk with frawk reduced the file processing time from 30sec to ~1sec, per file.

I'm running all this locally now to test how well d1 works in develop mode, before taking the time to run against --remote. Hopefully the experience is better once the data import process kinks are ironed out. I've been kinda surprised it was so painful.