XF 2.2 insertBulk

[Robert9]

When i use insertBulk, it changes my indexes.

no new data!
$db->insertBulk('xf_lala_team', $insert, false, 'team_id = VALUES(team_id)');
=> changes my indexes

the same data than inserted before
$db->insertBulk('xf_lala_team', $insert, true, 'team_id = VALUES(team_id)');
=> updates my data and changes my indexes.

new data
$db->insertBulk('xf_lala_team', $insert, true, 'team_id = VALUES(team_id)');
=> inserts new data and doesn't change my indexes.


Example
first run, i insert 18 new columns with team_id from 1 to 18.
In the second run $insert is empty, then my columns will be change to 19 to 38.

    public function insertBulk($table, array $rows, $replaceInto = false, $onDupe = false, $modifier = '')
    {
        if (!$rows)
        {
            throw new \InvalidArgumentException('Rows must be provided to bulk insert');
        }

        $firstRow = reset($rows);
        $cols = array_keys($firstRow);

        $rowSql = [];
        foreach ($rows AS $row)
        {
            $values = [];
            foreach ($cols AS $col)
            {
                if (!array_key_exists($col, $row))
                {
                    throw new \InvalidArgumentException("Row missing column $col in bulk insert");
                }

                $values[] = $this->quote($row[$col]);
            }

            $rowSql[] = '(' . implode(',', $values) . ')';
        }

        foreach ($cols AS &$col)
        {
            $col = "`$col`";
        }

        $keyword = ($replaceInto ? 'REPLACE' : 'INSERT');
        if ($replaceInto)
        {
            $onDupe = false;
        }

        try
        {
            $res = $this->query(
                "$keyword $modifier INTO `$table` (" . implode(', ', $cols) . ') VALUES '
                . implode(",\n", $rowSql)
                . ($onDupe ? " ON DUPLICATE KEY UPDATE $onDupe" : '')
            );
            return $res->rowsAffected();
        }
        catch (Exception $e)
        {
            return $this->processDbWriteException($table, $e);
        }
    }

 

[Robert9]

first insertBuld with teams of the english premier league



after a second insertBuld with the same teams of the english premier league

 

[Robert9]

Next test with "false"
$db->insertBulk('xf_lala_team', $insert, false, 'team_id = VALUES(team_id)');

1. import premierleague => id 1 to ...
2. import premierleague => nothing happens
3. import premierleague => nothing happens
4. import second league => inserts new teams, but with the id 61



step 2 and 3 have increased the mysql_count !



I have inserted 20; then two times nothing, then again 20.
Whats wrong with this function, please? Which reason it should have to increase the auto_increment value of a table?

 

[Lukas W.]

That's documented MySQL behavior. Duplicate key update will increase the auto increment index even when a row is updated back into the original. Since you're asking it to update the auto increment column ('team_id = VALUES(team_id)') it stores back the new key overwriting the old.

If you need to update existing rows without causing gaps in the auto increment key, use update queries instead, although gaps in the auto increment key are generally normal and will occur over the lifespan of a table through various occasions automatically.

 

[Robert9]

Thank you. What can i do to get rid of this behaviour?
I was not shure about this ('team_id = VALUES(team_id)', so i have changed it to another unique field, but the result is the same. Read it (auto_inc and unique fields), ok.

So i have to check every single row, if existing, before doing an insert?

 

[Robert9]

Have solved it now, because i have a second table saving league and teams.
But i am not shure, if one more query to check the existence would be a good/needed thing before having an insert.
Is there any mysql to say:

insert a, b (with a=inc, b=unique), if not existing and dont increase the counter!

 

[Lukas W.]

There is not. Inserting will increase the auto increment counter. You can extract the list of ids you're about to insert and run a single query to get all that have a matching record ready, then update them individually and insert the rest.