MariaDB is AutoIncrementing on update I have a weird issue and i can't find the fault. I have a MariaDB table with 4 rows

[id] [clientid] [lastcheck] [Version]


The id field is set to AutoIncrement. Everything is working fine with my script for adding and updating values

INSERT INTO $dbtable_update
    (clientid, lastcheck, currentversion)
VALUES
    ('$clientid', '$lastcheck', '$currentversion')
ON DUPLICATE KEY UPDATE
    lastcheck = '$lastcheck',
    currentversion = '$currentversion'


But as soon as I update values on an existing entry, the AutoIncement is counting the id row. Can someone help me out, what I am doing wrong?

Note (from Comments):  There is UNIQUE(clientid).
The following assumes that you feel the gaps in the id sequence is a problem. This usually isn't a problem, unless these gaps become enormous and there is a risk that at some point you will exceed the maximum id value. 

I'm further going to assume the table is defined similar to this:

CREATE TABLE clients(
  id int unsigned AUTO_INCREMENT, 
  clientid varchar(30) UNIQUE NOT NULL, 
  lastcheck datetime, 
  version varchar(10), 
  PRIMARY KEY (id)
);


If you execute an INSERT ... ON DUPLICATE KEY UPDATE statement against this table, then the id column will increase every time since it attempts to INSERT first, then if that fails it does the UPDATE.  

To avoid this you can do a SELECT against the table first to see if the clientid exists, then do an INSERT or an UPDATE depending on the result. 

However, if you instead had a table like this:

CREATE TABLE clients (
  clientid varchar(30) NOT NULL, 
  lastcheck datetime, 
  version varchar(10),
  createtime datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (clientid)
);


... then you could use the same INSERT ... ON DUPLICATE KEY statement as before. There would be no id column to worry about, the clientid would be unique (since it's the primary key) and the sequence could be seen from the new createtime column (which might be useful on its own).

You can alter your current table to get the new one with a statement similar to this:

ALTER TABLE clients 
  DROP PRIMARY KEY, 
  DROP id, 
  ADD PRIMARY KEY (clientid), 
  ADD COLUMN createtime datetime NOT NULL DEFAULT current_timestamp();   


(Note however that all the createtimes for existing records will then be set to the current timestamp when you run the ALTER.)