🐛 BUG: D1 migrations/executions with multiple statements & quote marks in comments fail obscurely on remote databases

[huw]

Which Cloudflare product(s) does this pertain to?

D1

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

3.22.3 [Wrangler]

What version of Node are you using?

20.9.0

What operating system are you using?

Linux (Debian 12 Bookworm)

Describe the Bug

Create the following SQL file:

-- It's dare
select 1;
select 1;

Call wrangler d1 execute database --file ./test.sql

You’ll see:

🌀 Mapping SQL input into an array of statements
🌀 Parsing 2 statements
🌀 Executing on remote database database (<account_id>):
🌀 To execute on your local development database, pass the --local flag to 'wrangler d1 execute'

✘ [ERROR] A request to the Cloudflare API (/accounts/<account_id>/d1/database/<database_id>/query) failed.

  A prepared SQL statement must contain only one statement. [code: 7500]
  
  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

Execution succeeds if you execute it on --local, or if there’s only one statement, or if you remove the quote mark. This is likely a bug in the multi-statement parser. It’s also just hard to debug, so I wanted to make this issue for people to land at if they experience it in the future.

Please provide a link to a minimal reproduction

No response

Please provide any relevant error logs

No response

[serinuntius]

same issue.

I removed a multi-line comment (/* ~~~~~~~~ */) and it worked.

[AverageHelper]

Also facing this issue. My SQL executes in local but not remote. Literally all I did to fix was remove a 's ending to a word in a -- comment, and now the whole file executes correctly in both environments.

[andrew-pyle]

I've encountered error code 7500 as well. Error was resolved by removing all SQL comments except the first automatically generated comment line.

-- Migration number: 0001 	 2024-03-28T21:27:10.888Z ← ✅ OK
-- ← ❌ FAILS
-- comment here ← ❌ FAILS

$ npx wrangler d1 migrations apply <database> --remote
✘ [ERROR] A prepared SQL statement must contain only one statement. [code: 7500]

It is possible that this is related to D1's serialization method. If newlines are removed naively, part of the valid SQL code could be sucked back onto another line which begins with a -- , like a SQL injection on yourself.

I got this idea because it happened to me when I copied and pasted SQL with comments into the D1 dashboard's console. It transformed the whole SQL command into a single line, and only the first line or two of SQL survived before the rest was commented out.

[Veloxious]

Also facing this issue. My SQL executes in local but not remote. Literally all I did to fix was remove a 's ending to a word in a -- comment, and now the whole file executes correctly in both environments.

Literally all I had to do to fix the same issue. Worked locally but not in remote. Simply removing the 's I had in an inline comment resolved my error. I do not however have any multiline comments.

[jackrdye]

Issue still present, comments in .sql file cause execution to fail. sql parser should be fixed for d1. Plenty of people will require sql scripts.