Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Laravel bulk insert fails with 'too many placeholders'

ErrorSQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders

Short answer

Laravel sends one placeholder per value, so one insert of many rows times many columns, or a whereIn with a huge list, creates a statement MySQL refuses. Split the work with collect($rows)->chunk(500) and run one insert per chunk. Use upsert() if rows may already exist.

  • Applies to Laravel 12, PHP 8.4, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Work out how many placeholders your query makes: rows times columns for an insert, or the length of the list for whereIn. Log that number before the query.

    $rows = count($data) * count($data[0]);
    logger()->info("placeholders: {$rows}");
  2. 2Match what you see, then jump to its fix

    1. 1The failing call is DB::table(...)->insert($bigArray) or Model::insert($bigArray).One insert() call carries every rowFix
    2. 2The failing query is a select, update or delete with whereIn('id', $ids).A whereIn list with thousands of idsFix
    3. 3After you chunk the insert, a re-run stops on duplicate keys.Rows may already exist, so you re-run a failed importFix

What does the error look like?

  • SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders

Cause 1 of 3

One insert() call carries every row

1Check

Laravel accepts an array of arrays and builds a single statement from it. MySQL's prepare step sends the number of parameters in a 2-byte field, so a statement with tens of thousands of values is refused.

logger()->info('rows: '.count($rows).', columns: '.count($rows[0]));

2Fix

Break the array into chunks and insert each one. 500 rows per chunk is a safe start for a table with a few dozen columns.

use Illuminate\Support\Facades\DB;

collect($rows)->chunk(500)->each(function ($chunk) {
    DB::table('products')->insert($chunk->toArray());
});

3Verify

Run the import again and count the rows.

php artisan tinker --execute="echo DB::table('products')->count();"

Confirmed by laravel.com

Did this fix it?

Cause 2 of 3

A whereIn list with thousands of ids

1Check

Each value in a whereIn list becomes its own placeholder.

logger()->info('ids: '.count($ids));

2Fix

Process the ids in slices, or fetch the rows with a subquery or join instead of a pasted list.

collect($ids)->chunk(1000)->each(function ($slice) {
    DB::table('orders')->whereIn('id', $slice->all())->update(['status' => 'done']);
});

3Verify

The update finishes with no HY000 error.

php artisan tinker --execute="echo DB::table('orders')->where('status','done')->count();"

Confirmed by laravel.com

Did this fix it?

Cause 3 of 3

Rows may already exist, so you re-run a failed import

1Check

A first run that failed halfway has already written some chunks.

php artisan tinker --execute="echo DB::table('products')->count();"

2Fix

Use upsert(), which inserts new rows and updates existing ones in one statement per chunk. Its second argument names the columns that identify a row.

collect($rows)->chunk(500)->each(function ($chunk) {
    DB::table('products')->upsert($chunk->toArray(), ['sku'], ['price', 'name']);
});

3Verify

Run the import twice; the row count does not grow the second time.

php artisan tinker --execute="echo DB::table('products')->count();"

Confirmed by laravel.com

Did this fix it?

How do I stop it coming back?

  • Never pass a whole file to insert(); chunk first.
  • Wrap the chunk loop in DB::transaction() if a half-finished import is not acceptable.
  • Use a queued job per chunk for imports that run longer than a web request.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.