1

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).

1
  • 3
    Please add CREATE TABLE statements and INSERT statements with sample data. Apr 1, 2018 at 7:57

1 Answer 1

3

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.)

7
  • Yes the idea is that every clientid only exist once. The id column is used for tracking the entries squence. The clientid column is unique, so the "ON DUPLICATE KEY UPDATE" is working that way. But when updating an existing entry multiple times, then a new entry will get a much higher id. It is not a real problem, but as i said i want to use this column for tracking entries later in the development.
    – andy
    Apr 1, 2018 at 12:24
  • @andy OK, I've re-written the answer based on what I assume your table looks like (based on your above comment), in particular what I think is your primary key and/or unique keys.
    – dbdemon
    Apr 1, 2018 at 14:42
  • 1
    So you got gaps. As you said, that's not a real problem. There are probably more than 10 reasons why gaps may appear in an auto-incremented column. Apr 1, 2018 at 20:01
  • Thanks @ypercubeᵀᴹ, you're right, I got carried away! I've updated the answer with that preamble.
    – dbdemon
    Apr 1, 2018 at 20:32
  • @dbdemon you already got my upvote ;) Apr 1, 2018 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.