Importing a 13MB SQL File
Hi, I'm having trouble importing my database. I'm using `npx wrangler d1 execute --config packages/root/wrangler.toml --e production --remote --file "mydb.sql" mydb-prod-main
While that works locally (with --local), it fails with a 504 Gateway timeout each time I try it... Any idea how to work around that? The .sql file is 13MB.
8 Replies
Unfortunately, I can't split it in multiple files / queries, because there are foreign keys all over the place.
Internet Speed Test - Measure Network Performance | Cloudflare
Test your Internet connection. Check your network performance with our Internet speed test. Powered by Cloudflare's global edge network.
44.5 Mbps down and 13.2 up
I just tried the command again and it complained with "UNIQUE constraint failed". I checked the db in the dashboard and it shows the correct number of rows for each table - seems like all the data was inserted anyways? Currently trying to check whether it is actually all the data.
Interestingly, I tried to do a backup via the backup tab in the dashboard - but I get
Failed to trigger backup. Please try again later.. Inspecting the API response in the dashboard shows: The request is malformed: Only alpha D1 databases support backups. Production databases support time-travel. I guess that makes sense, but why is the Backup tab shown then?That's a bug I'm fixing right this second 😅
Production databases shouldn't see the backup tab
I would try the execute command with
--batch-size=200000 to minimise round trips between wrangler and the APIWould that make a difference? My SQL file comes from a phpMyAdmin export (and is slightly modified). There are only 301 statements in total, because INSERT queries each insert about 150 values.
I'd recommend swapping that to a single insert per row - D1 follows sqlite's High-security Values (https://www.sqlite.org/security.html) as limits to queries and they're easy to hit (though you should've had a detailed error if you did...)
Interesting. I'll try that, though it seems like all the data has been imported correctly.
Thanks for your help!
this is fixed btw