Styjun

I have table structure like this

when I insert row to the table I'm using this query:

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

when I first insert data without triggering the ON DUPLICATE KEY the id increments fine:

but when the ON DUPLICATE KEY triggers and i INSERT A NEW ROW the id looks odd to me:

How can I keep the auto increment, increment properly even when it triggers ON DUPLICATE KEY?

This behavior is documented:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that
would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL
performs an UPDATE of the old row. For example, if column a is
declared as UNIQUE and contains the value 1, the following two
statements have similar effect:

 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

 UPDATE table SET c=c+1 WHERE a=1;

(The effects are not identical for
an InnoDB table where a is an auto-increment column. With an
auto-increment column, an INSERT statement increases the
auto-increment value but UPDATE does not.)

Here is a simple explanation. MySQL attempts to do the insert first. This is when the id gets auto incremented. Once increment, it stays. Then the duplicate is detected and the update happens. But the value gets missed.

You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.

I had the same frustration and I have a solution.

Let me first talk about "overheads". When I first wrote my DB update code, it did so many separate queries that it took 5 hours. Once I put on "ON DUPLICATE KEY UPDATE" I got it down to about 50 seconds. Amazing! Anyway the way I solved it means I have to do 2 queries, so instead of 1 minute I'm extending it to 2 minutes which I think is a fair cost considering.

First I did a standard sql query with data that had updates and inserts, but I included "IGNORE" so this just bypasses the updates and only inserts the new stuff.
"INSERT IGNORE INTO mytablename(stuff,stuff2) VALUES " -without the on duplicate key update. This means all the new records get put in. These new records wont break the auto_increment yet.

Next I did the "ON DUPLICATE KEY UPDATE" version of that sql statement, now this breaks the auto_increment value, but all the updates and inserts we've put in are all perfect auto_incrememnt values. It's just the next new record we add will be wrong...

So lastly you just patch up the mess by this sql:
"ALTER TABLE mytablename AUTO_INCREMENT = " . ($TableCount + 1);

remember to actually set $TableCount to the table count, then we add 1 and that's ready for when the next record gets written.

This is cheap and dirty but I like it. Make sure you don't write to the database at the same time using another function etc, because it could give errors. I think there is a way to lock the table? But I don't need that in my case.

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE material_item = data, ... hidden = data

Yes remove the ID=ID as it will automaticly add where PRIMARY KEY = PRIMARY KEY...