search

jmoiron / sqlx

general purpose extensions to golang's database/sql
http://jmoiron.github.io/sqlx/
MIT License
15.96k stars 1.07k forks source link

Named Bulk Insert: Expected 28556 arguments, got 290700 #825

Open jakoblorz opened 2 years ago

jakoblorz commented 2 years ago

Posting this because maybe I am not seeing something and am seeking help - in one of my (very) write heavy applications writing into Planetscale (MySQL) I get sql: expected 28556 arguments, got 290700. The funny thing is that it works correctly if I don't insert so many rows at once. Is there a know issue when scaling things up, an upper boundary where something breaks?

I am using bulk insert with tx.NamedExec() and passing in a struct-slice (no pointers). The struct has around 15 fields. If there are any more ideas from where this might come from (database/sql, github.com/go-sql-driver/mysql), from Planetscale's limitations (or a missing setting in MySQL), I am open for any suggestions.

silbinarywolf commented 1 year ago

I think the issue is that Planetscale (or maybe even your MySQL driver) has a limit of 28556 arguments and you're going over that limit by batch inserting in 1 NamedExec call.

I've hit something similar about a year ago with PostgreSQL at a different job and so my solution was to limit how many records I inserted per call to NamedExec.

Perhaps batch insert 1000 records at time and make that number adjustable incase you need to lower it later?