MariaDB ON DUPLICATE KEY UPDATE autoincrement

Question

Recently i saw in my database (InnoDB), that my "ID" column which is set to autoincrement, does count a bit weird. When i update a record with

INSERT INTO users (user, group) VALUES ('username', 'group') ON DUPLICATE KEY UPDATE username = 'username', group = 'group'

autoincrement counts the ID, so now my table is looking as follows:

----------------
|id|user |group|
----------------
|1 |test |3    |
|2 |test2|5    |
|5 |foo  |2    |
|6 |bar  |4    |
|10|user |2    |
----------------

I read about this "issue" but didn't really found and answer on how to make the autoincrement field not count, if the record gets updated only.

Whether or not the auto increment number changes is not really a concern. The contract only says it has to be unique and ever increasing, not that it will always be continuous. — Tim Biegeleisen, Dec 21 '18 at 08:21
So this is not a problem, when the id is getting very high (e.g. one Billion or so)? And there is no way to get arount this? — andy, Dec 21 '18 at 11:14
@TimBiegeleisen - I will dispute the "ever increasing", especially when viewed from a Slave. — Rick James, Dec 21 '18 at 20:47
@RickJames I spend 4-6 hours on this site every day, so I'm inclined to call myself the slave. Yes, you're probably right, and it may not always increase. — Tim Biegeleisen, Dec 22 '18 at 01:47
(I added tag [mysql] because this discussion applies identically to both MariaDB and MySQL.) — Rick James, Dec 22 '18 at 16:02
`id` has the column type you've assigned it (int, mediumint, bigint...) and integer numbers either fit or not fit. Values within the acceptable range do not become "very high" until they eventually overflow (thus no longer fit). — Álvaro González, Dec 22 '18 at 16:40
Yes that's what i mean, isn't it some sort of waste that there are perhaps many "unused" ids? — andy, Dec 22 '18 at 18:23
An unsigned `int` overflows after 4,294,967,295. An unsigned `bigint`, after 2^64-1 (18,446,744,073,709,551,615). You'll have bigger problems before you run out of numbers. — Álvaro González, Dec 22 '18 at 18:55
Ok i see, but is this the normal approach for handling auto increment ids? Or does this with another table type differ? I only want the optimal solution for that :) — andy, Dec 22 '18 at 19:41

score 1 · Answer 1 · answered Dec 21 '18 at 20:51

1

INSERT IGNORE, REPLACE, IODKU (that you are using), and some other commands will first allocate new id(s), then either use them or 'burn' them. Live with it.

In Multi-master and Clustering environments, auto_increment_increment is set to more than 1 so that the nodes can easily avoid each other. This can lead to lots of gaps.

Since ids are not visible outside the transaction until the COMMIT, other queries can see id=7 before id=6 is visible. The wreaks havoc with some simplistic designs for queuing.

answered Dec 21 '18 at 20:51

Rick James

135,179
13
127
222

Thanks for your answer. What is About sending a ALTER TABLE my_table AUTO_INCREMENT = 1 after every IODKU query? That does work, but does it have any drawbacks? – andy Dec 22 '18 at 08:38
@andy - You can't start over without throwing out all the ids. That is, you can't set it to 1 or anything else less than MAX(id)+1. If you must avoid burning ids, you must test before inserting. – Rick James Dec 22 '18 at 16:01
I understand, but why does the approach work then? What do you exaclty mean by test before inserting? – andy Dec 22 '18 at 18:22
@andy - If record does not exist, then insert. – Rick James Dec 22 '18 at 22:44
When using MyISAM format instead of INNODB, it does not create these gaps, so does it have any drawbacks when using that DB format? – andy Dec 23 '18 at 07:54
@andy: I never use MyISAM. See my answer to [MyISAM vs. InnoDB](https://stackoverflow.com/questions/20148/myisam-versus-innodb/17706717#17706717). – Bill Karwin Dec 24 '18 at 19:44
@Bill Karwin - Thank you for that post, really good explanation. So i will stick with InnoDB. But i found the mysql setting "innodb_autoinc_lock_mode=0", which does help to avoid unused ids on my table. I read the documentation, which says that "innodb_autoinc_lock_mode=0" does avoid the auto_increment gaps. So is it save to use? – andy Dec 25 '18 at 08:17
1

I think you need to get over worrying about gaps. If you rollback transactions or delete rows, you'll just get gaps for those reasons. Auto-inc primary keys are not meant to be consecutive, just unique. – Bill Karwin Dec 25 '18 at 08:33
So then you suggest that i just leave the default innodb_autoinc_lock_mode=1 and use InnoDB as my table format? The point is, in one table i have not that much new inserts, but a huge amount of updates, everything is inserted / updated with IODKU. – andy Dec 25 '18 at 08:44
@andy - If you know that the row exists, use `UPDATE`, not IODKU. – Rick James Dec 26 '18 at 02:48
@Rick James - I don't know, if a row does exist, but it mostly exists. When using UPDATE does it also count the auto_inc? Or is it simply faster than IODKU? – andy Dec 26 '18 at 07:07
Ok i tested that and when using UPDATE only, the auto_inc does not count. So is it save to use the following logic? 1. Send UPDATE query 2. If affected_rows > 0 - updated successfully 3. Else Send SELECT query - if num_rows === 0 4. Send INSERT query - if affected_rows > 0 - inserted successfully 5. Else - Nothing changed – andy Dec 26 '18 at 16:21
1

@andy - Less complex: `SELECT`, then either `UPDATE` or `INSERT`. – Rick James Dec 26 '18 at 17:11
#Rick James - Yes that would be easier, but as i said, most of the querys will be updates, that's why i do an update query first (i know that every update does change the timestamp inside my row). If there is no row to update (no clientid that matches), i do a select to be sure, that there is really no clientid that Matches. And then insert. The problem is, that when (for whatever reason) the update values are equal, i have to re-check. – andy Dec 26 '18 at 17:23

score 1 · Answer 2 · answered Dec 25 '18 at 08:43

I take from your comments that you may be concerned about running out of integers if you don't fill every gap in an auto-increment primary key.

I encourage you to do the math. An INT is 4 bytes, and holds values up to 2^31-1 (or 2^32-1 for UNSIGNED INT). If you were to generate 1 auto-inc id every second (whether it results in an INSERT or else it's discarded), that would last 136 years.

If you're still concerned, then use an UNSIGNED BIGINT which has 2^64-1 distinct values. That data type would let you consume 1000 id's per second for 584,542,046 years.

MariaDB ON DUPLICATE KEY UPDATE autoincrement

2 Answers2