Bug with $db->setQueryOption('IGNORE')->insertMulti

[LunarDevelopment]

Hello, I'm trying to use IGNORE with insertMulti but getting duplicate key errors, code and error below, with the only notable oddity being the table being inserted into has a compound primary key - that shouldn't affect IGNORE though :

        $records = $db->setQueryOption('IGNORE')->insertMulti('table', $array_of_arrays);

        if(!$records) {
            echo 'insert failed: ' . $db->getLastError();
        } else {
            echo 'new records inserted with following id\'s: ' . implode(', ', $records);
        }

insert failed: Duplicate entry 'recordname-2018-07-05' for key 'PRIMARY'

Results of $db->getLastQuery()

INSERT  INTO table... 

composer.json :

    "joshcam/mysqli-database-class": "dev-master",

Also tried on v2.9.2 with same error messages.

[michabbb]

because this looks like a regular mysql error, i would suggest, you show us your $array_of_arrays 😏

[LunarDevelopment]

$array_of_arrays is arbitrary - the issue is because the getLastQuery doesn't have "Ignore" in it - if it did then the script wouldn't bomb.

For illustration I've just tried with the following and had the same error:

$array_of_arrays = array(
array('name' =>'recordname', 'date' => '2018-07-05'),
array('name' =>'recordname', 'date' => '2018-07-05')
); 

[VeNoMouS]

I dunno... the code logic seems sound..

It goes insertMulti -> insert -> _buildInsert which then puts the option into the query

$this->_query = $operation . " " . implode(' ', $this->_queryOptions) . " INTO " . self::$prefix . $tableName;

And _queryOptions is populated in the function setQueryOption

Do you get the same issue if you

$db->setQueryOption('IGNORE')->insert('table', ['name' =>'recordname', 'date' => '2018-07-05']);

[michabbb]

maybe a trace shows more infos what´s going on here 🤔

[LunarDevelopment]

@michabbb @VeNoMouS

Array
(
    [0] => Array
        (
            [0] => INSERT IGNORE INTO db.table (`name`, `date`)  VALUES ('test',  '2018-07-18')
            [1] => 0.61394000053406
            [2] => MysqliDb->insertMulti() >>  file "/dir/bulkInsertTest.php" line #102 
        )

    [1] => Array
        (
            [0] => INSERT  INTO db.table (`name`, `date`)  VALUES ('test',  '2018-07-18')
            [1] => 0.54291319847107
            [2] => MysqliDb->insertMulti() >>  file "/dir/bulkInsertTest.php" line #102 
        )

)

[michabbb]

@LunarDevelopment i am new to this project, so sorry if i can´t offer much help. i study some issues here to evaluate if this class works for me. after seeing your case i got interested in this so i ask myself, what would i do ? 😏

i saw this class offers a trace, which seems to be nice feature.
so the IGNORE is only used in the first statment , do i see this correctly ?

[LunarDevelopment]

@michabbb Ah sorry, because you were being so helpful I assumed you were a collaborator or something.

Yeah it looks like the library is forgetting that IGNORE is set as a query option after first query..

I'll play around and see if I can fix, but any external help is much appreciated - it seems like a fairly large oversight in the library considering there's a closed thread about this issue in 2017.

[VeNoMouS]

This is due to _buildInsert calling the function reset which redefines _queryOptions $this->_queryOptions = array();

What needs to happen, is in multiInsert in the foreach, before the insert function is called, you need to reissue the options..

public function insertMulti($tableName, array $multiInsertData, array $dataKeys = null)
    {
        // only auto-commit our inserts, if no transaction is currently running
        $autoCommit = (isset($this->_transaction_in_progress) ? !$this->_transaction_in_progress : true);
        $ids = array();

        $options = [
            '_queryOptions' => $this->_queryOptions,
            '_nestJoin' => $this->_nestJoin,
            '_forUpdate' => $this->_forUpdate,
            '_lockInShareMode' => $this->_lockInShareMode 
        ];
        
        if($autoCommit) {
            $this->startTransaction();
        }

        foreach ($multiInsertData as $insertData) {
            if($dataKeys !== null) {
                // apply column-names if given, else assume they're already given in the data
                $insertData = array_combine($dataKeys, $insertData);
            }
       
            foreach ($options as $k => $v) {
                $this->${k} = $v;
            }

            $id = $this->insert($tableName, $insertData);
            if(!$id) {
                if($autoCommit) {
                    $this->rollback();
                }
                return false;
            }
            $ids[] = $id;
        }
        if($autoCommit) {
            $this->commit();
        }
        return $ids;
    }

[avbdr]

good catch guys. should be pretty trivial to fix

[VeNoMouS]

actually my above wont fully work, as @avbdr has some custom class variables in setQueryOption on certain ifs that arent populated over to _queryOptions

 if ($option == 'MYSQLI_NESTJOIN') {
                $this->_nestJoin = true;
            } elseif ($option == 'FOR UPDATE') {
                $this->_forUpdate = true;
            } elseif ($option == 'LOCK IN SHARE MODE') {
                $this->_lockInShareMode = true;
            } else {
                $this->_queryOptions[] = $option;
            }

[avbdr]

yep. we would need to store those variables before insert cycle, and then redefine them before each insert()

[VeNoMouS]

@avbdr i've updated my above fix... that should be enough?

[avbdr]

            $this->_nestJoin
            $this->_forUpdate
            $this->_lockInShareMode
            $this->_queryOptions[]

you would need to store all of those

[VeNoMouS]

@avbdr I have, refer the above fix :) in my post earlier