Disable InnoDB ON DUPLICATE KEY auto increment

Question

I am having problems with the primary key ID that is set to auto increment . He continues to increase ON DUPLICATE KEY .

Example:

 ID | field1 | field2 1 | user | value 5 | secondUser | value 86 | thirdUser | value 

From the above description, you will notice that I have 3 inputs in this table, but due to the automatic increase with each update, the identifier has 86 for the third input.

Anyway, to avoid this?

This is what my mySQL query looks like:

 INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2) ON DUPLICATE KEY UPDATE field1 = :value1, field2 = :value2 

And this is what my table looks like;

 CREATE TABLE IF NOT EXISTS `table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` varchar(200) NOT NULL, `field2` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `field1` (`field1`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Answer 1

You can set the innodb_autoinc_lock_mode option to "0" for the "traditional" auto-increment lock mode, which ensures that all <t22 statements> will assign sequential values ​​for AUTO_INCREMENT columns.

However, you should not depend on the fact that auto-increment identifiers are consistent in your application. Their goal is to provide unique identifiers.

Answer 2

I am having problems with the primary key ID that is set to auto increment . He continues to increase ON DUPLICATE KEY

One of us must misunderstand the problem, or you distort it. ON DUPLICATE KEY UPDATE never creates a new row, so it cannot be incremented. From the docs :

If you specify ON DUPLICATE KEY UPDATE and a row is inserted that will duplicate the value in the UNIQUE or PRIMARY KEY index, MySQL performs the UPDATE of the old row.

Now this is probably the case when auto-increment occurs when you insert and no duplicate key is found. If I assume that this is what happens, my question will be: why is this a problem?

If you absolutely want to control the value of your primary key, change the table structure to remove the auto-increment flag, but keep this required, non-zero field. This will force you to provide the keys yourself, but I would argue that this will be a bigger headache for you.

I'm really curious: why do you need to connect all the holes in the ID values?

Answer 3

This behavior is easily seen below with the default value for innodb_autoinc_lock_mode = 1 ("sequential" lock mode). Please also refer to the excellent manual page called AUTO_INCREMENT Handling at InnoDB . Changing this value will be lower than concurrency and performance with a setting of = 0 for the "Tranditional" locking mode, since it uses AUTO-INC locking at the table level.

However, below is the default value = 1.

I am going to show you four examples of how easy it is to create spaces.

Example 1:

 create table x ( id int auto_increment primary key, someOtherUniqueKey varchar(50) not null, touched int not null, unique key(someOtherUniqueKey) ); insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1; select * from x; +----+--------------------+---------+ | id | someOtherUniqueKey | touched | +----+--------------------+---------+ | 1 | dog | 2 | | 3 | cat | 1 | +----+--------------------+---------+ 

The space (id = 2 is omitted) is due to one of several operations and quirks and nervous INNODB engine. In high-performance mode, by default, concurrency, it distributes space intervals for various requests sent to it. Someone has good reasons to change this setting because it affects performance. Some later versions of MySQL deliver to you, and you disconnected due to hyperfocusing on gaps in the printout sheets (and bosses who say "Why do we have spaces").

In case of insertion of duplicate keys ( IODKU ) in the update, it takes 1 new row and allocates a slot for it. Remember, concurrency, and your peers perform the same operations, possibly hundreds at a time. When IODKU turns into Update , well, it uses the use of this abandoned and never inserted row with id = 2 for your connection and any other.

Example 2:

The same thing happens during Insert ... Select From , as shown in this answer . In it, I intentionally use MyISAM because of the counting message, min, max, otherwise, a space in the range of spaces will highlight and not fill everything. And the numbers would look strange, since this answer was about actual numbers. Thus, the older engine ( MyISAM ) worked perfectly for tough non-gaps. Please note that in this answer I tried to do something quickly and safely, and after that the table could be converted to INNODB using ALTER TABLE after the fact. If I made this example in INNODB for starters, there would be many spaces (in default mode). The reason Insert ... Select From would create gaps in this answer if I were using INNODB was due to the uncertainty of the count, the mechanism that the engine selects for a safe (indefinite) range allocation. The INNODB mechanism knows the operation very well, knows that it must create a secure pool from the AUTO_INCREMENT id, has concurrency (other users think), and spaces flourish. It is a fact. Try Example 2 with the INNODB engine and see what you come up with for min, max, and count. Max will not be equal to the score.

Examples 3 and 4:

There are various situations that cause INNODB errors documented on the Percona website when they stumble upon more and document them. For example, this happens during failed insertions due to foreign key restrictions observed in this 1452 Error image . Or a primary key error in this 1062 image error .

Remember that INNODB spaces exist as a side effect of system performance and a secure engine. Does this really want to disable something (performance, higher user satisfaction, higher concurrency, no locks in the table), for the sake of tighter identifier ranges? Ranges in which there are holes when removed. I would suggest not for my implementations, but the default value with Performance is just fine.