HY093 Error when executing prepared statement in PDO

Question

I'm getting data from multiple API requests and storing each in a separate MySQL table. For each request I have an associated table, with field names matching the JSON API response. Since I'm not using all of fields from the API, I'm finding the fields in the MySQL table and using that to create the prepared statement with PDO, then feeding the results array into that for execution. Here's the function that accepts the statement and the results array:

function insert_array($sql,$args)
{
    $this->connect();
    $q = $this->con->prepare($sql);
    foreach($args as $record) {
        $q ->execute($record);
        echo "<pre>";var_dump($record);echo "</pre>";
        $arr = $q->errorInfo();
        print_r($arr);
    }
    $this->disconnect();
    return $q;
}

The last three lines in the foreach loop are just for debugging.

This worked fine for my first request, but no records are inserted, and I receive HY093, for others.

For the one that works, the prepared statement ($sql) comes out as

INSERT INTO fs_dynamicagents (agent_id, firstname, lastname) VALUES (:agent_id, :firstname, :lastname) ON DUPLICATE KEY UPDATE firstname=:firstname, lastname=:lastname

I'm finding unique fields first, so that's why agent_id isn't in the update statement. This inserts successfully, even though I'm not using all the fields. Here's the output of the var_dump and errorInfo:

array(4) {
  ["agent_id"]=>
  string(3) "002"
  ["firstname"]=>
  string(9) "Bill"
  ["lastname"]=>
  string(5) "Murray"
  ["password"]=>
  string(4) "1212"
}
Array ( [0] => 00000 [1] => [2] => )

Now here's an example of one that doesn't work:

INSERT INTO fs_queue (name, record) VALUES (:name, :record) ON DUPLICATE KEY UPDATE record=:record

And part of the first API record:

array(79) {
  ["name"]=>
  string(7) "Choice1"
  ["fc_name"]=>
  string(7) "Choice1"
  ["friendlyname"]=>
  string(7) "Choice1"
  ["record"]=>
  string(1) "1"
  ["agent_announcement_file"]=>
  string(0) ""
  ["play_agent_announcement_file"]=>
  string(1) "0"
  ["incoming_call_script"]=>
  string(0) ""
  ["caller_agent_match"]=>
  string(1) "0"
  ["survey_id"]=>
  NULL
}

Array ( [0] => HY093 [1] => [2] => )

You can see I haven't included all 79 of the fields, but I've tried to include at least the fields with "name" in the label, and an empty string and a null value. Nothing but "name" and "record" should be bound, so I don't see those as a problem.

Every instance I've found online for this error code was due to a type (or case sensitivity). I've tried defining "record" as an int and a varchar.

Well, I had hoped that the process of typing this out would expose the problem to me, but no such luck. If a night's sleep doesn't help, I'd love to hear thoughts.

Edit: Something else I have tried is removing the ON DUPLICATE UPDATE section (and emptied the table so there will not be any duplicates) so that each parameter is only bound once. It sounds like that was a bug a few years ago that has been fixes, but even without that I receive the same error.

Edit2: Hmm, even stranger, removing the ON DUPLICATE UPDATE causes some of my previously working statements to fail with the same error. Not all of them, and of course those that don't fail for that reason will fail if it runs into a duplicate.

Edit3: Something else I have tried is removing the binding-by-key for the update statement, and changing this to

INSERT INTO fs_queue (name, record) VALUES (:name, :record) ON DUPLICATE KEY UPDATE record= VALUES(record)

I didn't think that would fix it, because it succeeds the first way on other tables, and this does in fact still fail.

Edit4: I was able to make one of these work by adding fields to the MySQL table so that all the columns from the input array were being used. However, I don't think that's what really solved the problem, because I have others succeeding without all columns being used, even in the middle of the array.

Answer 1

Ok, I figured it out. First, I was not setting ATTR_EMULATE_PREPARES at all, which means it would default to the database preparation engine unless the PDO engine was required. Since MySQL cannot re-use placeholders, it was using the PDO engine. Setting that to false would force the MySQL engine, and all requests would fail.

So the PDO engine can re-use placeholders, but however that happens it's not very good at finding the values. Even trying to find 2 out of 3 columns it would sometimes fail. So rather than let PDO sort it out, I'm throwing out everything I don't need before I send it to be inserted.

I'm using a function to delete columns that I found here.

<?php

function delete_col(&$array, $key) {
    return array_walk($array, function (&$v) use ($key) {
        unset($v[$key]);
    });
}

$table_fields = array("id","fruit");

$insert_data = array(
    array( 
        "id" => "1",
        "fruit" => "Apple",
        "color" => "Red"
    ),array( 
        "id" => "2",
        "fruit" => "Apple",
        "color" => "Green"
    ),array( 
        "id" => "3",
        "fruit" => "Pear",
        "color" => "Green"
    )
);

foreach($insert_data[0] as $key=>$value) {
    if(!in_array($key, $table_fields)) {
        delete_col($insert_data, $key);
    }
}
echo "<pre>";
print_r($insert_data);
echo "</pre>";

?>

This assumes that the first record will have an entry for every column. I do have some where that's not true, but so far it hasn't caused problems, but I will probably end up rewriting this to go through each row.