I occasionally keep getting an issue with CakePHP 3.1.3 that show this error SQLSTATE[HY000]: General error: 1 too many SQL variables. It is quite hard to re-produce this at my end as it comes and goes. I've attached an image of this error.
CakePHP 3.0 - SQLSTATE[HY000]: General error: 1 too many SQL variables
I've observed that usually I get the error with the code I've provided below. The code below will get multiple id based on multiple checkboxes selection. After which, it deletes the student based on the selected id. The question I am asking is whether the error I am getting is caused by my code or is it due to the Debugkit that I've seen someone posted a similar question in this link(https://github.com/cakephp/cakephp/issues/7373). If it caused by the Debugkit, how can I swiftly resolve it?
$deleteList = $this->request->data['selected'];
$classids = $studentclassTable->find()
->select(['classroom_id'=>'Studentclassrooms.classroom_id'])
->where(['student_id IN' => $deleteList])
->hydrate(false)
->toArray();
foreach($deleteList as $id) {
$student = $this->Students->findById($id)->first();
if($student){
array_push($usernames, $student['firstname'].' '.$student['lastname']);
$this->Students->delete($student);
}
}
If you look closely at the stacktrace, you should see that this stems from Debug Kit, it's the garbage collection mechanism that cleans the request history.
Looking at the query, it seems that your Debug Kit version is not up to date, the latest version will use a NOT IN
condition (since 3.1.7), where by default only a maximum of 20 rows is being kept, and thus the same amout of placeholders will be set in the query.
See https://github.com/cakephp/debug_kit/pull/381
The maximum number of rows to keep can be configured via the global DebugKit.requestCount
config option.
Long story short, update your Debug Kit dependency.