Too many placeholders

the error message "Prepared statement contains too many Placeholders" is clear, the values you provide in the query is less than the placeholders or does not match....

I am using MySql 5.7

I searched "max mysql placeholders" that I can send per query.

I found in stackeroverflow that the limit is 65,536. https://stackoverflow.com/a/11131824

I could not find in the documentation (yet) if this is a technical limitation, maybe it is a limitation imposed by a MySql variable?

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html

Thx SIANGBOON.

Yep ...

The file in question has a total of 18 columns. Considering the placeholder limit: 65536/18 = 3640,888 ... I can insert 3640 lines.

So I tried ...

// $header is a collection of column names;
// $data is a collection of lines from the file;
$cols = $header->count();
    
$limit = 65000; // Mysql placeholder limit ...
        
$data
    ->chunk( floor($limit / $cols) )
    ->each(function($calls) {
        \App\ Models\Call::insert($calls);
    });

it works. but ... now I have 3,296 querys. each with 3611 rows. 😅️

any tips to increase this mysql placeholder limit? if possible...

Chunking a few thousand at a time is probably best. If you do more, you just spend more time juggling large amounts of data in memory, and probably does not speed it up any.

probably turn indexing off and build indexes later.

You are not having to check for duplicates are you?

You have a table with more than 65,536 fields?

Or

Does your number of question marks (placeholders) not match the number of parameters you have.

The insertion is done on top of a temporary table (without indexing). After validation of the coordinator, the temporary table is inserted in the calls table and droped at the end of the process.

This database receives information from various telecommunications service providers.

The exchange of information is sometimes by api, direct query to third party db or files: csv, tsv, xls, json or xml.

<results feeder_id = "XXXX">
    <contact status = "success" queue = "XXXXXXXX" id = "3646">
        <number 
               answered_at = "28/05/2019 08:56:56"
               destination = "XXXXXXXXXX"
               duration = "127"
               callfilename = "1559044588.67525160.WAV"
               hangup_at = "28/05/2019 08:59:02"
               id = " 2889 "
               dialed_at =" 28/05/2019 08:56:28 "> 
               16
        </number>
  </contact>
  ... +10 million contacts or even more per file
</results>

This is one of the file types we receive. Asterisk Based (Asterisk.org) After importing we perform aggregate functions for management reports and make them available as a centralized query.

The biggest problem is the volume of data ..

JLRDW,

The number of question marks is greater than the 65536 (2¹⁶) limit. Then MySql throws an error 1390.

Just to get a sense: 1 of the files has 18 columns and 10 million more rows.

1 file x 18 columns x 10⁷ rows = 180,000,000 question marks.

The work around was to divide the data and insert according to the limit.

6500 placeholders / 18 columns = 3611 rows per query

as I mentioned earlier.

Updating.

I found this in the php documentation.

PDO :: ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers from not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE and emulated preparations are supported by the driver), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool.

https://www.php.net/manual/en/pdo.setattribute.php

So..

// it's the same instance.
DB::connection()->getPdo() === (new App\Models\Call)->getConnection()->getPdo(); // true

// set TRUE;
DB::connection()->getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

App\Models\Call::insert($data);

// set FALSE
DB::connection()->getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

It works!

now benchmarking.